ORA-01036 Illegal variable name / number

akhiln · Post by **akhiln** » Fri Jul 08, 2005 12:20 am

Hi All,

I am getting an error like this

"ORA-01036 Illegal variable name / number".

I have a DRS lookup stage in that i am having an user-defined query like this
SELECT A.LM_LRNR_OBJV_ID,A.SRC_SYS_ID,C.LM_PROF_MAP
FROM PS_LM_LRNR_OBJV A,
PS_LM_OBJV_TBL B,
PS_LM_RVW_RTNG_MAP C
WHERE A.LM_REVIEW_RATING = C.LM_REVIEW_RATING
AND A.SRC_SYS_ID = C.SRC_SYS_ID
AND A.SRC_SYS_ID = B.SRC_SYS_ID
AND A.LM_OBJV_ID = B.LM_OBJV_ID
AND B.LM_RATING_MODEL = C.LM_RATING_MODEL
AND C.EFFDT=(SELECT MAX(T.EFFDT) FROM PS_LM_RVW_RTNG_MAP T
WHERE C.LM_RATING_MODEL = T.LM_RATING_MODEL
AND C.LM_REVIEW_RATING= T.LM_REVIEW_RATING
AND C.EFFDT <=A.LM_OBJV_ASGN_DT)

The error is happening in this DRS stage only.

The above SQL is working fine in ORACLE without giving any error.

Following are the key columns for all the tables
PS_LM_LRNR_OBJV (LM_LRNR_OBJV_ID, SRC_SYS_ID)
PS_LM_OBJV_TBL (LM_OBJV_CD, LM_OBJV_ID, SRC_SYS_ID)
PS_LM_RVW_RTNG_MAP (LM_RATING_MODEL, EFFDT, LM_REVIEW_RATING, SRC_SYS_ID)

Any idea whay may be the problem???

Thanks,
Akhil

chulett · Post by **chulett** » Fri Jul 08, 2005 12:44 am

It's not a problem with your sql, per se. It's a mismatch between the sql, the column definitions in the stage and/or what DataStage is expecting. This really only becomes an issue when you use custom SQL.

Make sure you have the same number of columns defined in the stage that you are selecting - in this case three.

You seem to be missing any bind variables in this query. With no bind variables, marking any of the columns in the stage as Key columns will cause this error, I do believe. Do you really want a lookup that doesn't require any Key Expressions for the lookup and which returns the exact same answer, over and over, for each row?

Sreenivasulu · Post by **Sreenivasulu** » Fri Jul 08, 2005 1:05 am

In the lookup stage. Uncheck "Pre 4.2 User defined Sql" and compile.
By default this gets checked hence you have to uncheck deliberately.

Regards
Sreeni

chulett · Post by **chulett** » Fri Jul 08, 2005 1:20 am

Hmm... that just controls the behaviour of the parameter markers in a reference link and if they must start at :1 or not. Don't really think it will fix this problem, but it couldn't hurt to try it and see.

Sreenivasulu · Post by **Sreenivasulu** » Fri Jul 08, 2005 1:26 am

HI Chullet,

Actually i fixed one such issue which gave a error "illegal name/variable"
and i did this. It worked wonders

Regards
Sreeni

chulett · Post by **chulett** » Fri Jul 08, 2005 1:31 am

Well, I guess Akhil will let us know.

Still got the issue of no bind variables to deal with.

akhiln · Post by **akhiln** » Fri Jul 08, 2005 2:23 am

I didn't find any "Pre 4.2 User defined Sql" option. My lookup is a DRS stage.

I cannot change the "where" condition. Is there any other way of solving this issue.

Sreenivasulu · Post by **Sreenivasulu** » Fri Jul 08, 2005 2:52 am

Hi Akhlin

Can you tell the full form of DRS stage which you are referring to ?
I was answering based on the assumption that you were using OCI stage for lookup

Regards
Sreeni

akhiln · Post by **akhiln** » Fri Jul 08, 2005 2:56 am

Dynamic Relational Stage(DRS). It can be configured at runtime to read from or write to a number of different supported relational database management systems using native interfaces.

Thanks,
Akhil

chulett · Post by **chulett** » Fri Jul 08, 2005 7:10 am

And you have it set to Oracle right now, I assume.

How about my Key column statement? Have you marked any columns as 'keys' in the stage? And do you really want to run this complex query, which has no ties to the incoming row so it gets the exact same result each time, for every row that passes through your job?

akhiln · Post by **akhiln** » Sun Jul 10, 2005 10:04 pm

Yes i want to run this complex query. I cannot do any changes with the tables & the logic. Is it possible to do something with the datastage to avoid this kind of errors.

chulett · Post by **chulett** » Sun Jul 10, 2005 10:37 pm

Ok. There's nothing stopping you from running this query without error, just wanted to make sure that was really what you wanted to do.

To reiterate, you need to make sure that:

* You are selecting three values in your query so you need three columns defined in the DRS lookup. Since this is a User Defined query, it's up to you to make sure they match up properly (order and definition) with the selected columns.

* Since you have no parameter markers in the reference lookup query, you cannot have marked any of the columns as Key columns.

Then the three columns can be referenced in an output link without linking anything from the input link - because you can't. The query will fire for every row in your job and the same three values will be there to be taken further into the job.

If you've done all this, triple-checked everything and you still have the problem you may have found a problem with the DRS stage. As a test, swap the DRS for an OCI stage to see if there is still a problem.

A better way to accomplish this same thing would be to create a hash file with 4 columns - one single character key column and then three data columns to hold your query results. Feed your query into the hash data columns via a transformer and hard-code your key column to a fixed value, say 'X'. On the Output side enable the 'Preload to memory' option. Now you've got a hash file with a single record you can use in place of the DRS stage. Hard-code the Key Expression in the Transformer doing the lookup to the same value you used previously and you've got a high speed version of your original setup - the query is run once and then a high-speed in-memory lookup fetches that value for every row.

akhiln · Post by **akhiln** » Mon Jul 11, 2005 12:07 am

Thanks Chulett. When i included the hash file after the DRS stage its working fine. Thank you very much.