Concatenate/Update a record 2

[trudye10]

Hey Guys, I'm new to SQL Server and I have a question. I am trying to do concatenate 2 fields and 1 value into a new field.

Division (nvarchar(2), null)
Acct_Number (Nvarchar(11), null)
Account_Number (nvarchar(20), null)

I tried the following statement:

update dbo.MCF 
set Account_Number = 
CASE 
	WHEN DIVISION = 30 then (Division) + '00000000' + (Acct_Number)
	ELSE (Division) + '0000000' + (Acct_Number)
 END

I recieved the following error msg:
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

What pray-tell am I doing wrong?
Thanx,
Trudye

 

[gmmastros]

Apparently, you are trying to modify the account number.

New Account Number = Division + (Bunch of zero's) + Account Number

Account number can only store 20 characters. Since you are trying to add division and bunch of zero's to it, it's possible that the resulting account number is longer than 20 characters.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

11+8+2 > 20 That is why you get that message.
11 - the length of Acct_Number, if whole field is filled.
8 - number of zeros you try to add when the division is 30
2 - is the length of the division (in that case 30)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[trudye10]

DUH!!!

 

[trudye10]

My mistake I forgot to mention the Acct_Number can be 10 or 11 depending on the Division. If the Division = 30 then Acct_Number is 10 bytes, if not it is 11 bytes. That's why the 8 zeros on if = 30.

I put the DUH answer because I thought I had put the 8 Zeros on the Else. I gave the Stars and then it dawned on me to check the zero count.

Do you see any other reason(s)?

Thanx,
Trudye

 

[gmmastros]

Still... there must be unexpected data. Try running this...

Select CASE 
    WHEN DIVISION = 30 then (Division) + '00000000' + (Acct_Number)
    ELSE (Division) + '0000000' + (Acct_Number)
 END As Account_Number,
       Len(CASE 
    WHEN DIVISION = 30 then (Division) + '00000000' + (Acct_Number)
    ELSE (Division) + '0000000' + (Acct_Number)
 END) As Length_Of_Account_Number
From dbo.MCF
Where Len(CASE 
    WHEN DIVISION = 30 then (Division) + '00000000' + (Acct_Number)
    ELSE (Division) + '0000000' + (Acct_Number)
 END) > 20

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[trudye10]

I figured it out FINALLY. It woke me up last night, I need to add the TRIM function. I confirmed it this morning, in some of the records the first byte is a space.

Thanx guys for all your help,
Trudye