question

Ras_P avatar image
Ras_P asked

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
migrationcase-statement
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ras_P avatar image Ras_P commented ·
Thanks Magnus. This failed as well. I got the error "conversion failed when converting the varchar value '1003602' to data type int. That value is the first BU value from the list.
0 Likes 0 ·
Beandon10 avatar image
Beandon10 answered
Try putting the 14350 in quotes. I would change '.'+ 14350 + '.' to '.14350.' That is more concise and correctly references 14350 as string data.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ras_P avatar image Ras_P commented ·
hello Beandon, It fails before reaching the 14350 because it is still giving me same error as before. here is some more info. BU is varchar(8) OBJECT is varchar (5) SUBSIDIARY is varchar (7) They are being migrated from JDE to MAXIMO to a field called GLDEBITACCT which is of maxtype GL(19) hopefully this clears it more. Thanks for the contributions
0 Likes 0 ·
Beandon10 avatar image Beandon10 commented ·
I don't see how the case statement would have that error. Are you sure the error isn't coming from another section of code?
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
It makes sense that the error is same as before. Since 14350 is int, Sql Server tries to convert the other parts to int. So put 14350 in quotes. And since the rest is already string types: remove the casts totally.
0 Likes 0 ·
Ras_P avatar image
Ras_P answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.