got error , facing bit because alias head turning around stuffs, me solve alias wrong check
select b.parts_no, b.adj_date, b.cost, b.adj_desc, adj_fg_qty_prev = sum(b.adj_fg_qty_prev), adj_cogs_qty_prev = sum(b.adj_cogs_qtyprev) , adj_fg_qty = sum(b.adj_fg_qty), adj_cogs_qty = sum(b.adj_cogs_qty) ( select parts_no=a.parts_no, adj_date=convert(varchar(10),a.processing_datetime,105), cost=isnull((select case when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=1 isnull(a.ecost_01,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=2 isnull(a.ecost_02,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=3 isnull(a.ecost_03,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=4 isnull(a.ecost_04,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=5 isnull(a.ecost_05,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=6 isnull(a.ecost_06,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=7 isnull(a.ecost_07,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=8 isnull(a.ecost_08,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=9 isnull(a.ecost_09,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=10 isnull(a.ecost_10,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=11 isnull(a.ecost_11,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=12 isnull(a.ecost_12,0) else 0 end titembalance a.[year]=year(convert(datetime,ltrim(rtrim(@period))+'01',112)) , a.parts_no=a.parts_no),0), adj_desc ='cats', adj_fg_qty_prev=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))<>isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='1' b.quantity_f else 0 end) else 0 end), adj_cogs_qtyprev=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))<>isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='2' b.quantity_f else 0 end) else 0 end), adj_fg_qty=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))=isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='1' b.quantity_f else 0 end) else 0 end), adj_cogs_qty=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))=isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='2' b.quantity_f else 0 end) else 0 end) tpartadjustmentc inner join tpartadjustmentcl b on a.card_no=b.card_no , a.inhouse_outside=b.inhouse_outside , a.parts_no=b.parts_no , a.process_code=b.process_code , a.filler=b.filler , a.processing_datetime=b.processing_datetime isnull(a.costing1,0)=convert(numeric(6,0),@period) group a.parts_no,convert(varchar(10),a.processing_datetime,105),left(ltrim(b.reason_code),1) union select parts_no=a.parts_no, adj_date=convert(varchar(10),a.processing_datetime,105), cost=isnull((select case when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=1 isnull(a.ecost_01,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=2 isnull(a.ecost_02,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=3 isnull(a.ecost_03,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=4 isnull(a.ecost_04,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=5 isnull(a.ecost_05,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=6 isnull(a.ecost_06,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=7 isnull(a.ecost_07,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=8 isnull(a.ecost_08,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=9 isnull(a.ecost_09,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=10 isnull(a.ecost_10,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=11 isnull(a.ecost_11,0) when month(convert(datetime,ltrim(rtrim(@period))+'01',112))=12 isnull(a.ecost_12,0) else 0 end titembalance a.[year]=year(convert(datetime,ltrim(rtrim(@period))+'01',112)) , a.parts_no=a.parts_no),0), adj_desc ='impulse', adj_fg_qtyprev=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))<>isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='1' b.quantity_f else 0 end) else 0 end), adj_cogs_qtyprev=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))<>isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='2' b.quantity_f else 0 end) else 0 end), adj_fg_qty=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))=isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='1' b.quantity_f else 0 end) else 0 end), adj_cogs_qty=sum(case when convert(numeric(6,0),convert(varchar(6),a.processing_datetime,112))=isnull(a.costing1,0) (case when left(ltrim(b.reason_code),1)='2' b.quantity_f else 0 end) else 0 end) tpartadjustmenti inner join tpartadjustmentil b on a.card_no=b.card_no , a.inhouse_outside=b.inhouse_outside , a.parts_no=b.parts_no , a.process_code=b.process_code , a.filler=b.filler , a.processing_datetime=b.processing_datetime isnull(a.costing1,0)=convert(numeric(6,0),@period) group a.parts_no,convert(varchar(10),a.processing_datetime,105),left(ltrim(b.reason_code),1) ) b group b.parts_no, b.adj_date, b.cost, b.adj_desc
you using alias a both titembalance in subquery , tpartadjustmentc in query.
i think condition a.parts_no=a.parts_no supposed compare fields both tables, because of conflicting aliases compare field , query return parts. make subquery return more 1 record, , error message.
Comments
Post a Comment