Why cannot substr be used in case when in Oracle 10 Gb ????

 

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

;