ORA-01036 Illegal variable name / number
Moderators: chulett, rschirm, roy
ORA-01036 Illegal variable name / number
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
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
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers