x

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

more ▼

asked Sep 28, 2012 at 06:41 AM in Default

avatar image

Ras_P
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Sep 28, 2012 at 07:25 AM

avatar image

Magnus Ahlkvist
21.5k 19 39 42

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, 2012 at 03:26 PM Ras_P
(comments are locked)
10|1200 characters needed characters left

Try putting the 14350 in quotes. I would change '.'+ 14350 + '.' to '.14350.'

That is more concise and correctly references 14350 as string data.

more ▼

answered Sep 28, 2012 at 03:39 PM

avatar image

Beandon10
136 2 4

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, 2012 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, 2012 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, 2012 at 07:35 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Sep 28, 2012 at 11:35 PM

avatar image

Ras_P
10 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x80
x12

asked: Sep 28, 2012 at 06:41 AM

Seen: 1145 times

Last Updated: Sep 29, 2012 at 06:44 AM

Copyright 2016 Redgate Software. Privacy Policy