Hello experts,
I've never used a CTE and would like to know your inputs and need your help accordingly. I'm running the below query and would like to know how can I use a CTE as I'm using the cust_dim table repeatedly.
I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SELECT cust_a.cust_key,
MIN (cust_a.login_time) AS time_in,
MAX (cust_a.login_time) AS time_out,
CAST (CASE
WHEN cust_a.cust_num IN ('123123', '987987')
THEN 'NATIONAL'
ELSE 'INTERNATIONAL'
END AS VARCHAR2(8 CHAR)
) as cust_type ,
cust_a.cust_f_name,
'' AS cust_m_name,
cust_a.cust_l_name
FROM cust_log_a cust_a
WHERE p_dt = dev.process_dt('M')
AND NOT EXISTS
(SELECT *
FROM cust_dim ds
WHERE ds.cust_key = cust_a.cust_key
AND ds.cust_f_name = cust_a.cust_f_name
AND ds.cust_l_name = cust_a.cust_l_name
AND TO_CHAR (ds.time_in, 'YYYY-MM-DD') =
TO_CHAR (cust_a.login_time,
'YYYY-MM-DD'))
GROUP BY cust_a.cust_key,
CASE
WHEN cust_a.cust_num IN ('123123', '987987')
THEN
'NATIONAL'
ELSE
'INTERNATIONAL'
END,
cust_a.cust_f_name,
cust_a.cust_l_name
UNION ALL
SELECT cust_b.cust_key,
MIN (cust_b.login_time) AS time_in,
MAX (cust_b.login_time) AS time_out,
CAST (CASE
WHEN cust_b.cust_num IN ('123123', '987987')
THEN 'NATIONAL'
ELSE 'INTERNATIONAL'
END AS VARCHAR2(8 CHAR)
) AS cust_type,
cust_b.cust_f_name,
'' AS cust_m_name,
cust_b.cust_l_name
FROM cust_log_b cust_b
WHERE p_dt = dev.process_dt('M')
AND NOT EXISTS
(SELECT *
FROM cust_dim ds
WHERE ds.cust_key = cust_b.cust_key
AND ds.cust_f_name = cust_b.cust_f_name
AND ds.cust_l_name = cust_b.cust_l_name
AND TO_CHAR (ds.time_in, 'YYYY-MM-DD') =
TO_CHAR (cust_b.login_time,
'YYYY-MM-DD'))
GROUP BY cust_b.cust_key,
CASE
WHEN cust_b.cust_num IN ('123123', '987987')
THEN
'NATIONAL'
ELSE
'INTERNATIONAL'
END,
cust_b.cust_f_name,
cust_b.cust_l_name
UNION ALL
SELECT cust_c.cust_key,
MIN (cust_c.login_time) AS time_in,
MAX (cust_c.login_time) AS time_out,
CAST (CASE
WHEN cust_c.cust_num IN ('123123', '987987')
THEN 'NATIONAL'
ELSE 'INTERNATIONAL'
END AS VARCHAR2(8 CHAR)
) AS cust_type,
cust_c.cust_f_name,
'' AS cust_m_name,
cust_c.cust_l_name
FROM cust_log_c cust_c
WHERE p_dt = dev.process_dt('M')
AND NOT EXISTS
(SELECT *
FROM cust_dim ds
WHERE ds.cust_key = cust_c.cust_key
AND ds.cust_f_name = cust_c.cust_f_name
AND ds.cust_l_name = cust_c.cust_l_name
AND TO_CHAR (ds.time_in, 'YYYY-MM-DD') =
TO_CHAR (cust_c.login_time,
'YYYY-MM-DD'))
GROUP BY cust_c.cust_key,
CASE
WHEN cust_c.cust_num IN ('123123', '987987')
THEN
'NATIONAL'
ELSE
'INTERNATIONAL'
END,
cust_c.cust_f_name,
cust_c.cust_l_name
UNION ALL
SELECT cust_d.cust_key,
MIN (cust_d.login_time) AS time_in,
MAX (cust_d.login_time) AS time_out,
CAST (CASE
WHEN cust_d.cust_num IN ('123123', '987987')
THEN 'NATIONAL'
ELSE 'INTERNATIONAL'
END AS VARCHAR2(8 CHAR)
) AS cust_type,
cust_d.cust_f_name,
'' AS cust_m_name,
cust_d.cust_l_name
FROM cust_log_d cust_d
WHERE p_dt = dev.process_dt('M')
AND NOT EXISTS
(SELECT *
FROM cust_dim ds
WHERE ds.cust_key = cust_d.cust_key
AND ds.cust_f_name = cust_d.cust_f_name
AND ds.cust_l_name = cust_d.cust_l_name
AND TO_CHAR (ds.time_in, 'YYYY-MM-DD') =
TO_CHAR (cust_d.login_time,
'YYYY-MM-DD'))
GROUP BY cust_d.cust_key,
CASE
WHEN cust_d.cust_num IN ('123123', '987987')
THEN
'NATIONAL'
ELSE
'INTERNATIONAL'
END,
cust_d.cust_f_name,
cust_d.cust_l_name
DDL for the tables
CREATE TABLE CUST_LOG_A
( login_time TIMESTAMP (6),
cust_key VARCHAR2(50 CHAR),
cust_num VARCHAR2(9 CHAR),
cust_f_name VARCHAR2(50 CHAR),
cust_l_name VARCHAR2(50 CHAR),
login_name VARCHAR2(256 CHAR),
login_catgy VARCHAR2(50 CHAR),
login_txt VARCHAR2(75 CHAR),
search_txt VARCHAR2(1000 CHAR),
cust_code VARCHAR2(1 CHAR),
log_time NUMBER,
p_dt DATE
)
CREATE TABLE CUST_LOG_B
( login_time TIMESTAMP (6),
cust_key VARCHAR2(50 CHAR),
cust_num VARCHAR2(9 CHAR),
cust_f_name VARCHAR2(50 CHAR),
cust_l_name VARCHAR2(50 CHAR),
login_name VARCHAR2(256 CHAR),
login_catgy VARCHAR2(50 CHAR),
login_txt VARCHAR2(75 CHAR),
search_txt VARCHAR2(1000 CHAR),
cust_code VARCHAR2(1 CHAR),
log_time NUMBER,
p_dt DATE
)
CREATE TABLE CUST_LOG_C
( login_time TIMESTAMP (6),
cust_key VARCHAR2(50 CHAR),
cust_num VARCHAR2(9 CHAR),
cust_f_name VARCHAR2(50 CHAR),
cust_l_name VARCHAR2(50 CHAR),
login_name VARCHAR2(256 CHAR),
login_catgy VARCHAR2(50 CHAR),
login_txt VARCHAR2(75 CHAR),
search_txt VARCHAR2(1000 CHAR),
cust_code VARCHAR2(1 CHAR),
log_time NUMBER,
p_dt DATE
)
CREATE TABLE CUST_LOG_D
( login_time TIMESTAMP (6),
cust_key VARCHAR2(50 CHAR),
cust_num VARCHAR2(9 CHAR),
cust_f_name VARCHAR2(50 CHAR),
cust_l_name VARCHAR2(50 CHAR),
login_name VARCHAR2(256 CHAR),
login_catgy VARCHAR2(50 CHAR),
login_txt VARCHAR2(75 CHAR),
search_txt VARCHAR2(1000 CHAR),
cust_code VARCHAR2(1 CHAR),
log_time NUMBER,
p_dt DATE
)
Sample Data:
07-JAN-15 12.13.16.505000000 PM,IQK8-bmfiKum8S1wQLQGU_E,123456987,John,Paul,/cust/querysend.do,Inward,mnts.por.wck.web.actions.QuerySendAction,This_is_sample,D,71,07-JAN-15
07-JAN-15 12.13.16.992000000 PM,hH9LkFDuLbNf8FPbgZWOldf,234569871,Bunny,Ben, /cust/DownloadServlet,Outward,null,null,D,66,07-JAN-15
Thanks,
Julaayi
Added the DDL and sample data