Complex query with DECODE

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.