how to convert the below subquery into join..single update statment with joins

Question

Post reply

how to convert the below subquery into join..single update statment with joins

surendiran.balasubramanian

SSC Rookie

Points: 49
More actions
April 9, 2013 at 1:18 am

#291932

here is the complete query..the ulitmate aim is the update the claim table...
but it should be only one statement without any subquery only joins are allowed becuase as i said am going to run this in an appliance which wont support subquery
DECLARE @DecWdrwn as TABLE(CtryId smallint, CmId int, DecWdrwnDt int);
WITH s AS
(
SELECT Ctryid,CmId,Dt,
ISNULL((SELECT max(CmHistDtTmId) FROM ClaimHistory l WHERE St = 3 AND l.Ctryid = c.Ctryid AND l.CmId = c.CmId), 0) MaxDec,
ISNULL((SELECT max(CmHistDtTmId) FROM ClaimHistory l WHERE St = 7 AND l.Ctryid = c.Ctryid AND l.CmId = c.CmId), 0) MaxSet
FROM ClaimHistory c
WHERE St =3)
INSERT INTO @DecWdrwn
SELECT CtryId, CmId, Max(Dt) DecDt FROM s
WHERE MaxSet > MaxDec
GROUP BY CtryId,CmId
your response is much appreciated...
UPDATE Claims
SET CmDclnWdwnDt = (SELECT DecWdrwnDt FROM @DecWdrwn d WHERE d.CmId = Claims.CmId AND d.CtryId = Claims.CtryId)
WHERE EXISTS
(SELECT * FROM @DecWdrwn d WHERE d.CmId = Claims.CmId AND d.CtryId = Claims.CtryId)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DECLARE @DecWdrwn as TABLE(CtryId smallint, CmId int, DecWdrwnDt int);

WITH s AS

(

SELECT

c.Ctryid, c.CmId,

-- Dt, not used

MaxDec = ISNULL(MAX(c.CmHistDtTmId),0),

x.MaxSet

FROM ClaimHistory c

OUTER APPLY (

SELECT MaxSet = ISNULL(MAX(l.CmHistDtTmId),0)

FROM ClaimHistory l

WHERE l.St = 7

AND l.Ctryid = c.Ctryid

AND l.CmId = c.CmId

) x

WHERE c.St = 3

GROUP BY c.CtryId, c.CmId

HAVING x.MaxSet > ISNULL(MAX(c.CmHistDtTmId),0)

)

UPDATE c

SET CmDclnWdwnDt = s.DecWdrwnDt

FROM Claims c

INNER JOIN s ON s.CmId = c.CmId AND s.CtryId = c.CtryId

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

surendiran.balasubramanian SSC Rookie Points: 49 More actions · Answer 2

I cant use CTE..

because the datawarehouse applicance wont support that....

all i need is single update statement with the required join condition

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

surendiran.balasubramanian (4/9/2013)
I cant use CTE..
because the datawarehouse applicance wont support that....
all i need is single update statement with the required join condition