Wednesday, October 9, 2019

Putting multiple SQL Selects together to make one row

multiple sql select statements within 1 select statement to make a single row

I was working with one of the Tech Support guys at work putting together bits of information into a one row of data that would allow him to display information on any IBM i partition. This is not something I had done before, and after a little while "playing" I had a statement that put bits of information from different sources together into one row.

I am sure we are familiar with using a Select to get information from a table/file, or perhaps join tables/files together, to get the results. But they are always related, with columns/fields that can be used to link the tables/files together. In this scenario I am running several independent Select statements, each producing one row of results, I want to combine into one. The information want is:

  1. Partition name
  2. Date and time
  3. Default CCSID, from system value
  4. Number of spool files in QEZJOBLOG output queue
  5. Model number
  6. Release of IBM i

The first two columns are the system name can be got by using the special registers: CURRENT SERVER and CURRENT TIMESTAMP. In this case I do not want the microseconds in the timestamp, therefore, I use the (0) to indicate that no microseconds will be returned:

SELECT
  CURRENT SERVER AS "Partition",
  CURRENT TIMESTAMP(0) AS "Time"
  FROM SYSIBM.SYSDUMMY1


Partition  Time
---------  -------------------
DEV730     2019-10-08 19:25:05

Two of the other columns are system values and I can use the SYSTEM_VALUE_INFO View to get the data.

SELECT SYSTEM_VALUE_NAME AS "Value",
  CAST(CURRENT_CHARACTER_VALUE AS CHAR(4)) AS "Char value",
  CAST(CURRENT_NUMERIC_VALUE AS DEC(5,0)) AS "Dec value"
   FROM QSYS2.SYSTEM_VALUE_INFO
  WHERE SYSTEM_VALUE_NAME IN ('QCCSID','QMODEL')


Value   Char value  Dec value
------  ----------  ---------
QMODEL  41A         -
QCCSID  -           65535

The result for the model number is in the character column of the View, with the number column being null. The CCSID value is in the number column, with the character column being null.

Next up is the column that contains the number of spool files in the QEZJOBLOG output queue. As I am only interested in the contents of one output queue I can use the OUTPUT_QUEUE_ENTRIES table function. As I am only interested in the number of spool files in the output queue I can just use the COUNT(*), rather than give any column names:

SELECT COUNT(*) AS "Count"
  FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL',
                                        'QEZJOBLOG',
                                        '*NO'))


Count
-----
 1040

The last item was the hardest to get. If the IBM i partition I am using is at release 7.4 or 7.3 TR6 I can use the DATA_AREA_INFO table function:

SELECT CAST(DATA_AREA_VALUE As CHAR(6)) AS "Value"
     FROM TABLE(QSYS2.DATA_AREA_INFO(
            DATA_AREA_NAME => 'QSS1MRI',
            DATA_AREA_LIBRARY => 'QGPL'))


Value 
------
V7R3M0

If the partition is at a earlier release or TR the only place I could find the IBM i release in a place I could fetch it was from the PTF_INFO View:

SELECT PTF_PRODUCT_RELEASE_LEVEL 
   FROM QSYS2.PTF_INFO
  WHERE PTF_PRODUCT_ID = '5770999'
  LIMIT 1

PTF_PRODUCT_RELEASE_LEVEL
--------------------------
V7R3M0

I have to use the LIMIT as I only one of the possible result returned.

Having established what my SQL Select statements are I now need to put them together so they will produce one row of results. I must not call these "sub-selects" as that means something different. But I can put each of these of SQL select statements into one "master" SQL Select:

01  SELECT
02   CURRENT SERVER AS "Partition",
03   CURRENT TIMESTAMP(0) AS "Time",
04   (SELECT CAST(CURRENT_NUMERIC_VALUE AS DEC(5,0))
05      FROM QSYS2.SYSTEM_VALUE_INFO
06     WHERE SYSTEM_VALUE_NAME = 'QCCSID') AS "CCSID",
07   (SELECT COUNT(*)
08      FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL',
09             'QEZJOBLOG','*NO'))) AS "QEZJOBLOG",
10   (SELECT CAST(CURRENT_CHARACTER_VALUE AS CHAR(5))
11      FROM QSYS2.SYSTEM_VALUE_INFO
12     WHERE SYSTEM_VALUE_NAME = 'QMODEL') AS "Model",
13   (SELECT CAST(DATA_AREA_VALUE As CHAR(6))
14      FROM TABLE(QSYS2.DATA_AREA_INFO(
15             DATA_AREA_NAME => 'QSS1MRI',
16             DATA_AREA_LIBRARY => 'QGPL'))) AS "Release 1"
17   FROM SYSIBM.SYSDUMMY1

