ORA-01036: illegal variable name/number while running job

dodda · Post by **dodda** » Sat Oct 24, 2009 8:52 am

Hi all,

I got below fatal error while i tried to running a datastage error. I'm able see the data through viewdata in oracle datastage.

ORA-01036: illegal variable name/number.
Iam performing the reference lookup on the following SQL which is UserDefined from Table and columns

SELECT MAX(CUST_KEY) as CUST_MAX_KEY, 'Literal' FROM DS_BK.BK_CUST_DIM .

And the datatypes for CUST_MAX_KEY and Literal are numeric .Also, the key colun was Literal .
Could you plesae help me i getting this problem resolved..

Thanks

chulett · Post by **chulett** » Sat Oct 24, 2009 9:09 am

First suggestion - search here for that error message, it has been discussed here many times.

dodda · Post by **dodda** » Sat Oct 24, 2009 9:22 am

Hi Chulett,
I searced the forum before posting my query..
But i didn't get enough inforamtion.
So, could you advise me , how to proceed..

Thanks in advance.

chulett · Post by **chulett** » Sat Oct 24, 2009 9:28 am

54 matches and there's not enough information out there? [sigh]

How many columns do you have defined in the stage? Are any of them marked as Key columns?

dodda · Post by **dodda** » Sun Oct 25, 2009 1:27 am

I defined two columns in datastage job. And i marked Literal as key column.

chulett · Post by **chulett** » Sun Oct 25, 2009 9:31 am

Which is your problem, of course. You've got the "two columns" part right as whatever you select needs to have a place to go, it's the key column that's your issue. If you'd read through all of those other posts on the issue you should have seen that Key columns are bound into the "where" clause of the lookup query. Seeing as how you don't seem to have one, unmark the key column and see if it works.

Which then begs a couple of questions. If the 'literal' column was added just to be the key column, get rid of it. And if all you are doing for each row is computing the exact same max() value over and over, then you really should rethink your solution. Select that value once, put it in a hashed file with a 'hard coded' key value and then use that as your reference lookup instead.