THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP

We will need to see the code which is causing this error. Apparently it looks like a circular reference

This would be a warning rather than a error...
Kindly post your CTE to confirm..

This would be a warning rather than a error...
Kindly post your CTE to confirm..

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

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

Select allOpen 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

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

Kindly provide the query using WITH Clause..

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

Select allOpen 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

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

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

Select allOpen in new window

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