Case statement to populate GL Account for migration script
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 CASE WHEN [ORDER_TYPE] = 'SS' or [ORDER_TYPE] = 'SP' THEN CONVERT(INT,[BU]) + '.' + CONVERT(INT,[OBJECT]) ELSE CONVERT(INT,[BU]) + '.'+ 14350 + '.' + CONVERT(INT,[SUBSIDIARY]) END 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. Thanks
I guess you want the result as a string. Than you should cast the value to varchar, not to int. CASE WHEN [ORDER_TYPE] = 'SS' or [ORDER_TYPE] = 'SP' THEN CAST([BU] AS VARCHAR(10)) + '.' + CAST([OBJECT] AS VARCHAR(10)) ELSE CASTS([BU] AS VARCHAR(10)) + '.'+ 14350 + '.' + CAST([SUBSIDIARY] AS VARCHAR(10)) END
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] ELSE [BU] + '.'+ '14350' + '.' [SUBSIDIARY] END 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.