Case statement to populate GL Account for migration script

Question

question

Ras_P asked Sep 28, '12

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

migration case-statement

10 |1200

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

Answer 1 · 2012-09-28T07:25:12Z

Magnus Ahlkvist answered Sep 28, '12

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

10 |1200

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

Ras_P commented · Sep 28, 2012 at 03:26 PM

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 ·

Answer 2 · 2012-09-28T15:39:41Z

Beandon10 answered Sep 28, '12

Try putting the 14350 in quotes. I would change '.'+ 14350 + '.' to '.14350.' That is more concise and correctly references 14350 as string data.

3

10 |1200

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

Ras_P commented · Sep 28, 2012 at 04:02 PM

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 ·

Beandon10 commented · Sep 28, 2012 at 06:47 PM

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 ·

Magnus Ahlkvist commented · Sep 28, 2012 at 07:35 PM

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 ·

Answer 3 · 2012-09-28T23:35:37Z

Ras_P answered Sep 28, '12

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

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

question