July 11, 2013 at 8:16 am
Hi,
I am trying to generate ids as"Compidentity" as varchar .
If the record is inserted with productcode- " Health"
Column with value H1 shoul be added .
If the record is inserted with " Dental "
Column with Value D2 should be added.
Here is what I have :
( ProductCode Varchar(20),
Compidentity int identity(1,1),
status as case
when productcode = 'Health'
then 'H'+ cast(compidentity as varchar(20))
when productcode = 'Dental'
then 'D' + cast(compidentity as varchar(20))
else
1
end
)
Executes fine.
But when I write an insert statement gives an error :" Conversion failed when converting the varchar value 'H1' to data type int."
Please advise.
July 11, 2013 at 8:24 am
sharonsql2013 (7/11/2013)
Hi,I am trying to generate ids as"Compidentity" as varchar .
If the record is inserted with productcode- " Health"
Column with value H1 shoul be added .
If the record is inserted with " Dental "
Column with Value D2 should be added.
Here is what I have :
( ProductCode Varchar(20),
Compidentity int identity(1,1),
status as case
when productcode = 'Health'
then 'H'+ cast(compidentity as varchar(20))
when productcode = 'Dental'
then 'D' + cast(compidentity as varchar(20))
else
1
end
)
Executes fine.
But when I write an insert statement gives an error :" Conversion failed when converting the varchar value 'H1' to data type int."
Please advise.
That is because you else condition has an int as a hardcoded 1. Change to '1' and it should work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 8:29 am
It Worked!
Thank you so much.
July 11, 2013 at 8:31 am
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply