Wednesday, November 24, 2021

Using SQL LIKE with a Subselect

select with a wildcard subselect

This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.

In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:

SELECT * FROM OLDLIBS

The libraries I will be using are:

LIBRARY
-------
LIB1
LIB2
LIB3
LIB4
LIB5

There is a SQL view, JOB_DESCRIPTION_INFO, that allows me to search for information about all the job descriptions on the partition, including its library list.

Alas, the library list is contained in a single column, which is 2,750 bytes. If I was searching for a single library I could use the following, which contains a wildcard in the Where clause:

SELECT JOB_DESCRIPTION_LIBRARY,JOB_DESCRIPTION,LIBRARY_LIST
FROM QSYS2.JOB_DESCRIPTION_INFO
WHERE LIBRARY_LIST LIKE '%QGPL%'

I do not want to write and run separate SQL statements for each of the libraries in the table OLDLIBS, as in the "real world" it contained 58 libraries. I just want one statement and one set of results.

The Where clause would need to contain a subselect fetching rows from OLDLIBS. But how to code a wildcard with values from columns from the results of the subselect?

Using Google I found some things similar in different flavors of SQL, which I was able to adapt for Db2 for i. When I was done I had the following:

01  SELECT JOB_DESCRIPTION_LIBRARY AS "Lib",
02         JOB_DESCRIPTION AS "Jobd",
03         LIBRARY_LIST AS "Libl"
04    FROM QSYS2.JOB_DESCRIPTION_INFO
05   WHERE EXISTS (SELECT 1 FROM OLDLIBS B 
06                 WHERE LIBRARY_LIST LIKE '%' || B.LIBRARY || '%')

Lines 1 – 3: The columns I want to return in the results. I have given them short column headings too.

Lines 5 and 6: The Where clause contains EXIST, this means that the subselect must return a true value, the number 1, for the row to be included in the results. All I need to do is to concatenate the percentage symbol ( % ) with the column name to get:

%LIB1%

And yes I always use the double pipe symbols ( || ) to indicate concatenation rather than CONCAT.

The results look like:

Lib   Jobd   Libl
----- ------ ----------------------------
QGPL  JOHNB  QTEMP      LIB5      QGPL
LIB2  CASSIE QTEMP      LIB2      LIB3...
LIB1  TOMTE  QTEMP      LIB1      LIB2...

Using these results I know which job descriptions I need to change or delete before I delete the libraries.

 

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

5 comments:

  1. Nice to read more on QSYS2.JOB_DESCRIPTION_INFO!

    To avoid false positives (%LIB1% matches LIB10, ZLIB1...) here are my 2 cents:

    1. Stay with the SUBSELECT/LIKE Theme:
    `...where LIBRARY_LIST like '%' || rpad(trim(b.library),11, ' ') || '%'...`

    >> The list is an array of 11 character entries.
    >> Each entry contains a ten character
    >> name followed by one blank.

    2. Playing with DB2 features
    SELECT JOB_DESCRIPTION_LIBRARY AS "Lib",
    JOB_DESCRIPTION AS "Jobd",
    LIBRARY_LIST AS "Libl"
    FROM QSYS2.JOB_DESCRIPTION_INFO AS I
    WHERE EXISTS (SELECT 1
    FROM OLDLIBS B
    -- revisited 'filter'
    CROSS JOIN LATERAL (
    SELECT ELEMENT
    FROM TABLE(SYSTOOLS.SPLIT(TRIM(I.LIBRARY_LIST),' ')) S
    WHERE S.ELEMENT = B.LIBRARY
    ) JLIB
    )

    ReplyDelete
  2. I was going to be Mr. SmartyPants and describe how LOCATE was so much faster than LIKE. When I did the SQL Analyzer though, the difference was minuscule with LIKE winning 33.966 to 34.816. What I do prefer about LOCATE is getting rid of the ampersands. Now, in the past, I did test a batch job and for that condition with those files, LOCATE was much faster than LIKE so you would do well to consider both depending on the application.

    SELECT JOB_DESCRIPTION_LIBRARY,JOB_DESCRIPTION,LIBRARY_LIST
    FROM QSYS2.JOB_DESCRIPTION_INFO
    WHERE LOCATE('QGPL',LIBRARY_LIST) <> 0

    ReplyDelete
  3. I love your tip!

    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.