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
)
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
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.