User Variable Activity
Moderators: chulett, rschirm, roy
User Variable Activity
I have a parallel job where the query is parameterized(RCP ON).
Oracle Enterprise------>Seq File
This parallel job is connected in a job sequence wherein UserVariable activity used.
A where condition is kept in the query like "where to_char(sample_date,'YYYY-MM-DD')='2010-01-25'".
I am passing '2010-01-25' as a Job Param and using in UserVar Activity.I used " \' ":test_date:" \' ".But DS is logging an error stating "query not ended properly".Same query is running successfully from SQL command line.
Any solutions will be appreciated.
Thanks
Oracle Enterprise------>Seq File
This parallel job is connected in a job sequence wherein UserVariable activity used.
A where condition is kept in the query like "where to_char(sample_date,'YYYY-MM-DD')='2010-01-25'".
I am passing '2010-01-25' as a Job Param and using in UserVar Activity.I used " \' ":test_date:" \' ".But DS is logging an error stating "query not ended properly".Same query is running successfully from SQL command line.
Any solutions will be appreciated.
Thanks
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
What happens if you add a ";" to the end of your query?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
------Same errorArndW wrote:What happens if you add a ";" to the end of your query? ...
I forgot to mention another error:ORA-00972 identifier too long.
After some search,I found that some clients do not accept VARCHAR>31.I am using Oracle 10g version.
I have columns which had VARCHAR>31.Is this creating problem?
Thanks
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
The contents of the columns can be larger than 31 characters, but this is referring to your column name length(s) which need to be shortened.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
could you post the full error message, which (I think) will also contain the query as seen by Oracle.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
select <schemaname.tablename.columnname>(30 column names) from <schemaname.tablename> where to_char(schemaname.tablename.test_date,'YYYY-MM-DD')='2010-07-25'----Query used
I checked with select * from <tablename> where to_char(test_date,'YYYY-MM-DD')='2010-07-25'.
Errors logging for both:
Message:ORA-00972:Identifier too long
Statement:"select <schemaname.tablename.columnname>(30 column names) from <schemaname.tablename> where to_char(schemaname.tablename.test_date,'YYYY-MM-DD')='2010-07-25'" Provided query statement did not prepare correctly
The same query is running fine in parallel job but problem aroused in job sequence where User Variable Activity is used for passing test_date at run time.
I checked with select * from <tablename> where to_char(test_date,'YYYY-MM-DD')='2010-07-25'.
Errors logging for both:
Message:ORA-00972:Identifier too long
Statement:"select <schemaname.tablename.columnname>(30 column names) from <schemaname.tablename> where to_char(schemaname.tablename.test_date,'YYYY-MM-DD')='2010-07-25'" Provided query statement did not prepare correctly
The same query is running fine in parallel job but problem aroused in job sequence where User Variable Activity is used for passing test_date at run time.
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
Did you replace the text "schemaname" in the post or is that what is displayed? Cut-and-Paste your datastage SQL into the tool of your choice and see if it works or also shows an error.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
The mistake done by me was appending double quotes at the front and rear ends of the query in UserVar Activity.It was running successfully after I remove them.But DS puzzled me giving the Oracle:identifier too long...
Thanks for your help
Thanks for your help
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.