THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP

Senz79
Senz79 used Ask the Experts™
on
Hello All
Kindly hep me with the below error
THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP

Thanks Senz
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
We will need to see the code which is causing this error. Apparently it looks like a circular reference
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This would be a warning rather than a error...
Kindly post your CTE to confirm..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This would be a warning rather than a error...
Kindly post your CTE to confirm..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

when you have a table expression that use recursive sql, you may go into an infinite loop
in order to avoid that, you should code some extra predicate
look at the example here:

WITH NUMBERS (LEVEL, NEXTONE) AS
           (
          SELECT 1, 1
             FROM SYSIBM.SYSDUMMY1
               UNION ALL
             SELECT LEVEL +1, LEVEL +1
             FROM NUMBERS
             WHERE LEVEL < 100
        )
   SELECT NEXTONE
     FROM NUMBERS
     ORDER BY NEXTONE


the level column and predicate is used to limit the level of nesting

Author

Commented:
hi below is the query
 

SELECT DISTINCT 
              TASK_TARGET_VIEW.SERVER_ID, TASK_TARGET_VIEW.SERVER_NAME, TASK_TARGET_VIEW.OS_NAME, 
              TASK_TARGET_VIEW.OS_VERSION, TASK_TARGET_VIEW.POOL, TASK_TARGET_VIEW.TIER, 
              TASK_TARGET_VIEW.CUSTOMER_NAME, TASK_TARGET_VIEW.APPLICATION_NAME, 
              TASK_TARGET_VIEW.STATE, TASK_TARGET_VIEW.CUSTOMER_ID, TASK_TARGET_VIEW.APPLICATION_ID, 
              TASK_TARGET_VIEW.POOL_ID, TASK_TARGET_VIEW.TIER_ID, TASK_STATUS_VIEW.TASK_ID, 
              TASK_STATUS_VIEW.CREATED_BY_USER, TASK_STATUS_VIEW.TIMEOUT, TASK_STATUS_VIEW.TASK_JOB_NAME, 
              ASK_STATUS_VIEW.TASK_JOB_TYPE, TASK_STATUS_VIEW.DESCRIPTION TASK_DESRIPTION, TASK_TARGET_VIEW.TARGET_STATUS, 
              TASK_TARGET_VIEW.REQUEST_ID, TASK_STATUS_VIEW.START_DATE, TASK_STATUS_VIEW.END_DATE, 
              TASK_STATUS_VIEW.MAIN_TASK_STATUS, 
              
              UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID, UNCLIPPED_SOFTWARE_INFO_VIEW.SOFTWARE, 
              UNCLIPPED_SOFTWARE_INFO_VIEW.VENDOR, UNCLIPPED_SOFTWARE_INFO_VIEW.VERSION, 
              UNCLIPPED_SOFTWARE_INFO_VIEW.DESCRIPTION SOFTWARE_DESCRIPTION, 
              UNCLIPPED_SOFTWARE_INFO_VIEW."TYPE" SOFTWARE_TYPE, 
              
              TASK_TARGET_VIEW.GROUP_ID, TASK_TARGET_VIEW.GROUP_NAME 
              
              FROM
              
              TASK_STATUS_VIEW 
              
              INNER JOIN TASK_JOB_ITEM ON (TASK_STATUS_VIEW.TASK_JOB_ID=TASK_JOB_ITEM.TASK_JOB_ID) 
              INNER JOIN TASK_ARGUMENT ON ( (TASK_ARGUMENT."NAME" = 'SoftwareModuleID' OR TASK_ARGUMENT."NAME" = 'ImageID') 
              AND TASK_ARGUMENT.JOB_ITEM_ID=TASK_JOB_ITEM.TASK_JOB_ITEM_ID AND TASK_JOB_ITEM.WORKFLOW_NAME NOT LIKE '%Capture%') 
              INNER JOIN TASK_TARGET_VIEW ON (TASK_TARGET_VIEW.TASK_ID=TASK_STATUS_VIEW.TASK_ID) 
              INNER JOIN PATCH_IN_STACK_IN_STACK_VIEW  ON (CHAR(PATCH_IN_STACK_IN_STACK_VIEW.STACK_ID) = TASK_ARGUMENT."VALUE") 
              INNER JOIN UNCLIPPED_SOFTWARE_INFO_VIEW ON UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID = 
              PATCH_IN_STACK_IN_STACK_VIEW .MODULE_ID

Open in new window

well,
the recursive expression is probably inside one of the views you select from, so you will need to find which one
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Your Query looks normal without Common Table Expressions and no chances of looping in the query above..

Kindly provide the query using WITH Clause..

