DSXchange

Hi,
I want to run the following query in OCI stage.

select a1.idx,a1.namex,a1.creationdatex,a1.specialhandlingx,
a1.CONFIDENTIALX,b.codex from accountjdox a1,
brokeragencyjdox b
where a1.defaultbroker_fkx=b.idx and a1.idx in(#Dir#/Accountfile.txt)

I opt for read sql query from file as NO.
I have query to select Accountnumbers in Accountfile which is in UNIX and it will change frequenty.
when I test for it it is giving the error - missing expression.
Could somebody please help

Thanks,
Sridhar

In cases like this the only way to get a good answer is to cut-and-paste the entire error message, those two words "missing expression" aren't quite enough information.

Simple answer... you cannot use parameters in a SQL file.

Oh

I am not using parameter in file.
I am writing a query and in that there is subquery which is from file.

ther error message is
The OCI function OCIStmtExecute returned status-1. Error code:936, Error message: ORA-00936; missing expression

bobbysridhar wrote:I am not using parameter in file.

Yes, you are - hence my answer.
#Dir# is the parameter.

the sql query i posted, i am using it as user defined sql.
i am not reading sql from file
in user defined sql i am calling file which has accountnumber with paramerter/filename.txt.
we use parameters in user defined sql
and that is what i am doing

Then back to my request for the full error message.

the error message is
The OCI function OCIStmtExecute returned status-1. Error code:936, Error message: ORA-00936; missing expression

OK, didn't look close enough at the SQL. Oracle won't automagically import that file data into your query for the subselect. You'll need to pass the entire "in list" in as a parameter.

but that 'in list' is dynamic and business want to provide the 'in list' query dynamically whatever they want.
thats why I want to give them a file in Unix to put their query in that file.
is there a way I can acheive this

As I said, pass it in as a job parameter. Use something in a Sequence job to read the file and craft the string that needs to be passed - a routine can be used or it may be as simple as an Execute Command stage that does a 'cat' and passes the result to the parameter. All depends on the structure of the data in the file.