SQL query with IN clause

[Kirtikumar]

Hi,

I am facing a problem while doing lookup on ODBC stage. As it is is multi-row lookup, I am using ODBC stage.

My seq file contains, various empnames is single row in following format:
Kirti,Kumar,Amol
Sachin,Mark,John,Chris,

I want fetch all rows from Emp table where empname is from the list of each row i.e. wanted to put SQL like

Code: Select all

select * from emp where empname in (?)

but here the question mark is not getting replaces properly.

any inputs will be appreciated.

[roy]

Hi,
What do you mean by ? not being replaced properly?
if your trying to do something like:

Code: Select all

select <column name or comma seperated list of column names>
from table
where <column> in('<name>','<name>',...)

then you need to have each name enclosed with a single qoute character.

So try fisrt to write the SQL you generate to a sequntial file and check it's validity first, then you can go on with the lookup.

IHTH,

[loveojha2]

I think you are using a Lookup (for the values coming from XML)

In that case

Code: Select all

select * from emp where empname in (?)

should be

Code: Select all

select * from emp where empname =?

[Kirtikumar]

Thanks Roy,

The problem here is, there is no fixed no. of values coming in column i.e. emp name list. Some times it may contain, 10 names, some times 100 and so on...

The incoming list of names is formatted in job after reading, to make it like:
'Kirti','Kumar','Amol'
'Sachin','Mark','John','Chris'

But still the lookup is not returning any rows.

So try fisrt to write the SQL you generate to a sequntial file and check it's validity first, then you can go on with the lookup.

I am unable to understand the meaning stmt mentioned above. I have tested the SQL from DB2 prompt and it is working fine.
How can I write the generated SQL to seq file?
Is there any way to check the SQL that is executed by DS at run time with substituted value(s) in place of '?' in SQL?

[ray.wurlod]

How about a pivot preceding the lookup so that each lookup is only retrieving one name?

[Kirtikumar]

Thanks Ray!!!

But the prob will be splitting a single input column which is comma seperated.
In derivation rule of pivot, (if am not wrong) can not be used to provide substring kinda of functions.
Then how to pivot a single column to generate multiple rows thro' pivot.

The input is a single column:
'Sachin','Mark','John','Chris'
which then should be converted as
'Sachin'
'Mark'
'John'
'Chris'

Is there any way to specify the rule to take first value from input and then second and so on..

[ameyvaidya]

HI Kiritkumar,
IF the number of comma-seperated names is known and is fixed, a transformer can seperate the names into different columns using the field() function.

IHTH

[Kirtikumar]

HI Kiritkumar,
IF the number of comma-seperated names is known and is fixed, a transformer can seperate the names into different columns using the field() function.
IHTH

In first post of this disucssion I have mentioned it is not fixed and can vary from 1 to any number....

[loveojha2]

You can try replacing the comma(,) with a Char(10):Char(13)(which is CRLF) and writing it to a sequential file and reading it back into a Hashed file and using Hashed file for lookup.

[Kirtikumar]

There is a limitation to my job -
I have to do this in one job only and I can not use temp storage at all..
strange but ya its the requirement....

[loveojha2]

Then you can try doing it through a named pipe.

[Kirtikumar]

Then you can try doing it through a named pipe.

Cant do that as well....that is also a constraint...

u might be filling am kidding..
the thing is, original job is reading the data fro XML and not from seq.
Just as an example I mentioned it as seq file.

the job reads the data from XML and generates the output XML. In betweem, no seq file or hash file should be used.

Apologies for providing wrong info in OP.

[chulett]

the job reads the data from XML and generates the output XML. In betweem, no seq file or hash file should be used.

Sounds like an RTI/SOA job.

I'm not sure the ODBC stage allows a comma separated list to be handled by a single parameter marker. Have you turned on any form of 'trace' in either the job or the backend database to see exactly what kind of sql statement is being executed? Meaning, what the sql looks like that actually gets run.

[ray.wurlod]

There is a limitation to my job -
I have to do this in one job only and I can not use temp storage at all..
strange but ya its the requirement....

If you're going to be a DS professional you need to learn to challenge requirements (politely - get their reasons - then post them here, we could do with a laugh).

[Kirtikumar]

Sorry for that Ray!!!
Next time I will post the proper reqt.

The job is RTI job. I will try out trace on DS job.