Can you tell me the steps to "Create foreign function interval(param string) returns timestamp OPTIONS ("teiid_rel:native-query" $1)"? using VDB in Teiid-9.1.1-jdbc

[thiru28]

Can you tell me the steps to "Create foreign function interval(param string) returns timestamp OPTIONS ("teiid_rel:native-query" $1)"? using VDB in Teiid-9.1.1-jdbc

[rareddy]

See Source Supported Functions · Teiid Documentation  in the section using the Designer

[thiru28]

I am using DDL  based VDB

[rareddy]

Read further in same document.

[thiru28]

I have included the VDB xml

<model name="PI" type="PHYSICAL" visible="true">      

          <property name="importer.importProcedures" value="true"/>

          <property name="importer.tableTypes" value="TABLE"></property> 

           <property name="importer.importKeys" value="false"></property> 

            <source translator-name="osisoft-pi" connection-jndi-name="java:/PISrc" name="PISrc" />

           <metadata type="NATIVE"/> 

            <metadata type="DDL"> 

            <![CDATA[

                    CREATE FOREIGN FUNCTION interval1 (arg1 string) RETURNS timestamp OPTIONS ("teiid_rel:native-query" '$1');

            ]]> 

        </metadata>

    </model>   

I am executing the select statement on the Squirrel,

SELECT pi."Time", pi."value", pi.tag from "PiArchive"."PiAvg" pi where

pi.tag= 'CDT158' and

pi.timestep = pi.interval1('5d') AND

pi.time BETWEEN '2018-01-27'  AND '2018-01-28'

I got an error below,

Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PISrc: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT TOP 100 g_0.[time] AS c_0, g_0.[value] AS c_1, g_0.[tag] AS c_2 FROM [piarchive].[piavg] AS g_0 WHERE g_0.[tag] = 'CDT158' AND cast(g_0.[timestep] AS DateTime) = '5d' AND g_0.[time] >= '2018-01-27 00:00:00.0' AND g_0.[time] <= '2018-01-28 00:00:00.0']

SQLState:  50000

ErrorCode: 30504

Please advise me.

[thiru28]

Its working fine from the below code changes in VDB.xml

<metadata type="NATIVE"/> 

<metadata type="DDL"> 

            <![CDATA[

CREATE FOREIGN FUNCTION TIMEINTERVAL(arg1 string) RETURNS TIME OPTIONS ("teiid_rel:native-query" '$1');

]]> 

</metadata> 

Squirrel select statement,

SELECT pi.Tag, pi."Time", pi."value" FROM "PiArchive"."PiAvg" pi

WHERE pi.tag= 'CDT158' AND

pi.timestep = TIMEINTERVAL('1w')  AND

pi.time BETWEEN '2018-01-01' AND '2018-12-31'