Pages

Wednesday, October 28, 2020

Getting the day of the week in a CL program using SQL

getting day of week in a cl program

The germ for this post came from a Facebook post I saw. The question was posed: Is it possible to retrieve the day of the week in a CL program? Multiple people suggested using a CEE API. Then came a reply that really interested me. This person said he had built a SQL View for this purpose.

It struck me as a creative approach to this scenario. SQL has a global variable CURRENT_DATE which contains, as the name suggests, the current date:

SELECT CURRENT_DATE
  FROM SYSIBM.SYSDUMMY1

00001
----------
2020-10-28

What kind of information would I want to know about today's date?

  1. Day of the week (Wednesday)
  2. Month name (October)
  3. The full date (Wednesday October 28, 2020)
  4. Day of the month (28)
  5. Year (2020)
  6. Day of the year (302)
  7. Quarter (4)
  8. Month number (10)
  9. Week of the year (44)
  10. Day of the week number (4)

I know that some of the above information could be retrieved from system values. I will explain why I am including this information in the view later in this post.

I am going to show the source code for this view in two parts. Below is the first part, the definition of the columns:

01  CREATE OR REPLACE VIEW MYLIB.CL_DATE_NAMES
02  FOR SYSTEM NAME "CLDATENAME"
03  AS
04  (SELECT CAST(UPPER(DAYNAME(CURRENT_DATE)) AS CHAR(9)) 
05            AS DAYOFWEEK,
06          CAST(DAYOFMONTH(CURRENT_DATE) AS DEC(2,0)) 
07            AS DAYOFMONTH,
08          CAST(UPPER(MONTHNAME(CURRENT_DATE)) AS CHAR(9)) 
09            AS MONTHNAME,
10          CAST(YEAR(CURRENT_DATE) AS DEC(4,0)) AS YEAR,
11          CAST(RTRIM(UPPER(DAYNAME(CURRENT_DATE))) || ' ' ||
12               RTRIM(UPPER(MONTHNAME(CURRENT_DATE))) || ' ' ||
13               VARCHAR_FORMAT(DAYOFMONTH(CURRENT_DATE)) || ', ' ||
14               VARCHAR_FORMAT(YEAR(CURRENT_DATE)) AS CHAR(28))
15               AS FULLDATE,
16          CAST(EXTRACT(DOY FROM CURRENT_DATE) AS DEC(3,0)) 
17            AS DAYOFYEAR,
18          CAST(EXTRACT(QUARTER FROM CURRENT_DATE) AS NUM(1,0)) 
19            AS QUARTER,
20          CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS DEC(2,0)) 
21            AS MONTHNBR,
22          CAST(EXTRACT(WEEK FROM CURRENT_DATE) AS DEC(2,0)) 
23            AS WEEK,
24          CAST(EXTRACT(DOW FROM CURRENT_DATE) AS NUM(1,0)) 
25            AS DOWNBR
26   FROM SYSIBM.SYSDUMMY1) ;

Line 1: I always give my Views, Indexes, and Tables long descriptive names.

Line 2: As I can also give it a short system name that a CL program will use.

Line 4 - 26: This is the Select statement that builds the columns for the View. Every column has a Cast function. I am not going to give the reason why here, but I will later in this post. The Select is built over the table/file SYSDUMMY1 in the library SYSIBM. As the name suggests this is a dummy file that contains no meaningful data. None of the columns I am defining are related to this file. But a Select has to be built over a table/file and this is the dummy everyone uses.

Lines 4 and 5: I am extracting the day name from the current date using the DAYNAME function, and then converting the mixed case result, "Wednesday", to all upper case, "WEDNESDAY". On line 5 I am giving this column its name. The result is then cast to a fixed sized character column.

Lines 6 and 7: Extracting the day of the month and casting it to a decimal (packed) format.

Lines 8 and 9: Extracting the month's name and casting it as fixed sized character.

Line 10: Extracting year as decimal.

Lines 11 – 15: Here I am creating the formatted full date.

Lines 16 and 17: I am using the Extract function to extract the day of the year.

Lines 18 and 19: Extracting quarter and casting to a numeric/signed column.

Lines 20 and 21: Extracting month number.

Lines 22 and 23: Extracting week number.

