expertise in temp table
-
Hi ,
I need help with the temp table
ClaimNumber does not exists in GE_Reserve from where iam doing the rest of the inserts.
how can i handle ClaimNumber so iam able to do my inserts
iam getting the following error meassage
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ClaimNumber'.
drop table #tmp
create table #tmp
(PolicyNumber varchar(10)
, TableCode varchar(3)
, SeriesCode varchar(1)
, IssueAge tinyint
, Gender char
, EffectiveDate smalldatetime
, WaiverOfPremium decimal
, ClaimNumber VARCHAR(7)
)
Insert Into #tmp
Select PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, IssueDate, Sum(WaiverofPremium)AS WaiverofPremium,ClaimNumber
From GE_Reserve
Group By PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, IssueDate
-
If ClaimNumber does not exist in GE_Reserve, why would you have it in your select list?
Are you joining to another table so that you can insert claimnumber? If it doesn't exist in GE_Reserve, why do you need it in #tmp?
If you are insertign rows into #tmp and then adding ClaimNumber from another table you could do your insert like this,
INSERT INTO #tmp
SELECT PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, IssueDate, SUM(WaiverofPremium)AS WaiverofPremium, '' AS ClaimNumber
FROM GE_Reserve
GROUP BY PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, IssueDate
But if you are doing the insert like this and updating the claimnumber later why not use a Join? If it's not needed at all because it looks like you are doing an aggregate why not just remove that cloumn from #tmp and be done with it...
Hope that helps a bit.
-Luke.
-
Thanks a lot it works but can you tell me one more thing is there anything wrong with my update statement below with which i want to update my temp table
Update #tmp
Set ClaimNumber = c.ClaimNumber
From GE_Claim c Inner Join GE_Reserve 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
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)
-
It looks like you are referencing the same table more than once and you have a few columns that you haven't defined which table they come from. In your Group by you have PolicyNumber, TableCode, SeriesCode which table are they assiociated with?
I think that should point you in the right direction with perhaps some more useable error messages.
Also, I really think you should be able to to get this all in one insert statement. I could help you if you give me both table definitions (GE_Claim and GE_reserve) and some sample data to work with.
-
Thanks for your help
CREATE TABLE [GE_Claim] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Month_of_file] [datetime] NULL ,
[CompanyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[PolicyNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CoverageNumber] [tinyint] NULL ,
[ClaimSequence] [varchar] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CessionSequence] [tinyint] NULL ,
[ReinsuranceCompany] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReportingCompany] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[From_Date] [datetime] NULL ,
[To_Date] [datetime] NULL ,
[MonthReported] [smalldatetime] NULL ,
[ReinsuranceDuration] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CoverageDuration] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[EffectiveDate] [smalldatetime] NULL ,
[StateIssue] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[StateResident] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_AUTOFAC_SW] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[IssueType] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[TreatyNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReinsuranceType] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ClaimNumber] [varchar] (7) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ClaimStatus] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[LossDate] [datetime] NULL ,
[BenefitCeaseDate] [datetime] NULL ,
[CloseDate] [datetime] NULL ,
[PayStartDate] [datetime] NULL ,
[PayCloseDate] [datetime] NULL ,
[U_BNFT_EXP_IND] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_BEN_EXP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Diagnosys] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Diagnosys2] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReinsuranceClaimAmount] [float] NULL ,
[TotalAmountPaid] [float] NULL ,
[U_REEASON_DESC] [varchar] (20) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[FirstName] [varchar] (15) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[MiddleInitial] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ClientID] [varchar] (15) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[DOB] [datetime] NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[PricingSex] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[IssueAge] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[DailyBenefitMaximum1] [int] NULL ,
[DailyBenefitMaximum] [int] NULL ,
[U_BEN_ACCNT] [int] NULL ,
[RetentionAmount] [int] NULL ,
[CededAmount] [int] NULL ,
[NetAmountAtRisk] [int] NULL ,
[ClaimAmount] [float] NULL ,
[U_FILLER] [varchar] (53) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_MESSAGE] [varchar] (65) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_SITE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_CHANNEL] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_PRODUCT_TYPE] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_SUB_PRODUCT_TYPE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_STAT_PRODUCT_TYPE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_RECO_AFFILIATE_TYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_BUDGET_CENTER] [varchar] (5) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[TableCode] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[SeriesCode] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_NAIC_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_ORACLE_CO] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_CLIENT] [varchar] (20) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
CONSTRAINT [PK_GE_Claim] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [GE_Reserve] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Month_of_file] [datetime] NULL ,
[CompanyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[PolicyNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CoverageNumber] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CessionSequence] [varchar] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[BenefitType] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CalculationMethod] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReinsuranceCompany] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReportingCompany] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[LineofBusiness] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[TreatyNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[PlanNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_AUTOFAC_SW] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ProductTypeCode] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[JointType] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_JOINT_METHOD_SW] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Mode] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[TransactionType] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReinsuranceType] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReinsuranceDuration] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_QUAL] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[IssueDate] [datetime] NULL ,
[ToDate] [datetime] NULL ,
[FaceAmount] [int] NULL ,
[CededAmount] [int] NULL ,
[NetAmountAtRisk] [int] NULL ,
[Amount] [int] NULL ,
[Percent] [int] NULL ,
[PercentWaiverPremiun] [varchar] (5) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_CESSION_COUNT] [int] NULL ,
[U_AGE_BASIS] [char] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_INS_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[IssueAge] [tinyint] NULL ,
[Class] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_MORT] [varchar] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_INS_STATUS_1] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[IssueAge_1] [tinyint] NULL ,
[Class_1] [char] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Gender_1] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_MORT_1] [varchar] (4) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Reserve] [decimal](18, 0) NULL ,
[U_INTR_RATE] [int] NULL ,
[ReserveFactor] [decimal](18, 0) NULL ,
[ReserveCase] [decimal](18, 0) NULL ,
[WaiverofPremium] [decimal](18, 0) NULL ,
[ActiveLife] [varchar] (8) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ICOS] [decimal](18, 0) NULL ,
[Reserve_1] [decimal](18, 0) NULL ,
[U_INTR_RATE_1] [int] NULL ,
[ReserveFactor_1] [decimal](18, 0) NULL ,
[ReserveCase_1] [decimal](18, 0) NULL ,
[WaiverofPremium_1] [decimal](18, 0) NULL ,
[ActiveLife_1] [varchar] (8) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ICOS_1] [decimal](18, 0) NULL ,
[Reserve_2] [decimal](18, 0) NULL ,
[U_INTR_RATE_2] [int] NULL ,
[ReserveFactor_2] [decimal](18, 0) NULL ,
[ReserveCase_2] [decimal](18, 0) NULL ,
[WaiverofPremium_2] [decimal](18, 0) NULL ,
[ActiveLife_2] [varchar] (8) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ICOS_2] [decimal](18, 0) NULL ,
[U_FACTOR_PTR] [varchar] (10) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_ATTN_AGE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_SET_BACK] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_CLASS_SW] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_CURTATE_CONT] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Caption] [varchar] (15) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ErrorCode] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReserveClass] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[ReserveClass_1] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[CurrencyCode] [char] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_VAL_INTR_PTR] [varchar] (10) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[UNKNOWN_01] [varchar] (30) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_SITE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_CHANNEL] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_PRODUCT_TYPE] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_SUB_PRODUCT_TYPE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_STAT_PRODUCT_TYPE] [varchar] (2) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_RECO_AFFILIATE_IND] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_BUDGET_CENTER] [varchar] (5) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_NAIC_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[U_ORACLE_CO] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[TableCode] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[SeriesCode] [varchar] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[UNKNOWN_02] [varchar] (25) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
CONSTRAINT [PK_GE_Reserve] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
-
How about some sample data for the columns we're dealign with?
Like it seems there is a lot of group BYs happening in your queries... why? Can you give me some data to work with? Please since this looks like it could be confidential stuff please don't give me actual data... We don't need to break any laws here...
-
i am not able to give you data. you can send me and i can test run the script and make any modification if neccessary
-
Again, I'm not looking for real data, I'm looking for some sample data that could help me get a better understanding of what you were trying to do with the subquery from you second update statement... Instead of real numbers just give me some simple test data. For instance,
INSERT INTO #GE_reseve(PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, IssueDate, WaiverofPremium, ClaimNumber )
VALUES (1, 1b, 1c, 100, 'M', 10/10/2006, 10, '')
You just need to make the sample data fit your test needs. And you don't need to give me data for each column, just the ones involved in the queries you are trying to write...
-
Here are some facts that may help you, there are two types of temp tables in SQL Server Local # limited scope and global ## longer scope but you must always explictly drop because it could harm your application. I did not see any ORDER BY in your query so why are you using BIN (binary sort) in your collation? The reason I ask is BIN is the fastest sort but also require case sensitive data and query.
And you are updating a JOIN that is technically an invalid operation because JOIN by ANSI SQL definition is idempotent that is it is not supposed to alter the state of the data. SQL Server 2005 now rejects most JOIN updates but Microsoft have stopped short of calling the operation invalid by just adding updates using the FROM clause is none deterministic. Try the link below for some tricks with temp tables by Ken Henderson, you can ALTER the temp table several times for different operations before dropping it. Hope this helps.
http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1
Kind regards,
Gift Peddie
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply