Subquery as Join?

Question

My query is shown below.

SELECT *,
SUM(A.money_step) over (
    partition by A.id_key, A.P 
    ORDER BY A.actual_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)as accumulated
FROM
(
    SELECT A.*,
    (
         SELECT CASE WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END
         FROM example B 
         WHERE B.id_key = A.id_key
         AND B.actual_date <= A.actual_date
         AND attendance_status = 15
     ) P
     FROM example A
 )A
 ORDER BY A.id_key,A.actual_date

Is it possible to represent the subquery as a join in the FROM clause? I work with the "denodo" tool, where I cannot easily do the subquery in the from clause.

You need either a sub query or a CTE. – mkRabbani Jun 04 '20 at 07:14 — mkRabbani, Jun 04 '20 at 07:14
Don't reuse table aliases, that multiple `A` makes me lost. – jarlh Jun 04 '20 at 07:27 — jarlh, Jun 04 '20 at 07:27

mkRabbani · Accepted Answer · 2020-06-04T07:36:41.210

You can transfer your Sub Query to a CTE as below-

WITH CTE AS
(
    SELECT A.*,
    (
        SELECT CASE WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END
        FROM your_table B 
        WHERE B.id_key = A.id_key
        AND B.actual_date <= A.actual_date
        AND attendance_status = 15
    ) P
    FROM your_table A
)


SELECT actual_date,id_key,attendance_status,money_step,
SUM(A.money_step) over (
    partition by A.id_key, A.P 
    ORDER BY A.actual_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)as accumulated
FROM CTE A
ORDER BY A.id_key,A.actual_date

Also you can put your Sub query directly in the WINDOW function in the Partition part as below-

SELECT *,
SUM(A.money_step) over (
    partition by A.id_key,(
        SELECT CASE WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END
        FROM your_table B 
        WHERE B.id_key = A.id_key
        AND B.actual_date <= A.actual_date
        AND attendance_status = 15
    )
    ORDER BY A.actual_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)as accumulated
FROM your_table A

Subquery as Join?

1 Answers1