Avatar of Dale Edmonds
Dale Edmonds
 asked on

iSeries Stored Procedure with SQL CTE Error

When trying to run EXPLAIN on the following stored procedure we get the error message:

'Cannot display the query diagram because the query access plan information was not collected for the query'.

--  Generate SQL
--  Version:                         V6R1M0 080215
--  Generated on:                    04/28/15 14:48:42
-- --  Standards Option:                DB2 for i
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","USER" ;
 
CREATE PROCEDURE ADC/PROVIDERS_SELECT_V3_dee(
      IN IN_FACILITY_CODE CHAR(4) )
      DYNAMIC RESULT SETS 1
      LANGUAGE SQL
      SPECIFIC ADC/PROVIDERS_SELECT_V3_dee
      NOT DETERMINISTIC
      READS SQL DATA
      CALLED ON NULL INPUT
      SET OPTION  ALWBLK = *ALLREAD ,
      ALWCPYDTA = *OPTIMIZE ,
      COMMIT = *NONE ,
      CLOSQLCSR = *ENDMOD ,
      DECRESULT = (31, 31, 00) ,
      DFTRDBCOL = *NONE ,
      DYNDFTCOL = *NO ,
      DYNUSRPRF = *USER ,
      SRTSEQ = *HEX  
      BEGIN
DECLARE C1 CURSOR   FOR
 WITH
ACTIVEPROVIDERS AS (
      SELECT
            PIPRV@ AS ID ,
            PINAMD AS NAME ,
            PIPRVT AS TYPE ,
            PIDEL@ AS STATUS ,
            PIDTTERM AS TERMINATIONDATE ,
            PWEXTVAL AS DOSESPOTCLINICIANID
      FROM
            PRVMAS PM LEFT OUTER JOIN
            PRVEXT ON PIPRV@ = PWPRV@ AND PWEXT@ = 'CI'
      WHERE
            PIPRV@ <> '***' AND
            PIABRV <> '.' AND
            PIDEL@ = 'A' AND
            PIDTTERM = '1940-01-01' ) ,      

 
LOCALPROVIDERS AS (
      SELECT
            BWPRV@ AS ID
      
      FROM
            FACPRVLOOK
      WHERE
            BWFAC@ = '4131'
)
 
SELECT
      AP . ID ,
      AP . NAME ,
      AP . TYPE ,
      AP . STATUS ,
      AP . TERMINATIONDATE ,
      AP . DOSESPOTCLINICIANID ,
      CASE WHEN LP . ID IS NULL THEN 'False' ELSE 'True' END AS ISLOCAL
FROM
      ACTIVEPROVIDERS AP LEFT OUTER JOIN
      LOCALPROVIDERS LP ON AP . ID = LP . ID
FOR READ ONLY ;
 
OPEN C1 ;
END  ;
 
COMMENT ON SPECIFIC PROCEDURE ADC/PROVIDERS_SELECT_V3
      IS 'Returns list of all providers for ADC' ;
 
COMMENT ON PARAMETER SPECIFIC PROCEDURE ADC/PROVIDERS_SELECT_V3
( IN_FACILITY_CODE IS 'Facility Code' ) ;
IBM System i

Avatar of undefined
Last Comment
Gary Patterson, CISSP

8/22/2022 - Mon
Member_2_2484401

1. Have you been able to successfully RUN the stored procedure?
2. Does it do what you expect it to do?
3. Can you successfully use Visual Explain on the associated SELECT statement?

-- DaveSlash
Dale Edmonds

ASKER
1. No, can't get to the creation of stored procedure with error.

2. Yes, expected results in SQL realized.

3. No, and Yes.  If I remove the Declare Cursor and use the code below I can get Explain to work.

WITH
ACTIVEPROVIDERS AS (
      SELECT
            PIPRV@ AS ID ,
            PINAMD AS NAME ,
            PIPRVT AS TYPE ,
            PIDEL@ AS STATUS ,
            PIDTTERM AS TERMINATIONDATE ,
            PWEXTVAL AS DOSESPOTCLINICIANID
      FROM
            PRVMAS PM LEFT OUTER JOIN
            PRVEXT ON PIPRV@ = PWPRV@ AND PWEXT@ = 'CI'
      WHERE
            PIPRV@ <> '***' AND
            PIABRV <> '.' AND
            PIDEL@ = 'A' AND
            PIDTTERM = '1940-01-01' ) ,      

 
LOCALPROVIDERS AS (
      SELECT
            BWPRV@ AS ID
      
      FROM
            FACPRVLOOK
      WHERE
            BWFAC@ = '4131'
)
 
SELECT
      AP . ID ,
      AP . NAME ,
      AP . TYPE ,
      AP . STATUS ,
      AP . TERMINATIONDATE ,
      AP . DOSESPOTCLINICIANID ,
      CASE WHEN LP . ID IS NULL THEN 'False' ELSE 'True' END AS ISLOCAL
FROM
      ACTIVEPROVIDERS AP LEFT OUTER JOIN
      LOCALPROVIDERS LP ON AP . ID = LP . ID;
Member_2_2484401

> No, can't get to the creation of stored procedure with error

That explains why the Visual Explain won't work. If there are errors in the code, Visual Explain won't help at all. First, fix any errors in your code. Then, try Visual Explain.

Based on past experience, I would recommend getting a "simple-case" to work before adding more complexity. In other words, use an EXTREMELY simple select-statement, get the proc working. Then, gradually add more complexity to the query, compiling as you go.

HTH,
DaveSlash
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Gary Patterson, CISSP

As far as I'm aware, Visual Explain supports the following types of SQL statements: SELECT, INSERT, UPDATE, and DELETE.   DECLARE CURSOR isn't supported - need to unwrap the SELECT and VE that.
Dale Edmonds

ASKER
Yes, I agree, and I am sorry I am not being clear about the question.  I have no issue with cutting and pasting out the SQL to EXPLAIN>  I also agree to start simple and build but I must say that is what is done here.  

My question is related to why with a CTE there was still a Declare Cursor required to get this to create as Stored Procedure.  If you leave off the Declare Cursor the SQL is still fine but then iNavigator throws error stating an INTO is required.  

Once the Declare Cursor is back the stored procedure will create.  I see there are some PTF chats previously on IBM KB but we are current with what the APAR was discussing.

So basic question is why is Declare Cursor being required when we really don't want to use because of the single row processing and system performance impact.  

We have, like most today, a very SQL request heavy system because of User Window interface and of course all the I/O is QZDASOINIT related.  Most due to poorly developed SQL.  

Thanks for thoughts.
Dale Edmonds

ASKER
Just FYI.  This code does work and created stored procedure as we intended.  This is the error when the Declare Cursor is not there:  SQL0029 INTO clause missing from embedded statement.   Any thoughts?  Thank you all.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Member_2_2484401

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Gary Patterson, CISSP

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.