Subquery within SubQuery in SQL - DB2

Question

I am having issue when trying to make a the sub query shown in the first filter dynamically based on one of the results returned from the query. Can someone please tell me what I am doing wrong. In the first subquery it worked.

        ( SELECT
    MAX( MAX_DATE - MIN_DATE ) AS NUM_CONS_DAYS
  FROM
    (
    SELECT
        MIN(TMP.D_DAT_INDEX_DATE) AS MIN_DATE,
        MAX(TMP.D_DAT_INDEX_DATE) AS MAX_DATE,
        SUM(INDEX_COUNT)          AS SUM_INDEX
    FROM
        (
            SELECT
                D_DAT_INDEX_DATE,
                INDEX_COUNT,
                D_DAT_INDEX_DATE - (DENSE_RANK() OVER(ORDER BY D_DAT_INDEX_DATE)) DAYS AS G
            FROM
                DWH.MQT_SUMMARY_WATER_READINGS
            WHERE
                N_COD_METER_CNTX_KEY = 79094
        ) AS TMP
    GROUP BY
        TMP.G
    ORDER BY
        1
) ) AS MAX_NUM_CONS_DAYS

Above is the subquery I am trying to replace 123456 with CTXTKEY or CTXT.N_COD_METER_CNTX_KEY from query. Below is the full code. Please note than in the subquery before "MAX_NUM_CONS_DAYS" it worked. However, it was only one subquery down.

SELECT
    N_COD_WM_DWH_KEY, 
    V_COD_WM_SN_2,
    N_COD_SP_ID,
    CTXKEY,
    V_COD_MIU_SN,
    N_COD_POD,
    MIU_CAT,
    V_COD_SITR_ASSOCIATED,
    WO_INST_DATE,
    WO_MIU_CAT,
    DAYSRECEIVED3,
    MAX_NUM_CONS_DAYS,
    ( CASE WHEN ( DAYSRECEIVED3 = 3 ) THEN 'Y' ELSE 'N' END ) AS GREEN,
    ( CASE WHEN ( DAYSRECEIVED3 < 3 AND DAYSRECEIVED3 > 0 ) THEN 'Y' ELSE 'N' END ) AS BLUE,
    ( CASE WHEN ( DAYSRECEIVED3 = 0 AND MAX_NUM_CONS_DAYS >= 5 ) THEN 'Y' ELSE 'N' END ) AS ORANGE,
    ( CASE WHEN ( DAYSRECEIVED3 = 0 AND MAX_NUM_CONS_DAYS BETWEEN 1 and 4 ) THEN 'Y' ELSE 'N' END ) AS RED

FROM
(
SELECT
    WMETER.N_COD_WM_DWH_KEY,
    WMETER.V_COD_WM_SN_2,
    WMETER.N_COD_SP_ID,
    CTXT.N_COD_METER_CNTX_KEY AS CTXKEY,
    CTXT.V_COD_MIU_SN,
    CTXT.N_COD_POD,
    MIU.N_COD_MIU_CATEGORY AS MIU_CAT,
    CTXT.V_COD_SITR_ASSOCIATED,
    T1.D_DAT_PLAN_INST AS WO_INST_DATE,
    T1.N_COD_MIU_CATEGORY AS WO_MIU_CAT,

    ( SELECT COUNT( DISTINCT D_DAT_INDEX_DATE ) FROM DWH.MQT_SUMMARY_WATER_READINGS WHERE ( N_COD_METER_CNTX_KEY = CTXT.N_COD_METER_CNTX_KEY ) AND D_DAT_INDEX_DATE BETWEEN ( '2013-07-10' ) AND ( '2013-07-12' ) ) AS DAYSRECEIVED3,

    ( SELECT
        MAX( MAX_DATE - MIN_DATE ) AS NUM_CONS_DAYS
      FROM
        (
        SELECT
            MIN(TMP.D_DAT_INDEX_DATE) AS MIN_DATE,
            MAX(TMP.D_DAT_INDEX_DATE) AS MAX_DATE,
            SUM(INDEX_COUNT)          AS SUM_INDEX
        FROM
            (
                SELECT
                    D_DAT_INDEX_DATE,
                    INDEX_COUNT,
                    D_DAT_INDEX_DATE - (DENSE_RANK() OVER(ORDER BY D_DAT_INDEX_DATE)) DAYS AS G
                FROM
                    DWH.MQT_SUMMARY_WATER_READINGS
                WHERE
                    N_COD_METER_CNTX_KEY = 79094
            ) AS TMP
        GROUP BY
            TMP.G
        ORDER BY
            1
    ) ) AS MAX_NUM_CONS_DAYS



FROM DWH.DWH_WATER_METER AS WMETER
LEFT JOIN DWH.DWH_WMETER_CONTEXT AS CTXT
    ON WMETER.N_COD_WM_DWH_KEY = CTXT.N_COD_WM_DWH_KEY
LEFT JOIN DWH.DWH_MIU AS MIU
    ON CTXT.V_COD_MIU_SN = MIU.V_COD_MIU_SN
LEFT JOIN 
    ( SELECT V_COD_CORR_WAT_METER_SN, D_DAT_PLAN_INST, N_COD_MIU_CATEGORY
        FROM DWH.DWH_ORDER_MANAGEMENT_FACT
        JOIN DWH.DWH_MIU
            ON DWH.DWH_ORDER_MANAGEMENT_FACT.V_COD_MIU_SN = DWH.DWH_MIU.V_COD_MIU_SN
) AS T1
    ON WMETER.V_COD_WM_SN_2 = T1.V_COD_CORR_WAT_METER_SN
WHERE
    ( V_COD_SITR_ASSOCIATED = 'X' ) 
    AND ( ( MIU.N_COD_MIU_CATEGORY <> 4 ) OR ( ( MIU.N_COD_MIU_CATEGORY IS NULL ) AND ( ( T1.N_COD_MIU_CATEGORY <> 4  ) OR ( T1.N_COD_MIU_CATEGORY IS NULL  ) ) ) )
)

Error I am getting is:

Error Code: -204, SQL State: 42704

Answer 1

I would say that a good option here would be to use a CTE, or Common Table Expression. You can do something similar to the following:

WITH CTE_X AS(
SELECT VAL_A
      ,VAL_B
  FROM TABLE_A)
,CTE_Y AS(
SELECT VAL_C
      ,VAL_B
  FROM TABLE_B)
SELECT VAL_A
      ,VAL_B
  FROM CTE_X X
    JOIN CTE_Y Y
      ON X.VAL_A = Y.VAL_C;

While this isn't specific to your example, it does show that CTE's create a sort of temporary "in memory" table that you can access in a subsequent query. This should allow you to issue your inner two subselects as a CTE, and then use the CTE in the "SELECT MAX( MAX_DATE - MIN_DATE ) AS NUM_CONS_DAYS" query.

Answer 2

You cannot reference columns from the outer select in the subselect, no more than 1 level deep anyway. If I correctly understand what you're doing, you'll probably need to join DWH.MQT_SUMMARY_WATER_READINGS and DWH.DWH_WMETER_CONTEXT in the outer select.