3

I have the follwoing SQL statement. Is there a way to avoid writing the OVER(PARTIION... statement multiple times for each line as it is identical in every line?

            SELECT 
            DISTINCT
            LAST_VALUE (DSET_COB_DT)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_DSET_COB_DT,
            LAST_VALUE (SRC_HUB)              OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB,
            LAST_VALUE (TRD_SRC_SYS)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_SRC_SYS,
            LAST_VALUE (TRD_ID)               OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_ID,
            LAST_VALUE (NVL (TRD_VER,0))      OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_VER,
            LAST_VALUE (SRC_HUB_INGESTION_DT) OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB_INGESTION_DT,

            FROM DF_RISK_SIT2_OWNER.recon_ts_rs  

Any suggestions would be appreciated to make the statement easier to read.

1

Yes, there is a way to avoid writing the clause multiple times, but the cure is worse than the problem (and therefore is not recommended). You would have to write dynamic SQL, which would be more difficult to read, require more code, be more fragile, etc. What you have is not difficult to read save that it violates the DRY principle. It is good to recognize this and as TheGameiswar[+1] pointed out, you may one day have a solution without a downside.

|improve this answer|||||
1

In standard SQL, you have a clause where you can name the Partitioning option,but it is currently not supported by any RDBMS .You can see the connect item raised by Itzik Ben-Gan and vote for it

If implemented the Clause would look like this..

     SELECT empid, qty,
     SUM(qty) OVER W1 AS run_sum_qty,
     AVG(qty) OVER W1 AS run_avg_qty,
     FROM Sales.EmpOrders
    WINDOW W1 AS ( PARTITION BY empid
     ORDER BY ordermonth
     ROWS BETWEEN UNBOUNDED PRECEDING
     AND CURRENT ROW );
|improve this answer|||||
  • is there a way to replace the repeated part with a variable in sql script? – Nickpick Aug 3 '16 at 15:00
  • yes that will work,you will have to use Dynamic SQL – TheGameiswar Aug 3 '16 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.