Lines 24 and 25: I decided having the day of week as a number in the View would could be useful. The week starts on Sunday so it is day number 1. I am casting this single digit number to numeric in the view.

Line 26: Here is where I give the name of the dummy file.

The rest of the code for the View looks like:

27  LABEL ON COLUMN CL_DATE_NAMES (
28     DAYOFWEEK IS 'Day of              week',
29    DAYOFMONTH IS 'Day of              month',
30     MONTHNAME IS 'Month               name',
31          YEAR IS 'Year',
32      FULLDATE IS 'Full                date',
33     DAYOFYEAR IS 'Day of              year',
34       QUARTER IS 'Qtr of              year',
35      MONTHNBR IS 'Month',
36          WEEK IS 'Week of             year',
37        DOWNBR IS 'Day of              week as No.') ;

38  LABEL ON COLUMN CL_DATE_NAMES (
39     DAYOFWEEK TEXT IS 'Day of week',
40    DAYOFMONTH TEXT IS 'Day of month',
41     MONTHNAME TEXT IS 'Month name',
42          YEAR TEXT IS 'Year',
43      FULLDATE TEXT IS 'Full date',
44     DAYOFYEAR TEXT IS 'Day of year',
45       QUARTER TEXT IS 'Quarter of year',
46      MONTHNBR TEXT IS 'Month',
47          WEEK TEXT IS 'Week of year',
48        DOWNBR TEXT IS 'Day of week as number') ;

49  LABEL ON TABLE CL_DATE_NAMES IS 'CL_DATE_NAMES' ;

Lines 27 – 37: Here I am giving all the columns their column descriptions/headings.

Lines 38 – 48: And here the descriptions for the columns.

Line 49: Lastly I am giving this View a description.

As I am in the USA the day and month names are in English. The maximum length of the name of a day and a month is nine characters. If you use a partition that returns these values in another language you may need to adjust the size of the following columns:

  • DAYOFWEEK
  • MONTHNAME
  • FULLDATE

How do I use this View in a CL program?

The Run SQL command, RUNSQL, does not allow me to retrieve data from the View using a Select statement. But as a View is just another file, and I can use the Receive File command, RCVF, to get the data. I would only need to receive the data from the View once. My CL program looks like:

01  PGM

02  DCLF FILE(CLDATENAME) OPNID(DTE)

03  RCVF OPNID(DTE)

04  DMPCLPGM

05  ENDPGM

Line 2: The declaration for the View. The Open Id, OPNID, parameter means that the fields from this View are prefixed with "&DTE_".

Line 3: I receive the data from the View. As I gave an Open Id in the declaration I have to use it here too.

Line 4: By using the Dump CL program command, DMPCLPGM, I am dumping all the values in the program's variables, including those from the View, into a spool file.

The dump generates the spool file QPPGMDMP. When I open it I find the values from the View's columns:

Variable          Type   Length    Value                      
----------------  -----  -------   ---------------------------
&DTE_DAYOFMONTH   *DEC       2 0   28
&DTE_DAYOFWEEK    *CHAR      9     'WEDNESDAY'
&DTE_DAYOFYEAR    *DEC       3 0   302
&DTE_DOWNBR       *DEC       1 0   4
&DTE_FULLDATE     *CHAR     28     'WEDNESDAY OCTOBER 28, 2020
&DTE_MONTHNAME    *CHAR      9     'OCTOBER  '
&DTE_MONTHNBR     *DEC       2 0   10
&DTE_QUARTER      *DEC       1 0   4
&DTE_WEEK         *DEC       2 0   44
&DTE_YEAR         *DEC       4 0   2020

Now I will explain why I included those values I could have retrieved from system values. If I had not included the day of the month and year in the View I would have had to have four additional lines in my program to get that information:

DCL VAR(&DAYOFMONTH) TYPE(*CHAR) LEN(2)
DCL VAR(&YEAR) TYPE(*CHAR) LEN(2)


RTVSYSVAL SYSVAL(QDAY) RTNVAR(&DAYOFMONTH)
RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&YEAR)

The value in the QYEAR is just the year part of the date, and does not include the century.

Why would I want to perform two additional commands when I can get the same information from the View?

The other point I said I would explain is why I cast all the columns in the View. It is for two reasons.

