December 5, 2006 at 6:10 pm
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
December 5, 2006 at 6:36 pm
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
December 5, 2006 at 6:45 pm
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.
December 5, 2006 at 7:00 pm
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
December 5, 2006 at 7:44 pm
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!
December 5, 2006 at 8:07 pm
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
December 5, 2006 at 9:01 pm
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
December 5, 2006 at 9:15 pm
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 ...
December 5, 2006 at 9:53 pm
Populate it with what?
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply