CREATE materialized VIEW ctais2.V_SB_SKKP_FCGS_1
partition by range(NSR_SWJG_DM)
(
partition V_SB_SKKP_FCGS_P1 values less than ('11401000000'),
partition V_SB_SKKP_FCGS_P2 values less than ('11402000000'),
partition V_SB_SKKP_FCGS_P3 values less than ('11403000000'),
partition V_SB_SKKP_FCGS_P4 values less than ('11404000000'),
partition V_SB_SKKP_FCGS_P5 values less than ('11405000000'),
partition V_SB_SKKP_FCGS_P6 values less than ('11406000000'),
partition V_SB_SKKP_FCGS_P7 values less than ('11422000000'),
partition V_SB_SKKP_FCGS_P8 values less than ('11423000000'),
partition V_SB_SKKP_FCGS_P9 values less than ('11424000000'),
partition V_SB_SKKP_FCGS_P10 values less than ('11426000000'),
partition V_SB_SKKP_FCGS_P11 values less than ('11427000000'),
partition V_SB_SKKP_FCGS_P_MAX values less than (MAXVALUE)
)

[@more@]tablespace CTAIS2_SB_DAT
nologging
parallel
compress
build immediate
refresh complete on demand
enable query rewrite
as
SELECT A.NSRSBH,
A.NSRMC,
A.HGDM,
A.NSRDZDAH,
A.SCJYDZ,
B.YZPZZL_DM,
B.YZPZLRR_DM,
B.YZFSRQ_JZ,
B.JKFSRQ,
B.JKFSRQ_JZ,
B.JKQX,
B.HZJKSH,
B.SKCSFS_DM,
B.QLQQSLX_DM,
B.YSFPBL_ZY,
B.YSFPBL_SS,
B.YSFPBL_DS,
B.YSFPBL_XQ,
B.YSFPBL_XZ,
B.YSFPBL_XC,
DECODE(C.ZFRQ, NULL, B.SJRQ, NULL) SJRQ,
DECODE(C.ZFRQ, NULL, B.SJRQ_JZ, NULL) SJRQ_JZ,
DECODE(C.ZFRQ, NULL, B.SJXHRQ, NULL) SJXHRQ,
DECODE(C.ZFRQ, NULL, B.SJXHR_DM, NULL) SJXHR_DM,
DECODE(C.ZFRQ, NULL, B.RKRQ, NULL) RKRQ,
DECODE(C.ZFRQ, NULL, B.RKRQ_JZ, NULL) RKRQ_JZ,
DECODE(C.ZFRQ, NULL, B.RKXHRQ, NULL) RKXHRQ,
DECODE(C.ZFRQ, NULL, B.RKXHR_DM, NULL) RKXHR_DM,
C.YZPZXH,
C.YZFSRQ,
C.ZSFS_DM,
C.YSKM_DM,
C.YSFPBL_DM,
C.XSSR,
C.SL,
C.KSSL,
C.SJSE,
C.SKGK_DM,
C.SSSQ_Q,
C.SSSQ_Z,
C.YHZL_DM,
C.YH_DM,
C.YHZH,
C.ZSXM_DM,
C.ZSPM_DM,
C.BZ2,
C.SPZL_DM,
C.XTSPHM,
C.YSSPHM,
C.YSJYH,
C.YSCZZT_DM,
C.YSBL_YJFSRQ,
C.YSBL_FSRQ,
C.KPR_DM,
B.KPRQ,
C.JDXZ_DM,
C.SKSX_DM,
C.SKZL_DM,
C.DJZCLX_DM,
C.HY_DM,
C.ZSJG_DM,
C.NSR_SWJG_DM,
C.ZFRQ,
C.ZFR_DM,
C.SKSS_SWJG_DM,
C.JKFS_DM,
A.ZGSWRY_DM,
'00' YSCZJG_DM,
'00000000000' SZSC_DM
FROM SB_JKS C, SB_ZSXX B, DJ_NSRXX A
WHERE C.ZSXH = B.ZSXH
AND C.NSRDZDAH = A.NSRDZDAH
AND C.SPZL_DM IN ('501', '502', '505', '506', '591', '508', '593')
UNION ALL
SELECT A.NSRSBH,
A.NSRMC,
A.HGDM,
A.NSRDZDAH,
A.SCJYDZ,
B.YZPZZL_DM,
B.YZPZLRR_DM,
B.YZFSRQ_JZ,
B.JKFSRQ,
B.JKFSRQ_JZ,
B.JKQX,
B.HZJKSH,
B.SKCSFS_DM,
B.QLQQSLX_DM,
B.YSFPBL_ZY,
B.YSFPBL_SS,
B.YSFPBL_DS,
B.YSFPBL_XQ,
B.YSFPBL_XZ,
B.YSFPBL_XC,
DECODE(C.ZFRQ, NULL, B.SJRQ, NULL) SJRQ,
DECODE(C.ZFRQ, NULL, B.SJRQ_JZ, NULL) SJRQ_JZ,
DECODE(C.ZFRQ, NULL, B.SJXHRQ, NULL) SJXHRQ,
DECODE(C.ZFRQ, NULL, B.SJXHR_DM, NULL) SJXHR_DM,
DECODE(C.ZFRQ, NULL, B.RKRQ, NULL) RKRQ,
DECODE(C.ZFRQ, NULL, B.RKRQ_JZ, NULL) RKRQ_JZ,
DECODE(C.ZFRQ, NULL, B.RKXHRQ, NULL) RKXHRQ,
DECODE(C.ZFRQ, NULL, B.RKXHR_DM, NULL) RKXHR_DM,
C.YZPZXH,
C.YZFSRQ,
C.ZSFS_DM,
C.YSKM_DM,
C.YSFPBL_DM,
C.XSSR,
C.SL,
C.KSSL,
C.SJSE,
C.SKGK_DM,
C.SSSQ_Q,
C.SSSQ_Z,
C.YHZL_DM,
C.YH_DM,
C.YHZH,
C.ZSXM_DM,
C.ZSPM_DM,
'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000' BZ2,
C.SPZL_DM,
C.XTSPHM,
C.YSSPHM,
C.YSJYH,
C.YSCZZT_DM,
C.YSBL_YJFSRQ,
C.YSBL_FSRQ,
C.KPR_DM,
B.KPRQ,
C.JDXZ_DM,
C.SKSX_DM,
C.SKZL_DM,
C.DJZCLX_DM,
C.HY_DM,
C.ZSJG_DM,
C.NSR_SWJG_DM,
C.ZFRQ,
C.ZFR_DM,
C.SKSS_SWJG_DM,
C.JKFS_DM,
A.ZGSWRY_DM,
'00' YSCZJG_DM,
'00000000000' SZSC_DM
FROM SB_WSZ C, SB_ZSXX B, DJ_NSRXX A
WHERE C.ZSXH = B.ZSXH
AND C.NSRDZDAH = A.NSRDZDAH