Converting Subquery into Single Query

Question

I've a query that has multiple subqueries with parameters as follows:

SELECT 
    V.EMPNO,
    V.FIRST_NAME || ' ' || V.MIDDLE_NAME
        || ' '
        || V.LAST_NAME FULLNAME,
    M.APP_NO,
    K.TOTAL_AMT,
    K.TOTAL_AMT - (SELECT 
            SUM(Q.RECAMOUNT)
        FROM
            LOAN_ADJ_DETAILS_NEW q
        WHERE
            Q.APP_NO = M.APP_NO
                AND Q.RECDATE <= '01-AUG-2022') REMAINING,
    (SELECT 
            SUM(P.RECAMOUNT)
        FROM
            LOAN_ADJ_DETAILS_NEW p
        WHERE
            P.APP_NO = M.APP_NO
                AND P.RECDATE <= '01-AUG-2022') TOTALADJ,
    (SELECT 
            COUNT(*)
        FROM
            LOAN_ADJ_DETAILS_NEW p
        WHERE
            P.APP_NO = M.APP_NO
                AND P.RECDATE IS NOT NULL
                AND P.RECDATE <= '01-AUG-2022') INSTALLMENT,
    (SELECT 
            MAX(Q.RECAMOUNT)
        FROM
            LOAN_ADJ_DETAILS_NEW q
        WHERE
            Q.APP_NO = M.APP_NO
                AND Q.RECDATE = '01-AUG-2022') LASTINSTALL,
    (SELECT 
            MAX(S.RECDATE)
        FROM
            LOAN_ADJ_DETAILS_NEW s
        WHERE
            S.APP_NO = M.APP_NO
                AND S.RECDATE <= '01-AUG-2022') LASTDATE,
    (SELECT 
            SUM(P.RECAMOUNT)
        FROM
            LOAN_ADJ_DETAILS_NEW p
        WHERE
            P.APP_NO = M.APP_NO
                AND P.RECDATE <= '01-AUG-2022') PAID
FROM
    LOAN_ADJ_DETAILS_NEW m,
    TBL_LOAN_MASTER k,
    EMP_PERSONAL v
WHERE
    V.EMPNO = M.EMPNO
        AND K.LOAN_ID = M.APP_NO
        AND M.RECAMOUNT > 0
        AND M.RECDATE IS NOT NULL
        AND M.RECDATE = '01-AUG-2022'
        AND M.RECDATE >= '01-AUG-2022';

The query is simple, just to get user wise loan information. Now the thing is, I require to make it into one query something as follows:

SELECT * FROM TABLE WHERE COLUMN <= PARAMTER;

The above query will be used as a dynamic query from database to front-end. I was hoping if this could be converted to a single query or view anyway.

MT0 · Answer

You can use analytic functions and combine the sub-queries into a single one in the FROM clause:

SELECT V.EMPNO,
       V.FIRST_NAME || ' ' || V.MIDDLE_NAME || ' ' || V.LAST_NAME
         AS FULLNAME,
       M.APP_NO,
       K.TOTAL_AMT,
       K.TOTAL_AMT - m.paid AS REMAINING,
       m.totaladj,
       m.paid,
       m.installment,
       m.lastinstall,
       m.lastdate
FROM   ( SELECT app_no,
                empno,
                recamount,
                recdate,
                SUM(RECAMOUNT) OVER (PARTITION BY app_no) AS totaladj,
                SUM(RECAMOUNT) OVER (PARTITION BY app_no) AS paid,
                COUNT(*) OVER (PARTITION BY app_no) AS installment,
                MAX(RECAMOUNT) OVER (PARTITION BY app_no) AS lastinstall,
                MAX(RECDATE) OVER (PARTITION BY app_no) AS lastdate
         FROM   LOAN_ADJ_DETAILS_NEW
         WHERE  RECDATE <= DATE '2022-08-01'
       ) m
       INNER JOIN TBL_LOAN_MASTER k
       ON (K.LOAN_ID = M.APP_NO)
       INNER JOIN EMP_PERSONAL v
       ON (V.EMPNO = M.EMPNO)
WHERE  M.RECAMOUNT > 0
AND    M.RECDATE = DATE '2022-08-01';

Converting Subquery into Single Query

1 Answers