SELECT A.CD_YMD_DATE
,A.CD_COMPANY
,A.CD_ROOM
,A.NM_ROOM
FROM
(
SELECT A.DT_INDATE AS CD_YMD_DATE
,A.CD_COMPANY AS CD_COMPANY
,R.CD_ROOM AS CD_ROOM
,R.NM_ROOM AS NM_ROOM
FROM STG_ERP_CZ_POR A JOIN STG_ERP_CZ_ITEM I ON A.CD_COMPANY = I.CD_COMPANY AND A.CD_ITEM = I.CD_ITEM
LEFT OUTER JOIN STG_ERP_MA_CODEDTL L ON I.CD_COMPANY = L.CD_COMPANY AND I.CD_CLASSL = L.CD_SYSDEF AND L.CD_FIELD = 'CZ_WH00001'
LEFT OUTER JOIN STG_ERP_CZ_STOCK S ON A.CD_COMPANY = S.CD_COMPANY AND A.CD_ROOM = S.CD_ROOM AND A.CD_STOCK = S.CD_STOCK AND S.USE_YN = 'Y'
LEFT OUTER JOIN STG_ERP_CZ_ROOM R ON A.CD_COMPANY = R.CD_COMPANY AND A.CD_ROOM = R.CD_ROOM
WHERE A.CD_COMPANY = '1001'
GROUP BY A.DT_INDATE, R.CD_ROOM, R.NM_ROOM, A.CD_COMPANY
ORDER BY 1 DESC) A RIGHT OUTER JOIN STG_ERP_CZ_ROOM B ON A.CD_COMPANY = B.CD_COMPANY AND A.CD_ROOM = B.CD_ROOM
WHERE B.YN_USE = 'Y'
AND A.CD_YMD_DATE = '20170717';
이런 select문이 있을때 STG_ERP_CZ_POR와 STG_ERP_CZ_ROOM(전체room정보 테이블)를 비교해서 STG_ERP_CZ_ROOM의 모든 cd_room정보를 기준으로 STG_ERP_CZ_POR에 있는 cd_room이면 o표시를 없는 cd_room이면 x표시를 하는 열을 추가하고 싶습니다.
가능할까요?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DT_CLOSE AS CD_YMD_DATE
,CD_ROOM AS CD_ROOM
,COUNT(DECODE(INOUT,'02',1)) AS INOUT
,COUNT(DECODE(SALE,'02',1)) AS SALE
,COUNT(DECODE(SALE1,'02',1)) AS SALE1
,COUNT(DECODE(OPER,'02',1)) AS OPER
FROM
(
SELECT A.CD_COMPANY AS CD_COMPANY ,
A.CD_ROOM AS CD_ROOM,
A.NM_ROOM AS NM_ROOM,
A.YN_USE AS YN_USE,
B.DT_CLOSE AS DT_CLOSE,
B.GB_CLOSE AS GB_CLOSE,
NVL(B.INOUT,'01') as INOUT, --입고재고마감
NVL(B.SALE,'01') as SALE, --식권마감
NVL(B.SALE1,'01') as SALE1, --식수마감
NVL(B.OPER,'01') as OPER --운영일지
FROM STG_ERP_CZ_ROOM A LEFT OUTER JOIN STG_ERP_CZ_ROOM_CLOSE B
ON A.CD_COMPANY = B.CD_COMPANY
AND A.CD_ROOM = B.CD_ROOM
AND B.GB_CLOSE = '02'
)
WHERE CD_COMPANY = '1001'
AND YN_USE ='Y'
AND DT_CLOSE IS NOT NULL
GROUP BY DT_CLOSE, CD_ROOM
ORDER BY 1 DESC;
그리고 위에 있는 테이블과 밑에있는 테이블의 열 INOUT, SALE, SALE1, OPER를 옆으로 붙일 수 있을까요?
조인이 필요한지 의문이네요?
- 1. 전혀 사용되지 않는 테이블 : i
- 2. 아우터 조인 되고 있으나 특별한 역할이 없는 테이블 : l, s, r
SELECT b.cd_company , b.cd_room , b.nm_room , a.cd_ymd_date , NVL(a.ox, 'x' ) ox , NVL(c.inout, 0) inout , NVL(c.sale , 0) sale , NVL(c.sale1, 0) sale1 , NVL(c.oper , 0) oper FROM stg_erp_cz_room b LEFT OUTER JOIN (SELECT DISTINCT -- 중복제거가 필요한지 확인 필요. cd_ymd_date , cd_company , cd_room , 'o' ox FROM stg_erp_cz_por WHERE cd_company = '1001' AND cd_ymd_date = '20170717' ) a ON a.cd_company = b.cd_company AND a.cd_room = b.cd_room LEFT OUTER JOIN (SELECT dt_close , cd_company , cd_room , COUNT(DECODE(inout, '02', 1)) AS inout , COUNT(DECODE(sale , '02', 1)) AS sale , COUNT(DECODE(sale1, '02', 1)) AS sale1 , COUNT(DECODE(oper , '02', 1)) AS oper FROM stg_erp_cz_room_close WHERE cd_company = '1001' AND dt_close = '20170717' AND gb_close = '02' GROUP BY dt_close, cd_company, cd_room ) c ON b.cd_company = c.cd_company AND b.cd_room = c.cd_room WHERE b.cd_company = '1001' AND b.yn_use = 'Y' ORDER BY cd_company, cd_room ;
SELECT '20170717' cd_ymd_date , b.cd_company , b.cd_room , b.nm_room , NVL2(a.cd_room, 'o', 'x') ox , NVL(c.inout, 0) inout , NVL(c.sale , 0) sale , NVL(c.sale1, 0) sale1 , NVL(c.oper , 0) oper FROM stg_erp_cz_room b LEFT OUTER JOIN (SELECT DISTINCT -- 중복제거가 필요한지 확인 필요. cd_company , cd_room FROM stg_erp_cz_por WHERE cd_company = '1001' AND cd_ymd_date = '20170717' ) a ON b.cd_company = a.cd_company AND b.cd_room = a.cd_room LEFT OUTER JOIN (SELECT cd_company , cd_room , COUNT(DECODE(inout, '02', 1)) AS inout , COUNT(DECODE(sale , '02', 1)) AS sale , COUNT(DECODE(sale1, '02', 1)) AS sale1 , COUNT(DECODE(oper , '02', 1)) AS oper FROM stg_erp_cz_room_close WHERE cd_company = '1001' AND dt_close = '20170717' AND gb_close = '02' GROUP BY cd_company, cd_room ) c ON b.cd_company = c.cd_company AND b.cd_room = c.cd_room WHERE b.cd_company = '1001' AND b.yn_use = 'Y' ORDER BY cd_company, cd_room ;
SELECT b.cd_company
, b.cd_room
, b.nm_room
, a.cd_ymd_date
, NVL(a.ox, 'x' ) ox
, NVL(c.inout, 0) inout
, NVL(c.sale , 0) sale
, NVL(c.sale1, 0) sale1
, NVL(c.oper , 0) oper
FROM stg_erp_cz_room b
LEFT OUTER JOIN (SELECT DISTINCT
dt_por cd_ymd_date
, cd_company
, cd_room
, 'o' ox
FROM stg_erp_cz_por
WHERE cd_company = '1001'
ORDER BY 1 desc
) a
ON a.cd_company = b.cd_company
AND a.cd_room = b.cd_room
LEFT OUTER JOIN (SELECT dt_close cd_ymd_date
, cd_company
, cd_room
, COUNT(DECODE(inout, '02', 1)) AS inout
, COUNT(DECODE(sale , '02', 1)) AS sale
, COUNT(DECODE(sale1, '02', 1)) AS sale1
, COUNT(DECODE(oper , '02', 1)) AS oper
FROM stg_erp_cz_room_close
WHERE cd_company = '1001'
AND gb_close = '02'
GROUP BY dt_close, cd_company, cd_room
ORDER BY 1 desc
) c
ON b.cd_company = c.cd_company
AND b.cd_room = c.cd_room
WHERE b.cd_company = '1001'
AND b.yn_use = 'Y'
ORDER BY cd_ymd_date desc, cd_room
아..전체날짜를 가지고 올때 그날짜에 stg_erp_cz_por에 없는 room에 관해서도 stg_erp_cz_room과 outer조인하여 stg_erp_cz_por의 cd_ymd_date의 날짜를 가져올 수 있는지가 궁금합니다.
SELECT d.cd_ymd_date , b.cd_company , b.cd_room , b.nm_room , NVL(d.ox, 'x' ) ox , NVL(d.inout, 0) inout , NVL(d.sale , 0) sale , NVL(d.sale1, 0) sale1 , NVL(d.oper , 0) oper FROM stg_erp_cz_room b LEFT OUTER JOIN (SELECT NVL(a.dt_por , c.dt_close ) cd_ymd_date , NVL(a.cd_company, c.cd_company) cd_company , NVL(a.cd_room , c.cd_room ) cd_room , a.ox , c.inout , c.sale , c.sale1 , c.oper FROM (SELECT DISTINCT dt_por , cd_company , cd_room , 'o' ox FROM stg_erp_cz_por WHERE cd_company = '1001' ) a FULL OUTER JOIN (SELECT dt_close , cd_company , cd_room , COUNT(DECODE(inout, '02', 1)) AS inout , COUNT(DECODE(sale , '02', 1)) AS sale , COUNT(DECODE(sale1, '02', 1)) AS sale1 , COUNT(DECODE(oper , '02', 1)) AS oper FROM stg_erp_cz_room_close WHERE cd_company = '1001' AND gb_close = '02' GROUP BY dt_close, cd_company, cd_room ) c ON a.dt_por = c.dt_close AND a.cd_company = c.cd_company AND a.cd_room = c.cd_room ) d PARTITION BY (d.cd_ymd_date) ON b.cd_company = d.cd_company AND b.cd_room = d.cd_room WHERE b.cd_company = '1001' AND b.yn_use = 'Y' ORDER BY cd_ymd_date DESC, cd_room ;