DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
Headless_horseman
Participant



Joined: 12 Jun 2009
Posts: 1
Location: Săo Paulo - Brasil
Points: 17

Post Posted: Sat Jan 30, 2010 8:16 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Unix
Hi all,

I have a problem while running a server job that returns this message error: ORA-01036: illegal variable name/number. This issue occurs for the Oracle database connection component that execute the following SQL statement:
“SELECT '1' AS DUMMY,
MAX(NR_SEQU_EXEC_CRGA) AS NR_SEQU_EXEC_CRGA,
NVL(MAX(TO_CHAR(DT_REFE_CRGA, 'YYYY-MM-DD')),'1900-01-01') AS DT_REFE_CRGA
FROM #PAR_DB_NJ_OWNER#.TB_EXEC_CRGA
WHERE CD_SIST = '#PAR_CD_SIST#'
AND CD_SITU_CRGA = 1”

The parameters receive the following values from DataStage:
* #PAR_CD_SIST#: “NJ”;
* #PAR_DB_NJ_OWNER#: “NJ”;

I found solutions in several forums, but all I found did not work, such as number of variables, variables with different names, variables with different types, and even put hard code inside the selects statement.

I have a production environment working fine and I’ve exported the jobs to certification environment. So, I have both environments with the same jobs.

Could you help me?
Grateful.
Headless_Horseman
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42520
Location: Denver, CO
Points: 218805

Post Posted: Sat Jan 30, 2010 8:44 am Reply with quote    Back to top    

As already noted, it's all about the number of columns defined in the stage versus selected in your DML and what you've marked as keys. For yours to work, there must be three columns defined in the stage (in the order mentioned in the sql, of course) with none of them marked as a Key. Key fields must be bound into the where clause and would only be appropriate for a reference lookup where I'm assuming this is a source query.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54194
Location: Sydney, Australia
Points: 293930

Post Posted: Sat Jan 30, 2010 2:53 pm Reply with quote    Back to top    

The column and table names mentioned in the SQL must exactly match those in the Oracle schema. I've seen this error occur when the wrong schema was specified. You might also verify that the data typ ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours