CODEWITHSUNDEEP
sqldb2jdedwards
Joseph Gagnon
Joseph Gagnon

Reputation: 2135

DB2: Can you write a function or macro emedded in the SQL to simplify a complex query?

I know enough about SQL to do basic and slightly more advanced queries, but that's about where my knowledge ends.

I have a query that involves a complex sequence of function calls to convert a JD Edwards ERP application "Julian" date to a week-of-year value. The function is as follows:

WEEK(DATE(CAST(CAST((1900 + JulianDate/1000) AS INTEGER) AS CHAR(4)) || '-01-01') + (MOD(JulianDate, 1000) - 1) DAYS)

Where JulianDate is a column with a numeric value representing a Julian date that JD Edwards uses. The format is: CYYDDD, were C is the century, YY is the last 2 digits of the year, and DDD is the day of the year (1-366).

This is done in a couple of places in the query, including SELECT fields and GROUP BY fields.

This works, but is very complex and makes the SQL hard to read and understand. Also, some tools that can format the SQL, cannot handle this for some reason and leave it as an unformatted mess.

Here's an example snippet:

SELECT
  F31122.WTMMCU AS BU,
  F31122.WTMCU AS WC,
  WEEK(DATE(CAST(CAST((1900 + F31122.WTDGL/1000) AS INTEGER) AS CHAR(4)) || '-01-01') + (MOD(F31122.WTDGL, 1000) - 1) DAYS) AS Week,
  SUM(F31122.WTHRW) AS Hours
FROM PROD2DTA.F31122 AS F31122
WHERE
  (F31122.WTTYR IN ('3')) AND
  (F31122.WTDGL BETWEEN 120200 AND 120201)
GROUP BY
  F31122.WTMMCU,
  F31122.WTMCU,
  WEEK(DATE(CAST(CAST((1900 + F31122.WTDGL/1000) AS INTEGER) AS CHAR(4)) || '-01-01') + (MOD(F31122.WTDGL, 1000) - 1) DAYS)

Question:

Is it possible to write a "function" or "macro" (whatever the correct term is) that can be embedded with the SQL and then "called" where needed?

It would be really nice if I could do something like the following:

JulianToWeek(JulianDate) {
    return WEEK(DATE(CAST(CAST((1900 + JulianDate/1000) AS INTEGER) AS CHAR(4)) || '-01-01') + (MOD(JulianDate, 1000) - 1) DAYS)
}

And then use it to make the example SQL above a bit more readable (and less error prone):

SELECT
  F31122.WTMMCU AS BU,
  F31122.WTMCU AS WC,
  JulianToWeek(F31122.WTDGL) AS Week,
  SUM(F31122.WTHRW) AS Hours
FROM PROD2DTA.F31122 AS F31122
WHERE
  (F31122.WTTYR IN ('3')) AND
  (F31122.WTDGL BETWEEN 120200 AND 120201)
GROUP BY
  F31122.WTMMCU,
  F31122.WTMCU,
  JulianToWeek(F31122.WTDGL)

I've read a little about SQL functions, but it seems like it's something that's built in" to the database. Is there a way to do this, where it's just part of the SQL "code"?

Upvotes: 0

Views: 231

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

I would create a function to convert to a DATE, then you can use WEEK() or whatever functions against it in your SQL. So

CREATE OR REPLACE FUNCTION DB_JDE_DATE(I INTEGER) 
    NO EXTERNAL ACTION
    DETERMINISTIC
RETURNS DATE
RETURN
    '1899-12-31'::DATE + (I/1000) YEARS + MOD(I, 1000) DAYS

BTW when calling a UDF, you will need to qualify it with it's schema name if it was not created in a schema in the CURRENT PATH, which defaults to "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM",X where X is the value of the CURRENT USER special register.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48865

Creating the function is pretty easy. For example:

create function julian2week(in j integer) returns integer
language sql
begin
  return week(date(cast(cast((1900 + j/1000) as integer) as char(4))
    || '-01-01') + (mod(j, 1000) - 1) days);
end
//

Then you can just use it:

values julian2week(2020015);

Result:

1
-
3

Or in a typical SELECT:

select d, julian2week(d) as w from jw;

Result:

      D  W
-------  -
2020015  3
2020032  6

Upvotes: 1

Related Questions