DSXchange

ERROR:
Src_Scheme_Loans: The provided query statement did not prepare correctly; please verify that your statement is correct;

[
eg.,
select emp.a,emp.b,emp.c from table emp
union
select emp1.a,emp1.b,emp1.c from table1 emp1
]

If the number of columns in the individual select statements exceeds 120 (approx), we are unable to fetch the data.

But the query is working fine on sqlplus and
the job is working fine on IS-8.0 version on Windows

ANy inputs?

Regards,
psk

What stage? Sounds somewhat familiar, you might want to contact your official support peoples and see if there is a patch for this issue.

Why would you do this? I would combine these outside of SQL in the job.

check the column names you gave in the colums tab.
you are using union..
in the first query the first column is emp.a
in the second query it is emp1.a

use a common column name

like this

select emp.a as a,emp.b as b,emp.c as c from table emp
union
select emp1.a as a,emp1.b as b,emp1.c as c from table1 emp1

where a,b,c are the columns on the columns tab

Sorry, but any column aliases ("as c") you use in your sql are ignored by the stage. Not to mention unneeded in the sql as well.

thought diffrent column names on each query...you were right

hi all,

thanks for the replies. The stage in which the problem comes is teh Oracle Enterprise Stage.

The query actually selects all columns from teh table and also appends a dummy record as shown below.

Select e.ename,e.empno,e.deptno from emp e
union
Select 'a' as ename,1 as empno,'a' as deptno from dual

Even when i dont use alaises for table names, and teh table itself has say around 300 columns, even then we have the problem. Some queries have unions with different tables out of need, and in such cases query length shoots to 700 lines. The jobs thus fails.

Tried removing the aliases..same error. Pls suggest.

Regards,
PSK

chulett wrote:Sorry, but any column aliases ("as c") you use in your sql are ignored by the stage. Not to mention unneeded in the sql as well. ...

Are you sure?? We have had jobs fall over because the column name being extracted was different to the stage column name. As soon as we did an "as <stage col name>" it worked fine.

I'm pretty certain Server Edition use to ignore column names but EE does not appear to... certainly isn't for us anyway!

Fair enough. My assertion is certainly true for Server jobs, but if that's not the case on the Parallel side of the house, I stand corrected. Thanks.

For what it's worth, only the first sql in a set of "union" sqls would need any column aliases as that one drives the column names in the output set. The rest can be left without them.

We have tried that.

But there are queries of almost 400 lines (and max is 700 lines). This doesnt seem to work for them.

Thanks,
Shrinivas

Can you put together a test of where it breaks ?

Or just contact your support. You'll either find out the real maximum or get a patch if this is a bug. Or both.