Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4022

Materialization issue dynamic SQL command can't be executed due ambiguous group name

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Blocker Blocker
    • 9.0, 8.12.5, 8.13.2
    • 8.12.x
    • Server
    • None

      The defined model for materialization:

      	<model name="PersonMatModel" type="VIRTUAL">
      		<metadata type="DDL"><![CDATA[
      	CREATE view PersonMatView
      		(
                  name string,
                  id integer,
                  email string
       		) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',         
                     MATERIALIZED_TABLE 'PersonMatCache.Person', 
                     "teiid_rel:MATVIEW_TTL" 60000,
                     "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute PersonMatCache.native(''truncate cache'');',
                     "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT"  'execute PersonMatCache.native(''swap cache names'');', 
                      "teiid_rel:MATERIALIZED_STAGE_TABLE" 'PersonMatCache.Person',
                    "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
                     "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', 
                     "teiid_rel:MATVIEW_STATUS_TABLE" 'Accounts.status',                
                     "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION')
       		AS SELECT  name, id, email from PersonInfoModel.Person;
          	]]>
         		</metadata>
      	</model>   
      

      causes the following exception:

      org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't 
      execute the dynamic SQL command "EXECUTE IMMEDIATE ('SELECT count(*) as 
      rowCount FROM ' || matViewTable) AS rowCount integer INTO #load_count" with 
      the SQL statement "('SELECT count(*) as rowCount FROM ' || matViewTable)" due 
      to: Group specified is ambiguous, resubmit the query by fully qualifying the group 
      name: Person
      

      And executing the following internal call that Teiid uses does produce the non-qualifed name

      {ccode}

      SELECT TargetName from SYSADMIN.MatViews WHERE VDBName = 'PeopleMat' AND SchemaName = 'PersonMatModel' AND Name = 'PersonMatView'

      
      

      returns: Person

              rhn-engineering-shawkins Steven Hawkins
              van.halbert Van Halbert (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: