SELECT A.RNUM
, A.TOTCNT
, A.HMGPEDIA_ID
, A.GROUPNAME
, A.USE_YN
, A.LANG_CD
, A.COUNT
, A.THUMBNAIL_ID
, A.UPDATE_DATE
, B.ASSET_ID
, B.SAVE_FILE_NAME
, C.GRC_NAME
FROM (
SELECT
CEIL ((ROW_NUMBER() OVER(ORDER BY B.UPDATE_DATE DESC)+0.00) / (15+0.00)) AS RNUM
, COUNT(*) OVER() TOTCNT
, B.HMGPEDIA_GP
, B.HMGPEDIA_ID
, B.THUMBNAIL_ID
, B.USE_YN
, A.LANG_CD
, B.COUNT
, B.UPDATE_USER
, B.TAG_ID
, B.GROUPNAME
, TO_CHAR(B.UPDATE_DATE, 'YYYYMMDDHH24MISS') AS UPDATE_DATE
FROM(
SELECT
A.HMGPEDIA_ID
, AGGR_CONCAT(LANG_CD, ',' ORDER BY LANG_CD DESC) AS LANG_CD
FROM HMG.TB_HMGPEDIA A
WHERE 1=1
AND A.HMGPEDIA_GP = 1
AND A.DEL_YN = 'N'
AND C.GRC_NAME LIKE '%' || '위아' || '%'
GROUP BY A.HMGPEDIA_ID
) A JOIN HMG.TB_HMGPEDIA B
ON A.HMGPEDIA_ID = B.HMGPEDIA_ID
AND DECODE(INSTR(A.LANG_CD, ','), 0, A.LANG_CD, SUBSTR(A.LANG_CD, 0, INSTR(A.LANG_CD, ',') - 1)) = B.LANG_CD
) A LEFT JOIN HMG.TB_ATTACH_FILE B
ON A.THUMBNAIL_ID = B.ATTACH_ID
LEFT JOIN HMG.TB_MGMT_GROUPCOM C
ON A.GROUPNAME = C.CONTENTS_ID
WHERE RNUM = 1
ORDER BY A.UPDATE_DATE DESC
;
위 쿼리가 있는데요
중간에 AND C.GRC_NAME LIKE '%' || '위아' || '%'
이 쿼리를 사용하고 싶은데 C라는 부분이 제일 하단부에
LEFT JOIN HMG.TB_MGMT_GROUPCOM C
ON A.GROUPNAME = C.CONTENTS_ID
이렇게 조인이 되어 있어서 안쪽에서는 실행이 안되는거 같아요
그래서
FROM(
SELECT
A.HMGPEDIA_ID
, AGGR_CONCAT(LANG_CD, ',' ORDER BY LANG_CD DESC) AS LANG_CD
FROM HMG.TB_HMGPEDIA A
WHERE 1=1
AND A.HMGPEDIA_GP = 1
AND A.DEL_YN = 'N'
AND C.GRC_NAME LIKE '%' || '위아' || '%'
GROUP BY A.HMGPEDIA_ID
이 부분에서 FROM쪽에
FROM HMG.TB_HMGPEDIA A, HMG.TB_MGMT_GROUPCOM C
이렇게 해줬는데도...안되서 어떻게 해야할지...감이 안잡혀서
질문드립니다...도와주세요...
SELECT a.rnum , a.totcnt , a.hmgpedia_id , a.groupname , a.use_yn , a.lang_cd , a.count , a.thumbnail_id , a.update_date , b.asset_id , b.save_file_name , a.grc_name FROM (SELECT CEIL((ROW_NUMBER() OVER(ORDER BY b.update_date DESC)+0.00) / (15+0.00)) rnum , COUNT(*) OVER() totcnt , b.hmgpedia_gp , b.hmgpedia_id , b.thumbnail_id , b.use_yn , a.lang_cd , b.count , b.update_user , b.tag_id , b.groupname , TO_CHAR(b.update_date, 'yyyymmddhh24miss') update_date , c.grc_name FROM (SELECT hmgpedia_id , AGGR_CONCAT(lang_cd, ',' ORDER BY lang_cd DESC) AS lang_cd , MAX(lang_cd) AS max_lang_cd -- Decode 조건을 없애기 위해 추가 FROM hmg.tb_hmgpedia WHERE 1=1 AND hmgpedia_gp = 1 AND del_yn = 'N' GROUP BY hmgpedia_id ) a JOIN hmg.tb_hmgpedia b ON a.hmgpedia_id = b.hmgpedia_id AND a.max_lang_cd = b.lang_cd -- Decode 조건을 간결하게 JOIN hmg.tb_mgmt_groupcom c -- 조인을 서브쿼리 안으로 이동 ON b.groupname = c.contents_id WHERE c.grc_name LIKE '%' || '위아' || '%' -- 조건 ) a LEFT JOIN hmg.tb_attach_file b ON a.thumbnail_id = b.attach_id WHERE RNUM = 1 ORDER BY a.update_date DESC ;