SQL basic issue

   select x_pick.prod_code, x_order.order_state, x_pick.pick_state, x_pick.pick_qty, x_pick.pick_location, x_order.wave_id, x_prod.normal_pick_area, x_apr_loc.location, x_order.reqd_despatch_time, x_prod.bncd_awkward_item, sub1.LOC_COUNT
from x_pick, x_order, x_prod, x_apr_loc,

(select X_PICK.prod_CODE, COUNT(DISTINCT( X_APR_LOC.LOCATION)) AS LOC_COUNT
from X_APR_LOC, X_PICK
where X_APR_LOC.prod_CODE (+) = X_PICK.prod_CODE
group by X_PICK.prod_code) sub1,

(select x_prod.normal_pick_area,x_pick.pick_location,
(case when  x_prod.normal_pick_area = 'TP' then 'Miniload'
when x_prod.normal_pick_area = 'MTP' then 'Miniload'
when x_prod.normal_pick_area = 'PL' then 'Pallet Live'
when x_prod.normal_pick_area = 'HLOP' then 'HLOP'
when x_prod.normal_pick_area = 'STILLAGE' then 'High Value'
when x_prod.normal_pick_area = 'ZA' then 'Aerosol'
when x_prod.normal_pick_area = 'ZH' then 'Haz Shed'
when x_prod.normal_pick_area = 'APR' and substr(x_pick.pick_location,3,2) < 22 and x_prod.bncd_awkward_item = 'YES'  then 'APR Awkward'
when x_prod.normal_pick_area = 'APR' and substr(x_pick.pick_location,3,2) < 22 and x_prod.bncd_awkward_item = 'NO'  then 'APR'
when x_prod.normal_pick_area = 'APR' and substr(x_pick.pick_location,3,2) > 21 and x_prod.bncd_awkward_item = 'NO'  then 'APR 2'
when x_prod.normal_pick_area = 'APR' and substr(x_pick.pick_location,3,2) > 21 and x_prod.bncd_awkward_item = 'YES'  then 'TD1'
else 'Unknown' end) from x_pick) area,


where x_pick.prod_code  (+) = sub1.prod_code
and x_pick.prod_code = x_apr_loc.prod_code (+)
and x_pick.prod_code = x_prod.prod_code
and x_pick.order_id = x_order.order_id
and x_pick.pick_state in('RESERVED','WAIT REPLEN','STARTED')
and x_order.order_state in('RESERVED','STARTED')

Part of a larger query. Initially I am getting an invalid parathensis message, I can get rid of this but am them getting invalid identifier error message, even though all the fields I’m using have been called. I am pretty new to SQL so any help would be great!.

Answer

Please use below there was two issue First need to add comma after pick_location second from x_item will be after column name, Hopefully it will work at your side as well

(select x_item.pick_area,x_item.awkward_item,x_ploc.pick_location,
(case when  x_prod.normal_pick_area = '0' then 'Z'
when x_item.pick_area = '1' then 'A'
when x_item.pick_area = '2' then 'B'
when x_item.pick_area = '3' then 'C'
when x_item.pick_area = '4' then 'D'
when x_item.pick_area = '5' then 'E'
when x_item.pick_area = '6' then 'F'
when x_item.pick_area = '7' and substr(x_ploc.pick_location,3,2) < 22 and
                                    x_prod.awkward_item = 'YES'  then 'G'
when x_item.normal_pick_area = 'APR' and substr(x_ploc.pick_location,3,2)
   < 22 and x_item.awkward_item = 'NO'  then 'H'

when x_item.pick_area = 'APR' and substr(x_ploc.pick_location,3,2) > 21  
 and x_item.awkward_item = 'NO'  then 'I'
when x_item.pick_area = 'APR' and substr(x_ploc.pick_location,3,2) > 21   
and x_item.awkward_item = 'YES'  then 'J'

else 'Unknown' end)  area from x_item)