troubleshooting Question

SQL - Syntax Query

Avatar of Zack
ZackFlag for Australia asked on
SQLMicrosoft SQL Server
12 Comments4 Solutions24 ViewsLast Modified:
Hi EE,

I am having issues with the following query:


SELECT
   SUM(VF.TOTALPURCHASES) PURCHASEVALUE,
   SUM(VF.PURCHASERETURNS) PURCHASERETURNS,
   SUM(VF.TOTALPURCHASES) + SUM(VF.PURCHASERETURNS) AS NETPURCHASES,
   VF.DIMENSION_VALUE,
   VF.DIMENSION_DESCRIPTION,
   VF.DIMENSION_TYPE,
   VF.DAC,
   VF.DAC2
FROM
   (
   SELECT
      CONVERT(INT,SUM(TOTALPURCHASES)) AS TOTALPURCHASES,
      CONVERT(INT,SUM(PURCHASERETURNS)) AS PURCHASERETURNS,
      DIMENSION_VALUE,
      DIMENSION_DESCRIPTION,
      DIMENSION_TYPE,
      DAC,
      DAC2
   FROM
      (SELECT
         vs.RECID,
         vs.PURCHID,
         vs.ITEMID,
         vs.LINENUM,
         vs.DEFAULTDIMENSION,
         DA.NAME AS DIMENSION_TYPE,
         DACT.DataAreaId AS DAC,
         DAVT.DataAreaId AS DAC2,
         DAVSI.DISPLAYVALUE DIMENSION_VALUE,
         CASE
            WHEN DA.NAME='Customer' THEN DACT.NAME
            WHEN DA.NAME='Vendor' THEN DAVT.NAME
            WHEN DA.NAME='BusinessUnit' THEN DABT.NAME
            ELSE DFT.DESCRIPTION
         END AS DIMENSION_DESCRIPTION,
         vs.LINEAMOUNTMST AS TOTALPURCHASES,
         0 AS PURCHASERETURNS,
         vs.QTY
      FROM
         DBO.vendinvoicetrans Vs
      LEFT JOIN DimensionAttributeValueSetItem DAVSI ON vs.DEFAULTDIMENSION=DAVSI.DIMENSIONATTRIBUTEVALUESET
      LEFT JOIN DIMENSIONATTRIBUTEVALUE DAV ON DAVSI.DIMENSIONATTRIBUTEVALUE=DAV.RECID
      LEFT JOIN DimensionAttribute DA ON DAV.DIMENSIONATTRIBUTE=DA.RECID
      LEFT JOIN DimensionAttributeDirCategory DADC ON DADC.DIMENSIONATTRIBUTE=DA.RECID
      LEFT JOIN DimensionFinancialTag DFT ON DFT.FINANCIALTAGCATEGORY=DADC.DIRCATEGORY AND DFT.RECID=DAV.ENTITYINSTANCE
      LEFT JOIN DIMATTRIBUTECUSTTABLE DACT ON DACT.Value = DAVSI.DisplayValue AND DACT.RecId = DAV.EntityInstance AND DACT.DataAreaId = 'abc'
      LEFT JOIN DIMATTRIBUTEVENDTABLE DAVT ON DAVT.Value = DAVSI.DisplayValue AND DAVT.RecId = DAV.EntityInstance AND DAVT.DataAreaId = 'abc'
      LEFT JOIN DIMATTRIBUTEOMBUSINESSUNIT DABT ON DABT.Value = DAVSI.DisplayValue AND DABT.RecId = DAV.EntityInstance
      WHERE
      INVOICEDATE BETWEEN '2017-01-01' AND '2018-01-01'
      AND vs.QTY>0
      )V
   --WHERE DIMENSION_VALUE Like '%NDIS%'
   GROUP BY DIMENSION_VALUE,DIMENSION_DESCRIPTION,DIMENSION_TYPE
   --ORDER BY DIMENSION_DESCRIPTION
   UNION ALL
   SELECT
      CONVERT(INT,SUM(TOTALPURCHASES)) AS PURCHASEVALUE,
      CONVERT(INT,SUM(PURCHASERETURNS)) AS PURCHASERETURNS,
      --CONVERT(VARCHAR, CAST(SUM(TOTALPURCHASES) AS MONEY), 1) AS PURCHASEVALUE_C,
      --CONVERT(INT,SUM(QTY)) AS SALESQTY,
      DIMENSION_VALUE,
      DIMENSION_DESCRIPTION,
      DIMENSION_TYPE,
      DAC,
      DAC2
   FROM
      (SELECT
         vs.RECID,
         vs.PURCHID,
         vs.ITEMID,
         vs.LINENUM,
         vs.DEFAULTDIMENSION,
         DA.NAME AS DIMENSION_TYPE,
         DACT.DataAreaId AS DAC,
         DAVT.DataAreaId AS DAC2,
         DAVSI.DISPLAYVALUE DIMENSION_VALUE,
         CASE
            WHEN DA.NAME='Customer' THEN DACT.NAME
            WHEN DA.NAME='Vendor' THEN DAVT.NAME
            WHEN DA.NAME='BusinessUnit' THEN DABT.NAME
            ELSE DFT.DESCRIPTION
         END AS DIMENSION_DESCRIPTION,
         0 AS TOTALPURCHASES,
         vs.LINEAMOUNTMST AS PURCHASERETURNS,
         vs.QTY
      FROM
         VENDINVOICETRANS Vs
      LEFT JOIN DimensionAttributeValueSetItem DAVSI ON vs.DEFAULTDIMENSION=DAVSI.DIMENSIONATTRIBUTEVALUESET
      LEFT JOIN DIMENSIONATTRIBUTEVALUE DAV ON DAVSI.DIMENSIONATTRIBUTEVALUE=DAV.RECID
      LEFT JOIN DimensionAttribute DA ON DAV.DIMENSIONATTRIBUTE=DA.RECID
      LEFT JOIN DimensionAttributeDirCategory DADC ON DADC.DIMENSIONATTRIBUTE=DA.RECID
      LEFT JOIN DimensionFinancialTag DFT ON DFT.FINANCIALTAGCATEGORY=DADC.DIRCATEGORY AND DFT.RECID=DAV.ENTITYINSTANCE
      LEFT JOIN DIMATTRIBUTECUSTTABLE DACT ON DACT.Value = DAVSI.DisplayValue AND DACT.RecId = DAV.EntityInstance AND DACT.DataAreaId = 'EF'
      LEFT JOIN DIMATTRIBUTEVENDTABLE DAVT ON DAVT.Value = DAVSI.DisplayValue AND DAVT.RecId = DAV.EntityInstance AND DAVT.DataAreaId = 'EF'
      LEFT JOIN DIMATTRIBUTEOMBUSINESSUNIT DABT ON DABT.Value = DAVSI.DisplayValue AND DABT.RecId = DAV.EntityInstance
      WHERE
      INVOICEDATE BETWEEN '2017-01-01' AND '2019-01-01'
      AND vs.QTY<0
      )V
   --WHERE DIMENSION_VALUE Like '%NDIS%'
   GROUP BY DIMENSION_VALUE,DIMENSION_DESCRIPTION,DIMENSION_TYPE
   )VF
   GROUP BY VF.DIMENSION_VALUE,VF.DIMENSION_DESCRIPTION,VF.DIMENSION_TYPE, VF.DAC, VF.DAC2
   ORDER BY VF.DIMENSION_DESCRIPTION

Open in new window

Msg 8120, Level 16, State 1, Line 18
Column 'V.DAC' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Not too sure where the issue is any assistance is welcome.

Thank you. 



ASKER CERTIFIED SOLUTION
Partha Mandayam
Technical Director

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros