1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Decide 'where' field based on user input

Discussion in 'SQL PL/SQL' started by GarciasMuffin, Dec 4, 2014.

  1. GarciasMuffin

    GarciasMuffin Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,


    I'm new to sql so apologies if my question is not explained 100% correctly.

    I'm using Oracle SQL developer.

    I have a table that is called 'DAYS'

    Within that table are seven fields A,B,C,D,E,F,G each relating to the days of the week (A relates to monday, B relates to tuesday etc) and multiple rows.

    I need to do a where from whichever field is relevant for the day of the week that the person executing my SQL query inputs.

    I know about the :variablename bit to get the input and have used it in other sql queries, but not how to decide the field to use for my 'where' based on that input.

    The actual SQL is a bit more convoluted than this obviously, but in simple terms what I need is.

    e.g User inputs 'monday' so my SQL should execute:

    select * from DAYS
    where A is not null

    User inputs 'tuesday' so my SQL should be:
    select * from DAYS
    where B is not null

    etc, etc.

    I know I could simply ask them to run a different SQL query on each day of the week, but that seems a bit rubbish.

    How can I achieve this with a single SQL query ?




    Many thanks in advance for any answers provided.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Given pure SQL, generally this would be handled by either DECODE or CASE:

    Code (Text):
    SELECT *
    FROM   days
    WHERE DECODE([user_input], 'Monday', 'A',
                               'Tuesday', 'B',
                               'Wednesday', 'C',
                               'Thursday', 'D',
                               'Friday', 'E',
                               'Saturday', 'F',
                               'Sunday', 'G',
                               'X') IS NOT NULL;

    SELECT *
    FROM   days
    WHERE CASE
            WHEN [user_input] = 'Monday' THEN 'A'
            WHEN [user_input] = 'Tuesday' THEN 'B'
            WHEN [user_input] = 'Wednesday' THEN 'C'
            WHEN [user_input] = 'Thursday' THEN 'D'
            WHEN [user_input] = 'Friday' THEN 'E'
            WHEN [user_input] = 'Saturday' THEN 'F'
            WHEN [user_input] = 'Sunday' THEN 'G'
            ELSE 'X'
          END IS NOT NULL;
     
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    I'm not sure above would work (not having access to an instance or table structure of "Garcias" to confirm)

    The DECODE or CASE would return a character ('A', 'B', 'C' ...) which when "added" to the rest of the condition would give, for example, in the case of 'Monday', => 'A' IS NOT NULL - a condition which would always be true and thus return all the rows of the table.

    Try removing the quotes from the 'A', 'B' ..., i.e. (for the CASE statement, for ex.):

    Code (SQL):
    SELECT *
    FROM   days
    WHERE CASE
            WHEN [user_input] = 'Monday' THEN A
            WHEN [user_input] = 'Tuesday' THEN B
            WHEN [user_input] = 'Wednesday' THEN C
            WHEN [user_input] = 'Thursday' THEN D
            WHEN [user_input] = 'Friday' THEN E
            WHEN [user_input] = 'Saturday' THEN F
            WHEN [user_input] = 'Sunday' THEN G
          END IS NOT NULL;
     
  4. GarciasMuffin

    GarciasMuffin Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    That worked a treat (case)

    Thankyou very very much. :)
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The OP provided pseudo-SQL as an example. I provided pseudo-SQL as a possible answer. I certainly did not expect him to use the resulting text verbatim. I quoted A-F not so much because I thought they should be but rather from habit because string literals are always quoted. I could have used square brackets as easily... and the resulting SQL still wouldn't have been actual executable code.
     
  6. GarciasMuffin

    GarciasMuffin Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    The use of 'case' for things other than field values has made my SQL much more flexible for people so many thanks.

    I have come across one issue that I can't seem to get my head around though (again I use a simple example to demonstrate).

    This works:

    select * from food
    where forv in case
    when :choice = 'fruit' then '1'
    end;

    This however doesn't:

    select * from food
    where forv in case
    when :choice = 'both' then ('1','2')
    end;

    I get an error 'missing right parenthesis', any ideas why this is ?

    Again, thanks for any help you can give me.
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Because it's an illegal assignment.

    x = 1 <--- Makes sense
    x = '1' <--- Makes sense
    x = 'Fred' <--- Makes sense
    x = ('1', '2') <--- Makes no sense.
     
  8. GarciasMuffin

    GarciasMuffin Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Sorry, but I don't understand why that is ?

    Whenever I'm using the case statement on anything e.g

    select * from food
    where forv
    like case
    when :userinput = 'fruit' then 'apple%'
    end

    It appears to be translating it into English as "select all from food where forv like 'apple%, when the user inputs 'fruit' ", so simply replacing the word 'case' with whatever I tell it to choose based on the user input, and it is working fine.

    (I also don't get the 'cos it has to equal 1 exact value and not multiple values' (if that is what you were trying to tell me above, apologies if you weren't) because obviously with '%' I get multiple values like 'applebramley', 'applecox' etc all returned which aren't 1 exact value.)



    So I assumed (clearly incorrectly) it would translate:

    select * from food
    where forv
    in case
    when :userinput = 'both' then ('1','2')
    end;

    into "Select all from food where forv in ('1','2'), when the user inputs 'both' "

    and "where forv in ('1','2')" is a perfectly good statement when not inside a 'case' .


    I know damn well it is me not understanding as I am the newbie, but based on my results above, I can't quite see why just yet ?
     
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Because CASE is designed to generate 'a' return value, not a 'set' of return values. You could make CASE evaluate to '(1, 2)', because that is a valid string value (although it won't work with your IN condition, of course). However, ('1', '2') is neither a valid string nor a valid numeric value and therefore CASE can't return it. This has nothing to do with the IN portion of your SQL statement.

    Code (Text):
    SELECT CASE
             WHEN dummy = 'X' THEN ('1', '2')
           END
    FROM   dual;

    Error at Command Line : 3 Column : 36
    Error report -
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:


    SELECT CASE
             WHEN dummy = 'X' THEN '(1, 2)'
           END
    FROM   dual;

    CASEWHENDUMMY='X'THEN'(1,2)'END
    -------------------------------
    (1, 2)