This is related to AS400 DB2. Please see the below query(create statement
for a view) and lemme know if AS400 DB2 supports list in the predicate of
the where clause. And whether there is support for UNION in the subquery?

The create view is given below:-


CREATE VIEW VW_CHAPVIA_AMOUNT AS
SELECT
CH.EMP_CODE,CH.CHAPVIA_CODE,CH.FROM_DATE,CH.TO_DATE,CH.SUM
AMOUNT,CH.MAX_LIMIT,
CASE WHEN CH.MAX_LIMIT=0 THEN
CH.SUM
ELSE
CASE WHEN CH.MAX_LIMIT>=CH.SUM THEN
CH.SUM
ELSE
CH.MAX_LIMIT
END
END AS MIN_AMOUNT FROM
(SELECT CHAPDATA.EMP_CODE,CHAPDATA.CHAPVIA_CODE,
CHAPDATA.FROM_DATE,CHAPDATA.TO_DATE,
COALESCE(SUM(COALESCE(CHAPMAST.CHAPVIA_MAX_LIMIT,0)),0)
MAX_LIMIT,COALESCE(SUM(COALESCE(CHAPDATA.AMOUNT,0)),0)
SUM FROM CHAPVIA_DATA_ENTRY
CHAPDATA,CHAPVIA_MASTER CHAPMAST
WHERE CHAPMAST.CHAPVIA_CODE=CHAPDATA.CHAPVIA_CODE
GROUP BY
CHAPDATA.EMP_CODE,CHAPDATA.CHAPVIA_CODE,
CHAPDATA.FROM_DATE,CHAPDATA.TO_DATE ) CH WHERE
(CH.EMP_CODE,CH.FROM_DATE,CH.CHAPVIA_CODE)
NOT IN
(SELECT
SPL.EMP_CODE,SPL.FROM_DATE,SPL.CHAPVIA_CODE FROM
(SELECT
SPDM.ID,SPDM.EMP_CODE,CHAPMAST.CHAPVIA_CODE,SPDM.FROM_DATE,
SPDM.TO_DATE,COALESCE(SUM(CHAPMAST.CHAPVIA_MAX_SPL_LIMIT),0)
MAX_LIMIT FROM
CHAPVIA_MASTER CHAPMAST,
(SELECT
SPD.ID,SPM.CHAPVIA_CODE,SPD.EMP_CODE,SPD.SETUP_PARAM_CODE,SPD.FROM_DATE,
SPD.TO_DATE FROM
SETUP_PARAM_MASTER SPM,
(SELECT
ID,EMP_CODE,SETUP_PARAM_CODE,FROM_DATE,TO_DATE FROM
SETUP_PARAM_DATA_ENTRY
WHERE APPLICABLE=1) SPD
WHERE
SPD.SETUP_PARAM_CODE=SPM.SETUP_PARAM_CODE) SPDM WHERE
CHAPMAST.CHAPVIA_CODE=SPDM.CHAPVIA_CODE
GROUP BY
SPDM.ID,SPDM.EMP_CODE,
CHAPMAST.CHAPVIA_CODE,SPDM.FROM_DATE,SPDM.TO_DATE) SPL)
GROUP BY
CH.EMP_CODE,CH.FROM_DATE,CH.TO_DATE,CH.CHAPVIA_CODE,CH.MAX_LIMIT,CH.SUM
UNION
(SELECT
CH.EMP_CODE,CH.CHAPVIA_CODE,CH.FROM_DATE,CH.TO_DATE,CH.AMOUNT,SPL.MAX_LIMIT,
CASE WHEN SPL.MAX_LIMIT=0 THEN
CH.AMOUNT
ELSE
CASE WHEN
SPL.MAX_LIMIT<=CH.AMOUNT THEN
SPL.MAX_LIMIT
ELSE
CH.AMOUNT
END
END AS MIN_AMOUNT FROM CHAPVIA_DATA_ENTRY CH,
(SELECT
SPDM.ID,SPDM.EMP_CODE,CHAPMAST.CHAPVIA_CODE,

SPDM.FROM_DATE,SPDM.TO_DATE,COALESCE(SUM(CHAPMAST.CHAPVIA_MAX_SPL_LIMIT),0)
MAX_LIMIT FROM CHAPVIA_MASTER CHAPMAST,
(SELECT
SPD.ID,SPM.CHAPVIA_CODE,SPD.EMP_CODE,SPD.SETUP_PARAM_CODE,SPD.FROM_DATE,
SPD.TO_DATE FROM SETUP_PARAM_MASTER SPM,
(SELECT
ID,EMP_CODE,SETUP_PARAM_CODE,FROM_DATE,TO_DATE FROM
SETUP_PARAM_DATA_ENTRY WHERE APPLICABLE=1) SPD
WHERE SPD.SETUP_PARAM_CODE=SPM.SETUP_PARAM_CODE)
SPDM WHERE
CHAPMAST.CHAPVIA_CODE=SPDM.CHAPVIA_CODE
GROUP BY
SPDM.ID,SPDM.EMP_CODE,
CHAPMAST.CHAPVIA_CODE,SPDM.FROM_DATE,SPDM.TO_DATE
) SPL WHERE
CH.EMP_CODE=SPL.EMP_CODE AND
CH.CHAPVIA_CODE=SPL.CHAPVIA_CODE AND
CH.FROM_DATE=SPL.FROM_DATE AND
CH.TO_DATE=SPL.TO_DATE)

Arnab

DB2 & UDB email list listserv db2-l LazyDBA home page