Bram Gadeyne created TEIID-4822: ----------------------------------- Summary: 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)