Good day,
I have created an advanced query for very complicated code. I am getting the following error when trying to filter for my search variable at the end.
Here is the code for the where clause:
WHERE ( e.[control] LIKE '%' + @Search_Compliance_Dashboard + '%'OR e.[risk] LIKE '%' + @Search_Compliance_Dashboard + '%'OR e.[Risk_Description] LIKE '%' + @Search_Compliance_Dashboard + '%'OR e.[process] LIKE '%' + @Search_Compliance_Dashboard + '%'OR e.[DesiredAssuranceLevel] LIKE '%' + @Search_Compliance_Dashboard + '%'
OR e.[min_rqd_ass_obtained] LIKE '%' + @Search_Compliance_Dashboard + '%'OR e.[subprocess] LIKE '%' + @Search_Compliance_Dashboard + '%')
I am getting the following error:
ORA-01722: invalid number
I assume it is trying to 'add' the % to the search parameter as though they are numbers. How else would I code this??
You are writing sql for Oracle, Oracle's concatenation operator is || not +. Try it and see if it works.
Afonso Aguas wrote:
When I use || in place of + I get another error: ORA-00936: missing expression
Please post your full query
My query is too long to post, but without the search filter it works perfectly
Ok, I confess I don't have the time to really analyze that query, but when you changed the operator, the error changed, so you know that was not ok, don't revert to +, after that you should analyze if there's a missing concatenation anywhere or missing parenthesis.