is there a more elegant way to re-use code blocks?

user_10300113 0
Viewed 85

I enjoy writing my sql like below.

The benefit is that I can re-use code blocks that I use multiple times, and when I fix something, I always only need to fix it in one place.

E.g. I write 2 times current_year instead of extract (year from sys_date).

Other example, I write 2 times first_of_april_this_year instead of to_date ('01.APR.'|| current_year ||' 00:00:00', 'DD.MON.YYYY HH24:MI:SS').

It works, but as you can see below, it's not very pretty / easy to read. Do you have better suggestions where I do not lose the benefit of never having to fix something in multiple places, but make it more readable? I also used with-clauses, but IMHO that's even less readable. Thank you very much! <3

PS: Let's please stay on the topic of re-using code blocks here, and not dig into how this specific task of finding the previous 1st of April in a testable way could have been done better. TY!

-- gives the latest 1st of April based on sysdate, whereas for testing sysdate can freely be set     
select 
    case when sys_date <= first_of_april_this_year then first_of_april_last_year else first_of_april_this_year end previous_first_of_april
    -- ,params3.* 
from (    
    select 
        to_date ('01.APR.'|| current_year    ||' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_this_year,
        to_date ('01.APR.'||(current_year-1) ||' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_last_year,
        params2.*
    from (
        select 
            extract (year from sys_date) current_year,
            params1.* 
        from
        (select 
            to_date ('02.AUG.2018 00:00:01', 'DD.MON.YYYY HH24:MI:SS') sys_date  -- for testing, sysdate can be overwritten
            --sysdate sys_date 
        from dual) params1 
    ) params2 
) params3;
oracle sql
3 Answers

You can use Subquery Factoring (aka Common Table Expressions, aka CTEs) to make a query with multiple subqueries easier to read. E.g. your query would become:

WITH params1 AS (SELECT to_date('02.AUG.2018 00:00:01', 'DD.MON.YYYY HH24:MI:SS') sys_date -- for testing, sysdate can be overwritten
                 --sysdate sys_date 
                 FROM   dual),
     params2 AS (SELECT extract(YEAR FROM sys_date) current_year,
                        sys_date
                 FROM   params1),
     params3 AS (SELECT to_date('01.APR.' || current_year || ' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_this_year,
                        to_date('01.APR.' || (current_year - 1) || ' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_last_year,
                        sys_date
                 FROM   params2)
SELECT CASE
         WHEN sys_date <= first_of_april_this_year THEN
          first_of_april_last_year
         ELSE
          first_of_april_this_year
       END previous_first_of_april
FROM   params3;

As an aside, working out the 1st of April based on a date can be done more simply:

SELECT add_months(TRUNC(add_months(SYSDATE, -3), 'yyyy'), 3)
FROM   dual;

Here, we go back 3 months from the specified date, truncate the resultant date to the year (to get to 1st Jan of that year) and then simply add 3 months back, to take us to 1st April of that year.

user_44793090

You can use sub-query factoring (WITH) clauses then:

  • you can organise it so you read from top-to-bottom;
  • you can give the sub-queries meaningful names; and
  • you can specify all the column names in the sub-query's header so they are grouped together (and you can even add the same aliases in the body of the sub-query if you have particularly long lists of values).

Like this:

WITH current_date ( sys_date ) AS (
  SELECT TO_DATE( '02.AUG.2018 00:00:01', 'DD.MON.YYYY HH24:MI:SS' )
  FROM   DUAL
),
financial_year_boundaries ( first_of_april_this_year, first_of_april_last_year, sys_date ) AS (
  SELECT ADD_MONTHS( TRUNC( sys_date, 'YYYY' ), 3 ),
         ADD_MONTHS( TRUNC( sys_date, 'YYYY' ), 3 - 12 ),
         sys_date
  FROM   current_date
)
SELECT CASE
       WHEN sys_date < first_of_april_this_year
       THEN first_of_april_last_year
       ELSE first_of_april_this_year
       END AS previous_first_of_april
FROM   financial_year_boundaries
user_15092640

You can do it even shorter:

WITH params1 AS (SELECT to_date('02.JAN.2011 00:00:01', 'DD.MON.YYYY HH24:MI:SS') sys_date -- for testing, sysdate can be overwritten sysdate sys_date 
                 FROM   dual)
SELECT TO_DATE('04'||TO_CHAR(EXTRACT(YEAR FROM sys_date) - CASE WHEN EXTRACT(MONTH FROM sys_date) < 4 THEN 1 ELSE 0 END), 'MMRRRR') AS previous_first_of_april
  FROM params1
user_102231470
Related