please help me o fix this. i got a syntax error near last line.. thanks
Code:SELECT M.MACHINENAME, I.ITEMNAME [RAW MATERIAL], I.MATERIALSIZE, I2.ITEMNAME PRODUCTS, CASE WHEN IT.UNITMEASUREID = 11 THEN sum(IT.QUANTITY) Else sum(IT.QUANTITY/ coil) End Coil2, CASE WHEN IT.UNITMEASUREID = 1 THEN sum(IT.QUANTITY) else sum(IT.QUANTITY / coil) end Kilgram2 From ( Select (SELECT UNITVALUE FROM UNITMEASURECONVERSIONS WHERE UNITMEASUREID = 1 AND UNITMEASURECONVERSIONS.ITEMID = IT.ITEMID)* (SELECT UNITVALUE FROM UNITMEASURECONVERSIONS WHERE UNITMEASUREID = 11 AND UNITMEASURECONVERSIONS.ITEMID = IT.ITEMID ) As COIL FROM ITEMTRANSACTIONS IT JOIN DEPARTMENTS D ON D.DEPARTMENTID = IT.DEPARTMENTID JOIN SECTIONS S ON S.SECTIONID = IT.SECTIONID JOIN SUBSECTIONS SUBS ON SUBS.SUBSECTIONID = IT.SUBSECTIONID JOIN SUBSECTIONAREAS SUBA ON SUBA.SUBSECTIONAREAID = IT.SUBSECTIONAREAID JOIN ITEMS I ON I.ITEMID = IT.ITEMID JOIN ITEMS I2 ON I2.ITEMID = IT.ASSIGNTOITEMID JOIN UNITMEASURES UM ON UM.UNITMEASUREID = IT.UNITMEASUREID JOIN UNITMEASURECONVERSIONS UMC ON UMC.UNITMEASUREID = IT.UNITMEASUREID AND UMC.ITEMID = IT.ITEMID JOIN MACHINES M ON M.MACHINEID = IT.MACHINEID WHERE D.DEPARTMENTID = 4 AND S.SECTIONID = 13 AND SUBS.SUBSECTIONID = 4 AND SUBA.SUBSECTIONAREAID = 2 AND (UM.UNITMEASUREID = 11 OR UM.UNITMEASUREID = 1) Group by m.machineid,I.ITEMNAME,I.MATERIALSIZE, I2.ITEMNAME)




Reply With Quote