Aug 24, 2011 at 11:23 AM

Help in Query

56 Views

Hi all

SELECT A.CARDCODE , A.CARDNAME , A.ITEMCODE , CASE WHEN A1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE A1.FirmName END AS FIRMNAME, A.ITMSGRPNAM , A.ITEMNAME , SUM(A.QTY_LST) AS QTY_LST ,

SUM(A.VALUE_LST) AS vALUE_lST , SUM(A.QTY_CUR) AS QTY_CUR , SUM(A.VALUE_CUR) AS VALUE_CUR ,

SUM(A.LST_THIS) AS LST_THIS , SUM(A.CUR_THIS) AS CUR_THIS , SUM(A.OPN_QTY) As OPN_QTY FROM

( SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME , I2.ITMSGRPNAM ,

I0.ITEMNAME , SUM(T1.QUANTITY) AS QTY_LST , SUM(T1.LINETOTAL) AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR ,

0 AS LST_THIS , 0 AS CUR_THIS , 0 AS OPN_QTY

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '04/01/2010',102)

AND T0.DOCDATE < CONVERT(DATETIME , '04/01/2011',102) AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y'

AND C0.GROUPCODE IN (102 , 104 , 105)GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME , I2.ItmsGrpNam ,

I0.ITEMCODE , I0.ItemName Having SUM(T1.QUANTITY) > 0 Union All

SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME , I2.ITMSGRPNAM ,

I0.ITEMNAME , -SUM(T1.QUANTITY) AS QTY_LST , -SUM(T1.LINETOTAL) AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR ,

0 AS LST_THIS , 0 AS CUR_THIS , 0 AS OPN_QTY

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '04/01/2010',102)

AND T0.DOCDATE <= CONVERT(DATETIME , '04/01/2011',102) AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y'

AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME , I2.ItmsGrpNam ,

I0.ITEMCODE , I0.ItemName Having SUM(T1.QUANTITY) > 0 UNION ALL SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

I1.FIRMNAME , I2.ITMSGRPNAM , I0.ITEMNAME , 0 AS QTY_LST , 0 AS VALUE_LST , SUM(T1.QUANTITY) AS QTY_CUR ,

SUM(T1.LINETOTAL) AS VALUE_CUR , 0 AS LST_THIS , 0 AS CUR_THIS , 0 AS OPN_QTY

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '04/01/2011' , 102) AND T0.DOCDATE <= CONVERT(DATETIME , '08/24/2011' , 102)

AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y' AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME ,

I2.ItmsGrpNam , I0.ItemName , I0.ITEMCODE Having SUM(T1.QUANTITY) > 0 Union All

SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME ,

I2.ITMSGRPNAM , I0.ITEMNAME , 0 AS QTY_LST , 0 AS VALUE_LST , -SUM(T1.QUANTITY) AS QTY_CUR ,

-SUM(T1.LINETOTAL) AS VALUE_CUR , 0 AS LST_THIS , 0 AS CUR_THIS , 0 AS OPN_QTY

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '04/01/2011' , 102) AND T0.DOCDATE <= CONVERT(DATETIME , '08/24/2011' , 102)

AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y' AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME , I2.ItmsGrpNam ,

I0.ItemName , I0.ITEMCODE Having SUM(T1.QUANTITY) > 0 Union All SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

I1.FIRMNAME , I2.ITMSGRPNAM , I0.ITEMNAME , 0 AS QTY_LST , 0 AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR ,

SUM(T1.QUANTITY) AS LST_THIS , 0 AS CUR_THIS , 0 AS OPN_QTY

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '08/01/2010' , 102) AND T0.DOCDATE <= CONVERT(DATETIME , '08/31/2010' , 102)

AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y' AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME , I2.ItmsGrpNam ,

I0.ItemName , I0.ITEMCODE Having SUM(T1.QUANTITY) > 0 Union All

SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME,

I2.ITMSGRPNAM , I0.ITEMNAME , 0 AS QTY_LST , 0 AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR ,

