troubleshooting Question

SQL - Syntax Query

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
12 Comments1 Solution24 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

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. 



Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
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 1 Answer 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