Using Decode in where clause in free hand sql

[Former Member]

Hi,

I want to use decode in free hand sql.

for eg : this is a where condition

Outlet_Lookup.City = @variable('Enter City')

Requirement :

if we put the user prompt as "Enter City and % for all cities"

and the user enters % then the data display should be all cities or else it shld be the specific city entered in the prmpt..

Can we do that using Decode statement,

I have tried

Outlet_Lookup.City = Decode(@variable('Enter City'),'%',Outlet_Lookup.City,(@variable('Enter City')))

This is not working....

Pls guide on the same..

Thanks.

[former_member706687]

I have been struggling to put optional prompt in free hand SQL for one of the date and integer objects, and this solution just made my day.

Thank you both!

[Former Member]

it would be better to do something like this:

and (Outlet_Lookup.City = @variable('Enter City (or XXXX for all):') or @variable('Enter City (or XXXX for all):') = 'XXXX')

using the wildcard character my not work with the desired results in a decode.

[Former Member]

Hi Mathieu,

Thanks for the solution,

I tried the following

((Outlet_Lookup.City = @variable('Enter City or % for All') or @variable('Enter City or % for All')='%'))

This is working fine....

Thanks

Regards,

Aparna.