Programming Languages on Power

 View Only

 Multiple variable assignment using CASE

  • SQL
Ryan Speight's profile image
Ryan Speight posted 01/02/25 05:06 PM

I have LOG files that I want to check daily to ensure that the jobs completed successfully.  There are roughly 25 LOG files that generate on a daily basis with pages ranging from a few hundred to 11,000+.  I only need 5 pieces of data out of these LOGs.  I created a SQL procedure that uses IFS_OBJECT_STATISTICS to get the PATH_NAME of the LOG files in a LOOP, that passes PATH_NAME to IFS_READ and then used SET statements for each variable.  This takes roughly 19 to 23 minutes to run which is inefficient.  I'm still learning more about SQL.  

Someone suggested using a CTE and using a CASE statement to grab what I needed in one pass.  My attempt is below.  The problem I'm having is that I get an error that my syntax is wrong.  Perhaps I can't use a CASE statement to assign value to my variables in one pass?

BEGIN

DECLARE @rec_count INTEGER;
DECLARE @vault_job VARCHAR(256);
DECLARE @run_date TIMESTAMP;
DECLARE @warnings VARCHAR(4);
DECLARE @errors VARCHAR(4);
DECLARE @job_complete VARCHAR(20);
DECLARE @elapsed VARCHAR(8);

SET @rec_count = (SELECT COUNT(*) FROM TABLE(QSYS2.IFS_READ(
        PATH_NAME => '')));

WITH FileContent AS(
    SELECT * FROM TABLE(QSYS2.IFS_READ(
        PATH_NAME => <PATH_NAME>))
        WHERE LINE_NUMBER IN(5, @rec_count - 18, @rec_count - 17, @rec_count - 2, @rec_count - 1)
)
SELECT
    CASE
        WHEN LINE_NUMBER = 5 THEN @vault_job = REPLACE((SUBSTR(LINE, 46, 10)), '/', '')),
        WHEN LINE_NUMBER = @rec_count - 18 THEN @errors = SUBSTR(LINE, 78, 4),
        WHEN LINE_NUMBER = @rec_count - 17 THEN @warnings = SUBSTR(LINE, 78, 4),
        WHEN LINE_NUMBER = @rec_count - 2 THEN @job_completed = SUBSTR(LINE, 53, 20),
        WHEN LINE_NUMBER = @rec_count - 1 THEN @elapsed = SUBSTR(LINE, 49, 8)
    END
FROM FileContent;

CALL SYSTOOLS.LPRINTF('----------------------------');
CALL SYSTOOLS.LPRINTF('Vault Job: ' || @vault_job);
CALL SYSTOOLS.LPRINTF('errors: ' || @errors);
CALL SYSTOOLS.LPRINTF('warnings: ' || @warnings);
CALL SYSTOOLS.LPRINTF('job complete at ' || @job_complete);
CALL SYSTOOLS.LPRINTF('elapsed: ' || @elapsed);

END

#SQL
Birgitta Hauser's profile image
Birgitta Hauser IBM Champions posted 01/03/25 12:07 PM

Since your query will return 5 rows you have to declare a cursor and loop through the Cursor (DECLARE CURSOR / OPEN CURSOR / LOOP through all rows / FETCH into Variables / CLOSE CURSOR.


#SQL
Daniel Gross's profile image
Daniel Gross IBM Champions posted 01/06/25 12:30 AM

As Birgitta already said, a solution with a declared cursor is possible. 

But you can also assign your values with singular set statements, like this:

SET @vault_job = (SELECT REPLACE(SUBSTR(LINE, 46, 10), '/', '')

FROM ... WHERE LINE_NUMBER = 5);

(hopefully my syntax is OK as I'm sitting at the airport without my notebook)

You have to do that for each line, as you need values from different lines. 

And No - you can't "compress" different lines into one, using a CASE expression - we'll sure you can, but this might be more trouble, than doing 5 separate SET/SELECT statements.

HTH

Daniel


#SQL

Related Content