CASE WHEN ORDERLN.ITEMID IS NOT NULL THEN
Ltrim(rtrim(ORDERLN.ITEMID)) + '–' + ltrim(rtrim(ORDERLN.LINEID))
ELSE '1' + '–' + ltrim(rtrim(ORDERLN.LINEID)) END as ITEMID,
do you want the value of the calculation of
12646–112521 = -99,875?
or the string 12646–112521 converted into an int. (which will fail)
> "Perhaps the CASE block you posted is being INSERTed into an int column?"
> Yes, you're correct. So, what syntax do I need to add?
Ok. Then what do you want to insert, 12646, 112521, 12646112521, or ( {David's post above} 12646–112521 =) -99875 ?
Numeric data types can only have 0-9, decimal, NULL, or dash if it's the first character for a negative number. No dashes in the middle. So you'll have to tell us.
> Ltrim(rtrim(ORDERLN.ITEMID)) + '–' + ltrim(rtrim(ORDERLN.LINEID)) ELSE '1' + '–' + ltrim(rtrim(ORDERLN.LINEID)) END as ITEMID,
Also keep in mind that if you're doing numerical expressions such as subtraction then you'll have to lose the single quote marks ' converting these values to text, and CAST() the Ltrim(rtrim(ORDERLN.ITEMID)) values as a number, SQL Server will interpret that as string concatenation and not number math.
In such case the conversion must fail.