SQL date arithmetic with network databases
Article ID: 31124
Updated On:
Products
Issue/Introduction
Many network databases contain date fields that are stored in two-byte numeric fields – i.e., PIC 9(2). When referencing these fields in SQL statements, when the value is less than 10 then any leading zero is suppressed and the remaining digit is left-justified. This can make it challenging to convert the network data into a format that SQL can easily use in date comparisons and date arithmetic.
To make this workable, a few steps are required. These are summarized below, with detailed instructions following:
1- 1- To use these fields in SQL date arithmetic, because of SQL data type compatibility, the network data must be translated into a character field in a format SQL will recognize as a valid date.
2- 2- Create a function that will handle the leading zero in the numeric fields whose value is less than 10.
3- 3- Create a view that references the function. This is not strictly necessary, but will make the queries referencing the date fields much less complex.
4- 4- Select the desired data by issuing a query against the view.
5- 5- If the network data is stored in character fields - PIC X(2) – step # 2 (creating a function) can be skipped, and the view definition can reference these character fields directly instead of the references to the function operating on the numeric fields.
Here are details on these steps.
Step2: CREATE A FUNCTION
The syntax below can be used to create a function. There are a few important things to note here:
1- 1- In this syntax the external name must be unique within your dictionary. If there are already load modules with the name used here (DATECONV) then problems will occur. You can first check to see if load modules exist by issuing a LOOK PROGRAM=DATECONV (or the external name of your choice). Once you’re certain this name is unique, you can proceed to use the syntax.
2-2- In this syntax, note that the line with the opening bracket for a comment (/*) cannot begin in column 1. That’s because /* in column 1 is the EOF (end-of-file) indicator for the IDMS read routine (IDMSUTIO).
3-3- This syntax uses the SQL schema DEMOEMPL provided as part of the IDMS install. Feel free to change this to any SQL schema of your choice – but not an SQL schema that references a network schema.
4-4- Note that this function is written in SQL procedural language. That means each command line will end in a semi-colon. In order for the SQL parser to understand that this is not the end of the CREATE FUNCTION command, we must first specify a different command delimiter to use for that purpose. This is common when writing functions or procedures in SQL procedural language.
5-5- The DROP FUNCTION is not necessary unless you are running multiple attempts, and want to drop the previous definition.
6-6- The final SET OPTIONS in only necessary if you wish to continue issuing other SQL commands in the normal way, before ending your session, once the function definition is successful
SET OPTIONS COMMAND DELIMITER ‘++’ ;
DROP FUNCTION DEMOEMPL.DATE_CONV ++
CREATE FUNCTION DEMOEMPL.DATE_CONV
(MM_DD NUMERIC(2)) RETURNS CHAR(2)
EXTERNAL NAME DATECONV LANGUAGE SQL
LABEL_200:
BEGIN NOT ATOMIC
/*
** Convert a two-character numberic field to a character field,
**substituting ‘0’ for any leading zero that would normally be suppressed
*/
IF (MM_DD > 9)
THEN RETURN CAST(MM_DD AS CHAR(2));
ELSE RETURN ‘0’||CAST(MM_DD AS CHAR(2));
END IF;
END ++
COMMIT ++
SET OPTIONS COMMAND DELIMITER ‘;’ ++
Once this gets an SQLSTATE = 0000, you can test the function by specifying it in a query as follows:
SELECT
DEMOEMPL.DATE_CONV(START_MONTH_0420)||'-'||
DEMOEMPL.DATE_CONV(START_DAY_0420) AS DAYS_DIFF
FROM CJDEMPSM.EMPOSITION ;
This query references fields from the test network data]base, in the EMPOSITION record, using an SQL schema (CJDEMPSM) which has been defined to reference the network schema (EMPSCHM V 100).
STEP 3: CREATE A VIEW
By embedding the function references in a view, you can hide that part of the process from the end user and make the final business query much simpler. The specific view definition may depend somewhat on the end query you are wanting to craft. The example below shows:
1-1- that you can include any relevant key and data fields in the view from the network record.
2 2- It also includes an individual column in the view for each of the 2-byte numeric data fields in the network record (century, year, month, and day).
3 3- This is followed by a displayable format of the network date in the SQL format of “CCYY-MM-DD”; this character field format is compatible with the DATETIME fields in SQL and so can be used in date arithmetic.
4-4- The final field in the view definition is a calculated field that is the difference between the current date and the date in the network record.
5-5- Not all of these fields are required in any view you may want to define; some of good for debugging purposes to ensure that the individual date fields are showing up as you’d like. The others, such as displayable date or a date calculation, would depend on what your end goal is.
6-6- In this example, Sqlntwks is an SQL schema defined to reference the network schema. All field names beginning with "Ntwk_rec" are intended to represent key, data, or date fields contained in the target network record. :
DROP VIEW DEMOEMPL.DATEVIEW ; (only necessary if making multiple attempts)
CREATE VIEW DEMOEMPL.DATEVIEW
(VIEW_KEY1,
VIEW_DATA1,….
VIEW_DATE_CC,
VIEW_DATE_YY,
VIEW_DATE_MM,
VIEW_DATE_DD,
VIEW_DATE_DISPLAY,
VIEW_DATE_DIFF)
AS SELECT
Ntwk_rec_key_field1,
Ntwk_rec_data_field1,…
DEMOEMPL.DATE_CONV (Ntwk_rec_date_CC),
DEMOEMPL.DATE_CONV (Ntwk_rec_date_YY),
DEMOEMPL.DATE_CONV (Ntwk_rec_date_MM ),
DEMOEMPL.DATE_CONV (Ntwk_rec_date_DD),
DEMOEMPL.DATE_CONV (Ntwk_rec_date_CC)||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_YY)||’-‘||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_MM )||’-‘||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_DD),
DAYS(CURRENT DATE) –
DAYS(DEMOEMPL.DATE_CONV (Ntwk_rec_date_CC)||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_YY)||’-‘||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_MM )||’-‘||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_DD))
FROM Sqlntwks.ntwk_rec ;
STEP 4: ISSUE THE DESIRED QUERY AGAINST THE VIEW
If you wish to include the concatenations used in the view definition (such as for the column VIEW_DATE_DISPLAY) in your final query, you can skip the step to define the view. However, some things can’t be done without the view. For example, if you want to select all network records where the date is more than 30 days before the current date, you’d use this query from the view:
SELECT (desired columns) FROM DEMOEMPL.DATEVIEW
WHERE VIEW_DATE_DIFF > 30 ;
This comparison with the view field representing the calculated date difference to a numeric literal can be done in a WHERE clause, as shown. To try to do this without a view would mean coding a WHERE clause that looked like this:
WHERE (DAYS(CURRENT DATE) –
DAYS(DEMOEMPL.DATE_CONV (Ntwk_rec_date_CC)||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_YY)||’-‘||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_MM )||’-‘||
DEMOEMPL.DATE_CONV (Ntwk_rec_date_DD)) ) > 30
However, that would get a syntax error. This type of date comparison, while supported in a column list, is not supported in a WHERE clause.
STEP 5: NETWORK DATES IN CHARACTER FIELDS
If the network date fields are stored in PIC X(2) instead of PIC 9(2) fields, then you can skip the step to define the function, and use these character date fields directly in the view definition. That would look something like this:
CREATE VIEW DEMOEMPL.DATEVIEW
(VIEW_KEY1,
VIEW_DATA1,….
VIEW_DATE_CC,
VIEW_DATE_YY,
VIEW_DATE_MM,
VIEW_DATE_DD,
VIEW_DATE_DISPLAY,
VIEW_DATE_DIFF)
AS SELECT
Ntwk_rec_key_field1,
Ntwk_rec_data_field1,…
Ntwk_rec_char_date_CC,
Ntwk_rec_ char_date_YY,
Ntwk_rec_ char_date_MM,
Ntwk_rec_ char_date_DD,
Ntwk_rec_ char_date_CC||Ntwk_ char_rec_date_YY)||’-‘||
Ntwk_rec_ char_date_MM )||’-‘||Ntwk_rec_ char_date_DD,
DAYS(CURRENT DATE) –
DAYS(Ntwk_rec_ char_date_CC||Ntwk_ char_rec_date_YY)||’-‘||
Ntwk_rec_ char_date_MM )||’-‘||Ntwk_rec_ char_date_DD)
FROM Sqlntwks.ntwk_rec ;
Environment
Component:
Feedback
