4 Replies Latest reply on Feb 20, 2013 11:46 AM by Steven Hawkins

    Join of reusable and non reusable executions

    Rakesh Balguri Expert

      Hi all,

       

      I am facing a problem with a join in Reusable and Non-Reusable executions when one of the non-reusable datasource has data and the other doesn't have the data.

       

      The problem I am facing is that the Non-Reusable executions are being repeated continuously and the Reusable execution is not getting executed at all.

       

      The following is the SQL and please find the attached query plan and test case to reproduce this problem.

       

      SELECT LATEST_HEALTH_STATE.*

      FROM

        (SELECT LATEST_HEALTH_STATE.*

         FROM

           (SELECT 'healthstatestream.LATEST_HEALTH_STATES' AS "__objecttype__",

                   name,

                   rgb,

                   severity,

                   objecttype,

                   worst_severity_by_policy.title

            FROM healthstatestream.health_states

            INNER JOIN

              (SELECT MIN(healthstatefkey) AS worst_health_state,

                      Object_type(objfkeyxml) AS objectType,

                      metadata.propertyvalue AS title

               FROM (healthstatestream.latest_health_states

                     INNER JOIN

                       (SELECT '<demo_policy_metadata.op_demo_policy_type_1 policyid=''policy name 1'' policyname=''policy 1001'' systemid=''sysid 1''></demo_policy_metadata.op_demo_policy_type_1>' AS POLICYFKEYXML) AS policies_configured_in_traffic_light ON latest_health_states.policyfkeyxml = policies_configured_in_traffic_light.policyfkeyxml)

               INNER JOIN chorus_metadata.property AS metadata ON Object_type(objfkeyxml) = metadata.objectname

               AND metadata.propertyname = 'VisibleName'

               AND metadata.subtype = 'Object'

               GROUP BY Object_type(objfkeyxml),

                        metadata.propertyvalue) AS worst_severity_by_policy ON worst_severity_by_policy.worst_health_state = health_states.severity) AS LATEST_HEALTH_STATE,

              (EXEC chorus_time.timetable(30000)) AS chorus_refresh_expression) AS LATEST_HEALTH_STATE

       

      Note: We tested using teiid 8.3.0.Beta2.

       

      Please suggest.

       

      Thanks,

      Rakesh.