Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search condition IN

Status
Not open for further replies.

docam

Programmer
Jan 19, 2008
7
AT
Hi all,

does anyone know why a statement which looks like as follows does not work in DB2 z/OS:

SELECT ... WHERE (...,...) IN ((...,...),(...,...))

It works for ORACLE as far as I know.
 
Yes I know but this does not help very much. It would be great if someone knows a SQL statement equivalent to the mentioned one. Thanks.
 
Or
... WHERE FIELD1 IN (Val1, Val2) AND FIELD2 IN (Val11, Val21)
 
Your option with DB2 is as follows.

SELECT ... WHERE (fld1, fld2,...,fldn) IN
(
select fld1, fld2,...,fldn)
from SYSIBM.SYSDUMMY1
union
select fld1, fld2,...,fldn)
from SYSIBM.SYSDUMMY1
)
where the selects from SYSIBM.SYSDUMMY1 are built by yourself using the values you would place on the IN ((...,...),(...,...))

sample

select field1
from tbl1
where (key1,key2) in
(select 'a1','b1'
from SYSIBM.SYSDUMMY1
union
select 'a2','b2'
from SYSIBM.SYSDUMMY1
)


Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Yes, this was also my solution which seems to be the only one and works.

Thank you very much for all your answers!
 
Truusvlugindewind and Mercury2

DB2 allows for a where (fld1, fld2,...,fldn) in (fld1, fld2,...,fldn).
that is not the problem. CONCAT would do no good in this case, and if the fields in question were indexable, they would not be used anymore because of it.

The problem is that Oracle allow for a further extension of this, which is to have a "expression list"
see for full details.

DB2 does not allow for this, at least on the version the OP is using, so the solution is to code as I did.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
The only problem that I now have is that if I select columns of type TIMESTAMP a wrong value is returned, if I use the sysibm.sysdummy1 table in the subselect. If I use another table in the subselect everything is ok. My solution for now is that I do the following (assuming field1 is of type TIMESTAMP):

select TIMESTAMP(field1)
from tbl1
where (key1,key2) in
(select 'a1','b1'
from SYSIBM.SYSDUMMY1
union
select 'a2','b2'
from SYSIBM.SYSDUMMY1
)

It works but why is the wrong timestamp returned without using TIMESTAMP? Does anyone know a better solution?
 
I use DB2 z/OS 8.2. In this case the problem is not the code because even if I use the Command Editor of DB2 the returned timestamp value is wrong.

The original code using hibernate was:

Query q = this.session.createQuery(" FROM table_1 WHERE " + classMetaData.getIdentifierPropertyName() + " IN :)list)");

q.setParameterList("list", keyList);

List entityList = q.list();

-keyList is a list of objects which are returned from classMetaData.getIdentifier(object, EntityMode.POJO)
-object is an entity
-classMetaData is the metadata of the entity (sessionFactory.getClassMetadata(object.getClass())

With Oracle everything works fine and a sql statement as in my first question is generated. For DB2 hibernate generates the same sql statement which does not work. So I used this workaround with the subselect and the sysibm.sysdummy1 table.


 
You know the SQL already, it's the SQL I wrote on 23 Jan 08 2:37.
 
Just for the record.

I think your current version does not allow it. (8.2 does)

but the following will work on more recent versions for sure.
SELECT ... WHERE (...,...) IN VALUES (...,...),(...,...)

Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top