This discussion is archived
9 Replies Latest reply: Nov 12, 2009 8:40 AM by CrazyAnie RSS

If - Then -Else in a query

CrazyAnie Newbie
Currently Being Moderated
Hi,
Can i use an If-Ten-Else clause in a SQL query in Oracle 10g? I am trying to use it but am getting a "Missing right paranthesis" error.
  • 1. Re: If - Then -Else in a query
    21205 Oracle ACE Director
    Currently Being Moderated
    you can use CASE
    SQL> select case when val < 10 then 'Small'
      2         when val > 15 then 'Large'
      3        end
      4    from (select 1 val from dual union all
      5          select 9 from dual union all
      6          select 21 from dual
      7         )
      8  /
    
    CASEW
    -----
    Small
    Small
    Large
  • 2. Re: If - Then -Else in a query
    Himanshu Kandpal Pro
    Currently Being Moderated
    Hi,

    please let us know what do you want to do in the query, you can use IF else in the pl sql block.

    thanks
  • 3. Re: If - Then -Else in a query
    CrazyAnie Newbie
    Currently Being Moderated
    We are already using case but we need to return multiple values in the else clause. So, we were trying to use if then else with case.
  • 4. Re: If - Then -Else in a query
    21205 Oracle ACE Director
    Currently Being Moderated
    you can't use IF THEN ELSE in queries, but you can nest CASE expressions
  • 5. Re: If - Then -Else in a query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    You can use IF-THEN in a query; those keywords have meaning only in PL/SQL.

    The WHERE clause is similar to an IF-THEN-ELSE operation. I don't know what you want, but the smart way to do it might be using the WHERE clause and JOIN conditions.
    If you post some sample data (CREATE TABLE and INSERT statements) and the results you want from that data, somebody can help you get those results.

    CASE returns a single expression. If you need to return several values, you could concatenate them into a delimited string (which the CASE expression can return) and then split that string into its sub-strings.
  • 6. Re: If - Then -Else in a query
    CrazyAnie Newbie
    Currently Being Moderated
    Below is the query:

    SELECT DISTINCT
    STATE.STATE_CODE || '-' || STATE.STATE_NAME
    FROM
    STATE,
    PPP
    WHERE state.state_code in(
    CASE(substr('01X01##YYXXX',8,2)
    WHEN 'AK' THEN 'AK'
    WHEN 'AL' THEN 'AL'
    ELSE ('AK','AL','AR','AZ','CA')
    END
    )

    I would like to select all the values for the state_code from the state table in the else statement.
  • 7. Re: If - Then -Else in a query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    It looks like passing back a delimited list would work best for this problem.

    Assuming no state_code is a substring of another (and, if they are all the same length, this will always be so), then you can say something like:
    WHERE   INSTR  ( CASE  SUBSTR (var_cols_89_not_always_YY, 8, 2)
                            WHEN  'AK'  THEN  'AK'
                     WHEN  'AL'  THEN  'AL'
                                       ELSE      'AK,AL,AR,AZ,CA'
               END
                , state_code
                ) > 0
    This also assumes that no state_code contains ','.
  • 8. Re: If - Then -Else in a query
    734070 Newbie
    Currently Being Moderated
    Hi!
    Try this.
    SELECT DISTINCT
           state.state_code || '-' || state.state_name
      FROM state,
           ppp
      WHERE state.state_code = substr('01X01##YYXXX',8,2)
         OR substr('01X01##YYXXX',8,2) NOT IN ('AK', 'AL') AND state.state_code IN ('AK','AL','AR','AZ','CA')
  • 9. Re: If - Then -Else in a query
    CrazyAnie Newbie
    Currently Being Moderated
    Hi Frank,

    This works like a charm... Thank You so much!!

    Appreciate all your help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points