January 11, 2007 at 1:25 pm
SETUP: I have one local table (MAIN_TBL) and one linked server AS (rmt). I need dates from the remote table (rmt) to go with the list of order numbers already in MAIN_TBL. I'm concerned about how linking a local table with a remote table will work & perform. Am I going about this correctly? So thats the main goal. One other problem I haven't gotten past...the remote table also contains order versions numbered 0,1,2,3,4,etc. So one order# has say three rows version 0,1,2 each with a different date. I only need the MAX version number data pulled which will be the latest date.
UPDATE MAIN_TBL
SET MAIN_TBL.Date_Rtn = rmt.CUS_DATE
FROM (
SELECT LnkS.ORDER_VER, LnkS.CUS_DATE, LnkS.ORD_NBR
FROM LinkServer..DB.TableName AS LnkS
INNER JOIN MAIN_TBL AS mt ON mt.ordernum = LnkS.ORD_NBR
WHERE LnkS.CUS_DATE IS NOT NULL
) AS rmt
INNER JOIN MAIN_TBL ON MAIN_TBL.ordernum = rmt.ORD_NBR
Any help would be appreciated....
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
January 11, 2007 at 2:02 pm
Can you give a bit more in the way of example data? For instance how do you know what the ordernumber is? (which btw I'd sugest not using # in the column name as other applications might have problems with it ont he client side down the road) Is the Order# listed in another column? Can you use an Order BY to get the data you need, or is inbedded in the lnks.Order# column and have to substring it out or something?
Thanks.
-Luke.
January 11, 2007 at 2:15 pm
The order numbers are listed in the MAIN_TBL. Thats why I INNER JOIN it with the remote table so the subquery only pulls dates for those listed in the MAIN_TBL. At least thats my thinking...
Don't worry about the #....I just used it here for shorthand...lol
Thx for your input...
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
January 11, 2007 at 2:24 pm
so you don't need to worry about versoin now? or will the MAX(orderDate)always give you the correct date? IF so how about this...
UPDATE MAIN_TBL
SET MAIN_TBL.Date_Rtn = MAX(LnkS.CUS_DATE)
FROM LinkServer..DB.TableName LnkS
INNER JOIN MAIN_TBL
ON MAIN_TBL.ordernum = LnkS.ORD_NBR
AND LnkS.CUS_DATE IS NOT NULL
January 11, 2007 at 2:25 pm
I believe this will do it, but not sure how it will perform:
UPDATE M
SET Date_Rtn = rmt.CUS_DATE
FROM MAIN_TBL As M
INNER JOIN
-- Derived table
(
SELECT ORD_NBR, CUS_DATE
FROM LinkServer..DB.TableName AS L1
INNER JOIN
-- 2nd derived table - get most recent version
(
SELECT ORD_NBR, Max(ORDER_VER) As MaxVersion
FROM LinkServer..DB.TableName
GROUP BY ORD_NBR
) L2
ON (L1.ORD_NBR = L2.ORD_NBR AND
L1.ORDER_VER = L2.MaxVersion)
) rmt
ON (M.OrderNum = rmt.ORD_NBR)
January 11, 2007 at 3:04 pm
Thanks PW....with a couple a alias added, it appeared to have worked but it'll take some time to verify it worked correctly...thx for your input...its another option to play with...
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply