June 6, 2011 at 11:47 am
I have the following which works with a slightly different query but I have a second query that's very similar but receive "Column 'FIN_DESPATCH_NOTES.DESPATCH_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I have this in my query:
CAST(MONTH(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(DAY(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(YEAR(DN.DESPATCH_DATE) AS VARCHAR(10)) AS DESPATCH_DATE
In a similar query the Group by that works I have:
CAST(MONTH(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(DAY(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(YEAR(DN.DESPATCH_DATE) AS VARCHAR(10))
Then I want to group by this as the column contains hh:mm:ss which I want to exclude.
Am I missing something stupid? I don't understand why this works in one query but not another. Please help.
June 6, 2011 at 11:58 am
I'm not sure I get your question. Could you copy your query so I can take a look?
June 6, 2011 at 12:02 pm
Forgive the commented out lines. I'm still tweaking it. Query where the Cast under Group By doesn't work followed by the one that does work.
SELECT
SOIL.COMPANY_CODE
, SOIL.ORDER_NUMBER
, SOIL.ORDER_LINE_NUMBER
, SOIL.ORDER_SEQUENCE AS ORDER_LINE_SEQUENCE
, SOIL.CUSTOMER_NUMBER
, SOIL.DELIVERY_ADDRESS_CODE
, CA.CUSTOMER_NAME AS SHIP_NAME
, SOIL.PART_CODE
, PM.PART_DESC_1
, SOIL.SOP_NOMINATED_UNIT
, 0 AS ORDER_LINE_SEQUENCE_QTY
, 0 AS ORDER_LINE_SEQUENCE_DOLLARS
, 0 AS SHIP_QTY
, 0 AS SHIP_DOLLARS
, SUM(SILQ.INVOICE_QUANTITY) AS INVOICE_QTY
--JOIN ON UNIT_OF_MEASURE WITH SOP_NOMINATED_UNIT
, SOIL.SOP_INVOICE_TOTAL_CURRENCY AS INVOICE_DOLLARS
, DN.ORDER_DATE
, '' AS ORDER_DM
--, 0 AS SHIP_TIME
, 0 AS LEAD_TIME
, 0 AS SHIP_LEAD_TIME
, DN.PROMISED_DATE
, DN.REQUIRED_DATE
, '' AS SHIP_DM
, DN.DESPATCH_DATE
, SOI.INVOICE_DATE
, CAST(YEAR(SOI.INVOICE_DATE) AS VARCHAR(4))+CAST(MONTH(SOI.INVOICE_DATE) AS VARCHAR(2)) AS INVOICE_DM
--, '' AS REASON_DESC
, SOIL.DESPATCH_NOTE
, SOIL.DESPATCH_LINE
, SOIL.INVOICE_NUMBER
, SOIL.INVOICE_LINE_NUMBER
, 0 AS INVOICE_LINE_SEQUENCE
FROM
FIN_SALES_ORDER_INVOICE_LINES SOIL
INNER JOIN FIN_CUSTOMER_ADDRESSES CA
ON
CA.COMPANY_CODE=SOIL.COMPANY_CODE
AND CA.DIVISION=SOIL.DIVISION
AND CA.CUSTOMER_NUMBER=SOIL.CUSTOMER_NUMBER
AND CA.ADDRESS_CODE=SOIL.DELIVERY_ADDRESS_CODE
INNER JOIN FIN_PRODUCT_MASTER PM
ON
PM.COMPANY_CODE=SOIL.COMPANY_CODE
AND PM.PART_CODE=SOIL.PART_CODE
INNER JOIN FIN_DESPATCH_NOTES DN
ON DN.COMPANY_CODE=SOIL.COMPANY_CODE
AND DN.DIVISION=SOIL.DIVISION
AND DN.DESPATCH_NOTE=SOIL.DESPATCH_NOTE
AND DN.DESPATCH_LINE=SOIL.DESPATCH_LINE
INNER JOIN FIN_SALES_ORDER_INVOICES SOI
ON
SOI.COMPANY_CODE=SOIL.COMPANY_CODE
AND SOI.DIVISION=SOIL.DIVISION
AND SOI.INVOICE_NUMBER=SOIL.INVOICE_NUMBER
INNER JOIN FIN_SALES_INVOICE_LINE_QTYS SILQ
ON
SILQ.COMPANY_CODE=SOIL.COMPANY_CODE
AND SILQ.DIVISION=SOIL.DIVISION
AND SILQ.INVOICE_NUMBER=SOIL.INVOICE_NUMBER
AND SILQ.INVOICE_LINE_NUMBER=SOIL.INVOICE_LINE_NUMBER
AND SILQ.UNIT_OF_MEASURE=SOIL.SOP_NOMINATED_UNIT
INNER JOIN FIN_SALES_INVOICE_LINE_DETAIL_QTYS SILDQ
ON
SILDQ.COMPANY_CODE=SILQ.COMPANY_CODE
AND SILDQ.COMPANY_CODE=SILQ.COMPANY_CODE
AND SILDQ.DIVISION=SILQ.DIVISION
AND SILDQ.INVOICE_NUMBER=SILQ.INVOICE_NUMBER
AND SILDQ.INVOICE_LINE_NUMBER=SILQ.INVOICE_LINE_NUMBER
AND SILDQ.UNIT_OF_MEASURE=SILQ.UNIT_OF_MEASURE
--WHERE DN.DESPATCH_DATE>='05/01/11' AND DN.DESPATCH_DATE<='05/31/11'+' 23:59'
WHERE (SOIL.ORDER_NUMBER='116178' OR SOIL.ORDER_NUMBER='115896' OR SOIL.ORDER_NUMBER='116594')
AND SOIL.COMPANY_CODE=@COMPANY_CODE
AND SOIL.DIVISION=@DIVISION
AND (DN.DESPATCH_DATE>=@START_DATE AND DN.DESPATCH_DATE<=@END_DATE+' 23:59')
AND (SOIL.CUSTOMER_NUMBER>=@START_CUST AND SOIL.CUSTOMER_NUMBER<=@END_CUST)
GROUP BY
SOIL.COMPANY_CODE
, SOIL.ORDER_NUMBER
, SOIL.ORDER_LINE_NUMBER
, SOIL.CUSTOMER_NUMBER
, SOIL.DELIVERY_ADDRESS_CODE
, CA.CUSTOMER_NAME
, SOIL.PART_CODE
, PM.PART_DESC_1
, SOIL.SOP_NOMINATED_UNIT
, SOIL.SOP_INVOICE_TOTAL_CURRENCY
, DN.ORDER_DATE
, DN.PROMISED_DATE
, DN.REQUIRED_DATE
, DN.DESPATCH_DATE
, SOI.INVOICE_DATE
, SOIL.DESPATCH_NOTE
, SOIL.DESPATCH_LINE
, SOIL.INVOICE_NUMBER
, SOIL.INVOICE_LINE_NUMBER
, SOIL.ORDER_SEQUENCE
Query with the Cast Group by that does work:
SELECT
SOH.COMPANY_CODE
, SOH.ORDER_NUMBER
, SOL.ORDER_LINE_NUMBER
, SOLDQ.SEQUENCE AS ORDER_LINE_SEQUENCE
, SOH.CUSTOMER_NUMBER
, SOH.DELIVERY_ADDRESS_CODE
, CA.CUSTOMER_NAME AS SHIP_NAME
, SOL.PART_CODE
, PM.PART_DESC_1
, SOL.SOP_NOMINATED_UNIT
, SOLDQ.ORDER_QUANTITY AS ORDER_LINE_SEQUENCE_QTY
, CASE WHEN SOL.SOP_ORDER_TOTAL_CURRENCY=0 THEN 0
ELSE (SOL.SOP_ORDER_TOTAL_CURRENCY)/(SOLQ.ORDER_QUANTITY)*(SOLDQ.ORDER_QUANTITY)
END AS ORDER_LINE_SEQUENCE_DOLLARS
, 0 AS SHIP_QTY
, 0 AS SHIP_DOLLARS
, 0 AS INVOICE_QTY
, 0 AS INVOICE_DOLLARS
, SOH.ORDER_DATE
, CAST(YEAR(SOH.ORDER_DATE) AS VARCHAR(4))+CAST(MONTH(SOH.ORDER_DATE) AS VARCHAR(2)) AS ORDER_DM
--, 0 AS SHIP_TIME
, 0 AS LEAD_TIME
, 0 AS SHIP_LEAD_TIME
, SOLD.PROMISED_DATE
, SOLDQ.REQUIRED_DATE
, '' AS SHIP_DM
, CAST(MONTH(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(DAY(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(YEAR(DN.DESPATCH_DATE) AS VARCHAR(10)) AS DESPATCH_DATE
, '' AS INVOICE_DATE
, '' AS INVOICE_DM
, DN.DESPATCH_NOTE
, 0 AS DESPATCH_LINE
, '' AS INVOICE_NUMBER
, '' AS INVOICE_LINE_NUMBER
, '' AS INVOICE_LINE_SEQUENCE
FROM
FIN_SALES_ORDER_HEADERS AS SOH
INNER JOIN
FIN_SALES_ORDER_LINES AS SOL
ON
SOL.COMPANY_CODE = SOH.COMPANY_CODE
AND SOL.DIVISION = SOH.DIVISION
AND SOL.ORDER_NUMBER = SOH.ORDER_NUMBER
INNER JOIN
FIN_SALES_ORDER_LINE_QTYS AS SOLQ
ON
SOLQ.COMPANY_CODE=SOL.COMPANY_CODE
AND SOLQ.DIVISION=SOL.DIVISION
AND SOLQ.ORDER_NUMBER=SOL.ORDER_NUMBER
AND SOLQ.ORDER_LINE_NUMBER=SOL.ORDER_LINE_NUMBER
AND SOLQ.UNIT_OF_MEASURE=SOL.SOP_NOMINATED_UNIT
INNER JOIN
FIN_SALES_ORDER_LINE_DETAIL_QTYS AS SOLDQ
ON
SOLDQ.COMPANY_CODE = SOL.COMPANY_CODE
AND SOLDQ.DIVISION = SOL.DIVISION
AND SOLDQ.ORDER_NUMBER = SOL.ORDER_NUMBER
AND SOLDQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
AND SOLDQ.UNIT_OF_MEASURE = SOL.SOP_NOMINATED_UNIT
INNER JOIN
FIN_SALES_ORDER_LINE_DETAILS AS SOLD
ON
SOLD.COMPANY_CODE=SOLDQ.COMPANY_CODE
AND SOLD.DIVISION=SOLDQ.DIVISION
AND SOLD.ORDER_NUMBER=SOLDQ.ORDER_NUMBER
AND SOLD.ORDER_LINE_NUMBER=SOLDQ.ORDER_LINE_NUMBER
AND SOLD.SEQUENCE=SOLDQ.SEQUENCE
INNER JOIN
FIN_PRODUCT_MASTER AS PM
ON
PM.COMPANY_CODE = SOL.COMPANY_CODE
AND PM.PART_CODE = SOL.PART_CODE
INNER JOIN
FIN_CUSTOMER_ADDRESSES AS CA
ON
CA.COMPANY_CODE = SOH.COMPANY_CODE
AND CA.DIVISION = SOH.DIVISION
AND CA.CUSTOMER_NUMBER = SOH.CUSTOMER_NUMBER
AND CA.ADDRESS_CODE = SOH.DELIVERY_ADDRESS_CODE
INNER JOIN
FIN_DESPATCH_NOTES AS DN
ON
DN.COMPANY_CODE = SOLDQ.COMPANY_CODE
AND DN.DIVISION = SOLDQ.DIVISION
AND DN.ORDER_NUMBER = SOLDQ.ORDER_NUMBER
AND DN.ORDER_LINE_NUMBER = SOLDQ.ORDER_LINE_NUMBER
AND DN.SEQUENCE = SOLDQ.SEQUENCE
WHERE (SOH.ORDER_NUMBER='116178' OR SOH.ORDER_NUMBER='115896' OR SOH.ORDER_NUMBER='116594')
AND SOH.COMPANY_CODE=@COMPANY_CODE
AND SOH.DIVISION=@DIVISION
AND (DN.DESPATCH_DATE>=@START_DATE AND DN.DESPATCH_DATE<=@END_DATE+' 23:59')
AND (SOH.CUSTOMER_NUMBER>=@START_CUST AND SOH.CUSTOMER_NUMBER<=@END_CUST)
--DESPATCH_NOTE='219897'
GROUP BY SOH.COMPANY_CODE
,SOH.ORDER_NUMBER
,SOL.ORDER_LINE_NUMBER
,SOLDQ.SEQUENCE
,SOH.CUSTOMER_NUMBER
,SOH.DELIVERY_ADDRESS_CODE
,CA.CUSTOMER_NAME
,SOL.PART_CODE
,PM.PART_DESC_1
,SOL.SOP_NOMINATED_UNIT
,SOLDQ.ORDER_QUANTITY
,SOL.SOP_ORDER_TOTAL_CURRENCY
,SOLQ.ORDER_QUANTITY
,SOH.ORDER_DATE
,SOLD.PROMISED_DATE
,SOLDQ.REQUIRED_DATE
,CAST(MONTH(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(DAY(DN.DESPATCH_DATE) AS VARCHAR(10))+'/'+CAST(YEAR(DN.DESPATCH_DATE) AS VARCHAR(10))
,DN.DESPATCH_NOTE
June 6, 2011 at 12:50 pm
Instead of using CAST try with CONVERT(varchar(10), DESPATCH_DATE,101). That might help
June 6, 2011 at 1:53 pm
The number of columns is not even close to the number of columns in your group by. You are going to spend some time to format this and then go back through it to figure out what you have missed. I would comment our your aggregates and grouping until you are getting the records you want. Then add back in the aggregates and copy and paste your select to make your grouping (remove any aggregates).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply