Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version: N/A
Mapping documents: N/A
Code between sessionFactory.openSession() and session.close(): N/A
Full stack trace of any exception that occurs: N/A
Name and version of the database you are using: N/A
The generated SQL (show_sql=true): N/A
Debug level Hibernate log excerpt: N/A
Hello,
In our application we use Hibernate to do the object/relation mapping.
We are developing a new application that does some complex reporting. The queries contain complex
group by,
case statements,
sub selects in select lists,
tables based on selects in from clauses (also called in-line views), projections, etc..
Before starting the Hibernate-approach I would like to know if it is feasible to use hibernate or to create a view and to map a simple object to that view. Not only feasible in the sense of technically possible, but also with respect to
performance.
The most complex query we have looks like this:
Code:
SELECT
G.ID AS ID
,F.PLANE AS PLANE
,F.PLANETYU_ID AS PLANETYU_ID
,F.PLANETYU_ID||F.PLANE AS PLANETYU_KEY
,F.JUI_MOUSE_BICICL AS JUI_MOUSE_BICICL
,F.JUI_DESCR AS JUI_DESCR
,F.MOOD_JUI_TYPE AS MOOD_JUI_TYPE
,F.BLOBXRSDXRSDG AS BLOBXRSDXRSDG
,F.BLAZER AS BLAZER
,F.BLOBS AS BLOBS
,F.BLOBS_POOLMODID AS BLOBS_POOLMODID
,F.XRDSXRDS AS XRDSXRDS
,F.TELEPHON_BEAR AS TELEPHON_BEAR
,F.POOLDEFID_BLOBS AS POOLDEFID_BLOBS
,ROUND(F.SD_SMAL_POSN_SEC ,0) AS RERIP_SMAL_SD
,ROUND(F.SD_BIGGE_POSN_SEC ,0) AS RERIP_BIGGE_SD
,ROUND(F.TD_SMAL_POSN_SEC ,0) AS RERIP_SMAL_TD
,ROUND(F.TD_BIGGE_POSN_SEC ,0) AS RERIP_BIGGE_TD
,ROUND(F.MOODPOOLIDD_SEC ,0) AS SDF_BY_PLANETYU
,F.SD_SMAL_MV_SEC AS RERIP_SMAL_MKSD
,F.SD_BIGGE_MV_SEC AS RERIP_BIGGE_MKSD
,F.TD_SMAL_MV_SEC AS RERIP_SMAL_MKTD
,F.TD_BIGGE_MV_SEC AS RERIP_BIGGE_MKTD
,F.POOLKUID_IND AS POOLKUID_IND
,G.LOOP_NUMBER
,G.BRANCH_NUMBER
,G.LOOP_NAME
,G.LOOP_TYPE
,ROUND(G.SD_SMAL_WEERTIME , 0) AS SMAL_POS_SETTLE
,ROUND(G.SD_BIGGE_WEERTIME , 0) AS BIGGE_POS_SETTLE
,G.SD_SMAL_MKT_DOLAR AS SMAL_MK_SETTLE
,G.SD_BIGGE_MKT_DOLAR AS BIGGE_MK_SETTLE
,ROUND(G.TD_SMAL_WEERTIME , 0) AS SMAL_POS_MOUSE
,ROUND(G.TD_BIGGE_WEERTIME , 0) AS BIGGE_POS_MOUSE
,G.TD_SMAL_MKT_DOLAR AS SMAL_MK_MOUSE
,G.TD_BIGGE_MKT_DOLAR AS BIGGE_MK_MOUSE
,G.MOODPOOLIDD AS SDF_BY_LOOP
,G.MOUSE_POOL
,G.SETTL_POOL
,'OOO' AS MEMO_OOO_TYPE
,CHAR(G.OOO_IND) AS MEMO_OOO_IND
,'999' AS MEMO_999_TYPE
,CHAR(G.999_IND) AS MEMO_999_IND
,'POL' AS MEMO_POL_TYPE
,CHAR(G.POL_IND) AS MEMO_POL_IND
,'SDF' AS MEMO_SDF_TYPE
,CHAR(G.SDF_IND) AS MEMO_SDF_IND
,'FFF' AS MEMO_FFF_TYPE
,CHAR(G.FFF_IND) AS MEMO_FFF_IND
FROM
( SELECT
PLANE AS PLANE
,Z.PLANETYU_ID AS PLANETYU_ID
,Z.JUI_MOUSE_BICICL AS JUI_MOUSE_BICICL
,Z.JUI_DESCR AS JUI_DESCR
,Z.MOOD_JUI_TYPE AS MOOD_JUI_TYPE
,Z.BLOBXRSDXRSDG AS BLOBXRSDXRSDG
,Z.BLAZER AS BLAZER
,Z.BLOBS AS BLOBS
,Z.BLOBS_POOLMODID AS BLOBS_POOLMODID
,Z.XRDSXRDS AS XRDSXRDS
,Z.TELEPHON_BEAR AS TELEPHON_BEAR
,Z.POOLDEFID_BLOBS AS POOLDEFID_BLOBS
,SUM(Z.SD_SMAL_WEERTIME) AS SD_SMAL_POSN_SEC
,SUM(Z.SD_BIGGE_WEERTIME) AS SD_BIGGE_POSN_SEC
,SUM(Z.TD_SMAL_WEERTIME) AS TD_SMAL_POSN_SEC
,SUM(Z.TD_BIGGE_WEERTIME) AS TD_BIGGE_POSN_SEC
,SUM(Z.MOODPOOLIDD) AS MOODPOOLIDD_SEC
,SUM(Z.SD_SMAL_MKT_DOLAR) AS SD_SMAL_MV_SEC
,SUM(Z.SD_BIGGE_MKT_DOLAR) AS SD_BIGGE_MV_SEC
,SUM(Z.TD_SMAL_MKT_DOLAR) AS TD_SMAL_MV_SEC
,SUM(Z.TD_BIGGE_MKT_DOLAR) AS TD_BIGGE_MV_SEC
,Z.POOLKUID_IND AS POOLKUID_IND
FROM
(SELECT
CASE
WHEN JUI_PLANE_TYPE = 'P' THEN PLANE ELSE ' '
END AS PLANE
,DTL.PLANE AS PLANETYU_ID
,' ' AS JUI_MOUSE_BICICL
,JUI_DSCR AS JUI_DESCR
,X.MOOD_JUI_TYPE AS MOOD_JUI_TYPE
,CASE
WHEN JUI_MAT_POOL = '00000000' THEN ''
WHEN DTL.PLANE < 'ZZZZZZZZZ' THEN ''
WHEN DTL.PLANE = 'ZZZZZZZZZ' THEN ''
ELSE
SUBSTR(JUI_MAT_POOL,1,4)||'-'||
SUBSTR(JUI_MAT_POOL,5,2)||'-'||
SUBSTR(JUI_MAT_POOL,7,2) END AS BLOBXRSDXRSDG
, JUI_BLAZER AS BLAZER
,DTL.BLOBS_USD AS BLOBS
,CASE
WHEN BLOBS_SRCE ='TTT' THEN 'M'
WHEN BLOBS_SRCE ='KKK' THEN 'M'
WHEN BLOBS_SRCE ='UIO' THEN 'M'
WHEN BLOBS_SRCE ='TYU' THEN 'M'
ELSE
'A' END AS BLOBS_POOLMODID
,'840' AS XRDSXRDS
,'' AS TELEPHON_BEAR
,DTL.BLOBS_USD AS POOLDEFID_BLOBS
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN JUI_IND
ELSE 0 END AS SD_SMAL_WEERTIME
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN JUI_IND
ELSE 0 END AS SD_BIGGE_WEERTIME
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN 0
ELSE 0 END AS TD_SMAL_WEERTIME
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN 0
ELSE 0 END AS TD_BIGGE_WEERTIME
, CASE
WHEN DTL.UID_TUID_BKT = 'RTR' THEN JUI_IND
WHEN DTL.UID_TUID_BKT = 'YUI' THEN JUI_IND
WHEN DTL.UID_TUID_BKT = 'GGG' THEN JUI_IND
WHEN DTL.UID_TUID_BKT = 'HUI' THEN JUI_IND
WHEN ((DTL.UID_TUID_BKT = 'KLL') AND (EFF_AMT_USD=0) )
THEN JUI_IND
WHEN ((DTL.UID_TUID_BKT = 'POI') AND (EFF_AMT_USD=0) )
THEN JUI_IND
ELSE 0
END AS MOODPOOLIDD
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN MKT_VAL_USD
ELSE 0 END AS SD_SMAL_MKT_DOLAR
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN MKT_VAL_USD
ELSE 0 END AS SD_BIGGE_MKT_DOLAR
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN 0
ELSE 0 END AS TD_SMAL_MKT_DOLAR
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN 0
ELSE 0 END AS TD_BIGGE_MKT_DOLAR
, '' AS POOLKUID_IND
FROM TESTDBA.LUV_UID_DTL DTL
,(SELECT DISTINCT SAL_JUI_TYPE, MOOD_JUI_TYPE
FROM TESTDBA.LUV_MOOD_SECTYPE ) X
,UIOPLLL.SYT_SECROOT
WHERE DTL.PLANE =JUI_ID
AND JUI_TYPE_NEW = X.SAL_JUI_TYPE ) Z
GROUP BY
Z.PLANE
,Z.PLANETYU_ID
,Z.JUI_MOUSE_BICICL
,Z.JUI_DESCR
,Z.MOOD_JUI_TYPE
,Z.BLOBXRSDXRSDG
,Z.BLAZER
,Z.BLOBS
,Z.BLOBS_POOLMODID
,Z.XRDSXRDS
,Z.TELEPHON_BEAR
,Z.POOLDEFID_BLOBS
,Z.POOLKUID_IND ) F ,
( SELECT
SEC.DATA_SRCE||CHAR(SEC.DTL_SEQ_NUM) AS ID
,SEC.PLANE
,SEC.PLANETYU_ID
,SEC.JUI_MOUSE_BICICL
,SEC.JUI_DESCR
,SEC.MOOD_JUI_TYPE
,SEC.BLOBXRSDXRSDG
,SEC.BLAZER
,SEC.BLOBS
,SEC.BLOBS_POOLMODID
,SEC.XRDSXRDS
, SEC.TELEPHON_BEAR
, SEC.POOLDEFID_BLOBS
, SEC.SD_SMAL_IND AS SD_SMAL_WEERTIME
, SEC.SD_BIGGE_IND AS SD_BIGGE_WEERTIME
, SEC.TD_SMAL_IND AS TD_SMAL_WEERTIME
, SEC.TD_BIGGE_IND AS TD_BIGGE_WEERTIME
, SEC.MOODPOOLIDD
, SEC.SD_SMAL_MKT_VAL AS SD_SMAL_MKT_DOLAR
, SEC.SD_BIGGE_MKT_VAL AS SD_BIGGE_MKT_DOLAR
, SEC.TD_SMAL_MKT_VAL AS TD_SMAL_MKT_DOLAR
, SEC.TD_BIGGE_MKT_VAL AS TD_BIGGE_MKT_DOLAR
, SEC.POOLKUID_IND
, ACT.LOOP_NUMBER
, ACT.BRANCH_NUMBER
, ACT.LOOP_TYPE
, ACT.LOOP_NAME
, ACT.MOUSE_POOL
, ACT.SETTL_POOL
,'OOO'
, ACT.OOO_IND
,'999'
, ACT.999_IND
,'POL'
, ACT.POL_IND
,'SDF'
, ACT.SDF_IND
,'FFF'
, ACT.FFF_IND
FROM
(SELECT
CASE
WHEN JUI_PLANE_TYPE = 'P' THEN PLANE ELSE ' '
END AS PLANE
,DTL.PLANE AS PLANETYU_ID
,' ' AS JUI_MOUSE_BICICL
,JUI_DSCR AS JUI_DESCR
,X.MOOD_JUI_TYPE AS MOOD_JUI_TYPE
,CASE
WHEN JUI_MAT_POOL = '00000000' THEN ''
WHEN DTL.PLANE < 'ZZZZZZZZZ' THEN ''
WHEN DTL.PLANE = 'ZZZZZZZZZ' THEN ''
ELSE
SUBSTR(JUI_MAT_POOL,1,4)||'-'||
SUBSTR(JUI_MAT_POOL,5,2)||'-'||
SUBSTR(JUI_MAT_POOL,7,2) END AS BLOBXRSDXRSDG
, JUI_BLAZER AS BLAZER
,DTL.BLOBS_USD AS BLOBS
,CASE
WHEN BLOBS_SRCE ='TTT' THEN 'M'
WHEN BLOBS_SRCE ='KKK' THEN 'M'
WHEN BLOBS_SRCE ='UIO' THEN 'M'
WHEN BLOBS_SRCE ='TYU' THEN 'M'
ELSE
'A' END AS BLOBS_POOLMODID
,'840' AS XRDSXRDS
,'' AS TELEPHON_BEAR
,DTL.BLOBS_USD AS POOLDEFID_BLOBS
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN JUI_IND
ELSE 0 END AS SD_SMAL_IND
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN JUI_IND
ELSE 0 END AS SD_BIGGE_IND
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN 0
ELSE 0 END AS TD_SMAL_IND
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN 0
ELSE 0 END AS TD_BIGGE_IND
, CASE
WHEN DTL.UID_TUID_BKT = 'RTR' THEN JUI_IND
WHEN DTL.UID_TUID_BKT = 'YUI' THEN JUI_IND
WHEN DTL.UID_TUID_BKT = 'GGG' THEN JUI_IND
WHEN DTL.UID_TUID_BKT = 'HUI' THEN JUI_IND
WHEN ((DTL.UID_TUID_BKT = 'KLL') AND (EFF_AMT_USD=0) )
THEN JUI_IND
WHEN ((DTL.UID_TUID_BKT = 'POI') AND (EFF_AMT_USD=0) )
THEN JUI_IND
ELSE 0
END AS MOODPOOLIDD
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN MKT_VAL_USD
ELSE 0 END AS SD_SMAL_MKT_VAL
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN MKT_VAL_USD
ELSE 0 END AS SD_BIGGE_MKT_VAL
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'E%' THEN 0
ELSE 0 END AS TD_SMAL_MKT_VAL
, CASE
WHEN DTL.UID_TUID_BKT LIKE 'F%' THEN 0
ELSE 0 END AS TD_BIGGE_MKT_VAL
, '' AS POOLKUID_IND
, DTL.DTL_SEQ_NUM
, DTL.DATA_SRCE
FROM TESTDBA.LUV_UID_DTL DTL
,(SELECT DISTINCT SAL_JUI_TYPE, MOOD_JUI_TYPE
FROM TESTDBA.LUV_MOOD_SECTYPE ) X
,UIOPLLL.SYT_SECROOT
WHERE DTL.PLANE =JUI_ID
AND JUI_TYPE_NEW = X.SAL_JUI_TYPE ) SEC
,
(
SELECT '001'||DTL.CUST_LOOP_NUM||HLD.KKK_LOOP_TYPE AS LOOP_NUMBER
,'001' AS BRANCH_NUMBER
, HLD.KKK_LOOP_TYPE AS LOOP_TYPE
, CET.LOOP_NAME AS LOOP_NAME
, DTL.DATA_SRCE AS DATA_SRCE
, UID_TUID_BKT AS UID_TUID_BKT
, DTL.DTL_SEQ_NUM AS DTL_SEQ_NUM
, DTL.PLANE AS PLANE
, JUI_IND AS IND
, SETTL_POOL AS SETTL_POOL
, MOUSE_POOL AS MOUSE_POOL
,'OOO'
,OOO AS OOO_IND
,'999'
,999 AS 999_IND
,'POL'
,POL AS POL_IND
,'SDF'
,SDF AS SDF_IND
,'FFF'
,FFF AS FFF_IND
FROM TESTDBA.LUV_UID_DTL DTL
,TESTDBA.LUV_KKK_LOOP KKK
,(
SELECT PLANE, CUST_LOOP_NUM, KKK_LOOP_NUM,KKK_LOOP_TYPE,
OOO,999,SDF,FFF,POL,REO,
(OOO + 999 + SDF + FFF + POL + REO ) AS SDIND
FROM TESTDBA.LUV_KKK_HOLDERS
WHERE
(OOO + 999 + SDF + FFF + POL + REO ) <> 0
AND SDIND <>
(OOO + 999 + SDF + FFF + POL + REO )
UNION ALL
SELECT PLANE, CUST_LOOP_NUM, KKK_LOOP_NUM,KKK_LOOP_TYPE,
OOO,999,SDF,FFF,POL,REO,
(SDIND-(OOO + 999 + SDF + FFF + POL + REO )) AS SDIND
FROM TESTDBA.LUV_KKK_HOLDERS
WHERE
(OOO + 999 + SDF + FFF + POL + REO ) <> 0
AND SDIND <>
(OOO + 999 + SDF + FFF + POL + REO )
UNION ALL
SELECT PLANE, CUST_LOOP_NUM, KKK_LOOP_NUM, KKK_LOOP_TYPE,
OOO,999,SDF,FFF,POL,REO,
SDIND AS SDIND
FROM TESTDBA.LUV_KKK_HOLDERS
WHERE NOT (
(OOO + 999 + SDF + FFF + POL + REO ) <> 0
AND SDIND <>
(OOO + 999 + SDF + FFF + POL + REO ))
) HLD
,UIOPLLL.CET_PLANER1 CET
WHERE
KKK.DTL_SEQ_NUM = DTL.DTL_SEQ_NUM
AND KKK.DATA_SRCE = DTL.DATA_SRCE
AND DTL.DATA_SRCE = 'KKK'
AND SUBSTR(KKK.KKK_LOOP_NUM,2,7) = SUBSTR(DTL.CUST_LOOP_NUM,1,7)
AND HLD.PLANE = DTL.PLANE
AND HLD.KKK_LOOP_TYPE = KKK.KKK_LOOP_TYPE
AND CET.LOOP_NMBR = DTL.CUST_LOOP_NUM
AND HLD.CUST_LOOP_NUM = DTL.CUST_LOOP_NUM
AND HLD.KKK_LOOP_NUM = KKK.KKK_LOOP_NUM
AND HLD.SDIND = DTL.JUI_IND
AND PLUG_IND <> 'P'
UNION ALL
SELECT '001'||CUST_LOOP_NUM||'X' AS LOOP_NUMBER
,'001' AS BRANCH_NUMBER
, 'X' AS LOOP_TYPE
, CET.LOOP_NAME AS LOOP_NAME
, DTL.DATA_SRCE AS DATA_SRCE
, UID_TUID_BKT AS UID_TUID_BKT
, DTL.DTL_SEQ_NUM AS DTL_SEQ_NUM
, DTL.PLANE AS PLANE
, JUI_IND AS IND
, SETTL_POOL AS SETTL_POOL
, MOUSE_POOL AS MOUSE_POOL
,'OOO'
,DECIMAL(0,13,3) AS OOO_IND
,'999'
,DECIMAL(0,13,3) AS 999_IND
,'POL'
,DECIMAL(0,13,3) AS POL_IND
,'SDF'
,DECIMAL(0,13,3) AS SDF_IND
,'FFF'
,DECIMAL(0,13,3) AS FFF_IND
FROM TESTDBA.LUV_UID_DTL DTL ,
UIOPLLL.CET_PLANER1 CET
WHERE
( DTL.DATA_SRCE <> 'KKK' ) AND (PLUG_IND <> 'P')
AND CET.LOOP_NMBR = DTL.CUST_LOOP_NUM
UNION ALL
SELECT '001'||CUST_LOOP_NUM||'X' AS LOOP_NUMBER
,'001' AS BRANCH_NUMBER
, 'X' AS LOOP_TYPE
,'RECONCILIATION PLANER1 ' AS LOOP_NAME
, DTL.DATA_SRCE AS DATA_SRCE
, UID_TUID_BKT AS UID_TUID_BKT
, DTL.DTL_SEQ_NUM AS DTL_SEQ_NUM
, DTL.PLANE AS PLANE
, JUI_IND AS IND
, SETTL_POOL AS SETTL_POOL
, MOUSE_POOL AS MOUSE_POOL
,'OOO'
,DECIMAL(0,13,3) AS OOO_IND
,'999'
,DECIMAL(0,13,3) AS 999_IND
,'POL'
,DECIMAL(0,13,3) AS POL_IND
,'SDF'
,DECIMAL(0,13,3) AS SDF_IND
,'FFF'
,DECIMAL(0,13,3) AS FFF_IND
FROM TESTDBA.LUV_UID_DTL DTL
WHERE
PLUG_IND = 'P'
UNION ALL
SELECT '001'||CUST_LOOP_NUM||'X' AS LOOP_NUMBER
,'001' AS BRANCH_NUMBER
, 'X' AS LOOP_TYPE
,'TEST PLANER1 ' AS LOOP_NAME
, DTL.DATA_SRCE AS DATA_SRCE
, UID_TUID_BKT AS UID_TUID_BKT
, DTL.DTL_SEQ_NUM AS DTL_SEQ_NUM
, DTL.PLANE AS PLANE
, JUI_IND AS IND
, SETTL_POOL AS SETTL_POOL
, MOUSE_POOL AS MOUSE_POOL
,'OOO'
,DECIMAL(0,13,3) AS OOO_IND
,'999'
,DECIMAL(0,13,3) AS 999_IND
,'POL'
,DECIMAL(0,13,3) AS POL_IND
,'SDF'
,DECIMAL(0,13,3) AS SDF_IND
,'FFF'
,DECIMAL(0,13,3) AS FFF_IND
FROM TESTDBA.LUV_UID_DTL DTL
WHERE
CUST_LOOP_NUM LIKE 'YB%' ) ACT
WHERE ACT.DATA_SRCE = SEC.DATA_SRCE
AND ACT.DTL_SEQ_NUM = SEC.DTL_SEQ_NUM ) G
WHERE F.PLANETYU_ID = G.PLANETYU_ID
Grtx,
Ivo Willemsen
Login
FAQ
Search
