Q
Problem solve Get help with specific problems with your technologies, process and projects.

Complex query with DECODE

I have the following query but it's not working. Can we use nested queries with decode?

I have the following query but it's not working. I want to know where I have gone wrong and please provide me with...

the correct query. Can we use nested queries with decode?

SELECT A.TYPE,
(SELECT SUM(DECODE(TYPE,'HOT',1,0)) HOT_CAKE,
SUM(DECODE(TYPE,'SWEET',1,0)) SWEET_CAKE 
FROM PRODUCT_DETAIL WHERE PRODUCT_CODE = 1 GROUP BY TYPE) A, 
(SELECT SUM(DECODE(TYPE,'HOT',1,0)) HOT_BURGER,
SUM(DECODE(TYPE,'SWEET',1,0)) SWEET_BURGER 
FROM PRODUCT_DETAIL WHERE PRODUCT_CODE = 2 GROUP BY TYPE) B 
WHERE A.TYPE = B.TYPE;

I want the output as

HOT_CAKE   SWEET_CAKE   HOT_BURGER   SWEET_BURGER
   50          30         26             45

Where you've gone wrong (since you did ask) is to place the derived tables into the SELECT clause instead of the FROM clause.

This is probably what you intended --

select A.TYPE
     , HOT_CAKE
     , SWEET_CAKE
     , HOT_BURGER
     , SWEET_BURGER
  from ( SELECT TYPE
              , SUM(DECODE(TYPE,'HOT',1,0))
                     HOT_CAKE
              , SUM(DECODE(TYPE,'SWEET',1,0))
                     SWEET_CAKE 
           FROM PRODUCT_DETAIL 
          WHERE PRODUCT_CODE = 1 
         GROUP BY TYPE ) A
inner
  join ( SELECT TYPE
              , SUM(DECODE(TYPE,'HOT',1,0)) 
                     HOT_BURGER
              , SUM(DECODE(TYPE,'SWEET',1,0)) 
                     SWEET_BURGER 
           FROM PRODUCT_DETAIL 
          WHERE PRODUCT_CODE = 2 
         GROUP BY TYPE ) B 
    on A.TYPE 
     = B.TYPE

Here's another way to obtain the same result, which might be a little easier to write --

select TYPE
     , sum(case when PRODUCT_CODE = 1
                 and TYPE = 'HOT'
                then 1 else 0 end 
          ) as HOT_CAKE
     , sum(case when PRODUCT_CODE = 1
                 and TYPE = 'SWEET'
                then 1 else 0 end 
          ) as SWEET_CAKE
     , sum(case when PRODUCT_CODE = 2
                 and TYPE = 'HOT'
                then 1 else 0 end 
          ) as HOT_BURGER
     , sum(case when PRODUCT_CODE = 2
                 and TYPE = 'SWEET'
                then 1 else 0 end 
          ) as SWEET_BURGER
  from PRODUCT_DETAIL
group 
    by TYPE

This form of the query also makes it easier to add other products. In addition, it's likely more efficient, since it makes only one pass of the table instead of two passes with a join.

This was last published in April 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close