MYSQL error code: 1054 Unknown column in where clause. Error occurring in Nested SubQueries

Question

I am trying to get through a problem where there are multiple accounts of same scheme on same customer id. On a given txn date I want to retrieve the total Sanctioned Limit and total utilized amount from these accounts. Below is the SQL query I have constructed.

SELECT 
  cust_id, 
  tran_date, 
  rollover_date, 
  next_rollover, 
  (
    SELECT
      acc_num as kcc_ac 
    FROM
      dbzsubvention.acc_disb_amt a 
    WHERE
      (a.tran_date <= AB.tran_date) 
      AND a.sch_code = 'xxx' 
      AND a.cust_id = AB.cust_id 
    ORDER BY 
      a.tran_date desc 
    LIMIT 
      1
  ) KCC_ACC, 
  (
    SELECT
      SUM(kcc_prod) 
    FROM
      (
        SELECT
          prod_limit as kcc_prod, 
          acc_num, 
          s.acc_status 
        FROM
          dbzsubvention.acc_disb_amt a 
          inner join dbzsubvention.acc_rollover_all_sub_status s using (acc_num) 
          left join dbzsubvention.acc_close_date c using (acc_num) 
        WHERE
          a.cust_id = AB.cust_id 
          AND a.tran_date <= AB.tran_date 
          AND (
            ac_close > AB.tran_date || ac_close is null
          ) 
          AND a.sch_code = 'xxx' 
          AND s.acc_status = 'R' 
          AND s.rollover_date <= AB.tran_date 
          AND (
            AB.tran_date < s.next_rollover || s.next_rollover is null
          ) 
        GROUP BY 
          acc_num 
        order by 
          a.tran_date
      ) t
  ) kcc_prod, 
  (
    SELECT
      sum(disb_amt) 
    FROM
      (
        SELECT
          disb_amt, 
          acc_num, 
          tran_date 
        FROM
          (
            SELECT
              disb_amt, 
              a.acc_num, 
              a.tran_date 
            FROM
              dbzsubvention.acc_disb_amt a 
              inner join dbzsubvention.acc_rollover_all_sub_status s using (acc_num) 
              left join dbzsubvention.acc_close_date c using (acc_num) 
            WHERE
              a.tran_date <= AB.tran_date 
              AND (
                c.ac_close > AB.tran_date || c.ac_close is null
              ) 
              AND a.sch_code = 'xxx' 
              AND a.cust_id = AB.cust_id 
              AND s.acc_status = 'R' 
              AND s.rollover_date <= AB.tran_date 
              AND (
                AB.tran_date < s.next_rollover || s.next_rollover is null
              ) 
            GROUP BY 
              acc_num, 
              a.tran_date 
            order by 
              a.tran_date desc
          ) t 
        GROUP BY 
          acc_num
      ) tt
  ) kcc_disb 
FROM 
  dbzsubvention.acc_disb_amt AB 
WHERE
  AB.cust_id = 'abcdef' 
group by 
  cust_id, 
  tran_date 
order by 
  tran_date asc;

This query isn't working. Upon research I have found that correlated subquery works only till 1 level down. However I couldn't get a workaround to this problem.

I have tried searching the solution around this problem but couldn't find the desired one. Using the SUM function at the inner query will not give desired results as

1. In the second subquery that will sum all the values in column before applying the group by clause.
2. In third subquery the sorting has to be done first then the grouping and finally the sum.

Therefore I am reaching out to the community for help to suggest a workaround to the issue.

Answer 1

You're correct - external column cannot be transferred through the nesting level immediately.

Try this workaround:

SELECT ...               -- outer query
      (                  -- correlated subquery nesting level 1
          SELECT ...
                 (       -- correlated subquery nesting level 2
                      SELECT ...
                      ...
                      WHERE table0_level1.column0_1 ...   -- moved value
                  ) 
          FROM table1
          -- move through nesting level making it a source of current level
          CROSS JOIN ( SELECT table0.column0 AS column0_1 ) AS table0_level1  
       ) AS ...,
       ...
FROM table0
...