June 2016 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-4259) "TEIID30259 Cannot introduce new expressions" when using formattimestamp

by Bram Gadeyne (JIRA)

[ https://issues.jboss.org/browse/TEIID-4259?page=com.atlassian.jira.plugin... ] Bram Gadeyne commented on TEIID-4259: ------------------------------------- Hi Steven, Upgrading to a newer version in on my schedule for next week or the week after that. I'll make sure to test it then. Here is the query plan: ============================================================================ USER COMMAND: SELECT w.Patientid AS Patientid, formattimestamp(w.ValueTime, 'yyyy-MM-dd HH:mm') AS t2, w.ValueTime AS Tijdstip, MAX(w.Bed) AS Bed, MAX(w.Naam) AS Naam, CASE WHEN convert(MAX(w.opname), date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(MAX(w.opname), date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN COUNT(w.MondZorgAantal) IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN COUNT(w.EvaluatieMond) > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS FROM (SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT obs1.PatientID, obs1.ValueTime, COUNT(*) AS c, pi.PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) <> convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT obs1.PatientID, obs1.ValueTime, COUNT(*) AS c, pi.PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) < convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 15005534) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v) AS w GROUP BY w.Patientid, w.ValueTime ORDER BY Bed LIMIT 100 ---------------------------------------------------------------------------- OPTIMIZE: SELECT w.Patientid AS Patientid, formattimestamp(w.ValueTime, 'yyyy-MM-dd HH:mm') AS t2, w.ValueTime AS Tijdstip, MAX(w.Bed) AS Bed, MAX(w.Naam) AS Naam, CASE WHEN convert(MAX(w.opname), date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(MAX(w.opname), date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN COUNT(w.MondZorgAantal) IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN COUNT(w.EvaluatieMond) > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS FROM (SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT obs1.PatientID, obs1.ValueTime, COUNT(*) AS c, pi.PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) <> convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT obs1.PatientID, obs1.ValueTime, COUNT(*) AS c, pi.PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) < convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 15005534) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v) AS w GROUP BY w.Patientid, w.ValueTime ORDER BY Bed LIMIT 100 ---------------------------------------------------------------------------- GENERATE CANONICAL: SELECT w.Patientid AS Patientid, formattimestamp(w.ValueTime, 'yyyy-MM-dd HH:mm') AS t2, w.ValueTime AS Tijdstip, MAX(w.Bed) AS Bed, MAX(w.Naam) AS Naam, CASE WHEN convert(MAX(w.opname), date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(MAX(w.opname), date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN COUNT(w.MondZorgAantal) IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN COUNT(w.EvaluatieMond) > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS FROM (SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT obs1.PatientID, obs1.ValueTime, COUNT(*) AS c, pi.PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) <> convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT obs1.PatientID, obs1.ValueTime, COUNT(*) AS c, pi.PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) < convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 15005534) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v) AS w GROUP BY w.Patientid, w.ValueTime ORDER BY Bed LIMIT 100 CANONICAL PLAN: TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100}) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed}) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS]}) Group(groups=[w, anon_grp2], props={GROUP_COLS=[w.Patientid, w.ValueTime], SYMBOL_MAP={anon_grp2.gcol0=w.Patientid, anon_grp2.gcol1=w.ValueTime, anon_grp2.agg0=MAX(w.Bed), anon_grp2.agg1=MAX(w.Naam), anon_grp2.agg2=MAX(w.opname), anon_grp2.agg3=COUNT(w.MondZorgAantal), anon_grp2.agg4=COUNT(w.EvaluatieMond)}}) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) <> convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) < convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 15005534) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false}) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=convert(pi.AdmTime, date) <> convert(now(), date)}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=pi.PatGroupAbbr LIKE 'PICU%'}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=pi.PatientStatusID = 1}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID]}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) <> convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}}) Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID]}) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi, anon_grp0], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi.PatientStatusID, anon_grp0.agg0=COUNT(*)}}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=obs1.VariableID = 30010003}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi.PatientID = obs1.PatientID, pi.PatientStatusID = 1, convert(pi.AdmTime, date) <> convert(now(), date), pi.PatGroupAbbr LIKE 'PICU%']}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v.c AS EvaluatieMond, v.PatientStatusID]}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi.PatientID = obs1.PatientID AND pi.PatientStatusID = 1 AND convert(pi.AdmTime, date) < convert(now(), date) AND pi.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 15005534) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp1.gcol0, v.ValueTime=anon_grp1.gcol1, v.c=anon_grp1.agg0, v.PatientStatusID=anon_grp1.gcol2}}) Project(groups=[anon_grp1], props={PROJECT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID]}) Group(groups=[izisprod.prod_PV_ObservationVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi, anon_grp1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1.PatientID, anon_grp1.gcol1=obs1.ValueTime, anon_grp1.gcol2=pi.PatientStatusID, anon_grp1.agg0=COUNT(*)}}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1], props={SELECT_CRITERIA=convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1], props={SELECT_CRITERIA=obs1.VariableID = 15005534}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi.PatientID = obs1.PatientID, pi.PatientStatusID = 1, convert(pi.AdmTime, date) < convert(now(), date), pi.PatGroupAbbr LIKE 'PICU%']}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) ============================================================================ EXECUTING PlaceAccess AFTER: TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100}) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed}) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS]}) Group(groups=[w, anon_grp2], props={GROUP_COLS=[w.Patientid, w.ValueTime], SYMBOL_MAP={anon_grp2.gcol0=w.Patientid, anon_grp2.gcol1=w.ValueTime, anon_grp2.agg0=MAX(w.Bed), anon_grp2.agg1=MAX(w.Naam), anon_grp2.agg2=MAX(w.opname), anon_grp2.agg3=COUNT(w.MondZorgAantal), anon_grp2.agg4=COUNT(w.EvaluatieMond)}}) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false}) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=convert(pi.AdmTime, date) <> convert(now(), date)}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=pi.PatGroupAbbr LIKE 'PICU%'}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=pi.PatientStatusID = 1}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID]}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}}) Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID]}) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi__1.PatientStatusID, anon_grp0.agg0=COUNT(*)}}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=obs1.VariableID = 30010003}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID, pi__1.PatientStatusID = 1, convert(pi__1.AdmTime, date) <> convert(now(), date), pi__1.PatGroupAbbr LIKE 'PICU%']}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID]}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}}) Project(groups=[anon_grp1], props={PROJECT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID]}) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={GROUP_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1__1.PatientID, anon_grp1.gcol1=obs1__1.ValueTime, anon_grp1.gcol2=pi__2.PatientStatusID, anon_grp1.agg0=COUNT(*)}}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=obs1__1.VariableID = 15005534}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID, pi__2.PatientStatusID = 1, convert(pi__2.AdmTime, date) < convert(now(), date), pi__2.PatGroupAbbr LIKE 'PICU%']}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING AssignOutputElements AFTER: TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS]}) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed, OUTPUT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], UNRELATED_SORT=false}) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], OUTPUT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS]}) Group(groups=[w, anon_grp2], props={GROUP_COLS=[w.Patientid, w.ValueTime], SYMBOL_MAP={anon_grp2.gcol0=w.Patientid, anon_grp2.gcol1=w.ValueTime, anon_grp2.agg0=MAX(w.Bed), anon_grp2.agg1=MAX(w.Naam), anon_grp2.agg2=MAX(w.opname), anon_grp2.agg3=COUNT(w.MondZorgAantal), anon_grp2.agg4=COUNT(w.EvaluatieMond)}, OUTPUT_COLS=[anon_grp2.gcol0, anon_grp2.gcol1, anon_grp2.agg0, anon_grp2.agg1, anon_grp2.agg2, anon_grp2.agg3, anon_grp2.agg4]}) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}, OUTPUT_COLS=[w.Patientid, w.ValueTime, w.Bed, w.Naam, w.opname, w.MondZorgAantal, w.EvaluatieMond, w.PatientStatusID]}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID]}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID]}) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID], OUTPUT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=convert(pi.AdmTime, date) <> convert(now(), date), OUTPUT_COLS=[pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName, pi.AdmTime, pi.PatientStatusID]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=pi.PatGroupAbbr LIKE 'PICU%', OUTPUT_COLS=[pi.AdmTime, pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName, pi.PatientStatusID]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=pi.PatientStatusID = 1, OUTPUT_COLS=[pi.PatGroupAbbr, pi.AdmTime, pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName, pi.PatientStatusID]}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi.PatientStatusID, pi.PatGroupAbbr, pi.AdmTime, pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName]}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi], props={OUTPUT_COLS=[pi.PatientStatusID, pi.PatGroupAbbr, pi.AdmTime, pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName]}) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID], OUTPUT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID]}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}, OUTPUT_COLS=[v.PatientID, v.ValueTime, v.c, v.PatientStatusID]}) Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID], OUTPUT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID]}) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi__1.PatientStatusID, anon_grp0.agg0=COUNT(*)}, OUTPUT_COLS=[anon_grp0.gcol0, anon_grp0.gcol1, anon_grp0.agg0, anon_grp0.gcol2]}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date), OUTPUT_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID]}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=obs1.VariableID = 30010003, OUTPUT_COLS=[obs1.ValueTime, obs1.PatientID, pi__1.PatientStatusID]}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID, pi__1.PatientStatusID = 1, convert(pi__1.AdmTime, date) <> convert(now(), date), pi__1.PatGroupAbbr LIKE 'PICU%'], OUTPUT_COLS=[obs1.VariableID, obs1.ValueTime, obs1.PatientID, pi__1.PatientStatusID]}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[obs1.PatientID, obs1.VariableID, obs1.ValueTime]}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={OUTPUT_COLS=[obs1.PatientID, obs1.VariableID, obs1.ValueTime]}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi__1.PatientID, pi__1.PatientStatusID, pi__1.AdmTime, pi__1.PatGroupAbbr]}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={OUTPUT_COLS=[pi__1.PatientID, pi__1.PatientStatusID, pi__1.AdmTime, pi__1.PatGroupAbbr]}) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID], OUTPUT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID]}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}, OUTPUT_COLS=[v__1.PatientID, v__1.ValueTime, v__1.c, v__1.PatientStatusID]}) Project(groups=[anon_grp1], props={PROJECT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID], OUTPUT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID]}) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={GROUP_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1__1.PatientID, anon_grp1.gcol1=obs1__1.ValueTime, anon_grp1.gcol2=pi__2.PatientStatusID, anon_grp1.agg0=COUNT(*)}, OUTPUT_COLS=[anon_grp1.gcol0, anon_grp1.gcol1, anon_grp1.agg0, anon_grp1.gcol2]}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date), OUTPUT_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID]}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=obs1__1.VariableID = 15005534, OUTPUT_COLS=[obs1__1.ValueTime, obs1__1.PatientID, pi__2.PatientStatusID]}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID, pi__2.PatientStatusID = 1, convert(pi__2.AdmTime, date) < convert(now(), date), pi__2.PatGroupAbbr LIKE 'PICU%'], OUTPUT_COLS=[obs1__1.VariableID, obs1__1.ValueTime, obs1__1.PatientID, pi__2.PatientStatusID]}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[obs1__1.PatientID, obs1__1.VariableID, obs1__1.ValueTime]}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={OUTPUT_COLS=[obs1__1.PatientID, obs1__1.VariableID, obs1__1.ValueTime]}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi__2.PatientID, pi__2.PatientStatusID, pi__2.AdmTime, pi__2.PatGroupAbbr]}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={OUTPUT_COLS=[pi__2.PatientID, pi__2.PatientStatusID, pi__2.AdmTime, pi__2.PatGroupAbbr]}) ============================================================================ EXECUTING PushSelectCriteria AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID], OUTPUT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID]}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi.PatientStatusID, pi.PatGroupAbbr, pi.AdmTime, pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=(pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)), OUTPUT_COLS=[pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName, pi.AdmTime, pi.PatientStatusID]}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi], props={OUTPUT_COLS=[pi.PatientStatusID, pi.PatGroupAbbr, pi.AdmTime, pi.PatientID, pi.BedAbbr, pi.PatientLastName, pi.PatientFirstName]}) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi__1.PatientStatusID, anon_grp0.agg0=COUNT(*)}, OUTPUT_COLS=[anon_grp0.gcol0, anon_grp0.gcol1, anon_grp0.agg0, anon_grp0.gcol2]}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID, pi__1.PatientStatusID = 1, convert(pi__1.AdmTime, date) <> convert(now(), date), pi__1.PatGroupAbbr LIKE 'PICU%'], OUTPUT_COLS=[obs1.VariableID, obs1.ValueTime, obs1.PatientID, pi__1.PatientStatusID]}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[obs1.PatientID, obs1.VariableID, obs1.ValueTime]}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=(obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID]}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={OUTPUT_COLS=[obs1.PatientID, obs1.VariableID, obs1.ValueTime]}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={GROUP_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1__1.PatientID, anon_grp1.gcol1=obs1__1.ValueTime, anon_grp1.gcol2=pi__2.PatientStatusID, anon_grp1.agg0=COUNT(*)}, OUTPUT_COLS=[anon_grp1.gcol0, anon_grp1.gcol1, anon_grp1.agg0, anon_grp1.gcol2]}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID, pi__2.PatientStatusID = 1, convert(pi__2.AdmTime, date) < convert(now(), date), pi__2.PatGroupAbbr LIKE 'PICU%'], OUTPUT_COLS=[obs1__1.VariableID, obs1__1.ValueTime, obs1__1.PatientID, pi__2.PatientStatusID]}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[obs1__1.PatientID, obs1__1.VariableID, obs1__1.ValueTime]}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=(obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID]}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={OUTPUT_COLS=[obs1__1.PatientID, obs1__1.VariableID, obs1__1.ValueTime]}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING MergeVirtual AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed, OUTPUT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], UNRELATED_SORT=false}) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}, OUTPUT_COLS=[w.Patientid, w.ValueTime, w.Bed, w.Naam, w.opname, w.MondZorgAantal, w.EvaluatieMond, w.PatientStatusID]}) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID], OUTPUT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID]}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}, OUTPUT_COLS=[v.PatientID, v.ValueTime, v.c, v.PatientStatusID]}) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID], OUTPUT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID]}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}, OUTPUT_COLS=[v__1.PatientID, v__1.ValueTime, v__1.c, v__1.PatientStatusID]}) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING DecomposeJoin AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID, pi__1.PatientStatusID = 1, convert(pi__1.AdmTime, date) <> convert(now(), date), pi__1.PatGroupAbbr LIKE 'PICU%'], OUTPUT_COLS=[obs1.VariableID, obs1.ValueTime, obs1.PatientID, pi__1.PatientStatusID]}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID, pi__2.PatientStatusID = 1, convert(pi__2.AdmTime, date) < convert(now(), date), pi__2.PatGroupAbbr LIKE 'PICU%'], OUTPUT_COLS=[obs1__1.VariableID, obs1__1.ValueTime, obs1__1.PatientID, pi__2.PatientStatusID]}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING MergeVirtual AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed, OUTPUT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], UNRELATED_SORT=false}) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}, OUTPUT_COLS=[w.Patientid, w.ValueTime, w.Bed, w.Naam, w.opname, w.MondZorgAantal, w.EvaluatieMond, w.PatientStatusID]}) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID], OUTPUT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID]}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}, OUTPUT_COLS=[v.PatientID, v.ValueTime, v.c, v.PatientStatusID]}) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID], OUTPUT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID]}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}, OUTPUT_COLS=[v__1.PatientID, v__1.ValueTime, v__1.c, v__1.PatientStatusID]}) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING PushNonJoinCriteria AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID], OUTPUT_COLS=[obs1.VariableID, obs1.ValueTime, obs1.PatientID, pi__1.PatientStatusID]}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=pi__1.PatGroupAbbr LIKE 'PICU%'}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=convert(pi__1.AdmTime, date) <> convert(now(), date)}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=pi__1.PatientStatusID = 1}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi__1.PatientID, pi__1.PatientStatusID, pi__1.AdmTime, pi__1.PatGroupAbbr]}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID], OUTPUT_COLS=[obs1__1.VariableID, obs1__1.ValueTime, obs1__1.PatientID, pi__2.PatientStatusID]}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=pi__2.PatGroupAbbr LIKE 'PICU%'}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=convert(pi__2.AdmTime, date) < convert(now(), date)}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=pi__2.PatientStatusID = 1}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi__2.PatientID, pi__2.PatientStatusID, pi__2.AdmTime, pi__2.PatGroupAbbr]}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING PushSelectCriteria AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID], OUTPUT_COLS=[obs1.VariableID, obs1.ValueTime, obs1.PatientID, pi__1.PatientStatusID]}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi__1.PatientID, pi__1.PatientStatusID, pi__1.AdmTime, pi__1.PatGroupAbbr]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=(pi__1.PatientStatusID = 1) AND (convert(pi__1.AdmTime, date) <> convert(now(), date)) AND (pi__1.PatGroupAbbr LIKE 'PICU%')}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={OUTPUT_COLS=[pi__1.PatientID, pi__1.PatientStatusID, pi__1.AdmTime, pi__1.PatGroupAbbr]}) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID], OUTPUT_COLS=[obs1__1.VariableID, obs1__1.ValueTime, obs1__1.PatientID, pi__2.PatientStatusID]}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=[pi__2.PatientID, pi__2.PatientStatusID, pi__2.AdmTime, pi__2.PatGroupAbbr]}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=(pi__2.PatientStatusID = 1) AND (convert(pi__2.AdmTime, date) < convert(now(), date)) AND (pi__2.PatGroupAbbr LIKE 'PICU%')}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={OUTPUT_COLS=[pi__2.PatientID, pi__2.PatientStatusID, pi__2.AdmTime, pi__2.PatGroupAbbr]}) ============================================================================ EXECUTING CleanCriteria AFTER: TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null}) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed, OUTPUT_COLS=null, UNRELATED_SORT=false}) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], OUTPUT_COLS=null}) Group(groups=[w, anon_grp2], props={GROUP_COLS=[w.Patientid, w.ValueTime], SYMBOL_MAP={anon_grp2.gcol0=w.Patientid, anon_grp2.gcol1=w.ValueTime, anon_grp2.agg0=MAX(w.Bed), anon_grp2.agg1=MAX(w.Naam), anon_grp2.agg2=MAX(w.opname), anon_grp2.agg3=COUNT(w.MondZorgAantal), anon_grp2.agg4=COUNT(w.EvaluatieMond)}, OUTPUT_COLS=null}) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}, OUTPUT_COLS=null}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=null}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=null}) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID], OUTPUT_COLS=null}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=(pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi], props={OUTPUT_COLS=null}) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID], OUTPUT_COLS=null}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}, OUTPUT_COLS=null}) Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID], OUTPUT_COLS=null}) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi__1.PatientStatusID, anon_grp0.agg0=COUNT(*)}, OUTPUT_COLS=null}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID], OUTPUT_COLS=null}) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=(obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={OUTPUT_COLS=null}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=(pi__1.PatientStatusID = 1) AND (convert(pi__1.AdmTime, date) <> convert(now(), date)) AND (pi__1.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={OUTPUT_COLS=null}) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID], OUTPUT_COLS=null}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}, OUTPUT_COLS=null}) Project(groups=[anon_grp1], props={PROJECT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID], OUTPUT_COLS=null}) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={GROUP_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1__1.PatientID, anon_grp1.gcol1=obs1__1.ValueTime, anon_grp1.gcol2=pi__2.PatientStatusID, anon_grp1.agg0=COUNT(*)}, OUTPUT_COLS=null}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID], OUTPUT_COLS=null}) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=(obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={OUTPUT_COLS=null}) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=(pi__2.PatientStatusID = 1) AND (convert(pi__2.AdmTime, date) < convert(now(), date)) AND (pi__2.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={OUTPUT_COLS=null}) ============================================================================ EXECUTING PlanUnions AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Access(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Access(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Access(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING RaiseAccess LOW [Relational Planner] formattimestamp function not supported by source izisprod - formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') was not pushed AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], OUTPUT_COLS=null}) Access(groups=[w, anon_grp2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Group(groups=[w, anon_grp2], props={GROUP_COLS=[w.Patientid, w.ValueTime], SYMBOL_MAP={anon_grp2.gcol0=w.Patientid, anon_grp2.gcol1=w.ValueTime, anon_grp2.agg0=MAX(w.Bed), anon_grp2.agg1=MAX(w.Naam), anon_grp2.agg2=MAX(w.opname), anon_grp2.agg3=COUNT(w.MondZorgAantal), anon_grp2.agg4=COUNT(w.EvaluatieMond)}, OUTPUT_COLS=null}) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}, OUTPUT_COLS=null, INLINE_VIEW=true}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=null}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=null}) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID], OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=(pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID], OUTPUT_COLS=null}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}, OUTPUT_COLS=null, INLINE_VIEW=true}) Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID], OUTPUT_COLS=null}) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi__1.PatientStatusID, anon_grp0.agg0=COUNT(*)}, OUTPUT_COLS=null}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=(obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=(pi__1.PatientStatusID = 1) AND (convert(pi__1.AdmTime, date) <> convert(now(), date)) AND (pi__1.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID], OUTPUT_COLS=null}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}, OUTPUT_COLS=null, INLINE_VIEW=true}) Project(groups=[anon_grp1], props={PROJECT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID], OUTPUT_COLS=null}) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={GROUP_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1__1.PatientID, anon_grp1.gcol1=obs1__1.ValueTime, anon_grp1.gcol2=pi__2.PatientStatusID, anon_grp1.agg0=COUNT(*)}, OUTPUT_COLS=null}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=(obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=(pi__2.PatientStatusID = 1) AND (convert(pi__2.AdmTime, date) < convert(now(), date)) AND (pi__2.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING CopyCriteria AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=(pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, IS_COPIED=true}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=(obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=(pi__1.PatientStatusID = 1) AND (convert(pi__1.AdmTime, date) <> convert(now(), date)) AND (pi__1.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, IS_COPIED=true}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=(obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=(pi__2.PatientStatusID = 1) AND (convert(pi__2.AdmTime, date) < convert(now(), date)) AND (pi__2.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING CleanCriteria AFTER: TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null}) Sort(groups=[anon_grp2], props={SORT_ORDER=ORDER BY Bed, OUTPUT_COLS=null, UNRELATED_SORT=false}) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], OUTPUT_COLS=null}) Access(groups=[w, anon_grp2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Group(groups=[w, anon_grp2], props={GROUP_COLS=[w.Patientid, w.ValueTime], SYMBOL_MAP={anon_grp2.gcol0=w.Patientid, anon_grp2.gcol1=w.ValueTime, anon_grp2.agg0=MAX(w.Bed), anon_grp2.agg1=MAX(w.Naam), anon_grp2.agg2=MAX(w.opname), anon_grp2.agg3=COUNT(w.MondZorgAantal), anon_grp2.agg4=COUNT(w.EvaluatieMond)}, OUTPUT_COLS=null}) Source(groups=[w], props={NESTED_COMMAND=SELECT pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID FROM izisprod.prod_PV_PatientInfo AS pi WHERE (pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)) UNION SELECT v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v UNION SELECT v__1.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v.PatientStatusID FROM (SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID) AS v, SYMBOL_MAP={w.Patientid=pi.PatientID, w.ValueTime=null, w.Bed=pi.BedAbbr, w.Naam=concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)), w.opname=pi.AdmTime, w.MondZorgAantal=null, w.EvaluatieMond=null, w.PatientStatusID=pi.PatientStatusID}, OUTPUT_COLS=null, INLINE_VIEW=true}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=null}) SetOperation(groups=[], props={SET_OPERATION=UNION, USE_ALL=false, OUTPUT_COLS=null}) Project(groups=[izisprod.prod_PV_PatientInfo AS pi], props={PROJECT_COLS=[pi.PatientID AS Patientid, null AS ValueTime, pi.BedAbbr AS Bed, concat(pi.PatientLastName, concat(', ', pi.PatientFirstName)) AS Naam, pi.AdmTime AS opname, null AS MondZorgAantal, null AS EvaluatieMond, pi.PatientStatusID], OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=(pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi], props={OUTPUT_COLS=null}) Project(groups=[v], props={PROJECT_COLS=[v.PatientID AS Patientid, v.ValueTime, null AS Bed, null AS Naam, null AS opname, v.c AS MondZorgAantal, null AS EvaluatieMond, v.PatientStatusID], OUTPUT_COLS=null}) Source(groups=[v], props={NESTED_COMMAND=SELECT anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID FROM izisprod.prod_PV_DerivedVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__1.PatientID = obs1.PatientID AND pi__1.PatientStatusID = 1 AND convert(pi__1.AdmTime, date) <> convert(now(), date) AND pi__1.PatGroupAbbr LIKE 'PICU%' WHERE (obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v.PatientID=anon_grp0.gcol0, v.ValueTime=anon_grp0.gcol1, v.c=anon_grp0.agg0, v.PatientStatusID=anon_grp0.gcol2}, OUTPUT_COLS=null, INLINE_VIEW=true}) Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.gcol0 AS PatientID, anon_grp0.gcol1 AS ValueTime, anon_grp0.agg0 AS c, anon_grp0.gcol2 AS PatientStatusID], OUTPUT_COLS=null}) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1], props={GROUP_COLS=[obs1.PatientID, obs1.ValueTime, pi__1.PatientStatusID], SYMBOL_MAP={anon_grp0.gcol0=obs1.PatientID, anon_grp0.gcol1=obs1.ValueTime, anon_grp0.gcol2=pi__1.PatientStatusID, anon_grp0.agg0=COUNT(*)}, OUTPUT_COLS=null}) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__1.PatientID = obs1.PatientID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, IS_COPIED=true}) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=(obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=(pi__1.PatientStatusID = 1) AND (convert(pi__1.AdmTime, date) <> convert(now(), date)) AND (pi__1.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={OUTPUT_COLS=null}) Project(groups=[v AS v__1], props={PROJECT_COLS=[v__1.PatientID AS Patientid, v__1.ValueTime, null AS Bed, null AS Naam, null AS opname, null AS MondZorgAantal, v__1.c AS EvaluatieMond, v__1.PatientStatusID], OUTPUT_COLS=null}) Source(groups=[v AS v__1], props={NESTED_COMMAND=SELECT anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID FROM izisprod.prod_PV_ObservationVariables AS obs1 INNER JOIN izisprod.prod_PV_PatientInfo AS pi ON pi__2.PatientID = obs1__1.PatientID AND pi__2.PatientStatusID = 1 AND convert(pi__2.AdmTime, date) < convert(now(), date) AND pi__2.PatGroupAbbr LIKE 'PICU%' WHERE (obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)) GROUP BY obs1.PatientID, obs1.ValueTime, pi.PatientStatusID, SYMBOL_MAP={v__1.PatientID=anon_grp1.gcol0, v__1.ValueTime=anon_grp1.gcol1, v__1.c=anon_grp1.agg0, v__1.PatientStatusID=anon_grp1.gcol2}, OUTPUT_COLS=null, INLINE_VIEW=true}) Project(groups=[anon_grp1], props={PROJECT_COLS=[anon_grp1.gcol0 AS PatientID, anon_grp1.gcol1 AS ValueTime, anon_grp1.agg0 AS c, anon_grp1.gcol2 AS PatientStatusID], OUTPUT_COLS=null}) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={GROUP_COLS=[obs1__1.PatientID, obs1__1.ValueTime, pi__2.PatientStatusID], SYMBOL_MAP={anon_grp1.gcol0=obs1__1.PatientID, anon_grp1.gcol1=obs1__1.ValueTime, anon_grp1.gcol2=pi__2.PatientStatusID, anon_grp1.agg0=COUNT(*)}, OUTPUT_COLS=null}) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[pi__2.PatientID = obs1__1.PatientID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, IS_COPIED=true}) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=(obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={OUTPUT_COLS=null}) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=(pi__2.PatientStatusID = 1) AND (convert(pi__2.AdmTime, date) < convert(now(), date)) AND (pi__2.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={OUTPUT_COLS=null}) ============================================================================ EXECUTING PlanJoins AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING PushSelectCriteria AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi], props={SELECT_CRITERIA=(pi.PatientStatusID = 1) AND (pi.PatGroupAbbr LIKE 'PICU%') AND (convert(pi.AdmTime, date) <> convert(now(), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1], props={SELECT_CRITERIA=(obs1.VariableID = 30010003) AND (convert(obs1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1], props={SELECT_CRITERIA=(pi__1.PatientStatusID = 1) AND (convert(pi__1.AdmTime, date) <> convert(now(), date)) AND (pi__1.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1], props={SELECT_CRITERIA=(obs1__1.VariableID = 15005534) AND (convert(obs1__1.ValueTime, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date)), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2], props={SELECT_CRITERIA=(pi__2.PatientStatusID = 1) AND (convert(pi__2.AdmTime, date) < convert(now(), date)) AND (pi__2.PatGroupAbbr LIKE 'PICU%'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING RaiseAccess LOW [Relational Planner] formattimestamp function not supported by source izisprod - formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') was not pushed AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2], props={PROJECT_COLS=[anon_grp2.gcol0 AS Patientid, formattimestamp(anon_grp2.gcol1, 'yyyy-MM-dd HH:mm') AS t2, anon_grp2.gcol1 AS Tijdstip, anon_grp2.agg0 AS Bed, anon_grp2.agg1 AS Naam, CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END AS Opname, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END AS Comfort, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END AS NRS], OUTPUT_COLS=null}) Access(groups=[w, anon_grp2], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:095c2821-e16b-4f9d-9c78-0d2d20018d04, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING ChooseJoinStrategy AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING ChooseDependent AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING PushAggregates AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING ChooseDependent AFTER: TupleLimit(groups=[]) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING PushLimit AFTER: TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null}) Sort(groups=[anon_grp2]) Project(groups=[anon_grp2]) Access(groups=[w, anon_grp2]) Group(groups=[w, anon_grp2]) Source(groups=[w]) SetOperation(groups=[]) SetOperation(groups=[]) Project(groups=[izisprod.prod_PV_PatientInfo AS pi]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi]) Project(groups=[v]) Source(groups=[v]) Project(groups=[anon_grp0]) Group(groups=[izisprod.prod_PV_DerivedVariables AS obs1, anon_grp0, izisprod.prod_PV_PatientInfo AS pi__1]) Join(groups=[izisprod.prod_PV_DerivedVariables AS obs1, izisprod.prod_PV_PatientInfo AS pi__1]) Select(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Source(groups=[izisprod.prod_PV_DerivedVariables AS obs1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__1]) Project(groups=[v AS v__1]) Source(groups=[v AS v__1]) Project(groups=[anon_grp1]) Group(groups=[anon_grp1, izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Join(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1, izisprod.prod_PV_PatientInfo AS pi__2]) Select(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Source(groups=[izisprod.prod_PV_ObservationVariables AS obs1__1]) Select(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) Source(groups=[izisprod.prod_PV_PatientInfo AS pi__2]) ============================================================================ EXECUTING AssignOutputElements > "TEIID30259 Cannot introduce new expressions" when using formattimestamp > ------------------------------------------------------------------------ > > Key: TEIID-4259 > URL: https://issues.jboss.org/browse/TEIID-4259 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.11.3 > Reporter: Bram Gadeyne > Assignee: Steven Hawkins > > A certain query executes fine when using the normal timestamp column (w.valuetime). When this is changed to formattimestamp(w.valuetime, 'dd/MM/yyyy HH:mm') we get an exception complaining about a case statement that is part of the select a few columns after the particular valuetime column. > 14:00:00,055 WARN [org.teiid.PROCESSOR] (Worker17615_QueryProcessorQueue95374) 7OLMH/JCvk3W TEIID30020 Processing exception for request 7OLMH/JCvk3W.48 'TEIID30259 Cannot introduce new expressions [CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END] in duplicate removal.'. Originally QueryPlannerException RuleAssignOutputElements.java:407. Enable more detailed logging to see the entire stacktrace. > This was the query that caused the problem: > select w.patientid as Patientid, > formattimestamp(w.valuetime,'yyyy-MM-dd HH:mm') as t2, > w.valuetime as Tijdstip, > max(w.bed) as Bed, max(w.Naam) as Naam, > case when convert(max(w.opname), date) = timestampadd(SQL_TSI_DAY, -1, convert(now() , date)) then 'Gisteren' > else > case when convert(max(w.opname), date) = convert(now(), date) then 'Vannacht' else > '' end end as Opname, > case when count(w.MondZorgAantal) is null then 'Niet gemeten' else 'OK' end as Comfort, > case when count(w.EvaluatieMond) > 0 then 'OK' else 'Ontbreekt' end as NRS > from > ( > select pi.patientid as Patientid, null as ValueTime, pi.bedabbr as Bed, concat(pi.PatientLastName, concat(', ',pi.PatientFirstName)) as Naam, pi.AdmTime as opname, null as MondZorgAantal, null as EvaluatieMond, patientStatusID > from prod_PV_PatientInfo pi > where pi.PatientStatusID = 1 and PatGroupAbbr like 'PICU%' and cast(pi.admTime as date) <> cast(now() as date) > union > select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, > v.c as MondZorgAantal, null as EvaluatieMond, v.PatientStatusID from > (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID > from prod_PV_DerivedVariables obs1 > join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) <> cast(now() as date) and PatGroupAbbr like 'PICU%' > where obs1.variableid = 30010003 > and cast(valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date)) > group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v > union > select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, null as MondZorgAantal, v.c as EvaluatieMond, v.PatientStatusID from > (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID > from prod_PV_ObservationVariables obs1 > join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) < cast(now() as date) and PatGroupAbbr like 'PICU%' > where obs1.variableid = 15005534 > and cast(obs1.valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date)) > group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v > ) w > group by w.patientid, w.ValueTime > order by max(w.bed) -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4090) Issue with entitysets/properties ending in auth or token

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4090?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-4090: ------------------------------------- Ok, I remember now why we need the static servlet. The $metadata can contain Annotation document links, in order to serve those separate documents I added this one. One example of such is invalid char replacement for XML payload stuff, there are other core annotation documents. We can not get rid of it, any suggestions to narrow the scope of this? > Issue with entitysets/properties ending in auth or token > -------------------------------------------------------- > > Key: TEIID-4090 > URL: https://issues.jboss.org/browse/TEIID-4090 > Project: Teiid > Issue Type: Bug > Components: OData > Affects Versions: 8.12 > Reporter: Steven Hawkins > Assignee: Ramesh Reddy > Fix For: 9.0, 8.12.5 > > > To handle oauth, we are check for uri.endsWith("auth") || uri.endsWith("token") - however valid uris can end with those characters as well. An ioexception will be thrown instead of seeing the expected results - we also need to not simply throw an ioexception in ODataServlet as there is no server log of the exception and the client sees a 500 error. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4259) "TEIID30259 Cannot introduce new expressions" when using formattimestamp

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4259?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4259: --------------------------------------- Can you also provide the plan debug log - in trying to recreate this with a simplified query I'm not hitting the same condition. And do you know if it occurs on later releases? > "TEIID30259 Cannot introduce new expressions" when using formattimestamp > ------------------------------------------------------------------------ > > Key: TEIID-4259 > URL: https://issues.jboss.org/browse/TEIID-4259 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.11.3 > Reporter: Bram Gadeyne > Assignee: Steven Hawkins > > A certain query executes fine when using the normal timestamp column (w.valuetime). When this is changed to formattimestamp(w.valuetime, 'dd/MM/yyyy HH:mm') we get an exception complaining about a case statement that is part of the select a few columns after the particular valuetime column. > 14:00:00,055 WARN [org.teiid.PROCESSOR] (Worker17615_QueryProcessorQueue95374) 7OLMH/JCvk3W TEIID30020 Processing exception for request 7OLMH/JCvk3W.48 'TEIID30259 Cannot introduce new expressions [CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END] in duplicate removal.'. Originally QueryPlannerException RuleAssignOutputElements.java:407. Enable more detailed logging to see the entire stacktrace. > This was the query that caused the problem: > select w.patientid as Patientid, > formattimestamp(w.valuetime,'yyyy-MM-dd HH:mm') as t2, > w.valuetime as Tijdstip, > max(w.bed) as Bed, max(w.Naam) as Naam, > case when convert(max(w.opname), date) = timestampadd(SQL_TSI_DAY, -1, convert(now() , date)) then 'Gisteren' > else > case when convert(max(w.opname), date) = convert(now(), date) then 'Vannacht' else > '' end end as Opname, > case when count(w.MondZorgAantal) is null then 'Niet gemeten' else 'OK' end as Comfort, > case when count(w.EvaluatieMond) > 0 then 'OK' else 'Ontbreekt' end as NRS > from > ( > select pi.patientid as Patientid, null as ValueTime, pi.bedabbr as Bed, concat(pi.PatientLastName, concat(', ',pi.PatientFirstName)) as Naam, pi.AdmTime as opname, null as MondZorgAantal, null as EvaluatieMond, patientStatusID > from prod_PV_PatientInfo pi > where pi.PatientStatusID = 1 and PatGroupAbbr like 'PICU%' and cast(pi.admTime as date) <> cast(now() as date) > union > select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, > v.c as MondZorgAantal, null as EvaluatieMond, v.PatientStatusID from > (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID > from prod_PV_DerivedVariables obs1 > join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) <> cast(now() as date) and PatGroupAbbr like 'PICU%' > where obs1.variableid = 30010003 > and cast(valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date)) > group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v > union > select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, null as MondZorgAantal, v.c as EvaluatieMond, v.PatientStatusID from > (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID > from prod_PV_ObservationVariables obs1 > join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) < cast(now() as date) and PatGroupAbbr like 'PICU%' > where obs1.variableid = 15005534 > and cast(obs1.valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date)) > group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v > ) w > group by w.patientid, w.ValueTime > order by max(w.bed) -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4263) StringToTimestamp in convert fails during DST transition hour

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4263?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4263. ----------------------------------- Resolution: Done Added a secondary check to see if the string is strictly valid in a non-dst timezone to allow for transition values. > StringToTimestamp in convert fails during DST transition hour > ------------------------------------------------------------- > > Key: TEIID-4263 > URL: https://issues.jboss.org/browse/TEIID-4263 > Project: Teiid > Issue Type: Bug > Components: Common > Affects Versions: 8.7.7.6_2 > Reporter: Guy Davis > Assignee: Steven Hawkins > Fix For: 9.1 > > Attachments: ArgentinaTest.java > > > Issue is due to [https://github.com/teiid/teiid/blob/master/common-core/src/main/java/org/... | this line] in StringToTimestampTransform.java. In particular, if you pass in a string date for a timestamp at the beginning of the DST transition hour, the result will be an hour in the future or past, so this string comparison fails, throwing TEIID10060 exception. > Examples include: > * America/Edmonton 2016-03-13 02:00:00 > * America/Argentina/Buenos_Aires 1991-10-20 00:00:00 -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4259) "TEIID30259 Cannot introduce new expressions" when using formattimestamp

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4259?page=com.atlassian.jira.plugin... ] Work on TEIID-4259 started by Steven Hawkins. --------------------------------------------- > "TEIID30259 Cannot introduce new expressions" when using formattimestamp > ------------------------------------------------------------------------ > > Key: TEIID-4259 > URL: https://issues.jboss.org/browse/TEIID-4259 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.11.3 > Reporter: Bram Gadeyne > Assignee: Steven Hawkins > > A certain query executes fine when using the normal timestamp column (w.valuetime). When this is changed to formattimestamp(w.valuetime, 'dd/MM/yyyy HH:mm') we get an exception complaining about a case statement that is part of the select a few columns after the particular valuetime column. > 14:00:00,055 WARN [org.teiid.PROCESSOR] (Worker17615_QueryProcessorQueue95374) 7OLMH/JCvk3W TEIID30020 Processing exception for request 7OLMH/JCvk3W.48 'TEIID30259 Cannot introduce new expressions [CASE WHEN convert(anon_grp2.agg2, date) = convert(timestampadd(SQL_TSI_DAY, -1, convert(convert(now(), date), timestamp)), date) THEN 'Gisteren' ELSE CASE WHEN convert(anon_grp2.agg2, date) = convert(now(), date) THEN 'Vannacht' ELSE '' END END, CASE WHEN anon_grp2.agg3 IS NULL THEN 'Niet gemeten' ELSE 'OK' END, CASE WHEN anon_grp2.agg4 > 0 THEN 'OK' ELSE 'Ontbreekt' END] in duplicate removal.'. Originally QueryPlannerException RuleAssignOutputElements.java:407. Enable more detailed logging to see the entire stacktrace. > This was the query that caused the problem: > select w.patientid as Patientid, > formattimestamp(w.valuetime,'yyyy-MM-dd HH:mm') as t2, > w.valuetime as Tijdstip, > max(w.bed) as Bed, max(w.Naam) as Naam, > case when convert(max(w.opname), date) = timestampadd(SQL_TSI_DAY, -1, convert(now() , date)) then 'Gisteren' > else > case when convert(max(w.opname), date) = convert(now(), date) then 'Vannacht' else > '' end end as Opname, > case when count(w.MondZorgAantal) is null then 'Niet gemeten' else 'OK' end as Comfort, > case when count(w.EvaluatieMond) > 0 then 'OK' else 'Ontbreekt' end as NRS > from > ( > select pi.patientid as Patientid, null as ValueTime, pi.bedabbr as Bed, concat(pi.PatientLastName, concat(', ',pi.PatientFirstName)) as Naam, pi.AdmTime as opname, null as MondZorgAantal, null as EvaluatieMond, patientStatusID > from prod_PV_PatientInfo pi > where pi.PatientStatusID = 1 and PatGroupAbbr like 'PICU%' and cast(pi.admTime as date) <> cast(now() as date) > union > select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, > v.c as MondZorgAantal, null as EvaluatieMond, v.PatientStatusID from > (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID > from prod_PV_DerivedVariables obs1 > join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) <> cast(now() as date) and PatGroupAbbr like 'PICU%' > where obs1.variableid = 30010003 > and cast(valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date)) > group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v > union > select v.PatientID as Patientid, v.ValueTime, null as Bed, null as Naam, null as opname, null as MondZorgAantal, v.c as EvaluatieMond, v.PatientStatusID from > (select obs1.PatientID, obs1.ValueTime, count(*) as c , pi.PatientStatusID > from prod_PV_ObservationVariables obs1 > join prod_PV_PatientInfo pi on pi.PatientID = obs1.PatientID and pi.PatientStatusID = 1 and cast(pi.admTime as date) < cast(now() as date) and PatGroupAbbr like 'PICU%' > where obs1.variableid = 15005534 > and cast(obs1.valuetime as date) = timestampadd(SQL_TSI_DAY, -1, cast(now() as date)) > group by obs1.patientid, obs1.ValueTime, timestampadd(SQL_TSI_DAY, -1, cast(now() as date)), pi.PatientStatusID)v > ) w > group by w.patientid, w.ValueTime > order by max(w.bed) -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4090) Issue with entitysets/properties ending in auth or token

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4090?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-4090: ------------------------------------- Yes, I looked through the commit logs, did not find any. It best to take down for now. > Issue with entitysets/properties ending in auth or token > -------------------------------------------------------- > > Key: TEIID-4090 > URL: https://issues.jboss.org/browse/TEIID-4090 > Project: Teiid > Issue Type: Bug > Components: OData > Affects Versions: 8.12 > Reporter: Steven Hawkins > Assignee: Ramesh Reddy > Fix For: 9.0, 8.12.5 > > > To handle oauth, we are check for uri.endsWith("auth") || uri.endsWith("token") - however valid uris can end with those characters as well. An ioexception will be thrown instead of seeing the expected results - we also need to not simply throw an ioexception in ODataServlet as there is no server log of the exception and the client sees a 500 error. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4090) Issue with entitysets/properties ending in auth or token

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4090?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4090: --------------------------------------- > I can not seem to remember the reason I added the "static" content servlet? I can't find anything related to the commit comment - https://issues.jboss.org/issues/?jql=issuekey%20in%20(TEIID-4090%2C%20%20... It seems like it needs to be more secure - allowing any resource to be read from the classpath is a security issue. Did you add it for debugging or perhaps for other security work? > Issue with entitysets/properties ending in auth or token > -------------------------------------------------------- > > Key: TEIID-4090 > URL: https://issues.jboss.org/browse/TEIID-4090 > Project: Teiid > Issue Type: Bug > Components: OData > Affects Versions: 8.12 > Reporter: Steven Hawkins > Assignee: Ramesh Reddy > Fix For: 9.0, 8.12.5 > > > To handle oauth, we are check for uri.endsWith("auth") || uri.endsWith("token") - however valid uris can end with those characters as well. An ioexception will be thrown instead of seeing the expected results - we also need to not simply throw an ioexception in ODataServlet as there is no server log of the exception and the client sees a 500 error. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4262) Wrong result (empty or NULL) with an view and a single disjunction for join criteria

