SQL CASE WHEN Recurring expression in case clause

Question

my query:

SELECT
CASE 
WHEN 
DAYS(DATE(
SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
- DAYS(HUB_ARRIVAL_DT) < 31 THEN ' 030'
WHEN 
DAYS(DATE(
SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
- DAYS(HUB_ARRIVAL_DT) < 61 THEN ' 060'
WHEN 
DAYS(DATE(
SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
- DAYS(HUB_ARRIVAL_DT) < 91 THEN ' 090'
WHEN 
DAYS(DATE(
SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
- DAYS(HUB_ARRIVAL_DT) < 181 THEN ' 180'
ELSE '>180'
END AS AGED
FROM ...

you can see the following parts is copied several times

DAYS(DATE(
SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
- DAYS(HUB_ARRIVAL_DT)

Is it possible to have this only once? If so, how? Will this have a performance impact? Thanks! Database is DB2.

Answer 1

No idea about DB2, but I would try this:

select
  CASE 
  WHEN 
  computed_col < 31 THEN ' 030'
  ...
  END AS AGED
from
(
  select DAYS(DATE(
  SUBSTR(DIGITS(STOCK_MONTH),5,4)  CONCAT '-'  CONCAT
  SUBSTR(DIGITS(STOCK_MONTH),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
  - DAYS(HUB_ARRIVAL_DT) as computed_col
  from...
) as x

Answer 2

You could also try stored functions. Something like:

CREATE FUNCTION daysdiff (
    "start" INT, 
    "end" DATE
) RETURNS INT
  DETERMINISTIC NO EXTERNAL ACTION
  RETURN DAYS(DATE(
              SUBSTR(DIGITS(daysdiff."start"),5,4)  CONCAT '-'  CONCAT
              SUBSTR(DIGITS(daysdiff."start"),9,2)  CONCAT '-01') - 1 DAY + 1 MONTH)
         - DAYS(daysdiff."end")

CREATE FUNCTION monthly (
   "days" INT
) RETURNS INT
  DETERMINISTIC NO EXTERNAL ACTION
  RETURN CEILING(monthly."days" / 30) * 30

CREATE FUNCTION daystr ("days" INT, "max" INT)
  RETURNS char(4)
  DETERMINISTIC NO EXTERNAL ACTION
  RETURN
    CASE WHEN daystr."days" <= daystr."max" THEN
        CHAR(DECIMAL(daystr."days", INT(LOG10(daystr."max"))+1, 0))
    ELSE '>' CONCAT CHAR(DECIMAL(daystr."max", INT(LOG10(daystr."max"))+1, 0))
    END

CREATE FUNCTION monthlydiffstr (
    "start" INT, 
    "end" DATE
) RETURNS INT
  RETURN daystr(monthly(daysdiff(monthlydiffstr."start", monthlydiffstr."end")), 180)

The above was not tested on DB2; your mileage and syntax may vary. Feel free to rename the functions (which are admittedly bad) and break down the tasks into whatever functions you see fit. daysdiff could probably be improved, depending on the type of STOCK_MONTH.