-SUM(T1.QUANTITY) AS LST_THIS , 0 AS CUR_THIS , 0 AS OPN_QTY

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '08/01/2010' , 102)

AND T0.DOCDATE <= CONVERT(DATETIME , '08/31/2010' , 102) AND C0.FROZENFOR = 'N'

AND I0.QRYGROUP64 = 'Y' AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME, I2.ItmsGrpNam ,

I0.ItemName , I0.ITEMCODE Having SUM(T1.QUANTITY) > 0 Union All

SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME, I2.ITMSGRPNAM ,

I0.ITEMNAME , 0 AS QTY_LST , 0 AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR , 0 AS LST_THIS ,

SUM(T1.QUANTITY) AS CUR_THIS , 0 AS OPN_QTY

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '08/01/2011' , 102) AND T0.DOCDATE <= CONVERT(DATETIME , '08/31/2011' , 102)

AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y' AND T1.TARGETTYPE <> 14 AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME, I2.ItmsGrpNam ,

I0.ItemName , I0.ITEMCODE Having SUM(T1.QUANTITY) > 0 Union All

SELECT T0.CARDCODE , T0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME,

I2.ITMSGRPNAM , I0.ITEMNAME , 0 AS QTY_LST , 0 AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR , 0 AS LST_THIS ,

-SUM(T1.QUANTITY) AS CUR_THIS , 0 AS OPN_QTY

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DOCENTRY = T1.DocEntry INNER JOIN OCRD C0 ON T0.CARDCODE = C0.CARDCODE

INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0 ON T1.ItemCode = I0.ItemCode

INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE T0.DOCDATE >= CONVERT(DATETIME , '08/01/2011' , 102) AND T0.DOCDATE <= CONVERT(DATETIME , '08/31/2011' , 102)

AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y' AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY T0.CARDCODE , T0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME, I2.ItmsGrpNam ,

I0.ItemName , I0.ITEMCODE Having SUM(T1.QUANTITY) > 0 Union All SELECT O0.CARDCODE , O0.CARDNAME , I0.ITEMCODE ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END , I2.ITMSGRPNAM ,

I0.ITEMNAME ,0 AS QTY_LAST , 0 AS VALUE_LST , 0 AS QTY_CUR , 0 AS VALUE_CUR ,

0 AS LST_THIS , 0 AS CUR_THIS , SUM(O1.OPENQTY)

AS OPN_QTY FROM ORDR O0 INNER JOIN RDR1 O1 ON O0.DocEntry = O1.DOCENTRY INNER JOIN OCRD C0

ON O0.CARDCODE = C0.CARDCODE INNER JOIN OCRG C1 ON C0.GROUPCODE = C1.GROUPCODE LEFT OUTER JOIN OITM I0

ON O1.ItemCode = I0.ItemCode INNER JOIN OMRC I1 ON I1.FirmCode = I0.FirmCode

INNER JOIN OITB I2 ON I2.ItmsGrpCod = I0.ITMSGRPCOD

WHERE O1.SHIPDATE >= CONVERT(DATETIME, '08/24/2011' , 102) AND C0.FROZENFOR = 'N' AND I0.QRYGROUP64 = 'Y'

AND C1.GROUPCODE IN (102 , 104 , 105)

GROUP BY O0.CARDCODE , O0.CARDNAME ,

CASE WHEN I1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE I1.FirmName END AS FIRMNAME,

I2.ItmsGrpNam , I0.ItemName , I0.ITEMCODE HAVING SUM(O1.OPENQTY)> 0 ) A , OITM I3

WHERE A.ITEMCODE = I3.ITEMCODE

GROUP BY A.CARDCODE , A.CARDNAME , CASE WHEN A1.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE a1.FirmName END AS FIRMNAME, A.ITMSGRPNAM , A.ITEMNAME , A.ITEMCODE

Please help me in this query. This gives me an error incorrect syntax near the keyword AS

Thanking you

Malhaar