February 19, 2018 at 7:59 am
UPDATE #TEMP_STP
Set Signature_Date = Top 1 D.Close_Date
From
[SOLICIT].[GTM_DOC] D, #TEMP_STP, [SOLICIT].[GTM_DOC_PROD] P
where
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND #TEMP_STP.[prodid] = P.[PROD_ID]
AND D.[SUPPLIER_ID] = #TEMP_STP.Site
order by D.Close_Date desc
Results in: Incorrect syntax near the keyword 'top'.
I need the Max Close date to go to #TEMP_STP.
Thoughts?
I think maybe it is because the ORDER by hasn't happend yet, but the internet says this is legal . . .
February 19, 2018 at 8:23 am
jeffshelix - Monday, February 19, 2018 7:59 AMUPDATE #TEMP_STP
Set Signature_Date = Top 1 D.Close_Date
From
[SOLICIT].[GTM_DOC] D, #TEMP_STP, [SOLICIT].[GTM_DOC_PROD] P
where
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND #TEMP_STP.[prodid] = P.[PROD_ID]
AND D.[SUPPLIER_ID] = #TEMP_STP.Site
order by D.Close_Date descResults in: Incorrect syntax near the keyword 'top'.
I need the Max Close date to go to #TEMP_STP.
Thoughts?
I think maybe it is because the ORDER by hasn't happend yet, but the internet says this is legal . . .
UPDATE t SET Signature_Date = x.Close_Date
FROM #TEMP_STP t
CROSS APPLY (
SELECT Close_Date = MAX(D.Close_Date)
FROM [SOLICIT].[GTM_DOC] D
INNER JOIN [SOLICIT].[GTM_DOC_PROD] P
ON p.[SOLICITATION_ID] = d.[SOLICITATION_ID]
WHERE P.[PROD_ID] = t.[prodid]
AND D.[SUPPLIER_ID] = t.[Site]
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 19, 2018 at 8:25 am
You can't use TOP in the SET clause. You can try something like this - you may have to include GTM_DOC_PROD in the subquery and tweak the PARTITION BY clause for it to work.
UPDATE t
SET Signature_Date = D.Close_Date
From (
SELECT
SUPPLIER_ID
, SOLICITATION_ID
, MAX(Close_Date) OVER (PARTITION BY SOLICITATION_ID) AS Close_Date
FROM SOLICIT.GTM_DOC
) D
JOIN SOLICIT.GTM_DOC_PROD P ON D.SOLICITATION_ID = P.SOLICITATION_ID
JOIN #TEMP_STP t ON t.prodid = P.PROD_ID AND D.SUPPLIER_ID = t.[Site]
John
February 19, 2018 at 8:37 am
SOLVED;
CHRISM@Work's solution was golden.
I really do appreciate the support.
February 20, 2018 at 2:02 am
jeffshelix - Monday, February 19, 2018 8:37 AMSOLVED;CHRISM@Work's solution was golden.
I really do appreciate the support.
Yep, Please use the MAX function in place of TOP...
February 20, 2018 at 2:06 am
subramaniam.chandrasekar - Tuesday, February 20, 2018 2:02 AMjeffshelix - Monday, February 19, 2018 8:37 AMSOLVED;CHRISM@Work's solution was golden.
I really do appreciate the support.
Yep, Please use the MAX function in place of TOP...
Why's that then?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 20, 2018 at 2:11 am
ChrisM@Work - Tuesday, February 20, 2018 2:06 AMsubramaniam.chandrasekar - Tuesday, February 20, 2018 2:02 AMjeffshelix - Monday, February 19, 2018 8:37 AMSOLVED;CHRISM@Work's solution was golden.
I really do appreciate the support.
Yep, Please use the MAX function in place of TOP...
Why's that then?
Again, Wrong ping. Should be at the top for the reply to the OP.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply