-
1. Re: 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 Mar 2, 2018 6:22 PM (in response to thiru28)See Source Supported Functions · Teiid Documentation in the section using the Designer
-
2. Re: 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 Mar 2, 2018 7:08 PM (in response to rareddy)I am using DDL based VDB
-
3. Re: 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 Mar 2, 2018 7:46 PM (in response to thiru28)Read further in same document.
-
4. Re: 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 Mar 2, 2018 8:30 PM (in response to rareddy)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.
-
5. Re: 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 Mar 3, 2018 6:48 PM (in response to 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'