Avatar of Zack
Zack
Flag for Australia

asked on 

SQL - Syntax Query

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. 



SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Zack

8/22/2022 - Mon