oracle - SQL - Error with Sum(Case -
i have searched bunch of examples quite helpful in creating query having trouble 1 part of query...
sorry not being able provide complete table structure. here working query...
select i.spl_instr_code_1 lw, sum((d.units_ordered/i.std_sub_pack_qty) - (d.units_rcvd/i.std_sub_pack_qty)) "booked" wmdbadmin.item_master i, wmdbadmin.cust_po_appt c, wmdbadmin.po_hdr h, wmdbadmin.po_dtl d, wmdbadmin.appt_sched a, wmdbadmin.vendor_master v, wmdbadmin.sys_code s h.ref_field_2 = 'n' , c.appt_nbr = a.appt_nbr (+) , c.po_nbr = h.po_nbr , h.po_nbr = d.po_nbr , i.sku_id = d.sku_id , h.vendor_master_id = v.vendor_master_id , s.code_id = a.stat_code , s.rec_type = 's' , s.code_type = '628' , s.code_desc<>'scheduled' , a.stat_code <= '90' , ((to_char(a.sched_date_time,'yyyymmdd')>= '20150401' , to_char(a.sched_date_time, 'yyyymmdd')<='20150401')) group i.spl_instr_code_1 order i.spl_instr_code_1 asc
i tried alter above changing...
sum((d.units_ordered/i.std_sub_pack_qty) - (d.units_rcvd/i.std_sub_pack_qty)) "booked"
to...
sum( case when s.code_desc<>'checked in' (d.units_ordered/i.std_sub_pack_qty) else (d.units_rcvd/i.std_sub_pack_qty) end ) “booked”
but following error , uncertain error means , can not figure out how correct it...
#source: oraoledb number: -2147467259 description: ora-00972: identifier long sql state: native error: 972
#failed open recordset [hr=2147500037 (0x80004005)]
# source: adodb.recordset number: 3704 description: operation not allowed when object closed. sql: select i.spl_instr_code_1 lw, sum( case when s.code_desc<>'checked in' (d.units_ordered/i.std_sub_pack_qty) else (d.units_rcvd/i.std_sub_pack_qty) end ) “booked” wmdbadmin.item_master i, wmdbadmin.cust_po_appt c, wmdbadmin.po_hdr h, wmdbadmin.po_dtl d, wmdbadmin.appt_sched a, wmdbadmin.vendor_master v, wmdbadmin.sys_code s h.ref_field_2 = 'n' , c.appt_nbr = a.appt_nbr (+) , c.po_nbr = h.po_nbr , h.po_nbr = d.po_nbr , i.sku_id = d.sku_id , h.vendor_master_id = v.vendor_master_id , s.code_id = a.stat_code , s.rec_type = 's' , s.code_type = '628' , s.code_desc<>'scheduled' , a.stat_code <= '90' , ((to_char(a.sched_date_time,'yyyymmdd')>= '20150331' , to_char(a.sched_date_time, 'yyyymmdd')<='20150331')) group i.spl_instr_code_1 order i.spl_instr_code_1 asc
# source: sqlbuilder.clssqlbuilder number: 25004 description: closed recordset object returned database.
#failed on call getrecords on business object. @ line: 385
#failed on call refreshdatagrid.
Comments
Post a Comment