11 Replies Latest reply: Mar 15, 2012 8:12 AM by GeetaM RSS

    Subquery inside CASE statement

    666129
      I am trying to use a subquery within a Case statement with a 'where' condition that binds to the parent query. Like this:
      Select T.idperson , CASE WHEN  T.type = 'C' THEN (SELECT name from Customers where C.idcustomer = T.idperson) 
      ELSE
       (SELECT name from Providers where idprovider = T.idperson)
       END Name 
      from myTable T 
      This works for me in SQL Server but in ORACLE the subquery does not return any rows, i guess its the binding to parent query part. Any thoughts of another way I could get the result I want?

      Thanks,

      JuanDiego
        • 1. Re: Subquery inside CASE statement
          Satyaki_De
          Try this ->
          Select T.idperson , 
                 CASE 
                   WHEN  T.type = 'C' THEN 
                     (
                        SELECT name 
                        from Customers 
                        where idcustomer = T.idperson
                     ) 
                  ELSE
                    (
                      SELECT name 
                      from Providers 
                      where idprovider = T.idperson
                    )
                  END Name 
          from myTable T ;
          N.B.: Not Tested...

          Regards.

          Satyaki De.
          • 2. Re: Subquery inside CASE statement
            666133
            try this


            SELECT
            T.IDPERSON,
            CASE
            WHEN T.TYPE = 'C'
            THEN
            (
            nvl((SELECT NAME FROM CUSTOMERS WHERE C.IDCUSTOMER = T.IDPERSON),'')
            )
            ELSE
            (
            nvl((SELECT NAME FROM PROVIDERS WHERE IDPROVIDER = T.IDPERSON),'')
            )
            END as NAME
            FROM
            MYTABLE T

            Edited by: user7253247 on Oct 20, 2008 11:39 AM
            • 3. Re: Subquery inside CASE statement
              Frank Kulash
              Hi, Juandiego,

              If it's not returning any rows, then you must not have any rows in the table.
              If you have any rows in the table, you will either get
              (a) an error, or
              (b) some rows (even if the columns are NULL).

              There is another way to get the results you want.
              I probably wouldn't use scalar sub-queries. I would outer-join all the tables, like this:
              Select  T.idperson
              ,       CASE
                          WHEN  T.type = 'C'
                          THEN  c.name
                          ELSE  p.name
                      END  AS Name 
              from             myTable   t 
              LEFT OUTER JOIN  Customers c  ON c.idcustomer = T.idperson
              LEFT OUTER JOIN  Providers p  ON p.idprovider = T.idperson
              ;
              • 4. Re: Subquery inside CASE statement
                666129
                Thank you for your replies!

                I think im gonna go with the outer join. Frank, do youn know if its any better (performance-wise) using outer joins instead of the scalar queries?
                • 5. Re: Subquery inside CASE statement
                  Frank Kulash
                  Hi,

                  Yes, joins, even outer joins, are usually faster than scalar sub-queries.
                  If you had 20 look-up tables to outer-join, and one of them is known to be used 95% of the time, then scalar sub-queries might be faster. Though, even in that case, I might outer-join the first look-up table and do scalar sub-queries (when necessary) on the other 19.

                  Do you know anything special like that about your data?
                  • 6. Re: Subquery inside CASE statement
                    pl/sql novice
                    One thing I noticed about the original query and some of the suggestions using case clause: the original one case finished with 'end Name', but the others finished with 'end as Name'. Not sure if the 'as' keyword is compulsory or not.
                    • 7. Re: Subquery inside CASE statement
                      Frank Kulash
                      Hi,

                      "AS" is optional in
                      SELECT  col     AS  col_alias
                      I recommend always using it. I find it makes code easier to read and understand.
                      • 8. Re: Subquery inside CASE statement
                        666129
                        Thank you Frank. I've got it under control.

                        I appreciate that.
                        • 9. Re: Subquery inside CASE statement
                          GeetaM
                          Hi

                          Can we use case in a subquery?

                          Regards
                          • 10. Re: Subquery inside CASE statement
                            Frank Kulash
                            Hi,
                            user546629 wrote:
                            Hi

                            Can we use case in a subquery?
                            Sure, but don't take my word for it: try it and see.

                            If you have a question, start your own thread. More people will be interested in reading and answering a new thread than one that's over 3 years old.
                            Include your complete query, and anything needed to run it, such as CREATE TABLE and INSERT statements (unless you're using commonly availabe tables, like those in the scott schema). Post the results you want to get, and explain how you get those results from the sample data you gave.
                            Always say which version of Oracle you're using.
                            • 11. Re: Subquery inside CASE statement
                              GeetaM
                              Hi Frank,

                              I have started a new fourm for my question -

                              Subqueries and Case

                              Regards