Avatar of Brock
Brock
Flag for Canada asked on

Using the sequence object

Good Morning Experts,

I am trying to insert a sequence into the following insert record.

INSERT INTO SF4204_HDR ([OPRID]
 ,[RUN_CNTL_ID]
 ,[EMPLID]
 ,[NAME]
 ,[ACAD_PLAN]
 ,[ACAD_LEVEL_BOT]
 ,[PLAN_DESCR]
 ,[INVOICE_ID]
 ,[PRIOR_YEAR_AMT]
 ,[ADDRESS1]
 ,[ADDRESS2]
 ,[CITY]
 ,[STATE]
 ,[POSTAL]
 ,[COMMENTSHORT]
 ,[NC_SELCRIT_YN])
 SELECT 'LN'
 ,'TEST'
 ,COMMON_ID
 ,'  '
 ,'  '
 ,'  '
 ,'  '
 ,'  '
 ,NEXT VALUE FOR Invoice_Seq
 ,'  '
 ,'  '
 ,'  '
 ,'  '
 ,'  '
 ,'  '
 '  '
  FROM PS_EMPLOYEE

It is not populating.  Any ideas why.  It is sqlserver.  I can run the select ok and I see it populating but not when I do the insert.

Thanks, Brock.
Microsoft SQL Server

Avatar of undefined
Last Comment
Brock

8/22/2022 - Mon
Ryan Chong

value of NEXT VALUE FOR Invoice_Seq is at the 9th field, while [INVOICE_ID] is at the 8th field.

are you inserting wrong values into the fields?
Brock

ASKER
Thanks, Ryan.  However,  the invoice id is  char 22  -- so I converted it like this

convert(varchar(10),NEXT VALUE FOR Invoice_Seq )  and now it complains that

I am  ' Error converting data type varchar to numeric.'.

Any ideas.  Before I did not get an error since the prior amount  is numeric but no value was being inserted.  Perhaps I can't convert this ?

Thanks, Brock.
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Brock

ASKER
It is all happy now.  It was failing on my emplid which was a key and I accidental wiped it out.

Have a great day,

Brock
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes