2

I'm working on Oracle to Postgres conversion, I'm stuck with a single piece of code which is driving me nuts. I'm not sure how to use SUM(VALUE) KEEP (DENSE_RANK FIRST ORDER BY START_DATE DESC) AS LATEST_VALUE in Postgres.

I've referred this page and that doesn't work work me. Since in my case the query has GROUP BY condition seems like pretty difficult in this case.

Here is the piece of code which is written for Oracle, and got stuck with converting to Postgres.

SELECT
    R_ID,
    S_TYPE,
    I_ID,
    M_TYPE,
    TRUNC(CAST( START_DATE, 'D') AS START_DATE,
    SUM(VALUE) AS SUM_VALUE,
    SUM(VALUE) KEEP (DENSE_RANK FIRST ORDER BY START_DATE DESC) AS LATEST_VALUE
FROM
    TABLEX
WHERE
    ACTIVE = 'Y'
GROUP BY
    R_ID,
    S_TYPE,
    I_ID,
    M_TYPE,
    TRUNC( START_DATE, 'D')

The most important thing is to get the DENSE_RANK is used for START_DATE and GROUP BY is done with TRUNC(START_DATE, 'D').

If anyone have any insight on this, Please let me know about it.

Thanks in advance.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Gokul Velu
  • 31
  • 1
  • 7

1 Answers1

1

Try:

SELECT
    R_ID,
    S_TYPE,
    I_ID,
    M_TYPE,
    TRUNC(CAST( START_DATE, 'D') AS START_DATE,
    SUM(VALUE) AS SUM_VALUE,
    SUM(CASE START_DATE WHEN my_date THEN VALUE ELSE 0 END) AS LATEST_VALUE
FROM (
    SELECT *,
          max( START_DATE ) OVER (partition by  R_ID,
                                                S_TYPE,
                                                I_ID,
                                                M_TYPE,
                                                TRUNC( START_DATE, 'D')
          ) As my_date
    FROM TABLEX
    WHERE
        ACTIVE = 'Y'
) x
GROUP BY
    R_ID,
    S_TYPE,
    I_ID,
    M_TYPE,
    TRUNC( START_DATE, 'D')

Neither PostgreSQL nor any other RDBMS I know support Oracle's KEEP DENSE RANK syntax, you must simulate this function somehow, the above is such a simulation.

krokodilko
  • 35,300
  • 7
  • 55
  • 79