How to apply a search filter in a SQL query in OS 11

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

Afonso Aguas wrote:

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.