November 23, 2008 at 8:20 pm
I have a Query in SQL Server 2000 as follow that I need to migrate to SQL server 2005 . This query is having multiple outer joins. Please help me to migrate this query to SQL server 2005.
Select A.WebRefNum, L.LVDesc [Legal Vehicle], A.CCYCode [CCY], A.CutOffTime [CutOff Time], Convert(Varchar(12),A.ValueDate,113) [Value Date], A.Rep1 [Rep1], A.Rep2 [Rep2], A.Rep3 [Rep3],A.TrestelCashFlow [Trestel CashFlow] , A.IntellectCashFlow [Intellect CashFlow],Max(E.LastUpdatedOn) [Edited On], E.LastUpdatedBy [Edited By],Max(V.LastUpdatedOn) [Verified On], V.LastUpdatedBy [Verified By],Max(X.LastUpdatedOn) [Exported On], X.LastUpdatedBy [Exported By], Max(A.LastUpdatedOn) [Last Updated On],B.DisplayOrder
From AUDITTRAIL A, AUDITTRAIL E, AUDITTRAIL V, AUDITTRAIL X, LEGALVEHICLE L, USERLEGALVEHICLE U,BRANCH B WITH (NOLOCK)
Where A.LVCode = L.LVCode
And L.LVCode = U.LVCode
And A.WebRefNum *= E.WebRefNum
And A.WebRefNum *= V.WebRefNum
And A.WebRefNum *= X.WebRefNum
And B.BranchCode=L.BranchCode
And A.LastUpdatedOn = (Select max(LastUpdatedOn) From AUDITTRAIL AU Where A.WebRefNum = AU.WebRefNum)
And E.LastAction='EDIT'
And V.LastAction='VERIFY'
And X.LastAction='EXPORT'
And L.LVCode = ISNULL(1,L.LVCode)
And A.BusinessDate = '20071102'
And U.UserId = 'aj56756'
And A.CutOfftime = ISNULL('07:00',A.CutOffTime)
Group by A.WebRefNum, A.CcyCode, A.CutOffTime, A.ValueDate, A.LVCode, L.LVDesc, A.BusinessDate, A.Rep1, A.Rep2, A.Rep3, E.LastUpdatedBy, V.LastUpdatedBy, X.LastUpdatedBy,B.DisplayOrder,
A.TrestelCashFlow, A.IntellectCashFlow
Order By B.DisplayOrder, A.LVCode, A.CutOffTime, A.CcyCode
=======================================
[font="Tahoma"]"Dont let someone be priority in your life if you are simply option for someone."[/font]
November 23, 2008 at 11:31 pm
The query should work in SQL 2005, let me if it does not work
November 23, 2008 at 11:32 pm
Here is my untested rewrite of your query. As rewritten, this query will also work on SQL Server 2000. I could have written the derived table as a CTE and used it just just like a table in the WHERE clause.
Select
A.WebRefNum,
L.LVDesc [Legal Vehicle],
A.CCYCode [CCY],
A.CutOffTime [CutOff Time],
Convert(Varchar(12),A.ValueDate,113) [Value Date],
A.Rep1 [Rep1],
A.Rep2 [Rep2],
A.Rep3 [Rep3],
A.TrestelCashFlow [Trestel CashFlow],
A.IntellectCashFlow [Intellect CashFlow],
Max(E.LastUpdatedOn) [Edited On],
E.LastUpdatedBy [Edited By],
Max(V.LastUpdatedOn) [Verified On],
V.LastUpdatedBy [Verified By],
Max(X.LastUpdatedOn) [Exported On],
X.LastUpdatedBy [Exported By],
Max(A.LastUpdatedOn) [Last Updated On],
B.DisplayOrder
From
AUDITTRAIL A
INNER JOIN (SELECT
at.WebRefNum,
MAX(at.LastUpdatedOn) as LastUpdatedOn
FROM
AUDITTRAIL at
GROUP BY
at.WebRefNum) AU
on (A.WebRefNum = AU.WebRefNum)
INNER JOIN LEGALVEHICLE L
on (A.LVCode = L.LVCode)
INNER JOIN USERLEGALVEHICLE U
on (L.LVCode = U.LVCode)
INNER JOIN BRANCH B
on (B.BranchCode = L.BranchCode)
LEFT OUTER JOIN AUDITTRAIL E
on (A.WebRefNum = E.WebRefNum)
LEFT OUTER JOIN AUDITTRAIL V
on (A.WebRefNum = V.WebRefNum)
LEFT OUTER JOIN AUDITTRAIL X
on (A.WebRefNum = X.WebRefNum)
Where
A.LastUpdatedOn = AU.LastUpdatedOn -- corrected code here
And E.LastAction='EDIT'
And V.LastAction='VERIFY'
And X.LastAction='EXPORT'
And L.LVCode = ISNULL(1,L.LVCode)
And A.BusinessDate = '20071102'
And U.UserId = 'aj56756'
And A.CutOfftime = ISNULL('07:00',A.CutOffTime)
Group by
A.WebRefNum,
A.CcyCode,
A.CutOffTime,
A.ValueDate,
A.LVCode,
L.LVDesc,
A.BusinessDate,
A.Rep1,
A.Rep2,
A.Rep3,
E.LastUpdatedBy,
V.LastUpdatedBy,
X.LastUpdatedBy,
B.DisplayOrder,
A.TrestelCashFlow,
A.IntellectCashFlow
Order By
B.DisplayOrder,
A.LVCode,
A.CutOffTime,
A.CcyCode
November 23, 2008 at 11:34 pm
kevin van (11/23/2008)
The query should work in SQL 2005, let me if it does not work
If the database is still in compatibility mode 80, yes, but if the database has been upgraded to compatibility mode 90, the following will fail:
And A.WebRefNum *= E.WebRefNum
And A.WebRefNum *= V.WebRefNum
And A.WebRefNum *= X.WebRefNum
November 23, 2008 at 11:46 pm
Please note, I corrected the code in my post above. I didn't quite cut and paste correctly while rewriting it.
Sorry.
November 25, 2008 at 4:41 am
Hi Everyone,
Thanks for help. Actually I tried for it little bit differently and it seems that its working fine. New query is as follows for reference.
SELECT A.WebRefNum, L.LVDesc [Legal Vehicle], A.CCYCode [CCY], A.CutOffTime [CutOff Time],
Convert(Varchar(12),A.ValueDate,113) [Value Date], A.Rep1 [Rep1], A.Rep2 [Rep2], A.Rep3 [Rep3],
A.TrestelCashFlow [Trestel CashFlow] , A.IntellectCashFlow [Intellect CashFlow],
Max(E.LastUpdatedOn) [Edited On], E.LastUpdatedBy [Edited By],
Max(V.LastUpdatedOn) [Verified On], V.LastUpdatedBy [Verified By],
Max(X.LastUpdatedOn) [Exported On], X.LastUpdatedBy [Exported By], Max(A.LastUpdatedOn) [Last Updated On],B.DisplayOrder
FROM AUDITTRAIL A LEFT OUTER JOIN AUDITTRAIL E On A.WebRefNum = E.WebRefNum And E.LastAction='EDIT' LEFT OUTER JOIN AUDITTRAIL V on A.WebRefNum = V.WebRefNum And V.LastAction='VERIFY' LEFT OUTER JOIN AUDITTRAIL X on A.WebRefNum = X.WebRefNum And X.LastAction='EXPORT' INNER JOIN LEGALVEHICLE L ON A.LVCode = L.LVCode INNER JOIN USERLEGALVEHICLE U ON L.LVCode = U.LVCode INNER JOIN BRANCH B ON B.BranchCode=L.BranchCode
WHERE A.LastUpdatedOn = (Select max(LastUpdatedOn) From AUDITTRAIL AU Where A.WebRefNum = AU.WebRefNum) And A.BusinessDate = '20071102' And A.CutOfftime = ISNULL('07:00',A.CutOffTime) And L.LVCode = ISNULL(1,L.LVCode) AND U.UserId = 'aj56756'
GROUP BY A.WebRefNum, A.CcyCode, A.CutOffTime, A.ValueDate, A.LVCode, L.LVDesc, A.BusinessDate,
A.Rep1, A.Rep2, A.Rep3, E.LastUpdatedBy, V.LastUpdatedBy, X.LastUpdatedBy,B.DisplayOrder,
A.TrestelCashFlow, A.IntellectCashFlow
ORDER BY B.DisplayOrder, A.LVCode, A.CutOffTime, A.CcyCode
Let me know if anything seems wrong. Thanks all.
=======================================
[font="Tahoma"]"Dont let someone be priority in your life if you are simply option for someone."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply