January 18, 2007 at 12:26 pm
Hi ,
i need help on this
There are three tables
GE_Transaction
GE_Claim
GE_Reserve
Column WaiverofPremium exists only in GE_Reserve table
Column LossDate exists only in GE_Claim table
---------------------------------------------------------------------------------
error message
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'WaiverofPremium'.
Server: Msg 207, Level 16, State 1, Line 14
Invalid column name 'LossDate'.
Server: Msg 207, Level 16, State 1, Line 14
Invalid column name 'LossDate'.
Server: Msg 207, Level 16, State 1, Line 14
Invalid column name 'LossDate'.
Query
-----
Declare @P2 int
Declare @SH int
Declare @CU int
Declare @FA int
Declare @WPR_R float
SELECT @P2=count(*) FROM GE_Transaction WHERE ProductType='P2'
SELECT @SH=count(*) FROM GE_Transaction WHERE ProductType='SH'
SELECT @CU=count(*) FROM GE_Transaction WHERE ProductType='CU'
SELECT @FA=count(*) FROM GE_Transaction WHERE ProductType='FA'
SELECT @WPR_R = Sum(Convert(float,WaiverofPremium))* Case
When ([ProductType] = 'P2' or [ProductType] = 'SH') AND [EffectiveDate] <= '6/30/2002' AND [LossDate] <= '06/30/2002' THEN .50
When ([ProductType] = 'CU' or [ProductType] = 'FA') AND [EffectiveDate] <= '6/30/2002' AND [LossDate] <= '06/30/2002' THEN .80
When ([ProductType] = 'CU' or [ProductType] = 'FA') AND [EffectiveDate] <= '6/30/2002' AND [LossDate] > '06/30/2002' THEN .40
Else .25
end
FROM GE_Transaction
January 18, 2007 at 12:31 pm
NOTE :
Policy Number, tablecode are the two columns which exists in all the three TABLES
January 18, 2007 at 12:47 pm
Your last SELECT statement is trying to select WaiverOfPremium and LossDate from GE_Transaction.
You need to join to the other two tables in order to make those columns available. If you provide all the column names to those tables I could help you further.
January 18, 2007 at 12:51 pm
It looks like ProductType is what joins together GE_Transaction and the other two tables. You could do something like this:
SELECT @WPR_R = Sum(Convert(float,r.WaiverofPremium))* Case
When ([ProductType] = 'P2' or [t.ProductType] = 'SH') AND [t.EffectiveDate] <= '6/30/2002' AND [c.LossDate] <= '06/30/2002' THEN .50
When ([ProductType] = 'CU' or [t.ProductType] = 'FA') AND [t.EffectiveDate] <= '6/30/2002' AND [c.LossDate] <= '06/30/2002' THEN .80
When ([ProductType] = 'CU' or [t.ProductType] = 'FA') AND [t.EffectiveDate] '06/30/2002' THEN .40
Else .25
end
JOIN GE_Reserve r ON r.ProductType = t.ProductType
JOIN GE_Claim c ON c.ProductType = t.ProductType
FROM GE_Transaction t
January 18, 2007 at 1:03 pm
JOIN has to be ON policynumber AS IT EXISTS IN ALL THREE TABLES and not on product type because product type exists only in GE_Transaction.
STILL I GET THIS ERROR MESSAGE
Server: Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'JOIN'.
Declare @P2 int
Declare @SH int
Declare @CU int
Declare @FA int
Declare @WPR_R float
SELECT @P2=count(*) FROM GE_Transaction WHERE ProductType='P2'
SELECT @SH=count(*) FROM GE_Transaction WHERE ProductType='SH'
SELECT @CU=count(*) FROM GE_Transaction WHERE ProductType='CU'
SELECT @FA=count(*) FROM GE_Transaction WHERE ProductType='FA'
SELECT @WPR_R = Sum(Convert(float,r.WaiverofPremium))* Case
When ([ProductType] = 'P2' or [t.ProductType] = 'SH') AND [t.EffectiveDate] <= '6/30/2002' AND [c.LossDate] <= '06/30/2002' THEN .50
When ([ProductType] = 'CU' or [t.ProductType] = 'FA') AND [t.EffectiveDate] <= '6/30/2002' AND [c.LossDate] <= '06/30/2002' THEN .80
When ([ProductType] = 'CU' or [t.ProductType] = 'FA') AND [t.EffectiveDate] <= '6/30/2002' AND [c.LossDate] > '06/30/2002' THEN .40
Else .25
end
JOIN GE_Reserve r ON r.PolicyNumber = t.PolicyNumber
JOIN GE_Claim c ON c.PolicyNumber = t.PolicyNumber
FROM GE_Transaction t
January 18, 2007 at 1:11 pm
Best, you've been working on these GE Policy tables for over 6 weeks. All your 90+ postings to date are about these same tables & queries.
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=328266
You've had 'invalid column name' errors before and time has been spent explaining this to you.
Now you've placed your JOINs before your FROM, as you've done in the past.
At a certain point you'll exceed the goodwill of the forum regulars, if it becomes apparent that you aren't heeding advice given or learning anything from the time invested in responding to you. It also seems as if you're using the forums as a way to get your code written for you, instead of using them as a resource to improve your own SQL skills.
January 18, 2007 at 1:22 pm
Censored by the editor: This post was inappropriate and offensive to others who have posted in this thread.
January 18, 2007 at 1:33 pm
Your JOIN clauses must come after your FROM clause, as PW mentioned. And yes, if policy number is what links the three tables, then join on that, but the bottom line is you do need to join to those other two tables in order to use the columns in them obviously. I don't have your table structure here in front of me so I was guessing.
I would also say that, this stuff isn't for everyone. If you find yourself struggling with basic problems after working with it for a long time, you might want to reevaluate your job/career.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply