February 23, 2018 at 7:31 am
UPDATE t
SET response_sol_id = x.[solicitation_id]
FROM #temp_stp t
CROSS apply (SELECT P.solicitation_id
FROM [SOLICIT].[gtm_doc_prod] P
INNER JOIN [SOLICIT].[gtm_doc] d
ON p.[solicitation_id] = d.[solicitation_id]
WHERE P.[prod_id] = t.[prodid]
AND D.[supplier_id] = t.[site]
AND D.sent_date = (SELECT Max(D.sent_date)
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
FROM [SOLICIT].[gtm_doc])) x
This worked (it ran) before i added the
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
Lines. Now i get invalid Syntax near AND on the first list of new code, and Invalid Syntax on the last FROM.
Thoughts please?
February 23, 2018 at 7:33 am
I noticed the missing ) on Max Sent date line, but still get Syntax error new FROM.
thanks
February 23, 2018 at 7:37 am
What happens if you move the two lines you added between the penultimate and the final parentheses?
John
February 23, 2018 at 7:38 am
(1) Remove one of the )s from the last line.
(2) that last "FROM SOLICIT.gtm_doc" bit - does that belong to the SELECT MAX(D.sent_date) bit? because you've already got a FROM in the CROSS APPLY...
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 23, 2018 at 8:05 am
i agree that the list FROM probably doesnt belong, so now i have UPDATE t
SET response_sol_id = x.[solicitation_id]
FROM #temp_stp t
CROSS apply (SELECT P.solicitation_id
FROM [SOLICIT].[gtm_doc_prod] P
INNER JOIN [SOLICIT].[gtm_doc] d
ON p.[solicitation_id] = d.[solicitation_id]
WHERE P.[prod_id] = t.[prodid]
AND D.[supplier_id] = t.[site]
AND D.sent_date = (SELECT Max(D.sent_date))
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
) x
But now i get the
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggr.... error
February 23, 2018 at 8:10 am
Your subquery SELECT Max(D.sent_date)... needs a WHERE clause.
John
February 23, 2018 at 2:55 pm
UPDATE t
SET response_sol_id = x.[solicitation_id]
FROM #temp_stp t
CROSS apply (SELECT P.solicitation_id
FROM [SOLICIT].[gtm_doc_prod] P
INNER JOIN [SOLICIT].[gtm_doc] d
ON p.[solicitation_id] = d.[solicitation_id]
WHERE P.[prod_id] = t.[prodid]
AND D.[supplier_id] = t.[site]
AND [BLANKET_FROM_DATE] >= '20180101'
AND [BLANKET_FROM_DATE] < '20190101'
AND D.SUB_ORG = 'FORD'
AND D.sent_date = (SELECT Max(D.sent_date)
FROM [SOLICIT].[gtm_doc])) x
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply