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 '12 at 06:41 AM in Default

Ras_P gravatar 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 '12 at 07:25 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

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)
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 '12 at 03:39 PM

Beandon10 gravatar image

Beandon10
136 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)
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 '12 at 11:35 PM

Ras_P gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x58
x39

asked: Sep 28 '12 at 06:41 AM

Seen: 725 times

Last Updated: Sep 29 '12 at 06:44 AM