Search condition IN

[docam]

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.

 

[fredericofonseca]

because Oracle is not the same as DB2.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[docam]

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.

 

[Truusvlugindewind]

use CONCAT

 

[Mercury2]

I'd try

... WHERE FIELD IN (Val1, Val2, Val3, Val4)

 

[Mercury2]

Or
... WHERE FIELD1 IN (Val1, Val2) AND FIELD2 IN (Val11, Val21)

 

[fredericofonseca]

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

http://www.syssoft-int.com

 

[docam]

Yes, this was also my solution which seems to be the only one and works.

Thank you very much for all your answers!

 

[Truusvlugindewind]

where char(col1 || col2) in (select char(col1 || col2) from...)

 

[fredericofonseca]

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

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/expressions015.htm#i1033664

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

http://www.syssoft-int.com

 

[Mercury2]

Thanks for the lesson Frederico.

 

[docam]

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?

 

[fredericofonseca]

please post the real code so we can have a proper look at it.
Also please tell me exactly what version of DB2 you are using at this stage.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[docam]

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.

 

[docam]

Sorry, the DB2 version is 8.1.5

 

[fredericofonseca]

Sorry. We need the SQL itself. that isnt SQL.

Please post the SQL itself, even if you have already tested it on the Command editor.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[docam]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com