Author

Commented:

WITH RPL (STACK_ID, MODULE_ID) AS ( SELECT ROOT.STACK_ID, ROOT.MODULE_ID FROM software_stack_entry ROOT WHERE ROOT.MODULE_ID in (select software_patch_id  from software_patch) UNION ALL SELECT CHILD.STACK_ID, PARENT.MODULE_ID FROM RPL PARENT, software_stack_entry CHILD WHERE PARENT.STACK_ID = CHILD.MODULE_ID ) SELECT DISTINCT STACK_ID, module_id FROM RPL

Open in new window

try

WITH RPL (STACK_ID, MODULE_ID,LEVEL) AS ( SELECT ROOT.STACK_ID, ROOT.MODULE_ID,1 LEVEL FROM software_stack_entry ROOT WHERE ROOT.MODULE_ID in (select software_patch_id  from software_patch) UNION ALL SELECT CHILD.STACK_ID, PARENT.MODULE_ID, LEVEL+1 FROM RPL PARENT, software_stack_entry CHILD WHERE PARENT.STACK_ID = CHILD.MODULE_ID AND LEVEL < 100) SELECT DISTINCT STACK_ID, module_id FROM RPL

Author

Commented:
Hi Momi
the above syntax works fine but how di i include it in the parent query
 
SELECT DISTINCT TASK_TARGET_VIEW.SERVER_ID, TASK_TARGET_VIEW.SERVER_NAME, TASK_TARGET_VIEW.OS_NAME, TASK_TARGET_VIEW.OS_VERSION, TASK_TARGET_VIEW.POOL, TASK_TARGET_VIEW.TIER, TASK_TARGET_VIEW.CUSTOMER_NAME, TASK_TARGET_VIEW.APPLICATION_NAME, TASK_TARGET_VIEW.STATE, TASK_TARGET_VIEW.CUSTOMER_ID, TASK_TARGET_VIEW.APPLICATION_ID, TASK_TARGET_VIEW.POOL_ID, TASK_TARGET_VIEW.TIER_ID, TASK_STATUS_VIEW.TASK_ID, TASK_STATUS_VIEW.CREATED_BY_USER, TASK_STATUS_VIEW.TIMEOUT, TASK_STATUS_VIEW.TASK_JOB_NAME, ASK_STATUS_VIEW.TASK_JOB_TYPE, TASK_STATUS_VIEW.DESCRIPTION TASK_DESRIPTION, TASK_TARGET_VIEW.TARGET_STATUS, TASK_TARGET_VIEW.REQUEST_ID, TASK_STATUS_VIEW.START_DATE, TASK_STATUS_VIEW.END_DATE, TASK_STATUS_VIEW.MAIN_TASK_STATUS, UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID, UNCLIPPED_SOFTWARE_INFO_VIEW.SOFTWARE, UNCLIPPED_SOFTWARE_INFO_VIEW.VENDOR, UNCLIPPED_SOFTWARE_INFO_VIEW.VERSION, UNCLIPPED_SOFTWARE_INFO_VIEW.DESCRIPTION SOFTWARE_DESCRIPTION, UNCLIPPED_SOFTWARE_INFO_VIEW."TYPE" SOFTWARE_TYPE, TASK_TARGET_VIEW.GROUP_ID, TASK_TARGET_VIEW.GROUP_NAME FROM TASK_STATUS_VIEW INNER JOIN TASK_JOB_ITEM ON (TASK_STATUS_VIEW.TASK_JOB_ID=TASK_JOB_ITEM.TASK_JOB_ID) INNER JOIN TASK_ARGUMENT ON ( (TASK_ARGUMENT."NAME" = 'SoftwareModuleID' OR TASK_ARGUMENT."NAME" = 'ImageID') AND TASK_ARGUMENT.JOB_ITEM_ID=TASK_JOB_ITEM.TASK_JOB_ITEM_ID AND TASK_JOB_ITEM.WORKFLOW_NAME NOT LIKE '%Capture%') INNER JOIN TASK_TARGET_VIEW ON (TASK_TARGET_VIEW.TASK_ID=TASK_STATUS_VIEW.TASK_ID) INNER JOIN PATCH_IN_STACK_IN_STACK_VIEW ON (CHAR(PATCH_IN_STACK_IN_STACK_VIEW.STACK_ID) = TASK_ARGUMENT."VALUE") INNER JOIN UNCLIPPED_SOFTWARE_INFO_VIEW ON UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID = PATCH_IN_STACK_IN_STACK_VIEW .MODULE_ID Open in New Window Select All Accept and Award Points Accept as Solution
 
 
 
