Case statement to populate GL Account for migration script

Question

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

Answer 1

0

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

answered Sep 28 '12 at 07:25 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

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.

Sep 28 '12 at 03:26 PM Ras_P

(comments are locked)

Answer 2

0

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

Beandon10
138 ● 3

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

Sep 28 '12 at 04:02 PM Ras_P

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?

Sep 28 '12 at 06:47 PM Beandon10

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.

Sep 28 '12 at 07:35 PM Magnus Ahlkvist

(comments are locked)

Answer 3

0

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.

answered Sep 28 '12 at 11:35 PM

Ras_P
10 ● 1 ● 1

(comments are locked)

Case statement to populate GL Account for migration script

Follow this question

Related Questions