I am new to SQL and trying to migrate data into a GL account field which has the following format 0000000.00000.00000
The case statement I wrote is as follows
I got an error stating conversion failed when converting the varchar value '.' to data type int. I have tried to place the . without quotation marks and it does not pass. Alternatively when I used convert(int,'.') it adds up the three values instead of placing them in the GL format. Please point me to the right direction. Probably a rookie mistake or I am just totally off.
I guess you want the result as a string. Than you should cast the value to varchar, not to int.
answered Sep 28 '12 at 07:25 AM
Try putting the 14350 in quotes. I would change '.'+ 14350 + '.' to '.14350.'
That is more concise and correctly references 14350 as string data.
answered Sep 28 '12 at 03:39 PM
Thanks to all that contributed. After half a day of pulling my hair (have none) i finally got it to work. This is how it went. first, i did not need to convert BU, SUBSIDIARY or OBJECT to int nor did I need to cast. All I had to do is this...
CASE WHEN [ORDER_TYPE] = 'SS' or [ORDER_TYPE] = 'SC' THEN [BU] + '.' + [OBJECT]
This was one line in a migration script from JDE to Maximo. Sometimes its as easy as that, yet my 'newbie' self spent hours trying to make it more complex.
answered Sep 28 '12 at 11:35 PM