DSXchange

I have a simple job just a oci stage where i have sysdate dual in which refered to a set of dates.the field am using has a fuction to_char for ex in the source i have to_char(sysdate,'MMDDYY') which again refered to the source databases.hte error am gettng is ORA-00911: invalid character any thoughts

I assume what you have supplied is only part of the sql.

Try running the SQL outside datastage and ensure it returns no errors or warnings.

Sainath.Srinivasan wrote:I assume what you have supplied is only part of the sql.

Try running the SQL outside datastage and ensure it returns no errors or warnings. ...

I can view from the view data but i gives error on the job

Find out what Oracle error number -911 actually means - use the oerr command to get a recommendation about what to do about it.

Usually that comes from an unresolved parameter - #XXX# - or you've tacked on a trailing semi-colon. Both the hash and semi are "invalid characters" here.

chulett wrote:Usually that comes from an unresolved parameter - #XXX# - or you've tacked on a trailing semi-colon. Both the hash and semi are "invalid characters" here. ...

Basically i have the query in the i/p DB stage is

Select to_char(sysdate,'MMDDYY') from dual

On the ref DB i have query from DB in which from the where clause i use the runtime argument from the source which will be to_char(:1,'MMDDYY') so will that changing again gives the problem or do i take to char out in the source DB stage

any thoughts

So, more than just "a simple job just an oci stage", eh? Sorry, but I have no idea what you mean by "On the ref DB i have query from DB in which from the where clause i use the runtime argument from the source which will be to_char(:1,'MMDDYY')".

If it means you are using that bind variable in your source query, you can't, that would need to be a job parameter. If "ref DB" means some kind of lookup, that should work provided your first field is marked as a key.

chulett wrote:So, more than just "a simple job just an oci stage", eh? Sorry, but I have no idea what you mean by "On the ref DB i have query from DB in which from the where clause i use the runtime argument from the source which will be to_char(:1,'MMDDYY')".

If it means you are using that bind variable in your source query, you can't, that would need to be a job parameter. If "ref DB" means some kind of lookup, that should work provided your first field is marked as a key.

sorry about the late reply actually the bug is not with code but with the data stage versions we are migrating from 7.1 to.8.1 .eventually 8.1 gives o/p of oconv(sysdate,'MMDDYY') as 05/22/10 but 7.1 gives as 05 22 10 so corrected the query worked fine.thanks all for the i/p

Ah... yes, things were "fixed" or tightened up to ensure proper operation as the product matured and kudos for catching that issue. And we know you meant "7.5" when you said "8.5".

chulett wrote:Ah... yes, things were "fixed" or tightened up to ensure proper operation as the product matured and kudos for catching that issue. And we know you meant "7.5" when you said "8.5".

Typos

As noted, we know. You can always go back and edit it, if you so desire. Of course, then people will wonder what the heck I'm talking about.