Help from experienced Guru with the temp table

Question

Help from experienced Guru with the temp table

Best-330874

SSCrazy

Points: 2350
More actions
December 5, 2006 at 6:10 pm

#173631

Hi,
      I need help on the following code.if you look at the code it updates the following table GE_ReservesFile
What i need is i want to apply the same logic USED in this proc in the QUERY BELOW and the records to
be inserted in the Temp Table
-- Update Reserves From Claim File
Update GE_ReservesFile
Set ClaimNumber = c.ClaimNumber,
LastName = c.LastName,
FirstName = c.FirstName,
LossDate = c.LossDate,
InClaimFile = 1
From GE_Claim c Inner Join GE_ReservesFile r
On c.PolicyNumber = r.PolicyNumber And
c.TableCode = r.TableCode And c.SeriesCode = r.SeriesCode
And c.IssueAge = r.IssueAge And c.Gender = r.Gender
And c.EffectiveDate = r.EffectiveDate
Where c.LossDate in
(Select Max(LossDate) from GE_Claim
Where PolicyNumber = c.PolicyNumber And
TableCode = c.TableCode And SeriesCode = c.SeriesCode
Group By PolicyNumber, TableCode, SeriesCode)
IF(@@error!=0)
ROLLBACK TRAN
ELSE
BEGIN
-- Update Reserves From Master Claim
Update GE_ReservesFile
Set ClaimNumber = m.ClaimNumber,
LastName = m.LastName,
FirstName = m.FirstName,
LossDate = m.LossDate
From GE_Master_Claim m Inner Join GE_ReservesFile r
On m.PolicyNumber = r.PolicyNumber And
m.TableCode = r.TableCode And m.SeriesCode = r.SeriesCode
And m.IssueAge = r.IssueAge And m.Gender = r.Gender
And m.EffectiveDate = r.EffectiveDate
Where r.ClaimNumber Is Null And m.LossDate in
(Select Max(LossDate) from GE_Master_Claim
Where PolicyNumber = m.PolicyNumber And
TableCode = m.TableCode And SeriesCode = m.SeriesCode
Group By PolicyNumber, TableCode, SeriesCode)
IF(@@error!=0)
ROLLBACK TRAN
ELSE
BEGIN
-- Get Recaptured Claims
Update GE_ReservesFile
Set Recaptured = 'Recaptured'
Where LossDate Is Not Null
And DateAdd(yyyy, 10, EffectiveDate) < LossDate
IF(@@error!=0)
ROLLBACK TRAN
ELSE
BEGIN
Update GE_ReservesFile
Set Recaptured = 'Reinsured'
Where LossDate Is Not Null
And Recaptured Is Null
IF(@@error!=0)
ROLLBACK TRAN
-----------------------------------------------------------------------------------------------------
QUERY IN WHICH CHANGE IS REQUIRED
SELECT
    a.PolicyNumber,
    a.TableCode,
    a.SeriesCode,
    a.LossDate,
    a.EffectiveDate,
    SUM(b.WaiverofPremium) AS SumPremium
FROM tableA a
    INNER JOIN tableB b
    ON a.PolicyNumber = b.PolicyNumber AND a.TableCode = b.TableCode AND a.SeriesCode = b.SeriesCode
WHERE DATEADD(yyyy, 10, EffectiveDate) < LossDate
GROUP BY     a.PolicyNumber,
    a.TableCode,
    a.SeriesCode,
    a.LossDate,
    a.EffectiveDate

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

so why not do SELECT * INTO #TEMPGE_ReservesFile FROM GE_ReservesFile.then change the name in your update statments to the temp table just created: #TEMPGE_ReservesFile

why do you want to do it to a temp table instead? the above idea would work, but if the original table is huge, your tempdb is gonna grow just as large. might not be the best solution.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

i cannot use or in otherwords dont have access to that table GE_ReserveFile any more so can you show me how to deal with the situation.

Sergiy SSC Guru Points: 110192 More actions · Answer 3

Update r --~~GE_ReservesFile~~

Set ClaimNumber = c.ClaimNumber,

LastName = c.LastName,

FirstName = c.FirstName,

LossDate = c.LossDate,

InClaimFile = 1

From GE_Claim c

Inner Join GE_ReservesFile r ...

Utherwise you update not the same instance of the table!

_____________
Code for TallyGenerator

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

i undertand exactly what are you saying but just need to ask you

which table i have to do the inner join ? because i cannot make inner join on GE_ReservesFile as i told you earlier.

Update r --~~GE_ReservesFile~~

Set ClaimNumber = c.ClaimNumber,

LastName = c.LastName,

FirstName = c.FirstName,

LossDate = c.LossDate,

InClaimFile = 1

From GE_Claim c

Inner Join GE_ReservesFile r ...

Utherwise you update not the same instance of the table!

Lowell SSC Guru Points: 323482 More actions · Answer 5

if you do not have SELECT privileges to the table, you cannot get the data into a temp table for manipulation. there is no work around.

if you only have SELECT Priviledges, than you can do something with the temp table that some are suggesting.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sergiy SSC Guru Points: 110192 More actions · Answer 6

If you cannot make inner join to the table where all your data stored then what do you want to work with?

_____________
Code for TallyGenerator

Best-330874 SSCrazy Points: 2350 More actions · Answer 7

instead of making an inner join on table GE_ReserveFile can i create a temp table with the same columns as that of GE_Reservefile and populate it and then i can do the inner join on that temp table what i mean is like this

first i create temp table and then populate it and then do the update

Update r

Set ClaimNumber = c.ClaimNumber,

LastName = c.LastName,

FirstName = c.FirstName,

LossDate = c.LossDate,

InClaimFile = 1

From GE_Claim c

Inner Join r ...

Sergiy SSC Guru Points: 110192 More actions · Answer 8

Populate it with what?

_____________
Code for TallyGenerator