[teiid-issues] [JBoss JIRA] (TEIID-4187) Extend support for PI OLEDB Enterprise Queries in OSI PI Translator

[Ramesh Reddy (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13253814#comment-13253814 ] 

Ramesh Reddy commented on TEIID-4187:
-------------------------------------

Examples of executing the queries on PI through Teiid. See usage of LATERAL JOIN to execute TVF functions

{code}
SELECT eh.Path, eh.Name, ea.Name, a."Time", a."Value"
FROM Sample.Asset.ElementHierarchy eh
INNER JOIN Sample.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID
INNER JOIN Sample.Data.Archive a ON a.ElementAttributeID = ea.ID
WHERE eh.Path = '\Data Center\'
AND a."Time" BETWEEN TIMESTAMPADD(SQL_TSI_HOUR, -1, now()) AND now() 

SELECT g_0."StartTime", g_1."Name", g_0."Time", g_0."Number of Computers", g_0."Temperature" 
FROM "Sample"."DataT"."ft_TransposeArchive_Building Template" AS g_0, 
"Sample"."Asset"."ElementHierarchy" AS g_1 WHERE g_1."ElementID" = g_0."ElementID" 
AND g_0."EndTime" = '2016-06-16 16:16:50.187' AND 
g_0."ElementID" IN (SELECT cast(g_2."ElementID" as String) FROM "Sample"."Asset"."ElementHierarchy" AS g_2 WHERE g_2."Path" = '\Data Center\')


SELECT EH.Name, BT."Time", BT."Number of Computers", BT."Temperature"
	FROM Sample.Asset.ElementHierarchy EH
	LEFT JOIN  LATERAL (exec "TransposeArchive_Building Template"(EH.ElementID, TIMESTAMPADD(SQL_TSI_HOUR, -1, now()), now())) BT on 1=1
	WHERE EH.ElementID IN (SELECT ElementID FROM Sample.Asset.ElementHierarchy WHERE Path='\Data Center\')
{code}

> Extend support for PI OLEDB Enterprise Queries in OSI PI Translator
> -------------------------------------------------------------------
>
>                 Key: TEIID-4187
>                 URL: https://issues.jboss.org/browse/TEIID-4187
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Misc. Connectors
>    Affects Versions: 8.13.3
>            Reporter: Al S
>            Assignee: Ramesh Reddy
>            Priority: Minor
>         Attachments: PI-OLEDB-Enterprise-2012-User-Guide.pdf
>
>
> Background - PI OLEDB queries go against the PI Data Archive whereas PI OLEDB Enterprise go against PI AF, which is a metadata layer that sits atop the PI Data Archive. Both sets of queries are now accessible from the PI JDBC adapter. 
> Please add the following enhancements to allow the OSI PI translator to work more effectively with OLEDB Enterprise queries.
> 1) Import table valued functions as Teiid procedures
> 2) Allow the pushdown of nested table joins
> 3) Update the PI translator to use the CROSS APPLY syntax.
> 4) When importing schemas, importer.ImportKeys has to be set to false as otherwise we receive a DuplicateRecord exception since the table value function ft_GetPIPoint appears in more than one place in the AF schemas. Could we please put in a fix/workaround to address this?
> Please refer to the OLEDB Enterprise Guide for query syntax and rules for more detail.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)