Skip to Content
author's profile photo Former Member
Former Member

Using Decode in where clause in free hand sql

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 05, 2009 at 04:22 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Jul 13, 2020 at 11:31 AM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.