1 Reply Latest reply on Jul 22, 2016 5:40 PM by rareddy

    Reserved keyword encountered when defining a Teiid view

    jrod2016
    jrod2016 Jul 22, 2016 5:16 PM

      My view definition is as follows:

      CREATE VIEW View2 (City VARCHAR(255), Month VARCHAR(255), State VARCHAR(255), Store_name VARCHAR(255), Year VARCHAR(255),
Margin VARCHAR(255), Quantity_sold VARCHAR(255), Sales_revenue VARCHAR(255))
AS SELECT A.City, A.Month, A.State, A.Store_name, A.Year, A.Margin, A.Quantity_sold, A.Sales_revenue
FROM (EXEC AdfcoreSource.executeProfile(102)) AS f,
XMLTABLE('//*[local-name()=''tr''][not(@type)]' PASSING XMLPARSE(DOCUMENT f.result)
COLUMNS City VARCHAR(255) PATH '*[local-name()=''td''][1]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()',
Month VARCHAR(255) PATH '*[local-name()=''td''][2]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()',
State VARCHAR(255) PATH '*[local-name()=''td''][3]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()',
Store_name VARCHAR(255) PATH '*[local-name()=''td''][4]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()',
Year VARCHAR(255) PATH '*[local-name()=''td''][5]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()', 
Margin VARCHAR(255) PATH '*[local-name()=''td''][6]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()',
Quantity_sold VARCHAR(255) PATH '*[local-name()=''td''][7]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()',
Sales_revenue VARCHAR(255) PATH '*[local-name()=''td''][8]/*[local-name()=''cell'']/*[local-name()=''ct'']/text()') AS A

       

      Because I have a column called Month, I get the following exception while deploying the model:

      org.teiid.metadata.ParseException: TEIID30386 org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "255), [*]Month[*] VARCHAR(" at line 1, column 39.
Was expecting: "constraint" | "foreign" | "primary" | "unique" | id
  at org.teiid.query.parser.QueryParser.parseDDL(QueryParser.java:472) ~[teiid-engine-9.0.1.jar:9.0.1]
  at org.teiid.metadata.MetadataFactory.parse(MetadataFactory.java:776) ~[teiid-api-9.0.1.jar:9.0.1]
  at org.teiid.query.metadata.DDLMetadataRepository.loadMetadata(DDLMetadataRepository.java:40) ~[teiid-engine-9.0.1.jar:9.0.1]
  at org.teiid.runtime.AbstractVDBDeployer$MetadataRepositoryWrapper.loadMetadata(AbstractVDBDeployer.java:84) ~[teiid-runtime-9.0.1.jar:9.0.1]
  at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) ~[teiid-engine-9.0.1.jar:9.0.1]
  at org.teiid.runtime.EmbeddedServer.loadMetadata(EmbeddedServer.java:792) ~[teiid-runtime-9.0.1.jar:9.0.1]
  at org.teiid.runtime.AbstractVDBDeployer.loadMetadata(AbstractVDBDeployer.java:210) ~[teiid-runtime-9.0.1.jar:9.0.1]
  at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:742) ~[teiid-runtime-9.0.1.jar:9.0.1]
  at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:654) ~[teiid-runtime-9.0.1.jar:9.0.1]

       

      If I rename Month to something like MonthFoo, this view definition works.

       

      My question is, what is the list of reserved keywords in Teiid which will cause an exception when defining the view? If I know these beforehand, I can suffix/prefix them with something like an underscore to avoid this error.

       

      Thanks,

      John

      • 181 Views
      • Tags: