john k
asked on
CTE and joins
I need some advice in writing below query: I used to have joins before in the below query. I am re-writing that query using CTE. But i am not getting expected results. Query is:
WITH PERS_ADDRESS
AS (SELECT PERS_ID,
ZIP_CODE_NUM,
ROUTER_CALL_DAY_IDENTIF,
ROUTER_CALL_IDENTIF,
created_on,
LANG_CODE
FROM ( SELECT PA.PERS_ID,
ZIP_CODE_NUM,
CL.ROUTER_CALL_DAY_IDENTIF,
CL.ROUTER_CALL_IDENTIF,
cl.created_on,
CL.LANG_CODE,
ROW_NUMBER ()
OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE)
AS R
FROM call_log@ATLG03 CL
LEFT JOIN PERS_ADDR@ATLG03 PA
ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE
AND ( CL.ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND CL.ROUTER_CALL_IDENTIF IS NOT NULL)
AND cl.created_on >=
TO_DATE ('03/16/2017', 'mm/dd/yyyy')
AND cl.created_on <
TO_DATE ('03/17/2017', 'mm/dd/yyyy') + 1
AND cl.county_code = '36'
ORDER BY CL.ROUTER_CALL_DAY_IDENTIF,
CL.ROUTER_CALL_IDENTIF,
cl.created_on)
WHERE R = 1),
NEXT_APPT
AS (SELECT *
FROM ( SELECT ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
CP.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY CP.PERS_ID
ORDER BY APPT_DATE DESC)
AS R1
FROM cust_appt_attend@ATLG03 caa
JOIN case_pers@ATLG03 cp ON CP.ID = CAA.CASE_PERS_ID
JOIN PERS_ADDRESS PA ON CP.PERS_ID = PA.PERS_ID
LEFT OUTER JOIN cust_appt@ATLG03 ca
ON ( CA.ID = CAA.CUST_APPT_ID
AND TO_DATE (
( TO_CHAR (APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') <
PA.created_on)
ORDER BY appt_date DESC)
WHERE R1 = 1),
LAST_APPT
AS ( (SELECT *
FROM ( SELECT ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
CP.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY CP.PERS_ID
ORDER BY APPT_DATE DESC)
AS R2
FROM cust_appt_attend@ATLG03 caa
JOIN case_pers@ATLG03 cp
ON CP.ID = CAA.CASE_PERS_ID
JOIN PERS_ADDRESS PA ON CP.PERS_ID = PA.PERS_ID
LEFT OUTER JOIN cust_appt@ATLG03 ca
ON ( CA.ID = CAA.CUST_APPT_ID
AND TO_DATE (
( TO_CHAR (APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (start_time,
'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') <
PA.created_on)
ORDER BY appt_date DESC)
WHERE R2 = 1))
SELECT PA.ROUTER_CALL_DAY_IDENTIF,
PA.ROUTER_CALL_IDENTIF,
PA.PERS_ID,
PA.ZIP_CODE_NUM,
P.DOB,
P.GENDER_CODE,
P.MARITAL_STAT_CODE,
L.LANG_CODE,
PA.LANG_CODE LANG_SELECTED_IN_IVR,
PA.created_on,
NA.APPT_DATE NEXT_APPT_DATE,
NA.START_TIME NEXT_APPT_START_TIME,
NA.STAT_CODE NEXT_APPT_STATUS,
LA.APPT_DATE LAST_APPT_DATE,
LA.START_TIME LAST_APPT_START_TIME,
LA.STAT_CODE LAST_APPT_STATUS
FROM PERS_ADDRESS PA
JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN NEXT_APPT NA ON NA.PERS_ID = P.ID
LEFT JOIN LAST_APPT LA ON LA.PERS_ID = P.ID
after fixing next_app, r=2 after ordering by appt_date
/* Formatted on 5/30/2017 1:41:43 PM (QP5 v5.163.1008.3004) */
WITH PERS_ADDRESS
AS (SELECT PERS_ID,
ZIP_CODE_NUM,
ROUTER_CALL_DAY_IDENTIF,
ROUTER_CALL_IDENTIF,
created_on,
LANG_CODE
FROM (SELECT PA.PERS_ID,
ZIP_CODE_NUM,
CL.ROUTER_CALL_DAY_IDENTIF,
CL.ROUTER_CALL_IDENTIF,
cl.created_on,
CL.LANG_CODE,
ROW_NUMBER () OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE) AS R
FROM call_log@ATLG03 CL
LEFT JOIN PERS_ADDR@ATLG03 PA
ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN ADDR@ATLG03 AD
ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE
AND (CL.ROUTER_CALL_DAY_IDENTIF IS NOT NULL AND CL.ROUTER_CALL_IDENTIF IS NOT NULL)
AND cl.created_on >= TO_DATE ('03/16/2017', 'mm/dd/yyyy')
AND cl.created_on < TO_DATE ('03/17/2017', 'mm/dd/yyyy') + 1
AND cl.county_code = '36')
WHERE R = 1),
NEXT_APPT
AS (SELECT *
FROM (SELECT ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
CP.PERS_ID,
ROW_NUMBER () OVER (PARTITION BY CP.PERS_ID ORDER BY APPT_DATE) AS R1
FROM cust_appt_attend@ATLG03 caa
JOIN case_pers@ATLG03 cp
ON CP.ID = CAA.CASE_PERS_ID
JOIN PERS_ADDRESS PA
ON CP.PERS_ID = PA.PERS_ID
LEFT OUTER JOIN cust_appt@ATLG03 ca
ON (CA.ID = CAA.CUST_APPT_ID
AND TO_DATE (
(TO_CHAR (APPT_DATE, 'YYYY-MM-DD') || ' ' || TO_CHAR (start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') < PA.created_on))
WHERE R1 = 2),
LAST_APPT
AS ( (SELECT *
FROM (SELECT ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
CP.PERS_ID,
ROW_NUMBER () OVER (PARTITION BY CP.PERS_ID ORDER BY APPT_DATE DESC) AS R2
FROM cust_appt_attend@ATLG03 caa
JOIN case_pers@ATLG03 cp
ON CP.ID = CAA.CASE_PERS_ID
JOIN PERS_ADDRESS PA
ON CP.PERS_ID = PA.PERS_ID
LEFT OUTER JOIN cust_appt@ATLG03 ca
ON (CA.ID = CAA.CUST_APPT_ID
AND TO_DATE (
(TO_CHAR (APPT_DATE, 'YYYY-MM-DD') || ' ' || TO_CHAR (start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') < PA.created_on))
WHERE R2 = 1))
SELECT PA.ROUTER_CALL_DAY_IDENTIF,
PA.ROUTER_CALL_IDENTIF,
PA.PERS_ID,
PA.ZIP_CODE_NUM,
P.DOB,
P.GENDER_CODE,
P.MARITAL_STAT_CODE,
L.LANG_CODE,
PA.LANG_CODE LANG_SELECTED_IN_IVR,
PA.created_on,
NA.APPT_DATE NEXT_APPT_DATE,
NA.START_TIME NEXT_APPT_START_TIME,
NA.STAT_CODE NEXT_APPT_STATUS,
LA.APPT_DATE LAST_APPT_DATE,
LA.START_TIME LAST_APPT_START_TIME,
LA.STAT_CODE LAST_APPT_STATUS
FROM PERS_ADDRESS PA
JOIN pers@ATLG03 P
ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L
ON L.PERS_ID = P.ID
LEFT JOIN NEXT_APPT NA
ON NA.PERS_ID = P.ID
LEFT JOIN LAST_APPT LA
ON LA.PERS_ID = P.ID
sigh....
There is NO ADVANTAGE in using a CTE here.
with CTE as (select c1,c2,c3 from t1 where c4 = 5)
select
*
from cte
join t2 on cte.c1 = t2.t1_id
is EXACTLY THE SAME AS
select
*
from (select c1,c2,c3 from t1 where c4 = 5) AS cte
join t2 on cte.c1 = t2.t1_id
If you have a working existing query, why oh why re-work it to include a CTE when there is NO ADVANTAGE in doing so?
Please do not tell me it makes it easier to read - I dismiss that as a reason.
There is NO ADVANTAGE in using a CTE here.
with CTE as (select c1,c2,c3 from t1 where c4 = 5)
select
*
from cte
join t2 on cte.c1 = t2.t1_id
is EXACTLY THE SAME AS
select
*
from (select c1,c2,c3 from t1 where c4 = 5) AS cte
join t2 on cte.c1 = t2.t1_id
If you have a working existing query, why oh why re-work it to include a CTE when there is NO ADVANTAGE in doing so?
Please do not tell me it makes it easier to read - I dismiss that as a reason.
ASKER
I have a temporary table in 'A' database. I have to populate 'B' database multiple tables data to 'A' database temporary table. I created a DB link to access B database tables in A database. My select query is working in B database, not in A. I got below error:
Getting error: ORA-01799: a column may not be outer-joined to a subquery.
DBA team suggested to re-write the query using CTE, as the query has multiple sub queries.
Getting error: ORA-01799: a column may not be outer-joined to a subquery.
DBA team suggested to re-write the query using CTE, as the query has multiple sub queries.
ASKER
This is the final query i came up with. After suggestion from Huseyin. Thanks Huseyin for your assistance. Please review it and let me know if any changes.
/* Formatted on 5/30/2017 3:42:56 PM (QP5 v5.287) */
WITH PERS_ADDRESS
AS (SELECT PERS_ID, ZIP_CODE_NUM
FROM (SELECT PA.PERS_ID,
ZIP_CODE_NUM,
ROW_NUMBER ()
OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE)
AS R
FROM PERS_ADDR@ATLG03 PA
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE)
WHERE R = 1),
APPT
AS (SELECT *
FROM (SELECT ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
CP.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY CP.PERS_ID
ORDER BY APPT_DATE DESC)
AS R1
FROM CASE_PERS@ATLG03 CP
JOIN CUST_APPT_ATTEND@ATLG03 CAA ON CAA.CASE_PERS_ID = CP.ID
LEFT OUTER JOIN CUST_APPT@ATLG03 CA
ON CA.ID = CAA.CUST_APPT_ID)
WHERE R1 = 1),
GEN_DOC
AS (SELECT *
FROM (SELECT gd.id,
GD.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY GD.PERS_ID ORDER BY DOC_DATE DESC)
AS R2
FROM generate_doc@ATLG03 gd, doc_templ_lang@ATLG03, doc_templ@ATLG03
WHERE GD.DOC_TEMPL_LANG_ID = doc_templ_lang.id
AND doc_templ.id = doc_templ_lang.doc_templ_id
AND doc_templ.id IN (1259,
1239,
1219,
1561,
1562,
1563,
1199,
1179,
1159,
1139,
1603,
1691)
AND doc_date <= SYSDATE)
WHERE R2 = 1),
NOA_GEN_DOC
AS (SELECT *
FROM (SELECT gd.id,
GD.DOC_DATE,
GD.ACTN_CODE,
PGM.PGM_CODE,
GD.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY GD.PERS_ID ORDER BY DOC_DATE DESC)
AS R3
FROM generate_doc@ATLG03 gd, pgm@ATLG03
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id)
WHERE R3 = 1)
SELECT CL.ROUTER_CALL_DAY_IDENTIF,
CL.ROUTER_CALL_IDENTIF,
CL.PERS_ID,
PA.ZIP_CODE_NUM,
P.DOB,
P.GENDER_CODE,
P.MARITAL_STAT_CODE,
L.LANG_CODE,
CL.LANG_CODE LANG_SELECTED_IN_IVR,
CL.created_on,
NA.APPT_DATE NEXT_APPT_DATE,
NA.START_TIME NEXT_APPT_START_TIME,
NA.STAT_CODE NEXT_APPT_STATUS,
LA.APPT_DATE LAST_APPT_DATE,
LA.START_TIME LAST_APPT_START_TIME,
LA.STAT_CODE LAST_APPT_STATUS,
TO_CHAR (
(SELECT CUST_RPT_PGM_TYPE_DETL.CREATED_ON
FROM CUST_RPT_PGM_TYPE_DETL@ATLG03
WHERE CUST_RPT_PGM_TYPE_DETL.id =
(SELECT MAX (CUST_RPT_PGM_TYPE_DETL.ID)
FROM generate_doc@ATLG03,
cust_rpt@ATLG03,
cust_rpt_pgm_type@ATLG03,
cust_rpt_pgm_type_detl@ATLG03
WHERE generate_doc.id = GEN_DOC.id
AND cust_rpt.generate_doc_id = generate_doc.id
AND cust_rpt_pgm_type.cust_rpt_id = cust_rpt.id
AND cust_rpt_pgm_type_detl.id =
(SELECT MAX (id)
FROM cust_rpt_pgm_type_detl@ATLG03
WHERE cust_rpt_pgm_type_detl.cust_rpt_pgm_type_id =
cust_rpt_pgm_type.id
AND cust_rpt_pgm_type_detl.created_on <=
SYSDATE))),
'YYYY-MM-DD HH24:MI:SS')
LAST_RE_DATE,
(SELECT CUST_RPT_PGM_TYPE_DETL.stat_code
FROM CUST_RPT_PGM_TYPE_DETL@ATLG03
WHERE CUST_RPT_PGM_TYPE_DETL.id =
(SELECT MAX (CUST_RPT_PGM_TYPE_DETL.id)
FROM generate_doc@ATLG03,
cust_rpt@ATLG03,
cust_rpt_pgm_type@ATLG03,
cust_rpt_pgm_type_detl@ATLG03
WHERE generate_doc.id = GEN_DOC.id
AND cust_rpt.generate_doc_id = generate_doc.id
AND cust_rpt_pgm_type.cust_rpt_id = cust_rpt.id
AND cust_rpt_pgm_type_detl.id =
(SELECT MAX (id)
FROM cust_rpt_pgm_type_detl@ATLG03
WHERE cust_rpt_pgm_type_detl.cust_rpt_pgm_type_id =
cust_rpt_pgm_type.id
AND cust_rpt_pgm_type_detl.created_on <=
SYSDATE)))
LAST_RE_STATUS,
(SELECT CUST_RPT_PGM_TYPE.PGM_CODE
FROM CUST_RPT_PGM_TYPE@ATLG03
WHERE CUST_RPT_PGM_TYPE.id =
(SELECT MAX (CUST_RPT_PGM_TYPE.ID)
FROM generate_doc@ATLG03,
cust_rpt@ATLG03,
cust_rpt_pgm_type@ATLG03,
cust_rpt_pgm_type_detl@ATLG03
WHERE generate_doc.id = GEN_DOC.id
AND cust_rpt.generate_doc_id = generate_doc.id
AND cust_rpt_pgm_type.cust_rpt_id = cust_rpt.id
AND cust_rpt_pgm_type_detl.id =
(SELECT MAX (id)
FROM cust_rpt_pgm_type_detl@ATLG03
WHERE cust_rpt_pgm_type_detl.cust_rpt_pgm_type_id =
cust_rpt_pgm_type.id
AND cust_rpt_pgm_type_detl.created_on <=
SYSDATE)))
LAST_RE_PROGRAM,
TO_CHAR (gd_cw.doc_date, 'YYYY-MM-DD HH24:MI:SS') CW_NOA_DATE,
gd_cw.actn_code CW_NOA_TYPE,
TO_CHAR (gd_fs.doc_date, 'YYYY-MM-DD HH24:MI:SS') FS_NOA_DATE,
gd_fs.actn_code FS_NOA_TYPE,
TO_CHAR (gd_mc.doc_date, 'YYYY-MM-DD HH24:MI:SS') MC_NOA_DATE,
gd_mc.actn_code MC_NOA_TYPE
FROM CALL_LOG@ATLG03 CL
JOIN pers@ATLG03 P ON P.id = CL.pers_id
LEFT JOIN PERS_ADDRESS PA ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN APPT NA
ON ( NA.PERS_ID = P.ID
AND NA.APPT_DATE IS NOT NULL
AND NA.START_TIME IS NOT NULL
AND TO_DATE (
( TO_CHAR (NA.APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (NA.start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') >= CL.created_on)
LEFT JOIN APPT LA
ON ( LA.PERS_ID = P.ID
AND LA.APPT_DATE IS NOT NULL
AND LA.START_TIME IS NOT NULL
AND TO_DATE (
( TO_CHAR (LA.APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (LA.start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') < CL.created_on)
LEFT OUTER JOIN GEN_DOC ON GEN_DOC.PERS_ID = P.ID
LEFT OUTER JOIN NOA_GEN_DOC gd_FS
ON gd_FS.PERS_ID = P.ID AND gd_FS.PGM_CODE = 'FS'
LEFT OUTER JOIN NOA_GEN_DOC gd_MC
ON gd_MC.PERS_ID = P.ID AND gd_FS.PGM_CODE = 'MC'
LEFT OUTER JOIN NOA_GEN_DOC gd_CW
ON gd_CW.PERS_ID = P.ID AND gd_FS.PGM_CODE = 'CW'
WHERE ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND ROUTER_CALL_IDENTIF IS NOT NULL;
maybe this is shorter and better
>>>
AND TO_DATE (
( TO_CHAR (LA.APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (LA.start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') < CL.created_on)>>>
AND trunc(LA.APPT_DATE) + (LA.start_time - trunc(LA.start_time) < CL.created_on)
another thing is
LEFT OUTER JOIN
>>>
LEFT JOIN
and
JOIN
>>>
INNER JOIN
just to be consistent...
I use "INNER JOIN" "LEFT JOIN" "RIGHT JOIN" "FULL JOIN"
LEFT OUTER JOIN
>>>
LEFT JOIN
and
JOIN
>>>
INNER JOIN
just to be consistent...
I use "INNER JOIN" "LEFT JOIN" "RIGHT JOIN" "FULL JOIN"
also I am thinking of this
>>>
but it is too complicated, you should check the queries by hard-coding to see they give the same values...
I did not like those subqueries with lots of max inner queries...
similar structures
LAST_RE_DATE
LAST_RE_STATUS
LAST_RE_PROGRAM
(SELECT CUST_RPT_PGM_TYPE.PGM_CODE
FROM CUST_RPT_PGM_TYPE@ATLG03
WHERE CUST_RPT_PGM_TYPE.id =
(SELECT MAX (CUST_RPT_PGM_TYPE.ID)
FROM generate_doc@ATLG03,
cust_rpt@ATLG03,
cust_rpt_pgm_type@ATLG03,
cust_rpt_pgm_type_detl@ATLG03
WHERE generate_doc.id = GEN_DOC.id
AND cust_rpt.generate_doc_id = generate_doc.id
AND cust_rpt_pgm_type.cust_rpt_id = cust_rpt.id
AND cust_rpt_pgm_type_detl.id =
(SELECT MAX (id)
FROM cust_rpt_pgm_type_detl@ATLG03
WHERE cust_rpt_pgm_type_detl.cust_rpt_pgm_type_id =
cust_rpt_pgm_type.id
AND cust_rpt_pgm_type_detl.created_on <=
SYSDATE)))
LAST_RE_PROGRAM,>>>
(SELECT first_value(CUST_RPT_PGM_TYPE.PGM_CODE)
over (order by cust_rpt_pgm_type_detl@ATLG03.id desc, CUST_RPT_PGM_TYPE.ID desc)
FROM generate_doc@ATLG03,
cust_rpt@ATLG03,
cust_rpt_pgm_type@ATLG03,
cust_rpt_pgm_type_detl@ATLG03
WHERE generate_doc.id = GEN_DOC.id
AND cust_rpt.generate_doc_id = generate_doc.id
AND cust_rpt_pgm_type.cust_rpt_id = cust_rpt.id
AND cust_rpt_pgm_type_detl.created_on <= SYSDATE)))
LAST_RE_PROGRAM,but it is too complicated, you should check the queries by hard-coding to see they give the same values...
I did not like those subqueries with lots of max inner queries...
similar structures
LAST_RE_DATE
LAST_RE_STATUS
LAST_RE_PROGRAM
also, if we can write another cte that returns
purpose of this query:
returns id and LAST_RE_DATE, LAST_RE_STATUS, LAST_RE_PROGRAM for that id
then use this in main query and join with id...
so, we will not have any subqueries in main select!
the issue with those 3 columns is, they are running for every record selected!!!
LAST_VALUES as (
select * from (
select ... ID, ... LAST_RE_DATE, ... LAST_RE_STATUS, LAST_RE_PROGRAM
row_number() over (partition by id order by cust_rpt_pgm_type_detl@ATLG03.id desc, CUST_RPT_PGM_TYPE.ID desc) rn
from ...
) where rn = 1
)purpose of this query:
returns id and LAST_RE_DATE, LAST_RE_STATUS, LAST_RE_PROGRAM for that id
then use this in main query and join with id...
so, we will not have any subqueries in main select!
the issue with those 3 columns is, they are running for every record selected!!!
Without any sample data to work with making solid suggestions is difficult to say the least. However it looks like you are making this far more complex than it should be.
Please note the following will not work without some further information, but it looks to me like you could something like the following. see rows 20 & 21 in particular
Please note the following will not work without some further information, but it looks to me like you could something like the following. see rows 20 & 21 in particular
WITH cte AS (
SELECT
CP.PERS_ID
, ca.APPT_DATE
, CA.START_TIME
, ca.STAT_CODE
, ROW_NUMBER()
OVER (PARTITION BY CP.PERS_ID
ORDER BY CASE
WHEN ca.APPT_DATE <= sysdate THEN 0
ELSE 1
END, ca.APPT_DATE DESC) AS LAST_APPT
, ROW_NUMBER()
OVER (PARTITION BY CP.PERS_ID
ORDER BY CASE
WHEN ca.APPT_DATE > sysdate THEN 0
ELSE 1
END, ca.APPT_DATE ASC) AS NEXT_APPT
FROM cust_appt@ATLG03 ca
-- how do I gget from the table above to the table below?
-- JOIN case_pers@ATLG03 cp ??????????????
)
SELECT
PA.ROUTER_CALL_DAY_IDENTIF
, PA.ROUTER_CALL_IDENTIF
, PA.PERS_ID
, PA.ZIP_CODE_NUM
, P.DOB
, P.GENDER_CODE
, P.MARITAL_STAT_CODE
, L.LANG_CODE
, PA.LANG_CODE LANG_SELECTED_IN_IVR
, PA.created_on
, NA.APPT_DATE NEXT_APPT_DATE
, NA.START_TIME NEXT_APPT_START_TIME
, NA.STAT_CODE NEXT_APPT_STATUS
, LA.APPT_DATE LAST_APPT_DATE
, LA.START_TIME LAST_APPT_START_TIME
, LA.STAT_CODE LAST_APPT_STATUS
FROM
(SELECT
PERS_ID
, ZIP_CODE_NUM
, ROUTER_CALL_DAY_IDENTIF
, ROUTER_CALL_IDENTIF
, created_on
, LANG_CODE
FROM
(SELECT
PA.PERS_ID
, ZIP_CODE_NUM
, CL.ROUTER_CALL_DAY_IDENTIF
, CL.ROUTER_CALL_IDENTIF
, cl.created_on
, CL.LANG_CODE
, ROW_NUMBER() OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE) AS R
FROM call_log@ATLG03 CL
LEFT JOIN PERS_ADDR@ATLG03 PA ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE
AND (CL.ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND CL.ROUTER_CALL_IDENTIF IS NOT NULL)
AND cl.created_on >= TO_DATE('03/16/2017', 'mm/dd/yyyy')
AND cl.created_on < TO_DATE('03/17/2017', 'mm/dd/yyyy') + 1
AND cl.county_code = '36') X
WHERE R = 1) PA
INNER JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN CTE LA ON PA.pers_id = LA.PERS_ID AND LA.LAST_APPT = 1
LEFT JOIN CTE NA ON PA.pers_id = NA.PERS_ID AND NA.NEXT_APPT = 1
"DBA team suggested to re-write the query using CTE, as the query has multiple sub queries."
"Getting error: ORA-01799: a column may not be outer-joined to a subquery."
CTEs do not solve this problem.
Any subquery, in a CTE or not, could cause that oracle error.
"Getting error: ORA-01799: a column may not be outer-joined to a subquery."
CTEs do not solve this problem.
Any subquery, in a CTE or not, could cause that oracle error.
ASKER
Hi Portlet Paul,
Appointment is joined using Case_pers. Below is the joining of the queries.
Appointment is joined using Case_pers. Below is the joining of the queries.
WITH cte
AS (SELECT CP.PERS_ID,
ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE <= SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE DESC)
AS LAST_APPT,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE > SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE ASC)
AS NEXT_APPT
FROM cust_appt@ATLG03 ca j
join cust_appt_attend caa on caa.cust_appt_id = ca.id
join case_pers@ATLG03 cp on cp.id = caa.case_pers_id
-- how do I gget from the table above to the table below?
-- JOIN case_pers@ATLG03 cp ??????????????
)
OK, great, thank, but what I had hoped you would also do is to try it :)
Lets, start small first. Take that CTE with the joins just provided and do this:
OK, so now that main query should be tested, so try this please:
Please let us know if this works and produces expected results.
Lets, start small first. Take that CTE with the joins just provided and do this:
WITH cte
AS (SELECT CP.PERS_ID,
ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE <= SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE DESC)
AS LAST_APPT,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE > SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE ASC)
AS NEXT_APPT
FROM cust_appt@ATLG03 ca
INNER JOIN cust_appt_attend caa on caa.cust_appt_id = ca.id
INNER JOIN case_pers@ATLG03 cp on cp.id = caa.case_pers_id
)
select *
from cte where LAST_APPT = 1 or NEXT_APPT = 1OK, so now that main query should be tested, so try this please:
SELECT
PA.ROUTER_CALL_DAY_IDENTIF
, PA.ROUTER_CALL_IDENTIF
, PA.PERS_ID
, PA.ZIP_CODE_NUM
, P.DOB
, P.GENDER_CODE
, P.MARITAL_STAT_CODE
, L.LANG_CODE
, PA.LANG_CODE LANG_SELECTED_IN_IVR
, PA.created_on
, NA.APPT_DATE NEXT_APPT_DATE
, NA.START_TIME NEXT_APPT_START_TIME
, NA.STAT_CODE NEXT_APPT_STATUS
, LA.APPT_DATE LAST_APPT_DATE
, LA.START_TIME LAST_APPT_START_TIME
, LA.STAT_CODE LAST_APPT_STATUS
FROM
(SELECT
PERS_ID
, ZIP_CODE_NUM
, ROUTER_CALL_DAY_IDENTIF
, ROUTER_CALL_IDENTIF
, created_on
, LANG_CODE
FROM
(SELECT
PA.PERS_ID
, ZIP_CODE_NUM
, CL.ROUTER_CALL_DAY_IDENTIF
, CL.ROUTER_CALL_IDENTIF
, cl.created_on
, CL.LANG_CODE
, ROW_NUMBER() OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE) AS R
FROM call_log@ATLG03 CL
LEFT JOIN PERS_ADDR@ATLG03 PA ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE
AND (CL.ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND CL.ROUTER_CALL_IDENTIF IS NOT NULL)
AND cl.created_on >= TO_DATE('03/16/2017', 'mm/dd/yyyy')
AND cl.created_on < TO_DATE('03/17/2017', 'mm/dd/yyyy') + 1
AND cl.county_code = '36') X
WHERE R = 1) PA
INNER JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.IDWITH cte
AS (SELECT CP.PERS_ID,
ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE <= SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE DESC)
AS LAST_APPT,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE > SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE ASC)
AS NEXT_APPT
FROM cust_appt@ATLG03 ca
INNER JOIN cust_appt_attend caa on caa.cust_appt_id = ca.id
INNER JOIN case_pers@ATLG03 cp on cp.id = caa.case_pers_id
)
SELECT
PA.ROUTER_CALL_DAY_IDENTIF
, PA.ROUTER_CALL_IDENTIF
, PA.PERS_ID
, PA.ZIP_CODE_NUM
, P.DOB
, P.GENDER_CODE
, P.MARITAL_STAT_CODE
, L.LANG_CODE
, PA.LANG_CODE LANG_SELECTED_IN_IVR
, PA.created_on
, NA.APPT_DATE NEXT_APPT_DATE
, NA.START_TIME NEXT_APPT_START_TIME
, NA.STAT_CODE NEXT_APPT_STATUS
, LA.APPT_DATE LAST_APPT_DATE
, LA.START_TIME LAST_APPT_START_TIME
, LA.STAT_CODE LAST_APPT_STATUS
FROM
(SELECT
PERS_ID
, ZIP_CODE_NUM
, ROUTER_CALL_DAY_IDENTIF
, ROUTER_CALL_IDENTIF
, created_on
, LANG_CODE
FROM
(SELECT
PA.PERS_ID
, ZIP_CODE_NUM
, CL.ROUTER_CALL_DAY_IDENTIF
, CL.ROUTER_CALL_IDENTIF
, cl.created_on
, CL.LANG_CODE
, ROW_NUMBER() OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE) AS R
FROM call_log@ATLG03 CL
LEFT JOIN PERS_ADDR@ATLG03 PA ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE
AND (CL.ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND CL.ROUTER_CALL_IDENTIF IS NOT NULL)
AND cl.created_on >= TO_DATE('03/16/2017', 'mm/dd/yyyy')
AND cl.created_on < TO_DATE('03/17/2017', 'mm/dd/yyyy') + 1
AND cl.county_code = '36') X
WHERE R = 1) PA
INNER JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN CTE LA ON PA.pers_id = LA.PERS_ID AND LA.LAST_APPT = 1
LEFT JOIN CTE NA ON PA.pers_id = NA.PERS_ID AND NA.NEXT_APPT = 1Please let us know if this works and produces expected results.
Actually we can remove one join from within the CTE.
WITH cte
AS (SELECT caa.case_pers_id PERS_ID,
ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE <= SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE DESC)
AS LAST_APPT,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE > SYSDATE THEN 0 ELSE 1 END,
ca.APPT_DATE ASC)
AS NEXT_APPT
FROM cust_appt@ATLG03 ca
INNER JOIN cust_appt_attend caa on caa.cust_appt_id = ca.id
)
SELECT
PA.ROUTER_CALL_DAY_IDENTIF
, PA.ROUTER_CALL_IDENTIF
, PA.PERS_ID
, PA.ZIP_CODE_NUM
, P.DOB
, P.GENDER_CODE
, P.MARITAL_STAT_CODE
, L.LANG_CODE
, PA.LANG_CODE LANG_SELECTED_IN_IVR
, PA.created_on
, NA.APPT_DATE NEXT_APPT_DATE
, NA.START_TIME NEXT_APPT_START_TIME
, NA.STAT_CODE NEXT_APPT_STATUS
, LA.APPT_DATE LAST_APPT_DATE
, LA.START_TIME LAST_APPT_START_TIME
, LA.STAT_CODE LAST_APPT_STATUS
FROM
(SELECT
PERS_ID
, ZIP_CODE_NUM
, ROUTER_CALL_DAY_IDENTIF
, ROUTER_CALL_IDENTIF
, created_on
, LANG_CODE
FROM
(SELECT
PA.PERS_ID
, ZIP_CODE_NUM
, CL.ROUTER_CALL_DAY_IDENTIF
, CL.ROUTER_CALL_IDENTIF
, cl.created_on
, CL.LANG_CODE
, ROW_NUMBER() OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE) AS R
FROM call_log@ATLG03 CL
LEFT JOIN PERS_ADDR@ATLG03 PA ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE
AND (CL.ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND CL.ROUTER_CALL_IDENTIF IS NOT NULL)
AND cl.created_on >= TO_DATE('03/16/2017', 'mm/dd/yyyy')
AND cl.created_on < TO_DATE('03/17/2017', 'mm/dd/yyyy') + 1
AND cl.county_code = '36') X
WHERE R = 1) PA
INNER JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN CTE LA ON PA.pers_id = LA.PERS_ID AND LA.LAST_APPT = 1
LEFT JOIN CTE NA ON PA.pers_id = NA.PERS_ID AND NA.NEXT_APPT = 1ASKER
HI PortletPaul,
I really appreciate your effort. My requirement is , I have to make prediction based on the last event in appointments, NOA and re-eval. My bad, I haven't told the requirement before. appointments CTE have to pick latest record for each person and identify last or next appointment.
I have written a query with you and Huseyin suggestions. Below is the query.
NOA_GEN_DOC CTE is for NOA's. I need latest event for each person for specific 3 programs(program names: MC, FS,CW). current query returns only one row of the person irrespective of program. currently I am working on that part. Below is the working query.
Please check and let me know your inputs.
Above query is also not returning expected results. Still working on it. Could you please look into the query and give some input. Thanks PortletPaul and Huseyin for your inputs. I really appreciate your effort.
I really appreciate your effort. My requirement is , I have to make prediction based on the last event in appointments, NOA and re-eval. My bad, I haven't told the requirement before. appointments CTE have to pick latest record for each person and identify last or next appointment.
I have written a query with you and Huseyin suggestions. Below is the query.
/* Formatted on 5/31/2017 9:37:26 AM (QP5 v5.287) */
WITH PERS_ADDRESS
AS (SELECT PERS_ID, ZIP_CODE_NUM
FROM (SELECT PA.PERS_ID,
ZIP_CODE_NUM,
ROW_NUMBER ()
OVER (PARTITION BY PA.PERS_ID ORDER BY END_DATE)
AS R
FROM PERS_ADDR@ATLG03 PA
LEFT JOIN ADDR@ATLG03 AD ON AD.ID = PA.ADDR_ID
WHERE PA.END_DATE > SYSDATE)
WHERE R = 1),
APPT
AS (SELECT *
FROM (SELECT ca.APPT_DATE,
CA.START_TIME,
ca.STAT_CODE,
CP.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY CP.PERS_ID
ORDER BY APPT_DATE DESC)
AS R1
FROM CASE_PERS@ATLG03 CP
JOIN CUST_APPT_ATTEND@ATLG03 CAA
ON CAA.CASE_PERS_ID = CP.ID
LEFT OUTER JOIN CUST_APPT@ATLG03 CA
ON CA.ID = CAA.CUST_APPT_ID)
WHERE R1 = 1),
GEN_DOC
AS (SELECT *
FROM (SELECT gd.id,
GD.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY GD.PERS_ID ORDER BY DOC_DATE DESC)
AS R2
FROM generate_doc@ATLG03 gd,
doc_templ_lang@ATLG03,
doc_templ@ATLG03
WHERE GD.DOC_TEMPL_LANG_ID = doc_templ_lang.id
AND doc_templ.id = doc_templ_lang.doc_templ_id
AND doc_templ.id IN (1259,
1239,
1219,
1561,
1562,
1563,
1199,
1179,
1159,
1139,
1603,
1691)
AND doc_date <= SYSDATE)
WHERE R2 = 1),
[b]NOA_GEN_DOC
AS (SELECT *
FROM (SELECT gd.id,
GD.DOC_DATE,
GD.ACTN_CODE,
PGM.PGM_CODE,
GD.PERS_ID,
ROW_NUMBER ()
OVER (PARTITION BY GD.PERS_ID ORDER BY PGM.PGM_CODE,DOC_DATE DESC)
AS R3
FROM generate_doc@ATLG03 gd, pgm@ATLG03
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id)
WHERE R3 = 1)[/b],
REEVAL
AS (SELECT *
FROM (SELECT GD.ID,
GEN_DOC.PERS_ID,
CRPTD.STAT_CODE,
CRPTD.CREATED_ON,
CRPT.PGM_CODE,
ROW_NUMBER ()
OVER (PARTITION BY GD.id
ORDER BY CRPTD.id DESC, CRPT.ID DESC)
rn
FROM GENERATE_DOC@ATLG03 GD
JOIN GEN_DOC ON GEN_DOC.ID = GD.ID
JOIN CUST_RPT@ATLG03 CR ON CR.GENERATE_DOC_ID = GD.ID
JOIN CUST_RPT_PGM_TYPE@ATLG03 CRPT
ON CRPT.CUST_RPT_ID = CR.ID
JOIN CUST_RPT_PGM_TYPE_DETL@ATLG03 CRPTD
ON CRPTD.CUST_RPT_PGM_TYPE_ID = CRPT.ID
WHERE CRPTD.created_on <= SYSDATE)
WHERE rn = 1)
SELECT CL.ROUTER_CALL_DAY_IDENTIF,
CL.ROUTER_CALL_IDENTIF,
CL.PERS_ID,
PA.ZIP_CODE_NUM,
P.DOB,
P.GENDER_CODE,
P.MARITAL_STAT_CODE,
L.LANG_CODE,
CL.LANG_CODE LANG_SELECTED_IN_IVR,
CL.created_on,
NA.APPT_DATE NEXT_APPT_DATE,
NA.START_TIME NEXT_APPT_START_TIME,
NA.STAT_CODE NEXT_APPT_STATUS,
LA.APPT_DATE LAST_APPT_DATE,
LA.START_TIME LAST_APPT_START_TIME,
LA.STAT_CODE LAST_APPT_STATUS,
TO_CHAR (REEVAL.CREATED_ON, 'YYYY-MM-DD HH24:MI:SS') LAST_RE_DATE,
REEVAL.STAT_CODE LAST_RE_STATUS,
REEVAL.PGM_CODE LAST_RE_PROGRAM,
TO_CHAR (gd_cw.doc_date, 'YYYY-MM-DD HH24:MI:SS') CW_NOA_DATE,
gd_cw.actn_code CW_NOA_TYPE,
TO_CHAR (gd_fs.doc_date, 'YYYY-MM-DD HH24:MI:SS') FS_NOA_DATE,
gd_fs.actn_code FS_NOA_TYPE,
TO_CHAR (gd_mc.doc_date, 'YYYY-MM-DD HH24:MI:SS') MC_NOA_DATE,
gd_mc.actn_code MC_NOA_TYPE
FROM CALL_LOG@ATLG03 CL
JOIN pers@ATLG03 P ON P.id = CL.pers_id
LEFT JOIN PERS_ADDRESS PA ON PA.PERS_ID = CL.PERS_ID
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN APPT NA
ON ( NA.PERS_ID = P.ID
AND NA.APPT_DATE IS NOT NULL
AND NA.START_TIME IS NOT NULL
AND TO_DATE (
( TO_CHAR (NA.APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (NA.start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') >= CL.created_on)
LEFT JOIN APPT LA
ON ( LA.PERS_ID = P.ID
AND LA.APPT_DATE IS NOT NULL
AND LA.START_TIME IS NOT NULL
AND TO_DATE (
( TO_CHAR (LA.APPT_DATE, 'YYYY-MM-DD')
|| ' '
|| TO_CHAR (LA.start_time, 'HH24:MI:SS')),
'YYYY-MM-DD HH24:MI:SS') < CL.created_on)
LEFT JOIN REEVAL ON REEVAL.PERS_ID = P.ID
LEFT JOIN NOA_GEN_DOC gd_FS
ON gd_FS.PERS_ID = P.ID AND gd_FS.PGM_CODE = 'FS'
LEFT JOIN NOA_GEN_DOC gd_MC
ON gd_MC.PERS_ID = P.ID AND gd_MC.PGM_CODE = 'MC'
LEFT JOIN NOA_GEN_DOC gd_CW
ON gd_CW.PERS_ID = P.ID AND gd_CW.PGM_CODE = 'CW'
WHERE ROUTER_CALL_DAY_IDENTIF IS NOT NULL
AND ROUTER_CALL_IDENTIF IS NOT NULL;NOA_GEN_DOC CTE is for NOA's. I need latest event for each person for specific 3 programs(program names: MC, FS,CW). current query returns only one row of the person irrespective of program. currently I am working on that part. Below is the working query.
Please check and let me know your inputs.
/* Formatted on 5/31/2017 4:52:12 PM (QP5 v5.287) */
SELECT *
FROM (SELECT gd.id,
gd.pers_id,
pgm.pgm_code,
GD.DOC_DATE,
ROW_NUMBER ()
OVER (PARTITION BY PERS_ID,PGM_CODE ORDER BY DOC_DATE DESC)
rn
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id)
WHERE RN <= 3;Above query is also not returning expected results. Still working on it. Could you please look into the query and give some input. Thanks PortletPaul and Huseyin for your inputs. I really appreciate your effort.
you need rank()
check this
now apply this to your above query, and you should be ok :)
of course you need to add a filter like "prg_code in ('MC', 'FS', 'CW')"
check this
with t as (
select 'A' code, 10 qty
union select 'A', 12
union select 'A', 2
union select 'B', 8
union select 'B', 5
union select 'C', 7
union select 'C', 13
)
select * from
(
select code, qty, rank() over (partition by code order by qty desc) rn from t
) x where rn=1
code qty rn
A 12 1
B 8 1
C 13 1now apply this to your above query, and you should be ok :)
of course you need to add a filter like "prg_code in ('MC', 'FS', 'CW')"
by using "rank() over (partition by ... order by ...) rn" and selecting "rn=1", you will get one record per group...
and when you add "prg_code in ('MC', 'FS', 'CW')" to your query, you will have 3 groups, and 3 records with max or latest event...
and when you add "prg_code in ('MC', 'FS', 'CW')" to your query, you will have 3 groups, and 3 records with max or latest event...
ASKER
HI Huseyin,
Thanks for quick turn around. I already tried with rank(). But results are not expected as i need columns, like below:
WITH RANK(), i can get results. But how can I use that in the select clause, in the query I posted. I need results like above. That's where i am in trouble. Thanks.
Thanks for quick turn around. I already tried with rank(). But results are not expected as i need columns, like below:
TO_CHAR (gd_cw.doc_date, 'YYYY-MM-DD HH24:MI:SS') CW_NOA_DATE,
gd_cw.actn_code CW_NOA_TYPE,
TO_CHAR (gd_fs.doc_date, 'YYYY-MM-DD HH24:MI:SS') FS_NOA_DATE,
gd_fs.actn_code FS_NOA_TYPE,
TO_CHAR (gd_mc.doc_date, 'YYYY-MM-DD HH24:MI:SS') MC_NOA_DATE,
gd_mc.actn_code MC_NOA_TYPEWITH RANK(), i can get results. But how can I use that in the select clause, in the query I posted. I need results like above. That's where i am in trouble. Thanks.
what is result for
SELECT *
FROM (SELECT gd.id,
gd.pers_id,
pgm.pgm_code,
GD.DOC_DATE,
RANK() OVER (PARTITION BY PERS_ID, PGM_CODE ORDER BY DOC_DATE DESC) rn
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND PGM_CODE in ('MC', 'FS', 'CW')
AND pgm.id = gd.pgm_id) x
WHERE RN = 1;
>>"appointments CTE have to pick latest record for each person and identify last or next appointment. "
Yes, Understood. That's what I believe my suggested CTE will do. Did you actually try it?
What I'm trying to suggest to you is that you can do BOTH AT THE SAME TIME
Yes, Understood. That's what I believe my suggested CTE will do. Did you actually try it?
What I'm trying to suggest to you is that you can do BOTH AT THE SAME TIME
CREATE TABLE TABLE1
(PERS_ID int, APPT_DATE timestamp)
;
INSERT INTO TABLE1
(PERS_ID, APPT_DATE)
VALUES
(101, '2016-11-12 00:00:00'),
(101, '2017-03-03 00:00:00'),
(101, '2017-09-22 00:00:00'),
(101, '2017-11-23 00:00:00')
;
SELECT
PERS_ID
, ROW_NUMBER()
OVER (PARTITION BY PERS_ID
ORDER BY CASE
WHEN APPT_DATE <= current_date THEN 0
ELSE 1
END, APPT_DATE DESC) AS LAST_APPT
, ROW_NUMBER()
OVER (PARTITION BY PERS_ID
ORDER BY CASE
WHEN APPT_DATE > current_date THEN 0
ELSE 1
END, APPT_DATE ASC) AS NEXT_APPT
FROM TABLE1
;
SELECT
*
FROM (
SELECT
PERS_ID
, ROW_NUMBER()
OVER (PARTITION BY PERS_ID
ORDER BY CASE
WHEN APPT_DATE <= current_date THEN 0
ELSE 1
END, APPT_DATE DESC) AS LAST_APPT
, ROW_NUMBER()
OVER (PARTITION BY PERS_ID
ORDER BY CASE
WHEN APPT_DATE > current_date THEN 0
ELSE 1
END, APPT_DATE ASC) AS NEXT_APPT
FROM TABLE1
) D
WHERE last_appt = 1 OR next_appt = 1+----+---------+-----------+-----------+
| | pers_id | last_appt | next_appt |
+----+---------+-----------+-----------+
| 1 | 101 | 4 | 1 |
| 2 | 101 | 3 | 2 |
| 3 | 101 | 2 | 3 |
| 4 | 101 | 1 | 4 |
+----+---------+-----------+-----------+
+----+---------+-----------+-----------+
| | pers_id | last_appt | next_appt |
+----+---------+-----------+-----------+
| 1 | 101 | 4 | 1 |
| 2 | 101 | 1 | 4 |
+----+---------+-----------+-----------+
rank() and dense_rank() have calculation overheads that row_number() does not carry. Yet row_number() provides the same result:
with t as (
select 'A' code, 10 qty
union select 'A', 12
union select 'A', 2
union select 'B', 8
union select 'B', 5
union select 'C', 7
union select 'C', 13
)
select * from
(
select code, qty
, rank() over (partition by code order by qty desc) num_by_rank
, dense_rank() over (partition by code order by qty desc) num_by_denserank
, row_number() over (partition by code order by qty desc) num_by_row_number
from t
) x +----+------+-----+-------------+------------------+-------------------+
| | code | qty | num_by_rank | num_by_denserank | num_by_row_number |
+----+------+-----+-------------+------------------+-------------------+
| 1 | A | 12 | 1 | 1 | 1 |
| 2 | A | 10 | 2 | 2 | 2 |
| 3 | A | 2 | 3 | 3 | 3 |
| 4 | B | 8 | 1 | 1 | 1 |
| 5 | B | 5 | 2 | 2 | 2 |
| 6 | C | 13 | 1 | 1 | 1 |
| 7 | C | 7 | 2 | 2 | 2 |
+----+------+-----+-------------+------------------+-------------------+
forget about rank() :) it was late for me I guess...
what is the result of this?
it should give you 3 records, one for each program code with latest doc_date
then you can use this in cte and join with the rest of the query
what is the result of this?
SELECT *
FROM (SELECT gd.id,
gd.pers_id,
pgm.pgm_code,
GD.DOC_DATE,
ROW_NUMBER() OVER (PARTITION BY PERS_ID, PGM_CODE ORDER BY DOC_DATE DESC) rn
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND PGM_CODE in ('MC', 'FS', 'CW')
AND pgm.id = gd.pgm_id) x
WHERE RN = 1;it should give you 3 records, one for each program code with latest doc_date
then you can use this in cte and join with the rest of the query
ASKER
HI Huseyin,
I need doc_date,pgm_code columns for each document(MC,FS,CW).
My results should be like
pers_id_1 MC_DOC_DATE MC_PGM_CODE,FS_DOC_DATE FS_PGM_CODE,CW_DOC_DATE CW_PGM_CODE
pers_id_2 MC_DOC_DATE MC_PGM_CODE,FS_DOC_DATE FS_PGM_CODE,CW_DOC_DATE CW_PGM_CODE
The query you posted is returning results like:
pers_id_1 MC_DOC_DATE MC_PGM_CODE
pers_id_1 CW_DOC_DATE MC_PGM_CODE
pers_id_2 FS_DOC_DATE MC_PGM_CODE
pers_id_2 MC_DOC_DATE MC_PGM_CODE
I should have told how my expected results should be.
To get my expected results, I am working on below query. Please check it and let me know. This query is taking longer time.
Could you review it and Please let me know if any changes. Thanks.
I need doc_date,pgm_code columns for each document(MC,FS,CW).
My results should be like
pers_id_1 MC_DOC_DATE MC_PGM_CODE,FS_DOC_DATE FS_PGM_CODE,CW_DOC_DATE CW_PGM_CODE
pers_id_2 MC_DOC_DATE MC_PGM_CODE,FS_DOC_DATE FS_PGM_CODE,CW_DOC_DATE CW_PGM_CODE
The query you posted is returning results like:
pers_id_1 MC_DOC_DATE MC_PGM_CODE
pers_id_1 CW_DOC_DATE MC_PGM_CODE
pers_id_2 FS_DOC_DATE MC_PGM_CODE
pers_id_2 MC_DOC_DATE MC_PGM_CODE
I should have told how my expected results should be.
To get my expected results, I am working on below query. Please check it and let me know. This query is taking longer time.
Could you review it and Please let me know if any changes. Thanks.
/* Formatted on 6/1/2017 10:56:48 AM (QP5 v5.287) */
WITH NOA_GD_MC
AS (SELECT *
FROM (SELECT gd.id,
gd.pers_id,
GD.actn_code,
GD.DOC_DATE,
RANK ()
OVER (PARTITION BY PERS_ID, PGM_CODE, DOC_DATE
ORDER BY DOC_DATE DESC)
RN
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id
AND pgm_code = 'MC')
WHERE RN = 1),
NOA_GD_CW
AS (SELECT *
FROM (SELECT gd.id,
gd.pers_id,
GD.actn_code,
GD.DOC_DATE,
RANK ()
OVER (PARTITION BY PERS_ID, PGM_CODE, DOC_DATE
ORDER BY DOC_DATE DESC)
RN
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id
AND pgm_code = 'CW')
WHERE RN = 1),
NOA_GD_FS
AS (SELECT *
FROM (SELECT gd.id,
gd.pers_id,
GD.actn_code,
GD.DOC_DATE,
RANK ()
OVER (PARTITION BY PERS_ID, PGM_CODE, DOC_DATE
ORDER BY DOC_DATE DESC)
RN
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id
AND pgm_code = 'FS')
WHERE RN = 1)
SELECT CL.PERS_ID,
TO_CHAR (gd_cw.doc_date, 'YYYY-MM-DD HH24:MI:SS') CW_NOA_DATE,
gd_cw.actn_code CW_NOA_TYPE,
TO_CHAR (gd_fs.doc_date, 'YYYY-MM-DD HH24:MI:SS') FS_NOA_DATE,
gd_fs.actn_code FS_NOA_TYPE,
TO_CHAR (gd_mc.doc_date, 'YYYY-MM-DD HH24:MI:SS') MC_NOA_DATE,
gd_mc.actn_code MC_NOA_TYPE
FROM CALL_LOG CL
LEFT JOIN NOA_GD_MC GD_MC ON GD_MC.PERS_ID = CL.PERS_ID
LEFT JOIN NOA_GD_CW GD_CW ON GD_CW.PERS_ID = CL.PERS_ID
LEFT JOIN NOA_GD_FS GD_FS ON GD_FS.PERS_ID = CL.PERS_ID
WHERE COUNTY_CODE = 36;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Huseyin for your assistance. Thanks Paul.
do be careful about using old-fashioned joins when the majority of a query uses ANSI standard join syntax.
in the snippet below there is a join that is almost invisible
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id
AND pgm_code in ('MC','CW','FS')
suggested re-write
FROM generate_doc gd
INNER JOIN pgm ON pgm.id = gd.pgm_id
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm_code in ('MC','CW','FS')
make that small change and your query will become more maintainable
in the snippet below there is a join that is almost invisible
FROM generate_doc gd, pgm
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm.id = gd.pgm_id
AND pgm_code in ('MC','CW','FS')
suggested re-write
FROM generate_doc gd
INNER JOIN pgm ON pgm.id = gd.pgm_id
WHERE doc_date <= SYSDATE
AND noa_id IS NOT NULL
AND pgm_code in ('MC','CW','FS')
make that small change and your query will become more maintainable
ASKER
Hi Portlet Paul,
The query you gave for appointments has an issue. A person has an appointment on 8/1/2016. This date is compared with 5/1/2016.
when there is only one appointment(ex: 08/01/2016) for a person then both last and next appointment values are 1(as rownum is 1). This appointment should come in the next appointment only not in the last appointment. last appointment should show empty for that person. could you give some idea to work out this. Thanks.
The query you gave for appointments has an issue. A person has an appointment on 8/1/2016. This date is compared with 5/1/2016.
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE <= 05/01/2016 THEN 0 ELSE 1 END,
ca.APPT_DATE DESC)
AS LAST_APPT,
ROW_NUMBER ()
OVER (
PARTITION BY CP.PERS_ID
ORDER BY
CASE WHEN ca.APPT_DATE > 05/01/5216 THEN 0 ELSE 1 END,
ca.APPT_DATE ASC)
AS NEXT_APPTwhen there is only one appointment(ex: 08/01/2016) for a person then both last and next appointment values are 1(as rownum is 1). This appointment should come in the next appointment only not in the last appointment. last appointment should show empty for that person. could you give some idea to work out this. Thanks.
As this question is resolved, you should stop seeking further advice on that question.
The intention behind my suggestion was:
a: it would be FAR MORE EFFICIENT to pass through the appointments data once instead of several times
b: that the result would subsequently used as left joins like the following
However to avoid the condition you state you can extend that join logic like this:
INNER JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN CTE LA ON PA.pers_id = LA.PERS_ID AND LA.LAST_APPT = 1 AND APPT_DATE <= 05/01/2016
LEFT JOIN CTE NA ON PA.pers_id = NA.PERS_ID AND NA.NEXT_APPT = 1 AND APPT_DATE > 05/01/5216
Please don't fall into the practice of ongoing advice on a closed question
The intention behind my suggestion was:
a: it would be FAR MORE EFFICIENT to pass through the appointments data once instead of several times
b: that the result would subsequently used as left joins like the following
However to avoid the condition you state you can extend that join logic like this:
INNER JOIN pers@ATLG03 P ON P.id = PA.pers_id
LEFT JOIN LANG@ATLG03 L ON L.PERS_ID = P.ID
LEFT JOIN CTE LA ON PA.pers_id = LA.PERS_ID AND LA.LAST_APPT = 1 AND APPT_DATE <= 05/01/2016
LEFT JOIN CTE NA ON PA.pers_id = NA.PERS_ID AND NA.NEXT_APPT = 1 AND APPT_DATE > 05/01/5216
Please don't fall into the practice of ongoing advice on a closed question
Open in new window
also, use CODE to format your query before posting here