[teiid-issues] [JBoss JIRA] (TEIID-4822) predicate in on clause is not pushed down to view's underlying select statement
Bram Gadeyne (JIRA)
issues at jboss.org
Wed Mar 22 08:37:00 EDT 2017
[ https://issues.jboss.org/browse/TEIID-4822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13382283#comment-13382283 ]
Bram Gadeyne commented on TEIID-4822:
-------------------------------------
Adding /*+ MAKEDEP */ resolves this issue
> predicate in on clause is not pushed down to view's underlying select statement
> -------------------------------------------------------------------------------
>
> Key: TEIID-4822
> URL: https://issues.jboss.org/browse/TEIID-4822
> Project: Teiid
> Issue Type: Enhancement
> Affects Versions: 9.1.4
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
>
> Hi,
> I have a view called tv_retrieve_labres that contains the following select statement
> {code:sql}
> SELECT
> f.PatientID AS admissionid, f.Labogroep, f.SampleTime, f.EnterTime, f.varvalue, f.StringValue
> FROM (
> SELECT v.PatientID, v.Labogroep, v.SampleTime, v.EnterTime, v.varvalue, v.StringValue,
> ROW_NUMBER() OVER (PARTITION BY v.PatientID, v.Labogroep, v.SampleTime ORDER BY v.EnterTime DESC) AS rang
> FROM (
> SELECT lcv.PatientID, lm.Labogroep, lcv.SampleTime, lcv.EnterTime, lcv.varvalue, lcv.StringValue,
> ROW_NUMBER() OVER (PARTITION BY lcv.PatientID, lcv.variableid_oud, lcv.ResultID ORDER BY lcv.ResultNo DESC) AS rang
> FROM izisprod.prod_U_P_LabMapping AS lm
> INNER JOIN /*+ MAKEDEP */ izisprod.prod_U_PV_LabCorrectValues AS lcv ON
> lcv.variableid_oud = lm.VariableID AND
> bitand(convert(lcv.Status, integer), 8) = 8 AND
> bitand(convert(lcv.Status, integer), 2) <> 2
> UNION
> SELECT lcv.PatientID, lm.Labogroep, lcv.SampleTime, lcv.EnterTime, lcv.varvalue, lcv.StringValue,
> ROW_NUMBER() OVER (PARTITION BY lcv.PatientID, lcv.variableid_oud, lcv.ResultID ORDER BY lcv.ResultNo DESC) AS rang
> FROM iziswh.wh_U_P_LabMapping AS lm
> INNER JOIN /*+ MAKEDEP */ iziswh.wh_U_PV_LabCorrectValues AS lcv ON
> lcv.variableid_oud = lm.VariableID AND
> bitand(convert(lcv.Status, integer), 8) = 8 AND
> bitand(convert(lcv.Status, integer), 2) <> 2
> ) AS v
> WHERE v.rang = 1
> ) AS f
> WHERE
> f.rang = 1
> {code}
> When I execute the following select statement the value for lrs.Labogroep is not pushed down.
> {code:sql}
> select gd.patientid, lrs.varvalue, lrs.entertime, lrs.sampletime
> from prod_P_GeneralData gd
> join tv_retrieve_labres lrs on
> lrs.admissionid = gd.PatientID
> and lrs.Labogroep = 'pH-type'
> and lrs.varvalue = 1
> where gd.status = 1
> {code}
> Should this normally be working? I'm wondering if it doesn't work because of the union statement in the view.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
More information about the teiid-issues
mailing list