cancel
Showing results for 
Search instead for 
Did you mean: 

Help in Query

former_member218051
Active Contributor
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member206488
Active Contributor
0 Kudos

Hi Malhar,

try this:

SELECT A.CARDCODE , A.CARDNAME , A.ITEMCODE , CASE WHEN A.FirmName = 'Studiomaster (SI)' THEN 'Studiomaster (SM)' ELSE A.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 , [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 , [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 , [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 , [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 ,[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 ,[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 , [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 , [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 ,[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 , [FIRMNAME], A.ITMSGRPNAM , A.ITEMNAME , A.ITEMCODEThanks,

Neetu

Edited by: Neetu Dhami on Aug 25, 2011 12:18 PM

jitin_chawla
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

Please check it by changing the part of the query as follows :


ELSE I1.FirmName END AS [FIRMNAME]

Use

 [  ] 

Kind Regards,

Jitin

SAP Business One Forum Team

former_member218051
Active Contributor
0 Kudos

Hi jitin,

I removed as firmname from group by clause in every select statement keeping it as it is at the top and it worked.

Thank you all experts with the replies.

thanks for giving as [firmname] information.

Thanking you

Malhaar

jitin_chawla
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

In your query the error is coming at the following part of the query you have written. Please recheck it :


ELSE I1.FirmName END AS FIRMNAME

Kind Regards,

Jitin

SAP Business One Forum Team