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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.