Hi,
I use HIBERNATE with ORACLE in my application. I have a view which name is VW_LABORATORY_TESTS. The part of the SQL code from the view for which I have a problem is the following :
Code:
select '#' ||
(case when (select max(idat1.VALUE) from itemdata idat1, itemsequences iseq1, items i1
where idat1.ITEM_GROUP_DATA_ID=igdat.ITEM_GROUP_DATA_ID
and i1.ITEM_ID=iseq1.ITEM_ID
and iseq1.ITEM_SEQUENCE_ID=idat1.ITEM_SEQUENCE_ID
and ((i1.SDS_VAR_NAME like '%' || ';LBDTC;' || '%') or (i1.SDS_VAR_NAME like 'LBDTC;' || '%') or (i1.SDS_VAR_NAME like '%' || ';LBDTC') or (i1.SDS_VAR_NAME = 'LBDTC'))
and i1.ITEMDATA_TYPE_ID=5)
is NULL then (case when ((select max(idat1.VALUE) from itemdata idat1, itemsequences iseq1, items i1
where idat1.ITEM_GROUP_DATA_ID=igdat.ITEM_GROUP_DATA_ID
and i1.ITEM_ID=iseq1.ITEM_ID
and iseq1.ITEM_SEQUENCE_ID=idat1.ITEM_SEQUENCE_ID
and ((i1.SDS_VAR_NAME like '%' || ';LBDTC;' || '%') or (i1.SDS_VAR_NAME like 'LBDTC;' || '%') or (i1.SDS_VAR_NAME like '%' || ';LBDTC') or (i1.SDS_VAR_NAME = 'LBDTC'))
and i1.ITEMDATA_TYPE_ID=4) ||
(select max (idat1.VALUE) from itemdata idat1, itemsequences iseq1, items i1
where idat1.ITEM_GROUP_DATA_ID=igdat.ITEM_GROUP_DATA_ID
and i1.ITEM_ID=iseq1.ITEM_ID
and iseq1.ITEM_SEQUENCE_ID=idat1.ITEM_SEQUENCE_ID
and ((i1.SDS_VAR_NAME like '%' || ';LBDTC;' || '%') or (i1.SDS_VAR_NAME like 'LBDTC;' || '%') or (i1.SDS_VAR_NAME like '%' || ';LBDTC') or (i1.SDS_VAR_NAME = 'LBDTC'))
and i1.ITEMDATA_TYPE_ID=3))
is NULL then (case when CAST('null' AS VARCHAR(255))
= 'null' then CAST(' ' AS VARCHAR(255))
else CAST('null' AS VARCHAR(255)) end)
else CAST(((select max(idat1.VALUE) from itemdata idat1, itemsequences iseq1, items i1
where idat1.ITEM_GROUP_DATA_ID=igdat.ITEM_GROUP_DATA_ID
and i1.ITEM_ID=iseq1.ITEM_ID
and iseq1.ITEM_SEQUENCE_ID=idat1.ITEM_SEQUENCE_ID
and ((i1.SDS_VAR_NAME like '%' || ';LBDTC;' || '%') or (i1.SDS_VAR_NAME like 'LBDTC;' || '%') or (i1.SDS_VAR_NAME like '%' || ';LBDTC') or (i1.SDS_VAR_NAME = 'LBDTC'))
and i1.ITEMDATA_TYPE_ID=4) ||
(select max (idat1.VALUE) from itemdata idat1, itemsequences iseq1, items i1
where idat1.ITEM_GROUP_DATA_ID=igdat.ITEM_GROUP_DATA_ID
and i1.ITEM_ID=iseq1.ITEM_ID
and iseq1.ITEM_SEQUENCE_ID=idat1.ITEM_SEQUENCE_ID
and ((i1.SDS_VAR_NAME like '%' || ';LBDTC;' || '%') or (i1.SDS_VAR_NAME like 'LBDTC;' || '%') or (i1.SDS_VAR_NAME like '%' || ';LBDTC') or (i1.SDS_VAR_NAME = 'LBDTC'))
and i1.ITEMDATA_TYPE_ID=3))AS VARCHAR(255)) end)
else (select max(idat1.VALUE) from itemdata idat1, itemsequences iseq1, items i1
where idat1.ITEM_GROUP_DATA_ID=igdat.ITEM_GROUP_DATA_ID
and i1.ITEM_ID=iseq1.ITEM_ID
and iseq1.ITEM_SEQUENCE_ID=idat1.ITEM_SEQUENCE_ID
and ((i1.SDS_VAR_NAME like '%' || ';LBDTC;' || '%') or (i1.SDS_VAR_NAME like 'LBDTC;' || '%') or (i1.SDS_VAR_NAME like '%' || ';LBDTC') or (i1.SDS_VAR_NAME = 'LBDTC'))
and i1.ITEMDATA_TYPE_ID=5) end) || '#'
LBDTC
from
itemgroupdata igdat,
formdata fdat,
eventdata edat,
subjects sbj,
projects s,
sites si,
addresses ad,
countries c,
(select idat2.ITEM_GROUP_DATA_ID from items i2,itemsequences iseq2, itemdata idat2 where
i2.ITEM_ID=iseq2.ITEM_ID and iseq2.ITEM_SEQUENCE_ID=idat2.ITEM_SEQUENCE_ID and i2.SDS_VAR_NAME like '%' || 'LBTESTCD' || '%') res
where igdat.ITEM_GROUP_DATA_ID=res.ITEM_GROUP_DATA_ID
and igdat.FORM_DATA_ID=fdat.FORM_DATA_ID
and fdat.EVENT_DATA_ID=edat.EVENT_DATA_ID
and edat.SUBJECT_ID=sbj.SUBJECT_ID
and sbj.SITE_ID=si.SITE_ID
and sbj.PROJECT_ID=s.PROJECT_ID
and si.ADDRESS_ID=ad.ADDRESS_ID(+)
and ad.COUNTRY_ID=c.ID(+)
and sbj.QA='Y'
and s.PROJECT_ID=42264;
When I ran this request on SQL DEVELOPPER, everything is fine : I get 49 rows result :
Code:
LBDTC
----------------------------
# #
# #
# #
# #
#12/14/2007 08:45#
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
# #
#12/30/2007 09:00#
#12/30/2007 09:00#
#12/30/2007 09:00#
#12/30/2007 09:20#
49 rows selected
In my application, I try to get the result by an HIBERNATE QUERY :
Code:
line 1:String sql="select LBDTC from VW_LABORATORY_TESTS";
line 2:Query reportQuery = getSession().createSQLQuery(sql);
line 3:List<Object> result = reportQuery.list();
And the logs of the application tells me that at the line 3, the first 20 rows are loaded and afterwards the program is looping infinitively without giving me any results. There is no stack trace. The program is just running infinitively without giving me any informations. I add that in the logs information, the program seems to load the results 10 by 10 : so I have the information that from row 1 to row 10 are loaded, than afterwards from row 11 to row 20 are loaded and than nothing is happening.
I don't understand why the request is running with ORACLE SQL DEVELOPPER and not with the HIBERNATE QUERY.
Can anybody give me a clue to solve that problem.
Thank you in advance
Login
FAQ
Search
