Select GRP, site, item, sum (plan_qty), sum (diff_qty)
From (
Select/* case
When substr (productid, 1, 1) = 'l' then
'Sesl'
When substr (productid, 1, 1) = 'M' then
'Lcm'
Else
Substr (productid, 1, 1)
End "GRP ",*/
Decode (substr (productid, 1, 1), 'l', 'sesl ', 'M', 'lcm', substr (productid, 1, 1) "GRP ",
Case
When currentsite = 'modproc' then
'S1mod'
When currentsite = 'modproc2 'then
'S2mod'
Else
Currentsite
End "Site", floorid item, plan_qty, diff_qty
From (
Select 'ltm0000e4-L02-LLC5 'As productid, 'modproc' as currentsite, '1f' floorid, 1 plan_qty, 2 diff_qty from dual
Union all
Select 'mtn0000e4-L02-LLC5 'As productid, 'modproc2' As currentsite, '2f 'floorid, 3 plan_qty, 4 diff_qty from dual
)
) Group by GRP, site, item
;
------------------ When case when is used, an error is reported .....
Select GRP, site, item, sum (plan_qty), sum (diff_qty)
From (
Select case
When substr (productid, 1, 1) = 'l' then
'Sesl'
When substr (productid, 1, 1) = 'M' then
'Lcm'
Else
Substr (productid, 1, 1)
End "GRP ",
--- Decode (substr (productid, 1, 1), 'l', 'sesl ', 'M', 'lcm', substr (productid, 1, 1 )) "GRP ",
Case
When currentsite = 'modproc' then
'S1mod'
When currentsite = 'modproc2 'then
'S2mod'
Else
Currentsite
End "Site", floorid item, plan_qty, diff_qty
From (
Select 'ltm0000e4-L02-LLC5 'As productid, 'modproc' as currentsite, '1f' floorid, 1 plan_qty, 2 diff_qty from dual
Union all
Select 'mtn0000e4-L02-LLC5 'As productid, 'modproc2' As currentsite, '2f 'floorid, 3 plan_qty, 4 diff_qty from dual
)
) Group by GRP, site, item
;