by Johnathon Lee (JIRA)

[ https://issues.jboss.org/browse/TEIID-4262?page=com.atlassian.jira.plugin... ] Johnathon Lee updated TEIID-4262: --------------------------------- Fix Version/s: 8.7.7.6_2 > Wrong result (empty or NULL) with an view and a single disjunction for join criteria > ------------------------------------------------------------------------------------ > > Key: TEIID-4262 > URL: https://issues.jboss.org/browse/TEIID-4262 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 7.0 > Reporter: Salvatore R > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 9.0, 8.12.5, 8.7.7.6_2, 8.13.6 > > > The following query returns an empty result instead of 1: > {code:sql} > SELECT alias3.a1 FROM (select 1 as a) as alias2 INNER JOIN (SELECT t2.a AS a1, t1.a FROM (SELECT 1 AS a) AS t1 INNER JOIN (select 1 as a) as t2 ON t1.a = t2.a) AS alias3 ON ((alias3.a = alias2.a) OR (alias3.a > alias2.a)) > {code} > when updating the parent join on removing the alias3 view layer, the OR condition is being converted to AND - which can never be true. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4262) Wrong result (empty or NULL) with an view and a single disjunction for join criteria

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4262?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4262. ----------------------------------- Resolution: Done Added a check of the compound criteria to make sure it's an AND. > Wrong result (empty or NULL) with an view and a single disjunction for join criteria > ------------------------------------------------------------------------------------ > > Key: TEIID-4262 > URL: https://issues.jboss.org/browse/TEIID-4262 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 7.0 > Reporter: Salvatore R > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 9.0, 8.12.5, 8.13.6 > > > The following query returns an empty result instead of 1: > {code:sql} > SELECT alias3.a1 FROM (select 1 as a) as alias2 INNER JOIN (SELECT t2.a AS a1, t1.a FROM (SELECT 1 AS a) AS t1 INNER JOIN (select 1 as a) as t2 ON t1.a = t2.a) AS alias3 ON ((alias3.a = alias2.a) OR (alias3.a > alias2.a)) > {code} > when updating the parent join on removing the alias3 view layer, the OR condition is being converted to AND - which can never be true. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4262) Wrong result (empty or NULL) with an view and a single disjunction for join criteria

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4262?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-4262: ------------------------------------------- Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1344462, https://bugzilla.redhat.com/show_bug.cgi?id=1344475 (was: https://bugzilla.redhat.com/show_bug.cgi?id=1344462) > Wrong result (empty or NULL) with an view and a single disjunction for join criteria > ------------------------------------------------------------------------------------ > > Key: TEIID-4262 > URL: https://issues.jboss.org/browse/TEIID-4262 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 7.0 > Reporter: Salvatore R > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 9.0, 8.12.5, 8.13.6 > > > The following query returns an empty result instead of 1: > {code:sql} > SELECT alias3.a1 FROM (select 1 as a) as alias2 INNER JOIN (SELECT t2.a AS a1, t1.a FROM (SELECT 1 AS a) AS t1 INNER JOIN (select 1 as a) as t2 ON t1.a = t2.a) AS alias3 ON ((alias3.a = alias2.a) OR (alias3.a > alias2.a)) > {code} > when updating the parent join on removing the alias3 view layer, the OR condition is being converted to AND - which can never be true. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues June 2016