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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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