Where the line
 INNER JOIN PATCH_IN_STACK_IN_STACK_VIEW
has the recurrsion
you have to options,
either correct the definition of the view, or instead of writing
INNER JOIN PATCH_IN_STACK_IN_STACK_VIEW
write
inner join (SELECT DISTINCT STACK_ID, module_id FROM RPL) PATCH_IN_STACK_IN_STACK_VIEW

Author

Commented:
will it be something like below
SELECT DISTINCT TASK_TARGET_VIEW.SERVER_ID, TASK_TARGET_VIEW.SERVER_NAME, 
TASK_TARGET_VIEW.OS_NAME, TASK_TARGET_VIEW.OS_VERSION, TASK_TARGET_VIEW.POOL, 
TASK_TARGET_VIEW.TIER, TASK_TARGET_VIEW.CUSTOMER_NAME, 
TASK_TARGET_VIEW.APPLICATION_NAME, TASK_TARGET_VIEW.STATE, TASK_TARGET_VIEW.CUSTOMER_ID, 
TASK_TARGET_VIEW.APPLICATION_ID, TASK_TARGET_VIEW.POOL_ID, 
TASK_TARGET_VIEW.TIER_ID, TASK_STATUS_VIEW.TASK_ID, TASK_STATUS_VIEW.CREATED_BY_USER, 
TASK_STATUS_VIEW.TIMEOUT, TASK_STATUS_VIEW.TASK_JOB_NAME, TASK_STATUS_VIEW.TASK_JOB_TYPE, 
TASK_STATUS_VIEW.DESCRIPTION TASK_DESRIPTION, TASK_TARGET_VIEW.TARGET_STATUS, 
TASK_TARGET_VIEW.REQUEST_ID, TASK_STATUS_VIEW.START_DATE, TASK_STATUS_VIEW.END_DATE, 
TASK_STATUS_VIEW.MAIN_TASK_STATUS, 
--UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID, UNCLIPPED_SOFTWARE_INFO_VIEW.SOFTWARE, UNCLIPPED_SOFTWARE_INFO_VIEW.VENDOR, UNCLIPPED_SOFTWARE_INFO_VIEW.VERSION, UNCLIPPED_SOFTWARE_INFO_VIEW.DESCRIPTION SOFTWARE_DESCRIPTION, UNCLIPPED_SOFTWARE_INFO_VIEW."TYPE" SOFTWARE_TYPE,
 TASK_TARGET_VIEW.GROUP_ID, TASK_TARGET_VIEW.GROUP_NAME FROM TASK_STATUS_VIEW 
 
INNER JOIN TASK_JOB_ITEM ON (TASK_STATUS_VIEW.TASK_JOB_ID=TASK_JOB_ITEM.TASK_JOB_ID) 
 
INNER JOIN TASK_ARGUMENT ON ( (TASK_ARGUMENT."NAME" = 'SoftwareModuleID' OR TASK_ARGUMENT."NAME" = 'ImageID') 
AND TASK_ARGUMENT.JOB_ITEM_ID=TASK_JOB_ITEM.TASK_JOB_ITEM_ID AND 
TASK_JOB_ITEM.WORKFLOW_NAME NOT LIKE '%Capture%') 
 
INNER JOIN TASK_TARGET_VIEW ON (TASK_TARGET_VIEW.TASK_ID=TASK_STATUS_VIEW.TASK_ID) 
 
INNER JOIN 
(WITH RPL (STACK_ID, MODULE_ID,LEVEL) AS 
	( SELECT ROOT.STACK_ID, ROOT.MODULE_ID,1 LEVEL FROM software_stack_entry ROOT WHERE ROOT.MODULE_ID in 
		(select software_patch_id  from software_patch) UNION ALL SELECT CHILD.STACK_ID, PARENT.MODULE_ID, LEVEL+1 
			FROM RPL PARENT, software_stack_entry CHILD WHERE PARENT.STACK_ID = CHILD.MODULE_ID AND LEVEL < 100)
				SELECT DISTINCT STACK_ID, module_id FROM RPL) 
					
PATCH_IN_STACK_IN_STACK_VIEW  ON (CHAR(PATCH_IN_STACK_IN_STACK_VIEW.STACK_ID) = TASK_ARGUMENT."VALUE") 
 
INNER JOIN UNCLIPPED_SOFTWARE_INFO_VIEW ON UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID = 
PATCH_IN_STACK_IN_STACK_VIEW .MODULE_ID

Open in new window

no
like this

