Page 1 of 1
SQL query with IN clause
Posted: Tue Feb 14, 2006 1:11 am
by 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.
Posted: Tue Feb 14, 2006 1:27 am
by 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,
Posted: Tue Feb 14, 2006 2:17 am
by 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 =?
Posted: Tue Feb 14, 2006 3:34 am
by 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.
roy wrote: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?
Posted: Tue Feb 14, 2006 3:51 pm
by ray.wurlod
How about a pivot preceding the lookup so that each lookup is only retrieving one name?
Posted: Wed Feb 15, 2006 2:33 am
by 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..
Posted: Wed Feb 15, 2006 2:57 am
by 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
Posted: Wed Feb 15, 2006 3:08 am
by Kirtikumar
ameyvaidya wrote: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....
Posted: Wed Feb 15, 2006 3:19 am
by 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.
Posted: Wed Feb 15, 2006 3:52 am
by 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....
Posted: Wed Feb 15, 2006 3:56 am
by loveojha2
Then you can try doing it through a named pipe.
Posted: Wed Feb 15, 2006 4:05 am
by Kirtikumar
loveojha2 wrote: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.
Posted: Wed Feb 15, 2006 7:10 am
by chulett
Kirtikumar wrote: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.
Posted: Wed Feb 15, 2006 3:53 pm
by ray.wurlod
Kirtikumar wrote: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).
Posted: Fri Feb 17, 2006 4:46 am
by 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.