sql

SELECT
WK6.HNSHTN_CD,
WK6.TRHKSK_CD,
WK6.NHNSK_CD,
CASE
WHEN WK6.NHNSK_CD = '001' THEN CASE
WHEN (
SELECT
COUNT(*) AS CNT
FROM (
SELECT======>there are 5 datas
*
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1')) > 0 THEN CASE
WHEN TO_DATE('20081031','YYYY-MM-DD') IN (
SELECT
MAX(SKY_DY)======>2007/7/20
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD ='001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') THEN 'T'
ELSE 'S'
END
ELSE 'S'
END
ELSE NULL
END AS OUT_SAI_KB,
CASE
WHEN LENGTH(RTRIM(a.YBN_NB)) > 3 THEN SUBSTR(RPAD(a.YBN_NB,7,' '),1,3)||'-'||SUBSTR(RPAD(a.YBN_NB,7,' '),
4,
7)
ELSE NVL(a.YBN_NB, ' ')
END YBN_NB,
a.TDFKN_NM,
a.SHKGN_NM,
a.BNCH,
a.TRHKSK_NM,
WK6.OUT_SEI_KNGK,
WK6.OUT_NB_SEI_KNGK,
WK6.OUT_SYOU,
WK6.OUT_DN_HS_KB,
TO_CHAR(TO_NUMBER(WK6.OUT_SHKN_KB)) AS OUT_SHKN_KB,
WK6.MS ,
NULL,
DECODE(TRIM(a.TL_NB_1_1),NULL,' ','TEL '||TRIM(a.TL_NB_1_1)) ,
NVL(b.KSH_NM,' ') AS KSH_NM
FROM TRHKSK_MST a,
KSH_MST b,
(
SELECT
WK5.HNSHTN_CD AS HNSHTN_CD,
WK5.TRHKSK_CD AS TRHKSK_CD,
WK5.NHNSK_CD AS NHNSK_CD,
SUM(WK5.OUT_SEI_KNGK) AS OUT_SEI_KNGK,
SUM(WK5.OUT_NB_SEI_KNGK) AS OUT_NB_SEI_KNGK,
SUM(WK5.OUT_SYOU) AS OUT_SYOU,
WK5.OUT_DN_HS_KB AS OUT_DN_HS_KB,
WK5.OUT_SHKN_KB AS OUT_SHKN_KB,
SUM(WK5.MS) AS MS
FROM (
SELECT
WK4.HNSHTN_CD,
WK4.TRHKSK_CD,
DECODE(a.KKYK_KB,'2','001',WK4.NHNSK_CD) AS NHNSK_CD,
WK4.OUT_SEI_KNGK,
WK4.OUT_NB_SEI_KNGK,
WK4.OUT_SYOU,
WK4.OUT_DN_HS_KB,
WK4.OUT_SHKN_KB,
WK4.MS
FROM (
SELECT
HNSHTN_CD,
TRHKSK_CD,
NHNSK_CD,
KKYK_KB
FROM TRHKSK_MST
WHERE TRHKSK_KB = '1'
GROUP BY HNSHTN_CD,
TRHKSK_CD,
NHNSK_CD,
KKYK_KB) a,
(
SELECT
WK3.HNSHTN_CD,
WK3.TRHKSK_CD,
WK3.NHNSK_CD,
WK3.OUT_SEI_KNGK,
WK3.OUT_NB_SEI_KNGK,
WK3.OUT_SYOU,
WK3.OUT_DN_HS_KB,
TO_CHAR(TO_NUMBER(WK3.OUT_SHKN_KB)) AS OUT_SHKN_KB,
WK3.MS
FROM (
SELECT
HNSHTN_CD AS HNSHTN_CD,
TRHKSK_CD AS TRHKSK_CD,
NHNSK_CD AS NHNSK_CD,
SUM(OUT_SEI_KNGK) AS OUT_SEI_KNGK,
SUM(OUT_NB_SEI_KNGK) AS OUT_NB_SEI_KNGK,
SUM(OUT_SYOU) AS OUT_SYOU,
OUT_DN_HS_KB AS OUT_DN_HS_KB,
OUT_SHKN_KB AS OUT_SHKN_KB,
SUM(MS) AS MS
FROM ((
SELECT
WK1.HNSHTN_CD,
WK1.TRHKSK_CD,
WK1.NHNSK_CD,
CASE
WHEN WK1.NHNSK_CD = '001' THEN ZEN_KN_SEI_FLAG
ELSE NULL
END AS OUT_SAI_KB,
WK1.YBN_NB,
WK1.TDFKN_NM,
WK1.SHKGN_NM,
WK1.BNCH,
WK1.TRHKSK_NM,
WK1.SUM_GNY_SEI_KNGK AS OUT_SEI_KNGK,
WK1.SUM_NB_SEI_KNGK AS OUT_NB_SEI_KNGK,
WK1.SUM_KN_SYOU AS OUT_SYOU,
WK1.DN_HS_KB AS OUT_DN_HS_KB,
WK1.SHKN_KB AS OUT_SHKN_KB,
WK1.MS
FROM (
SELECT
m.HNSHTN_CD,
m.TRHKSK_CD,
m.NHNSK_CD,
n.YBN_NB,
n.TDFKN_NM,
n.SHKGN_NM,
n.BNCH,
n.TRHKSK_NM,
n.DN_HS_KB,
n.SHKN_KB,
m.SUM_GNY_SEI_KNGK AS SUM_GNY_SEI_KNGK,
m.SUM_NB_KNGK_SEI AS SUM_NB_SEI_KNGK,
(m.SUM_UCHZ_KURO_GYU_ZERO - m.SUM_UCHZ_AKA_GYU_ZERO) AS SUM_KN_SYOU,
m.MS
FROM (
SELECT
e.HNSHTN_CD,
e.TRHKSK_CD,
e.NHNSK_CD,
SUM(NVL(e.SUM_GNY_SEI_KNGK,0)) AS SUM_GNY_SEI_KNGK,
SUM(NVL(e.SUM_NB_KNGK_SEI,0)) AS SUM_NB_KNGK_SEI,
SUM(NVL(e.SUM_UCHZ_KURO_GYU_ZERO,0)) AS SUM_UCHZ_KURO_GYU_ZERO,
SUM(NVL(e.SUM_UCHZ_AKA_GYU_ZERO,0)) AS SUM_UCHZ_AKA_GYU_ZERO,
SUM(e.MS) AS MS
FROM (
SELECT
ed.HNSHTN_CD,
ed.TRHKSK_CD,
ed.NHNSK_CD,
SUM(NVL(ed.GNY_SEI_KNGK,0)) AS SUM_GNY_SEI_KNGK,
SUM(NVL(ed.NB_KNGK_SEI,0)) AS SUM_NB_KNGK_SEI,
SUM(NVL(ed.UCHZ_KURO_GYU_ZERO,0)) AS SUM_UCHZ_KURO_GYU_ZERO,
SUM(NVL(ed.UCHZ_AKA_GYU_ZERO,0)) AS SUM_UCHZ_AKA_GYU_ZERO,
DECODE(SUM(ed.MS_KEY),0,0,SUM(ed.MS)) AS MS
FROM (
SELECT
HNSHTN_CD,
TRHKSK_CD,
NHNSK_CD,
NY_TN_NB,
DN_KB,
DN_NB,
DN_D,
KKR_KB,
GY_NB,
CASE
WHEN DN_KK_GNKN_KB = '0'
AND DN_GYBN_KB = 'E'
AND (GY_NB <> 0) THEN DECODE(KKR_KB,'0',KNGK,(-1)*(KNGK))
ELSE 0
END AS GNY_SEI_KNGK,
CASE
WHEN DN_KK_GNKN_KB = '0'
AND DN_GYBN_KB = 'F'
AND (GY_NB <> 0) THEN DECODE(KKR_KB,'0',KNGK,(-1)*(KNGK))
ELSE 0
END AS NB_KNGK_SEI,
CASE
WHEN KKR_KB = '0'
AND GY_NB = 0 THEN UCHZ_GK
ELSE 0
END AS UCHZ_KURO_GYU_ZERO,
CASE
WHEN KKR_KB = '1'
AND GY_NB = 0 THEN UCHZ_GK
ELSE 0
END AS UCHZ_AKA_GYU_ZERO,
CASE
WHEN GY_NB = 0 THEN MS
ELSE 0
END AS MS,
MS_KEY
FROM (
SELECT
a.HNSHTN_CD,
a.TRHKSK_CD,
a.NHNSK_CD,
a.NY_TN_NB,
a.DN_KB,
a.DN_NB,
a.DN_D,
a.KKR_KB,
a.GY_NB,
a.DN_GYBN_KB,
a.DN_KK_GNKN_KB,
a.KNGK,
a.UCHZ_GK,
a.STZ_GK,
a.MS,
CASE
WHEN a.DN_GYBN_KB IN ('1',
'2') THEN 1
ELSE 0
END AS MS_KEY
FROM TRNSCTN a,
(
SELECT
b.*
FROM (
SELECT
a.HNSHTN_CD,
a.NY_TN_NB,
a.DN_KB,
a.DN_NB,
a.DN_D,
(
SELECT
MAX(KKR_KB)
FROM TRNSCTN
WHERE HNSHTN_CD = '001'
AND NY_TN_NB = a.NY_TN_NB
AND DN_KB = '1'
AND DN_NB = a.DN_NB
AND DN_D = a.DN_D
AND DN_DY = a.DN_DY
AND NY_TN_NB < '80') AS KKR_KB,
a.DN_DY,
(
SELECT
COUNT(*)
FROM TRHKSK_MST
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND TRHKSK_CD = a.TRHKSK_CD
AND NHNSK_CD = a.NHNSK_CD
AND TRHKSK_KB = '1') AS ID
FROM TRNSCTN a
WHERE a.HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND a.DN_KB = '1'
AND a.GY_NB = 0
AND (a.SKY_DY = TO_DATE('20081031','YYYY-MM-DD'))
AND a.TRHKSK_KB = '1'
AND a.DN_KK_GNKN_KB = '0'
AND a.NY_TN_NB < '80'
GROUP BY a.HNSHTN_CD,
a.NY_TN_NB,
a.DN_KB,
a.DN_NB,
a.DN_D,
a.GY_NB,
a.DN_DY,
a.TRHKSK_CD,
a.NHNSK_CD,
a.TRHKSK_KB
HAVING COUNT(*) = 1 ) b
WHERE b.ID > 0) b,
TRHKSK_MST c
WHERE a.HNSHTN_CD = b.HNSHTN_CD
AND a.NY_TN_NB = b.NY_TN_NB
AND a.DN_KB = b.DN_KB
AND a.DN_NB = b.DN_NB
AND a.DN_D = b.DN_D
AND a.KKR_KB = b.KKR_KB
AND a.DN_DY = b.DN_DY
AND a.HNSHTN_CD = c.HNSHTN_CD
AND a.TRHKSK_CD = c.TRHKSK_CD
AND a.NHNSK_CD = c.NHNSK_CD
AND a.TRHKSK_KB = c.TRHKSK_KB
AND a.NY_TN_NB < '80')
WHERE TRHKSK_CD = 00000003) ed
GROUP BY ed.HNSHTN_CD,
ed.TRHKSK_CD,
ed.NHNSK_CD,
ed.NY_TN_NB,
ed.DN_KB,
ed.DN_NB,
ed.DN_D,
ed.KKR_KB ) e
GROUP BY e.HNSHTN_CD,
e.TRHKSK_CD,
e.NHNSK_CD) m,
TRHKSK_MST n
WHERE m.HNSHTN_CD = n.HNSHTN_CD
AND m.TRHKSK_CD = n.TRHKSK_CD
AND m.NHNSK_CD = n.NHNSK_CD
AND n.TRHKSK_KB = '1')WK1,
(
SELECT
CASE
WHEN CNT > 0 THEN CASE
WHEN TO_DATE('20081031','YYYY-MM-DD') IN (
SELECT
MAX(SKY_DY)
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') THEN 'T'
ELSE 'S'
END
ELSE 'S'
END AS ZEN_KN_SEI_FLAG,
CASE
WHEN CNT > 0 THEN CASE
WHEN TO_DATE('20081031','YYYY-MM-DD') IN (
SELECT
MAX(SKY_DY)
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') THEN (
SELECT
MAX(Z_SKY_GK)
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1'
AND SKY_DY = TO_DATE('20081031','YYYY-MM-DD')
GROUP BY HNSHTN_CD,
TRHKSK_CD,
NHNSK_CD,
TRHKSK_KB
HAVING COUNT(*) = 1)
ELSE (
SELECT
MAX(h.K_SKY_GK)
FROM SHM_TRHKSK_ZN h,
(
SELECT
MAX(SKY_DY) AS SKY_DY
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') i
WHERE h.HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND h.NHNSK_CD = '001'
AND h.TRHKSK_KB = '1'
AND h.SKY_DY = i.SKY_DY
GROUP BY h.HNSHTN_CD,
h.TRHKSK_CD,
h.NHNSK_CD,
h.TRHKSK_KB,
h.SKY_DY
HAVING COUNT(*) = 1)
END
ELSE 0
END AS ZEN_KN_SEI_GK
FROM (
SELECT
COUNT(*) AS CNT
FROM (
SELECT
*
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') y) x) WK2)
UNION ALL (
SELECT
t.HNSHTN_CD,
t.TRHKSK_CD,
t.NHNSK_CD,
CASE
WHEN CNT > 0 THEN CASE
WHEN TO_DATE('20081031','YYYY-MM-DD') IN (
SELECT
MAX(SKY_DY)
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD ='001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') THEN 'T'
ELSE 'S'
END
ELSE 'S'
END AS OUT_SAI_KB,
t.YBN_NB,
t.TDFKN_NM,
t.SHKGN_NM,
t.BNCH,
t.TRHKSK_NM,
0 AS OUT_SEI_KNGK,
0 AS OUT_NB_SEI_KNGK,
0 AS OUT_SYOU,
t.DN_HS_KB AS OUT_DN_HS_KB,
t.SHKN_KB AS OUT_SHKN_KB,
0 AS MS
FROM (
SELECT
COUNT(*) AS CNT
FROM (
SELECT
*
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD ='001'
AND TRHKSK_CD = 00000003
AND NHNSK_CD = '001'
AND TRHKSK_KB = '1') y) x,
TRHKSK_MST t
WHERE t.HNSHTN_CD = '001'
AND TRHKSK_CD = 00000003
AND t.NHNSK_CD = '001'
AND t.TRHKSK_KB = '1'))
WHERE HNSHTN_CD = '001'
GROUP BY HNSHTN_CD,
TRHKSK_CD,
NHNSK_CD,
OUT_SAI_KB,
YBN_NB,
TDFKN_NM,
SHKGN_NM,
BNCH,
TRHKSK_NM,
OUT_DN_HS_KB,
OUT_SHKN_KB) WK3 ) WK4
WHERE a.HNSHTN_CD=WK4.HNSHTN_CD
AND a.TRHKSK_CD=WK4.TRHKSK_CD
AND a.NHNSK_CD=WK4.NHNSK_CD) WK5
GROUP BY WK5.HNSHTN_CD,
WK5.TRHKSK_CD,
WK5.NHNSK_CD,
WK5.OUT_DN_HS_KB,
WK5.OUT_SHKN_KB) WK6
WHERE a.HNSHTN_CD=WK6.HNSHTN_CD
AND a.TRHKSK_CD=WK6.TRHKSK_CD
AND a.NHNSK_CD=WK6.NHNSK_CD
AND a.TRHKSK_KB='1'
AND a.KSH_CD = b.KSH_CD(+)
ORDER BY a.TRHKSK_CD,
a.NHNSK_CD


getFirstpageData() mehtod has sql

SELECT
DISTINCT TRH.TRHKSK_CD,
TRH.HNSHTN_CD
FROM TRHKSK_MST TRH
WHERE TRH.TRHKSK_CD IN (
SELECT
DHYTRHT.TRHKSK_CD
FROM DHYTRHKSK_MST DHYTRHT
WHERE DHYTRHT.DHYTRHKSK_CD ='00000001'
AND SKJ_DY='9999/12/31'
AND HNSHTN_CD='001')
AND TRH.TRHKSK_KB='1'


SELECT
Z_SKY_GK,
K_URG_GK,
K_STZ_GK,
K_NYKN_GK,
K_NBK_GK,
K_YK_GK
FROM SHM_TRHKSK_ZN
WHERE HNSHTN_CD = '001'
AND TRHKSK_KB = '1'
AND SKY_DY = '20081031'
AND NHNSK_CD = '001'
AND TRHKSK_CD = '00000007'