Advanced Query Syntax

  
Whem execute the above query i get the error:
ORA-00936: missing expression
The problem are in where clause, if remove it, works.


SELECT {Licao}.[Id], {Licao}.[titulo] FROM {Licao}

WHERE ((@SearchKeyword = '') or ({Licao}.[titulo] Like @SearchKeyword))

GROUP BY {Licao}.[Id], {Licao}.[titulo] ORDER BY @OrderBy


Hi

Try this:
((@SearchKeyword = '') or (@SearchKeyword <> '' and {Licao}.[titulo] Like '%' || @SearchKeyword || '%'))
Expand Inline need to be "yes" ?
Without it the query runs but dont show any value.

How i can see the real query without @Parameters?


Edit:

Worked

WHERE ((@SearchKeyword = '')  or ({Licao}.[titulo] Like '%' || @SearchKeyword || '%'))

You use expand inline if you need to inject sql into the query... If you use the parameter only for search/filter by text, date, its not necessary...

In advanced queries, when you click Run, the Service Studio doesn't show any generated sql, this is a simple query feature...
Now i got a problem with return rows.

With the query above i get 0 results:

((@SearchKeyword = '') or (@SearchKeyword <> '' and {Licao}.[titulo] Like '%' || @SearchKeyword || '%'))

With this query above i get 2 of 4 records:

((@SearchKeyword = '') or ({Licao}.[titulo] Like '%' || @SearchKeyword || '%'))

In all cases @SearchKeyword is empty.

It semms like (@SearchKeyword = '') are not working.
Hello Euber,

Why are you trying to perform this query as an advanced query? 

It seems simple enough that it would be able to be expressed as a simple query and in that case you let the syntax be handled by the OutSystems Platform.

Best regards,
Ricardo Silva
Hi Euber,

You are running that query in Oracl database, so instead of @SearchKeyword = '' you should use @SearchKeyword = ' '

You need to put a space between.
Ricardo Silva
You right, but i have some GROUP BY in the real query, this is just a sample of problem.

Nuno Rolo
Works, thanks. Now i changed the query to (@SearchKeyword = '') or (@SearchKeyword = ' ') to work with differ database systems.



Yes, I noticed that earlier today.

With a group by you'll have to use an Advanced query.

Your problem is probably what Nuno Rolo mentioned.
I have been looking for a solution to my problem with an Advanced Query for almost a day know and all it took was the space between '' to check for empty!
Yes, If you are using an Oracle database, you should use '<single space>' to test if a string is empty.

@keyword=' '
See more on the OutSystems Platform docs