============================================================================ USER COMMAND: INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X ---------------------------------------------------------------------------- OPTIMIZE: INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X ---------------------------------------------------------------------------- GENERATE CANONICAL: INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X CANONICAL PLAN: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables}) Source(groups=[tmptables.rep149_variables], props={ATOMIC_REQUEST=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X, VIRTUAL_COMMAND=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X}) Project(groups=[X], props={PROJECT_COLS=[X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue]}) Source(groups=[X], props={NESTED_COMMAND=SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1, SYMBOL_MAP={X.PatientID=v.PatientID, X.Code=v.Code, X.EnterTime=v.EnterTime, X.varvalue=1}}) Project(groups=[v], props={PROJECT_COLS=[v.PatientID, v.Code, v.EnterTime, 1 AS varvalue]}) Select(groups=[v], props={SELECT_CRITERIA=v.rang = 1}) Source(groups=[v], props={NESTED_COMMAND=SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33), SYMBOL_MAP={v.Code=cr.Code, v.PatientID=ci.PatientID, v.EnterTime=ci.EnterTime, v.rang=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime)}}) Project(groups=[izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={PROJECT_COLS=[cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang], HAS_WINDOW_FUNCTIONS=true}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33)}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID, ci.ItemStatus = 0]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID, cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID]}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Source(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PlaceAccess AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables}) Source(groups=[tmptables.rep149_variables], props={ATOMIC_REQUEST=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X, VIRTUAL_COMMAND=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1) AS X}) Project(groups=[X], props={PROJECT_COLS=[X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue]}) Source(groups=[X], props={NESTED_COMMAND=SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1, SYMBOL_MAP={X.PatientID=v.PatientID, X.Code=v.Code, X.EnterTime=v.EnterTime, X.varvalue=1}}) Project(groups=[v], props={PROJECT_COLS=[v.PatientID, v.Code, v.EnterTime, 1 AS varvalue]}) Select(groups=[v], props={SELECT_CRITERIA=v.rang = 1}) Source(groups=[v], props={NESTED_COMMAND=SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33), SYMBOL_MAP={v.Code=cr.Code, v.PatientID=ci.PatientID, v.EnterTime=ci.EnterTime, v.rang=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime)}}) Project(groups=[izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={PROJECT_COLS=[cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang], HAS_WINDOW_FUNCTIONS=true}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33)}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID, ci.ItemStatus = 0]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID, cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID]}) Access(groups=[izisprod.S_CodeSystemRef AS csr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Access(groups=[izisprod.S_CodeGroupRef AS cgr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000}) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000}) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000}) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING AssignOutputElements AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=[Count]}) Source(groups=[tmptables.rep149_variables]) Project(groups=[X], props={PROJECT_COLS=[X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue], OUTPUT_COLS=[X.PatientID AS patientid, X.Code AS variableid, X.EnterTime AS scoretime, X.varvalue AS varvalue]}) Source(groups=[X], props={NESTED_COMMAND=SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1, SYMBOL_MAP={X.PatientID=v.PatientID, X.Code=v.Code, X.EnterTime=v.EnterTime, X.varvalue=1}, OUTPUT_COLS=[X.PatientID, X.Code, X.EnterTime, X.varvalue]}) Project(groups=[v], props={PROJECT_COLS=[v.PatientID, v.Code, v.EnterTime, 1 AS varvalue], OUTPUT_COLS=[v.PatientID, v.Code, v.EnterTime, 1 AS varvalue]}) Select(groups=[v], props={SELECT_CRITERIA=v.rang = 1, OUTPUT_COLS=[v.PatientID, v.Code, v.EnterTime]}) Source(groups=[v], props={NESTED_COMMAND=SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33), SYMBOL_MAP={v.rang=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime), v.PatientID=ci.PatientID, v.Code=cr.Code, v.EnterTime=ci.EnterTime}, OUTPUT_COLS=[v.rang, v.PatientID, v.Code, v.EnterTime]}) Project(groups=[izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={PROJECT_COLS=[ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang, ci.PatientID, cr.Code, ci.EnterTime], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=[ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang, ci.PatientID, cr.Code, ci.EnterTime]}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=[csr.CodeSysID, ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID, ci.ItemStatus = 0], OUTPUT_COLS=[ci.PatientID, csr.CodeSysID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID, cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')], OUTPUT_COLS=[cr.CodeID, csr.CodeSysID, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, csr.CodeSysID]}) Access(groups=[izisprod.S_CodeSystemRef AS csr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[csr.CodeSysID]}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=[csr.CodeSysID]}) Access(groups=[izisprod.S_CodeGroupRef AS cgr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cgr.CodeSysID, cgr.GroupID]}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=[cgr.CodeSysID, cgr.GroupID]}) Access(groups=[izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.GroupID, cr.Code, cr.CodeID]}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=[cr.GroupID, cr.Code, cr.CodeID]}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.CodeID, ci.ItemStatus, ci.PatientID, ci.EnterTime]}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=[ci.CodeID, ci.ItemStatus, ci.PatientID, ci.EnterTime]}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=[c.PatientID]}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=[c.PatientID]}) ============================================================================ EXECUTING RemoveOptionalJoins AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=[Count]}) Source(groups=[tmptables.rep149_variables]) Project(groups=[X]) Source(groups=[X], props={NESTED_COMMAND=SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE v.rang = 1, SYMBOL_MAP={X.PatientID=v.PatientID, X.Code=v.Code, X.EnterTime=v.EnterTime, X.varvalue=1}, OUTPUT_COLS=[X.PatientID, X.Code, X.EnterTime, X.varvalue]}) Project(groups=[v]) Select(groups=[v], props={SELECT_CRITERIA=v.rang = 1, OUTPUT_COLS=[v.PatientID, v.Code, v.EnterTime]}) Source(groups=[v]) Project(groups=[izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci]) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=[csr.CodeSysID, ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID, ci.ItemStatus = 0], OUTPUT_COLS=[ci.PatientID, csr.CodeSysID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID, cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')], OUTPUT_COLS=[cr.CodeID, csr.CodeSysID, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, csr.CodeSysID]}) Access(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Access(groups=[izisprod.S_CodeGroupRef AS cgr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PushSelectCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[X]) Source(groups=[X]) Project(groups=[v]) Select(groups=[v], props={SELECT_CRITERIA=v.rang = 1, OUTPUT_COLS=[v.PatientID, v.Code, v.EnterTime]}) Source(groups=[v], props={NESTED_COMMAND=SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID AND cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID AND ci.ItemStatus = 0) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33), SYMBOL_MAP={v.rang=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime), v.PatientID=ci.PatientID, v.Code=cr.Code, v.EnterTime=ci.EnterTime}, OUTPUT_COLS=[v.rang, v.PatientID, v.Code, v.EnterTime]}) Project(groups=[izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={PROJECT_COLS=[ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang, ci.PatientID, cr.Code, ci.EnterTime], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=[ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang, ci.PatientID, cr.Code, ci.EnterTime]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=[csr.CodeSysID, ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID, ci.ItemStatus = 0], OUTPUT_COLS=[ci.PatientID, csr.CodeSysID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID, cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')], OUTPUT_COLS=[cr.CodeID, csr.CodeSysID, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, csr.CodeSysID]}) Access(groups=[izisprod.S_CodeSystemRef AS csr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[csr.CodeSysID]}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=[csr.CodeSysID]}) Access(groups=[izisprod.S_CodeGroupRef AS cgr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING MergeVirtual AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=[Count]}) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=[v.PatientID, v.Code, v.EnterTime]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=[csr.CodeSysID, ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeSystemRef AS csr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Access(groups=[izisprod.S_CodeGroupRef AS cgr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PushNonJoinCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=[csr.CodeSysID, ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=[ci.PatientID, csr.CodeSysID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=[cr.CodeID, csr.CodeSysID, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, csr.CodeSysID]}) Access(groups=[izisprod.S_CodeSystemRef AS csr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Access(groups=[izisprod.S_CodeGroupRef AS cgr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')}) Access(groups=[izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.GroupID, cr.Code, cr.CodeID]}) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.CodeID, ci.ItemStatus, ci.PatientID, ci.EnterTime]}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PushSelectCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=[csr.CodeSysID, ci.PatientID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=[ci.PatientID, csr.CodeSysID, ci.CodeID, ci.EnterTime, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=[cr.CodeID, csr.CodeSysID, cr.Code]}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeSystemRef AS csr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Access(groups=[izisprod.S_CodeGroupRef AS cgr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Access(groups=[izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.GroupID, cr.Code, cr.CodeID]}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=[cr.GroupID, cr.Code, cr.CodeID]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=[v.PatientID, v.Code, v.EnterTime]}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.CodeID, ci.ItemStatus, ci.PatientID, ci.EnterTime]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=[ci.CodeID, ci.ItemStatus, ci.PatientID, ci.EnterTime]}) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING CleanCriteria AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_variables], props={ATOMIC_REQUEST=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, VIRTUAL_COMMAND=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, OUTPUT_COLS=null}) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null}) Access(groups=[izisprod.S_CodeSystemRef AS csr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=null}) Access(groups=[izisprod.S_CodeGroupRef AS cgr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=null}) Access(groups=[izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=null}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=null}) ============================================================================ EXECUTING RaiseAccess LOW [Relational Planner] Aggregate function pushdown not supported by source iziswh - ROW_NUMBER() was not pushed AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=null}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null}) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PlanOuterJoins AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING CopyCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID, cgr.CodeSysID IN (16, 33)], OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PushNonJoinCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null, IS_COPIED=true}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33)}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PushSelectCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33)}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING RaiseAccess LOW [Relational Planner] Aggregate function pushdown not supported by source iziswh - ROW_NUMBER() was not pushed AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING CopyCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING CleanCriteria AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_variables], props={ATOMIC_REQUEST=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, VIRTUAL_COMMAND=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, OUTPUT_COLS=null}) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID], OUTPUT_COLS=null, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, iziswh.P_CodedItem AS ci], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=null}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=null}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=null}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=null}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=null}) ============================================================================ EXECUTING PlanJoins AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=ci.CodeID = cr.CodeID, EST_SELECTIVITY=0.34674764}) Select(groups=[tmptables.rep149_cohort AS c, iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=c.PatientID = ci.PatientID, EST_SELECTIVITY=0.33693445}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=null, EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=null, EST_COL_STATS={csr.ArchStatus=[5.0, 10.0, -1.0], csr.ArchTime=[5.0, 10.0, -1.0], csr.Code=[5.0, 10.0, -1.0], csr.CodeSysID=[5.0, 10.0, -1.0], ...}, EST_CARDINALITY=20.0}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=null, EST_COL_STATS={cgr.ArchStatus=[13.0, 75.0, -1.0], cgr.ArchTime=[13.0, 75.0, -1.0], cgr.Code=[13.0, 75.0, -1.0], cgr.CodeSysID=[13.0, 75.0, -1.0], ...}, EST_CARDINALITY=150.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=null, EST_COL_STATS={cr.ArchStatus=[149.0, 11000.0, -1.0], cr.ArchTime=[149.0, 11000.0, -1.0], cr.Code=[149.0, 11000.0, -1.0], cr.CodeID=[149.0, 11000.0, -1.0], ...}, EST_CARDINALITY=22000.0}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=11138.0, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=null, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING PushSelectCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=null}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID]}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING RaiseAccess LOW [Relational Planner] Aggregate function pushdown not supported by source iziswh - ROW_NUMBER() was not pushed AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID]}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=11138.0, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}}) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PlanOuterJoins AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[c.PatientID = ci.PatientID]}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[ci.CodeID = cr.CodeID]}) Select(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING ChooseJoinStrategy AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[]}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[]}) Select(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PushLargeIn AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING ChooseDependent AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=null, DEPENDENT_VALUE_SOURCE=$dsc/id4}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=11365.821, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}, DEPENDENT_VALUE_SOURCE=$dsc/id5}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=null, EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.CodeID IN (), IS_DEPENDENT_SET=true}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=null, EST_COL_STATS={csr.ArchStatus=[5.0, 10.0, -1.0], csr.ArchTime=[5.0, 10.0, -1.0], csr.Code=[5.0, 10.0, -1.0], csr.CodeSysID=[5.0, 10.0, -1.0], ...}, EST_CARDINALITY=20.0}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=null, EST_COL_STATS={cgr.ArchStatus=[13.0, 75.0, -1.0], cgr.ArchTime=[13.0, 75.0, -1.0], cgr.Code=[13.0, 75.0, -1.0], cgr.CodeSysID=[13.0, 75.0, -1.0], ...}, EST_CARDINALITY=150.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=null, EST_COL_STATS={cr.ArchStatus=[149.0, 11000.0, -1.0], cr.ArchTime=[149.0, 11000.0, -1.0], cr.Code=[149.0, 11000.0, -1.0], cr.CodeID=[149.0, 11000.0, -1.0], ...}, EST_CARDINALITY=22000.0}) Select(groups=[tmptables.rep149_cohort AS c], props={SELECT_CRITERIA=c.PatientID IN (), IS_DEPENDENT_SET=true}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=11138.0, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=null, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING PushSelectCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=null, DEPENDENT_VALUE_SOURCE=$dsc/id4}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=11365.821, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}, DEPENDENT_VALUE_SOURCE=$dsc/id5}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}, IS_DEPENDENT_SET=true}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.CodeID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=11138.0, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, IS_DEPENDENT_SET=true}) Select(groups=[tmptables.rep149_cohort AS c], props={SELECT_CRITERIA=c.PatientID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=null, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING CleanCriteria AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_variables], props={ATOMIC_REQUEST=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, VIRTUAL_COMMAND=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, OUTPUT_COLS=null}) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=null}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=null, DEPENDENT_VALUE_SOURCE=$dsc/id4, OUTPUT_COLS=null}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=11365.821, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}, DEPENDENT_VALUE_SOURCE=$dsc/id5, OUTPUT_COLS=null}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=null, EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}, IS_DEPENDENT_SET=true}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.CodeID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=null}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=null, EST_COL_STATS={csr.ArchStatus=[5.0, 10.0, -1.0], csr.ArchTime=[5.0, 10.0, -1.0], csr.Code=[5.0, 10.0, -1.0], csr.CodeSysID=[5.0, 10.0, -1.0], ...}, EST_CARDINALITY=20.0}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=null, EST_COL_STATS={cgr.ArchStatus=[13.0, 75.0, -1.0], cgr.ArchTime=[13.0, 75.0, -1.0], cgr.Code=[13.0, 75.0, -1.0], cgr.CodeSysID=[13.0, 75.0, -1.0], ...}, EST_CARDINALITY=150.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=null, EST_COL_STATS={cr.ArchStatus=[149.0, 11000.0, -1.0], cr.ArchTime=[149.0, 11000.0, -1.0], cr.Code=[149.0, 11000.0, -1.0], cr.CodeID=[149.0, 11000.0, -1.0], ...}, EST_CARDINALITY=22000.0}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=null, EST_CARDINALITY=11138.0, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, IS_DEPENDENT_SET=true}) Select(groups=[tmptables.rep149_cohort AS c], props={SELECT_CRITERIA=c.PatientID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=null}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=null, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING AssignOutputElements AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=[Count]}) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue]}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=null, DEPENDENT_VALUE_SOURCE=$dsc/id4, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime]}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=11365.821, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}, DEPENDENT_VALUE_SOURCE=$dsc/id5, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=[ci.CodeID, ci.PatientID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=[ci.ItemStatus, ci.PatientID, ci.CodeID, ci.EnterTime], EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.CodeID, cr.Code], CONFORMED_SOURCES=null, EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}, IS_DEPENDENT_SET=true}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.CodeID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=[cr.CodeID, cr.Code]}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=[cr.CodeID, cr.Code], EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=[cgr.CodeSysID, cr.CodeID, cr.Code], MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, cgr.CodeSysID], MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=[csr.CodeSysID], IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=[csr.CodeSysID], EST_COL_STATS={csr.ArchStatus=[5.0, 10.0, -1.0], csr.ArchTime=[5.0, 10.0, -1.0], csr.Code=[5.0, 10.0, -1.0], csr.CodeSysID=[5.0, 10.0, -1.0], ...}, EST_CARDINALITY=20.0}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=[cgr.CodeSysID, cgr.GroupID], EST_COL_STATS={cgr.ArchStatus=[13.0, 75.0, -1.0], cgr.ArchTime=[13.0, 75.0, -1.0], cgr.Code=[13.0, 75.0, -1.0], cgr.CodeSysID=[13.0, 75.0, -1.0], ...}, EST_CARDINALITY=150.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=[cr.GroupID, cr.CodeID, cr.Code], IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=[cr.Code, cr.GroupID, cr.CodeID], EST_COL_STATS={cr.ArchStatus=[149.0, 11000.0, -1.0], cr.ArchTime=[149.0, 11000.0, -1.0], cr.Code=[149.0, 11000.0, -1.0], cr.CodeID=[149.0, 11000.0, -1.0], ...}, EST_CARDINALITY=22000.0}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=[c.PatientID], EST_CARDINALITY=11138.0, EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, IS_DEPENDENT_SET=true}) Select(groups=[tmptables.rep149_cohort AS c], props={SELECT_CRITERIA=c.PatientID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=[c.PatientID]}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=[c.PatientID], EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING CalculateCost AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=[Count], EST_CARDINALITY=1067.542, EST_COL_STATS={Count=[-1.0, -1.0, -1.0]}}) Source(groups=[tmptables.rep149_variables], props={ATOMIC_REQUEST=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, VIRTUAL_COMMAND=INSERT INTO tmptables.rep149_variables (patientid, variableid, scoretime, varvalue) SELECT ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue FROM (SELECT v.PatientID, v.Code, v.EnterTime, 1 AS varvalue FROM (SELECT cr.Code, ci.PatientID, ci.EnterTime, ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) AS rang FROM (((izisprod.S_CodeSystemRef AS csr INNER JOIN izisprod.S_CodeGroupRef AS cgr ON cgr.CodeSysID = csr.CodeSysID) INNER JOIN izisprod.S_CodeRef AS cr ON cr.GroupID = cgr.GroupID) INNER JOIN iziswh.P_CodedItem AS ci ON ci.CodeID = cr.CodeID) INNER JOIN tmptables.rep149_cohort AS c ON c.PatientID = ci.PatientID WHERE csr.CodeSysID IN (16, 33)) AS v WHERE ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1) AS X, OUTPUT_COLS=null, EST_CARDINALITY=1067.542}) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], EST_CARDINALITY=1067.542, EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0], 1=[1.0, 1.0, 1.0]}}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1067.542, DEPENDENT_VALUE_SOURCE=$dsc/id4, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime], EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0]}}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1067.542, EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0]}, DEPENDENT_VALUE_SOURCE=$dsc/id5, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=[ci.CodeID, ci.PatientID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=[ci.ItemStatus, ci.PatientID, ci.CodeID, ci.EnterTime], EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.CodeID, cr.Code], CONFORMED_SOURCES=null, EST_CARDINALITY=738.51324, EST_COL_STATS={cr.CodeID=[27.1756, 369.25662, -1.0], cr.Code=[27.1756, 369.25662, -1.0]}, IS_DEPENDENT_SET=true}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.CodeID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=[cr.CodeID, cr.Code], EST_CARDINALITY=738.51324, EST_COL_STATS={cr.CodeID=[27.1756, 369.25662, -1.0], cr.Code=[27.1756, 369.25662, -1.0]}}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=[cr.CodeID, cr.Code], EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=[cgr.CodeSysID, cr.CodeID, cr.Code], MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, cgr.CodeSysID], MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=[csr.CodeSysID], IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=[csr.CodeSysID], EST_COL_STATS={csr.ArchStatus=[5.0, 10.0, -1.0], csr.ArchTime=[5.0, 10.0, -1.0], csr.Code=[5.0, 10.0, -1.0], csr.CodeSysID=[5.0, 10.0, -1.0], ...}, EST_CARDINALITY=20.0}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=[cgr.CodeSysID, cgr.GroupID], EST_COL_STATS={cgr.ArchStatus=[13.0, 75.0, -1.0], cgr.ArchTime=[13.0, 75.0, -1.0], cgr.Code=[13.0, 75.0, -1.0], cgr.CodeSysID=[13.0, 75.0, -1.0], ...}, EST_CARDINALITY=150.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=[cr.GroupID, cr.CodeID, cr.Code], IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=[cr.Code, cr.GroupID, cr.CodeID], EST_COL_STATS={cr.ArchStatus=[149.0, 11000.0, -1.0], cr.ArchTime=[149.0, 11000.0, -1.0], cr.Code=[149.0, 11000.0, -1.0], cr.CodeID=[149.0, 11000.0, -1.0], ...}, EST_CARDINALITY=22000.0}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=[c.PatientID], EST_CARDINALITY=5.6152434, EST_COL_STATS={c.PatientID=[5.6152434, 5.6152434, 0.0]}, IS_DEPENDENT_SET=true}) Select(groups=[tmptables.rep149_cohort AS c], props={SELECT_CRITERIA=c.PatientID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=[c.PatientID], EST_CARDINALITY=5.6152434, EST_COL_STATS={c.PatientID=[5.6152434, 5.6152434, 0.0]}}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=[c.PatientID], EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING ImplementJoinStrategy AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=ENHANCED_SORT, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1067.542, DEPENDENT_VALUE_SOURCE=$dsc/id4, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime], EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0]}, SORT_LEFT=SORT, IS_RIGHT_DISTINCT=true}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=ENHANCED_SORT, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1067.542, EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0]}, DEPENDENT_VALUE_SOURCE=$dsc/id5, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime], SORT_RIGHT=SORT}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=[ci.CodeID, ci.PatientID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=14.773668, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Sort(groups=[], props={SORT_ORDER=ORDER BY ci.CodeID, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime]}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=[ci.ItemStatus, ci.PatientID, ci.CodeID, ci.EnterTime], EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.CodeID, cr.Code], CONFORMED_SOURCES=null, EST_CARDINALITY=738.51324, EST_COL_STATS={cr.CodeID=[27.1756, 369.25662, -1.0], cr.Code=[27.1756, 369.25662, -1.0]}, IS_DEPENDENT_SET=true}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.CodeID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=[cr.CodeID, cr.Code], EST_CARDINALITY=738.51324, EST_COL_STATS={cr.CodeID=[27.1756, 369.25662, -1.0], cr.Code=[27.1756, 369.25662, -1.0]}}) Select(groups=[izisprod.S_CodeGroupRef AS cgr], props={SELECT_CRITERIA=cgr.CodeSysID IN (16, 33), IS_COPIED=true, OUTPUT_COLS=[cr.CodeID, cr.Code], EST_CARDINALITY=17297.346, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[1.0, 1.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cr.GroupID = cgr.GroupID], OUTPUT_COLS=[cgr.CodeSysID, cr.CodeID, cr.Code], MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=112432.75, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[cgr.CodeSysID = csr.CodeSysID], OUTPUT_COLS=[cgr.GroupID, cgr.CodeSysID], MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, IS_COPIED=true, EST_CARDINALITY=227.86636, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Select(groups=[izisprod.S_CodeSystemRef AS csr], props={SELECT_CRITERIA=csr.CodeSysID IN (16, 33), OUTPUT_COLS=[csr.CodeSysID], IS_COPIED=true, EST_CARDINALITY=7.745967, EST_COL_STATS={csr.ArchStatus=[2.7831578, 3.8729835, -1.0], csr.ArchTime=[2.7831578, 3.8729835, -1.0], csr.Code=[2.7831578, 3.8729835, -1.0], csr.CodeSysID=[2.0, 2.0, 0.0], ...}}) Source(groups=[izisprod.S_CodeSystemRef AS csr], props={OUTPUT_COLS=[csr.CodeSysID], EST_COL_STATS={csr.ArchStatus=[5.0, 10.0, -1.0], csr.ArchTime=[5.0, 10.0, -1.0], csr.Code=[5.0, 10.0, -1.0], csr.CodeSysID=[5.0, 10.0, -1.0], ...}, EST_CARDINALITY=20.0}) Source(groups=[izisprod.S_CodeGroupRef AS cgr], props={OUTPUT_COLS=[cgr.CodeSysID, cgr.GroupID], EST_COL_STATS={cgr.ArchStatus=[13.0, 75.0, -1.0], cgr.ArchTime=[13.0, 75.0, -1.0], cgr.Code=[13.0, 75.0, -1.0], cgr.CodeSysID=[13.0, 75.0, -1.0], ...}, EST_CARDINALITY=150.0}) Select(groups=[izisprod.S_CodeRef AS cr], props={SELECT_CRITERIA=cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout'), OUTPUT_COLS=[cr.GroupID, cr.CodeID, cr.Code], IS_COPIED=true, EST_CARDINALITY=21556.537, EST_COL_STATS={cr.ArchStatus=[146.82144, 10778.269, -1.0], cr.ArchTime=[146.82144, 10778.269, -1.0], cr.Code=[146.82144, 10778.269, -1.0], cr.CodeID=[146.82144, 10778.269, -1.0], ...}}) Source(groups=[izisprod.S_CodeRef AS cr], props={OUTPUT_COLS=[cr.Code, cr.GroupID, cr.CodeID], EST_COL_STATS={cr.ArchStatus=[149.0, 11000.0, -1.0], cr.ArchTime=[149.0, 11000.0, -1.0], cr.Code=[149.0, 11000.0, -1.0], cr.CodeID=[149.0, 11000.0, -1.0], ...}, EST_CARDINALITY=22000.0}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=[c.PatientID], EST_CARDINALITY=5.6152434, EST_COL_STATS={c.PatientID=[5.6152434, 5.6152434, 0.0]}, IS_DEPENDENT_SET=true}) Sort(groups=[], props={SORT_ORDER=ORDER BY c.PatientID, OUTPUT_COLS=[c.PatientID]}) Select(groups=[tmptables.rep149_cohort AS c], props={SELECT_CRITERIA=c.PatientID IN (), IS_DEPENDENT_SET=true, IS_PUSHED=true, OUTPUT_COLS=[c.PatientID], EST_CARDINALITY=5.6152434, EST_COL_STATS={c.PatientID=[5.6152434, 5.6152434, 0.0]}}) Source(groups=[tmptables.rep149_cohort AS c], props={OUTPUT_COLS=[c.PatientID], EST_COL_STATS={c.PatientID=[11138.0, 11138.0, 0.0], c.episodenumber=[106.0, 5569.0, -1.0], c.adremanumber=[106.0, 5569.0, -1.0], c.Birthdate=[106.0, 5569.0, -1.0], ...}, EST_CARDINALITY=11138.0}) ============================================================================ EXECUTING MergeCriteria AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1, OUTPUT_COLS=[ci.CodeID, ci.PatientID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=null, EST_COL_STATS={ci.AddCode=[3.843653, 7.386834, -1.0], ci.AddDescription=[3.843653, 7.386834, -1.0], ci.Certainty=[3.843653, 7.386834, -1.0], ci.CodedItemID=[3.843653, 7.386834, -1.0], ...}}) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], EST_CARDINALITY=264.07385, EST_COL_STATS={ci.PatientID=[16.250349, 132.03693, -1.0], ci.CodeID=[16.250349, 132.03693, -1.0], ci.EnterTime=[16.250349, 132.03693, -1.0]}}) Sort(groups=[], props={SORT_ORDER=ORDER BY ci.CodeID, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], EST_CARDINALITY=264.07385, EST_COL_STATS={ci.PatientID=[16.250349, 132.03693, -1.0], ci.CodeID=[16.250349, 132.03693, -1.0], ci.EnterTime=[16.250349, 132.03693, -1.0]}}) Select(groups=[iziswh.P_CodedItem AS ci], props={SELECT_CRITERIA=ci.ItemStatus = 0, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], IS_COPIED=true, EST_CARDINALITY=264.07385, EST_COL_STATS={ci.AddCode=[16.250349, 132.03693, -1.0], ci.AddDescription=[16.250349, 132.03693, -1.0], ci.Certainty=[16.250349, 132.03693, -1.0], ci.CodedItemID=[16.250349, 132.03693, -1.0], ...}}) Source(groups=[iziswh.P_CodedItem AS ci], props={OUTPUT_COLS=[ci.ItemStatus, ci.PatientID, ci.CodeID, ci.EnterTime], EST_COL_STATS={ci.AddCode=[265.0, 35000.0, -1.0], ci.AddDescription=[265.0, 35000.0, -1.0], ci.Certainty=[265.0, 35000.0, -1.0], ci.CodedItemID=[265.0, 35000.0, -1.0], ...}, EST_CARDINALITY=70000.0}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c]) Sort(groups=[]) Select(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING PlanSorts AFTER: Project(groups=[]) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=ENHANCED_SORT, JOIN_CRITERIA=[c.PatientID = ci.PatientID], LEFT_EXPRESSIONS=[ci.PatientID], RIGHT_EXPRESSIONS=[c.PatientID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1067.542, DEPENDENT_VALUE_SOURCE=$dsc/id4, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime], EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0]}, SORT_LEFT=SORT, IS_RIGHT_DISTINCT=true}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=ENHANCED_SORT, JOIN_CRITERIA=[ci.CodeID = cr.CodeID], LEFT_EXPRESSIONS=[ci.CodeID], RIGHT_EXPRESSIONS=[cr.CodeID], NON_EQUI_JOIN_CRITERIA=[], EST_CARDINALITY=1067.542, EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0]}, DEPENDENT_VALUE_SOURCE=$dsc/id5, OUTPUT_COLS=[ci.PatientID, cr.Code, ci.EnterTime], SORT_RIGHT=SORT}) Select(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[iziswh.P_CodedItem AS ci]) Sort(groups=[]) Select(groups=[iziswh.P_CodedItem AS ci]) Source(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeRef AS cr]) Select(groups=[izisprod.S_CodeGroupRef AS cgr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Join(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeSystemRef AS csr]) Source(groups=[izisprod.S_CodeGroupRef AS cgr]) Select(groups=[izisprod.S_CodeRef AS cr]) Source(groups=[izisprod.S_CodeRef AS cr]) Access(groups=[tmptables.rep149_cohort AS c]) Sort(groups=[]) Select(groups=[tmptables.rep149_cohort AS c]) Source(groups=[tmptables.rep149_cohort AS c]) ============================================================================ EXECUTING CollapseSource AFTER: Project(groups=[], props={PROJECT_COLS=[Count], INTO_GROUP=tmptables.rep149_variables, OUTPUT_COLS=[Count], EST_CARDINALITY=1067.542, EST_COL_STATS={Count=[-1.0, -1.0, -1.0]}}) Source(groups=[tmptables.rep149_variables]) Project(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeRef AS cr], props={PROJECT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], OUTPUT_COLS=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue], EST_CARDINALITY=1067.542, EST_COL_STATS={ci.PatientID=[3.843653, 7.386834, -1.0], cr.Code=[27.1756, 369.25662, -1.0], ci.EnterTime=[3.843653, 7.386834, -1.0], 1=[1.0, 1.0, 1.0]}}) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr, tmptables.rep149_cohort AS c]) Join(groups=[iziswh.P_CodedItem AS ci, izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr]) Select(groups=[iziswh.P_CodedItem AS ci]) Access(groups=[iziswh.P_CodedItem AS ci], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=tid:8d0a68ff6cf0-ba15862c-00000000, OUTPUT_COLS=[ci.PatientID, ci.CodeID, ci.EnterTime], EST_CARDINALITY=264.07385, EST_COL_STATS={ci.PatientID=[16.250349, 132.03693, -1.0], ci.CodeID=[16.250349, 132.03693, -1.0], ci.EnterTime=[16.250349, 132.03693, -1.0]}, ATOMIC_REQUEST=SELECT ci.PatientID, ci.CodeID, ci.EnterTime FROM iziswh.P_CodedItem AS ci WHERE ci.ItemStatus = 0 ORDER BY ci.CodeID}) Access(groups=[izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=tid:741426d4f5b6-8ac9aff2-00000000, OUTPUT_COLS=[cr.CodeID, cr.Code], CONFORMED_SOURCES=null, EST_CARDINALITY=738.51324, EST_COL_STATS={cr.CodeID=[27.1756, 369.25662, -1.0], cr.Code=[27.1756, 369.25662, -1.0]}, IS_DEPENDENT_SET=true, ATOMIC_REQUEST=SELECT cr.CodeID, cr.Code FROM izisprod.S_CodeSystemRef AS csr, izisprod.S_CodeGroupRef AS cgr, izisprod.S_CodeRef AS cr WHERE (cgr.CodeSysID = csr.CodeSysID) AND (cr.GroupID = cgr.GroupID) AND (csr.CodeSysID IN (16, 33)) AND (cr.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) AND (cgr.CodeSysID IN (16, 33)) AND (cr.CodeID IN ())}) Access(groups=[tmptables.rep149_cohort AS c], props={SOURCE_HINT=null, MODEL_ID=Schema name=tmptables, nameInSource=null, uuid=tid:16a73f7104f8-5a13923c-00000000, OUTPUT_COLS=[c.PatientID], EST_CARDINALITY=5.6152434, EST_COL_STATS={c.PatientID=[5.6152434, 5.6152434, 0.0]}, IS_DEPENDENT_SET=true, ATOMIC_REQUEST=SELECT c.PatientID FROM tmptables.rep149_cohort AS c WHERE c.PatientID IN () ORDER BY c.PatientID}) ============================================================================ CONVERTING PLAN TREE TO PROCESS TREE PROCESS PLAN = ProjectIntoNode(0) output=[Count] tmptables.rep149_variables ProjectNode(1) output=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue] [ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue] JoinNode(2) [Dependent] [ENHANCED SORT JOIN (SORT/ALREADY_SORTED)] [INNER JOIN] criteria=[ci.PatientID=c.PatientID] output=[ci.PatientID, cr.Code, ci.EnterTime] JoinNode(3) [Dependent] [ENHANCED SORT JOIN (ALREADY_SORTED/SORT)] [INNER JOIN] criteria=[ci.CodeID=cr.CodeID] output=[ci.PatientID, cr.Code, ci.EnterTime] SelectNode(4) output=[ci.CodeID, ci.PatientID, ci.EnterTime] ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1 AccessNode(5) output=[ci.PatientID, ci.CodeID, ci.EnterTime] SELECT g_0.PatientID AS c_0, g_0.CodeID AS c_1, g_0.EnterTime AS c_2 FROM iziswh.P_CodedItem AS g_0 WHERE g_0.ItemStatus = 0 ORDER BY c_1 DependentAccessNode(6) output=[cr.CodeID, cr.Code] SELECT g_2.CodeID, g_2.Code FROM izisprod.S_CodeSystemRef AS g_0, izisprod.S_CodeGroupRef AS g_1, izisprod.S_CodeRef AS g_2 WHERE (g_1.CodeSysID = g_0.CodeSysID) AND (g_2.GroupID = g_1.GroupID) AND (g_0.CodeSysID IN (16, 33)) AND (g_2.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) AND (g_1.CodeSysID IN (16, 33)) AND (g_2.CodeID IN ()) DependentAccessNode(7) output=[c.PatientID] SELECT g_0.PatientID AS c_0 FROM tmptables.rep149_cohort AS g_0 WHERE g_0.PatientID IN () ORDER BY c_0 ============================================================================ ---------------------------------------------------------------------------- OPTIMIZATION COMPLETE: PROCESSOR PLAN: ProjectIntoNode(0) output=[Count] tmptables.rep149_variables ProjectNode(1) output=[ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue] [ci.PatientID AS patientid, cr.Code AS variableid, ci.EnterTime AS scoretime, 1 AS varvalue] JoinNode(2) [Dependent] [ENHANCED SORT JOIN (SORT/ALREADY_SORTED)] [INNER JOIN] criteria=[ci.PatientID=c.PatientID] output=[ci.PatientID, cr.Code, ci.EnterTime] JoinNode(3) [Dependent] [ENHANCED SORT JOIN (ALREADY_SORTED/SORT)] [INNER JOIN] criteria=[ci.CodeID=cr.CodeID] output=[ci.PatientID, cr.Code, ci.EnterTime] SelectNode(4) output=[ci.CodeID, ci.PatientID, ci.EnterTime] ROW_NUMBER() OVER (PARTITION BY ci.PatientID, ci.CodeID ORDER BY ci.EnterTime) = 1 AccessNode(5) output=[ci.PatientID, ci.CodeID, ci.EnterTime] SELECT g_0.PatientID AS c_0, g_0.CodeID AS c_1, g_0.EnterTime AS c_2 FROM iziswh.P_CodedItem AS g_0 WHERE g_0.ItemStatus = 0 ORDER BY c_1 DependentAccessNode(6) output=[cr.CodeID, cr.Code] SELECT g_2.CodeID, g_2.Code FROM izisprod.S_CodeSystemRef AS g_0, izisprod.S_CodeGroupRef AS g_1, izisprod.S_CodeRef AS g_2 WHERE (g_1.CodeSysID = g_0.CodeSysID) AND (g_2.GroupID = g_1.GroupID) AND (g_0.CodeSysID IN (16, 33)) AND (g_2.Code NOT IN ('HK_GEEN', 'MZ_GEEN', 'zMG_fout')) AND (g_1.CodeSysID IN (16, 33)) AND (g_2.CodeID IN ()) DependentAccessNode(7) output=[c.PatientID] SELECT g_0.PatientID AS c_0 FROM tmptables.rep149_cohort AS g_0 WHERE g_0.PatientID IN () ORDER BY c_0 ============================================================================