Each of the individual SQL statements are enclosed within parenthesis ( ( ) ). If you are from an English-English speaking nation, rather than American-English, they are brackets.

Lines 2 and 3: No need for their own separate SQL Select statements to get the partition name and the current timestamp.

Lines 4 - 6: The first Select is enclosed in parenthesis. Here I get the CCSID number.

Lines 7 – 9: Number of spool files in QEZJOBLOG.

Lines 10 – 12: Model number.

Lines 13 – 16: Get the release by retrieving the value from the data structure.

Line 17: The FROM is needed, and as I am not getting data I use Db2 dummy table.

For pre-IBM i 7.4 and 7.3 TR6 then I need to use the PTF_INFO View my statement, lines 13 - 16:

01  SELECT
02   CURRENT SERVER AS "Partition",
03   CURRENT TIMESTAMP(0) AS "Time",
04   (SELECT CAST(CURRENT_NUMERIC_VALUE AS DEC(5,0))
05      FROM QSYS2.SYSTEM_VALUE_INFO
06     WHERE SYSTEM_VALUE_NAME = 'QCCSID') AS "CCSID",
07   (SELECT COUNT(*)
08      FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL',
09             'QEZJOBLOG','*NO'))) AS "QEZJOBLOG",
10   (SELECT CAST(CURRENT_CHARACTER_VALUE AS CHAR(5))
11      FROM QSYS2.SYSTEM_VALUE_INFO
12     WHERE SYSTEM_VALUE_NAME = 'QMODEL') AS "Model",
13   (SELECT PTF_PRODUCT_RELEASE_LEVEL
14      FROM QSYS2.PTF_INFO
15     WHERE PTF_PRODUCT_ID = '5770999'
16     LIMIT 1) AS "Release 2"
17   FROM SYSIBM.SYSDUMMY1

The results are the same (well not the timestamp), no matter how the IBM i release is fetched:

Partition  Time                CCSID QEZJOBLOG Model Release 1
---------  ------------------- ----- --------- ----- ---------
DEV730     2019-10-08 20:03:48 65535      1040 41A   V7R3M0


Partition  Time                CCSID QEZJOBLOG Model Release 2
---------  ------------------- ----- --------- ----- ---------
DEV730     2019-10-08 20:06:47 65535      1040 41A   V7R3M0

Having discovered this I am sure that I will be using this in the future as the ability to pull unrelated data together is going to be useful in a lot of the Tech Support Views and statements I have built.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.

2 comments:

  1. Great example Simon. He's another way to do the same thing using WITH, which I think helps organize these complex queries:

    with sys as (
    select CURRENT SERVER AS "Partition", CURRENT TIMESTAMP(0) AS "Time"
    FROM SYSIBM.SYSDUMMY1
    ),
    ccsid as (
    SELECT CAST(CURRENT_NUMERIC_VALUE AS DEC(5, 0)) as "CCSID"
    FROM QSYS2.SYSTEM_VALUE_INFO
    WHERE SYSTEM_VALUE_NAME = 'QCCSID'
    ),
    job as (
    SELECT COUNT(*) AS "QEZJOBLOG"
    FROM TABLE (
    QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL', 'QEZJOBLOG', '*NO')
    )
    ),
    model as (
    (SELECT CAST(CURRENT_CHARACTER_VALUE AS CHAR(5)) as "Model"
    FROM QSYS2.SYSTEM_VALUE_INFO
    WHERE SYSTEM_VALUE_NAME = 'QMODEL')
    ),
    release as (
    SELECT CAST(DATA_AREA_VALUE As CHAR(6)) AS "Release 1"
    FROM TABLE (
    QSYS2.DATA_AREA_INFO(DATA_AREA_NAME => 'QSS1MRI', DATA_AREA_LIBRARY => 'QGPL')
    )
    )
    SELECT sys."Partition", sys."Time", ccsid."CCSID", job."QEZJOBLOG", model."Model",
    release."Release 1"
    from sys, ccsid, job, model, release;

    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.