Firstly if I had not cast the columns and left them with the default sizes the columns would be:

Variable          Type                Length
----------------  ------------------  -------
&DTE_DAYOFMONTH   Binary                  9 0
&DTE_DAYOFWEEK    Variable character    102
&DTE_DAYOFYEAR    Binary                  9 0
&DTE_DOWNBR       Binary                  9 0
&DTE_FULLDATE     Variable character    290
&DTE_MONTHNAME    Variable character    102
&DTE_MONTHNBR     Binary                  9 0
&DTE_QUARTER      Binary                  9 0
&DTE_WEEK         Binary                 10 0
&DTE_YEAR         Binary                  9 0

These sizes struck me as a bit too unwieldly for something as simple as information about today's date.

Secondly, while the data is in the View only when it is used it may not be a storage space utilization issue if the Day of the Month is in a 9,0 binary column, it is an problem for CL. When I ran the program without casting the columns I received the following error:

Message ID . . . . : CPF0863    Severity .  . . . . : 40
Message type . . . : Escape
Date sent  . . . . : DD/DD/DD   Time sent   . . . . : TT:TT:TT

Message . . . . :   Value of binary data too large for decimal 
  CL variable.
Cause . . . . . :   A binary field in the record has a value
  that is too large for the control language (CL) decimal variable
  declared for that field. A CL decimal variable of the length
  and decimal precision specified in the data   description 
  specification (DDS) for the file is declared for binary fields.
Recovery  . . . :   Correct the data in the file that is not 
  valid or, if the file is a data base file, use a logical view
  of the file that omits the field that is not valid.

Using the Cast function to define those columns as decimal and numeric prevented the error.

 

This article was written for IBM i 7.4, and should work for releases 7.2 and greater with the latest Technology Refreshes.

9 comments:

  1. Some of the extracted fields could be separate functions (like YEAR is) and some of the separate functions could also be extracted. Do you see IBM deprecating the separate functions requiring effort in the future to convert them to extracts?

    ReplyDelete
    Replies
    1. I could have used YEAR, but decided to use EXTRACT to show what it does.

      Is YEAR going away? I don't think it will as it is part of the ANSI standard.

      Delete
  2. Can you just use Current Date instead of Current_Date?

    ReplyDelete
    Replies
    1. Either one does the same thing.
      It is just a question of which one you prefer.

      Delete
  3. Thanks Simon. I had to do something similar to get month end. Is there an advantage to one way over the other?

    PGM
    DCL VAR(&MTHEND) TYPE(*CHAR) LEN(6)
    DCLF FILE(TMPLIB/SAVMTHEND) RCDFMT(*ALL)
    DLTF FILE(TMPLIB/SAVMTHEND)
    MONMSG MSGID(CPF0000)
    RUNSQL SQL('CREATE TABLE TMPLIB.SAVMTHEND (MONTHEND) AS +
    (SELECT LAST_DAY(CURRENT_DATE) FROM +
    SYSIBM.SYSDUMMY1) WITH DATA') COMMIT(*NC)
    CVTDAT DATE(&MONTHEND) TOVAR(&MTHEND) FROMFMT(*ISO) +
    TOFMT(*MDY) TOSEP(*NONE)

    ....then I compared dates and processed desired result accordingly.
    ENDPGM

    ReplyDelete
    Replies
    1. With my approach you do not have to create a table every time you need the information. It is already there.

      You could add the first and last day of the month to the view I suggested. You could even format a date to be *MDY in the view too and save yourself the programming step to do that.

      Delete
  4. I have done something similar except instead of creating a permanent view, I use RUNSQL inside the CL program to create a table in QTEMP and then read that table with a RCVF. The advantage is that it's self contained, the disadvantage is that you have to manually create the file to recompile the program.

    ReplyDelete
    Replies
    1. I prefer having the view so my CL programs do not have to waste their time building the same file time and time again.

      If you made a view and put it in a library that is not QTEMP you would not have to create the file every time you compile one of your CL programs.

      Delete
  5. Simon, thanks for sharing. Most of these I have seem from the Cobol side of the box. Good to see the merger of the two. Great info and examples. Date functions are great tools for retrieving dates and their formats, no matter which country you do business in.
    Again, great read and information. Another learning and teaching moment for the IBMi users world.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.