9 Replies Latest reply: Nov 12, 2009 10:40 AM by CrazyAnie RSS

    If - Then -Else in a query

    CrazyAnie
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          Hi Frank,

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

                          Appreciate all your help.