WITH RPL (STACK_ID, MODULE_ID,LEVEL) AS
      ( SELECT ROOT.STACK_ID, ROOT.MODULE_ID,1 LEVEL FROM software_stack_entry ROOT WHERE ROOT.MODULE_ID in
            (select software_patch_id  from software_patch) UNION ALL SELECT CHILD.STACK_ID, PARENT.MODULE_ID, LEVEL+1
                  FROM RPL PARENT, software_stack_entry CHILD WHERE PARENT.STACK_ID = CHILD.MODULE_ID AND LEVEL < 100)

SELECT DISTINCT TASK_TARGET_VIEW.SERVER_ID, TASK_TARGET_VIEW.SERVER_NAME,
TASK_TARGET_VIEW.OS_NAME, TASK_TARGET_VIEW.OS_VERSION, TASK_TARGET_VIEW.POOL,
TASK_TARGET_VIEW.TIER, TASK_TARGET_VIEW.CUSTOMER_NAME,
TASK_TARGET_VIEW.APPLICATION_NAME, TASK_TARGET_VIEW.STATE, TASK_TARGET_VIEW.CUSTOMER_ID,
TASK_TARGET_VIEW.APPLICATION_ID, TASK_TARGET_VIEW.POOL_ID,
TASK_TARGET_VIEW.TIER_ID, TASK_STATUS_VIEW.TASK_ID, TASK_STATUS_VIEW.CREATED_BY_USER,
TASK_STATUS_VIEW.TIMEOUT, TASK_STATUS_VIEW.TASK_JOB_NAME, TASK_STATUS_VIEW.TASK_JOB_TYPE,
TASK_STATUS_VIEW.DESCRIPTION TASK_DESRIPTION, TASK_TARGET_VIEW.TARGET_STATUS,
TASK_TARGET_VIEW.REQUEST_ID, TASK_STATUS_VIEW.START_DATE, TASK_STATUS_VIEW.END_DATE,
TASK_STATUS_VIEW.MAIN_TASK_STATUS,
--UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID, UNCLIPPED_SOFTWARE_INFO_VIEW.SOFTWARE, UNCLIPPED_SOFTWARE_INFO_VIEW.VENDOR, UNCLIPPED_SOFTWARE_INFO_VIEW.VERSION, UNCLIPPED_SOFTWARE_INFO_VIEW.DESCRIPTION SOFTWARE_DESCRIPTION, UNCLIPPED_SOFTWARE_INFO_VIEW."TYPE" SOFTWARE_TYPE,
 TASK_TARGET_VIEW.GROUP_ID, TASK_TARGET_VIEW.GROUP_NAME FROM TASK_STATUS_VIEW
 
INNER JOIN TASK_JOB_ITEM ON (TASK_STATUS_VIEW.TASK_JOB_ID=TASK_JOB_ITEM.TASK_JOB_ID)
 
INNER JOIN TASK_ARGUMENT ON ( (TASK_ARGUMENT."NAME" = 'SoftwareModuleID' OR TASK_ARGUMENT."NAME" = 'ImageID')
AND TASK_ARGUMENT.JOB_ITEM_ID=TASK_JOB_ITEM.TASK_JOB_ITEM_ID AND
TASK_JOB_ITEM.WORKFLOW_NAME NOT LIKE '%Capture%')
 
INNER JOIN TASK_TARGET_VIEW ON (TASK_TARGET_VIEW.TASK_ID=TASK_STATUS_VIEW.TASK_ID)
 
INNER JOIN
(SELECT DISTINCT STACK_ID, module_id FROM RPL)
                              
PATCH_IN_STACK_IN_STACK_VIEW  ON (CHAR(PATCH_IN_STACK_IN_STACK_VIEW.STACK_ID) = TASK_ARGUMENT."VALUE")
 
INNER JOIN UNCLIPPED_SOFTWARE_INFO_VIEW ON UNCLIPPED_SOFTWARE_INFO_VIEW.MODULE_ID =
PATCH_IN_STACK_IN_STACK_VIEW .MODULE_ID

Author

Commented:
Hi Momi
This should return data but the query is giving 0 results
are you sure the problem is with part i gave you? do you get any results when running

WITH RPL (STACK_ID, MODULE_ID,LEVEL) AS
      ( SELECT ROOT.STACK_ID, ROOT.MODULE_ID,1 LEVEL FROM software_stack_entry ROOT WHERE ROOT.MODULE_ID in
            (select software_patch_id  from software_patch) UNION ALL SELECT CHILD.STACK_ID, PARENT.MODULE_ID, LEVEL+1
                  FROM RPL PARENT, software_stack_entry CHILD WHERE PARENT.STACK_ID = CHILD.MODULE_ID AND LEVEL < 100)
select * from rpl

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial