| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109 |
- DROP VIEW VW_MAIN_CATEGORY;
- CREATE VIEW VW_MAIN_CATEGORY AS
- SELECT
- IF(CATE4_NO IS NULL, IF(CATE3_NO IS NULL, IF(CATE2_NO IS NULL, CATE1_NO, CATE2_NO), CATE3_NO),CATE4_NO) AS LEAF_CATE_NO
- , CATE1_NO
- , CATE1_NM
- , CATE2_NO
- , CATE2_NM
- , CATE3_NO
- , CATE3_NM
- , CATE4_NO
- , CATE4_NM
- , IF(CATE4_NO IS NULL, IF(CATE3_NO IS NULL, IF(CATE2_NO IS NULL, CONVERT(CATE1_NO,CHAR), CONVERT(CONCAT(CATE1_NO,'>',CATE2_NO),CHAR)),CONVERT((CONCAT(CATE1_NO,'>',CATE2_NO,'>',CATE3_NO)),CHAR)),
- CONVERT((CONCAT(CATE1_NO,'>',CATE2_NO,'>',CATE3_NO,'>',CATE4_NO)),CHAR)) AS FULL_CATE_NO
- , IF(CATE4_NM IS NULL, IF(CATE3_NM IS NULL, IF(CATE2_NM IS NULL, CONVERT(CATE1_NM,CHAR), CONVERT(CONCAT(CATE1_NM,'>',CATE2_NM),CHAR)),CONVERT((CONCAT(CATE1_NM,'>',CATE2_NM,'>',CATE3_NM)),CHAR)),
- CONVERT((CONCAT(CATE1_NM,'>',CATE2_NM,'>',CATE3_NM,'>',CATE4_NM)),CHAR)) AS FULL_CATE_NM
- , DISP_ORD
- , CLOC_PREFIX
- FROM( SELECT
- 0 AS CATE1_NO
- , '-----STYLE24-----' AS CATE1_NM
- , NULL AS CATE2_NO
- , NULL AS CATE2_NM
- , NULL AS CATE3_NO
- , NULL AS CATE3_NM
- , NULL AS CATE4_NO
- , NULL AS CATE4_NM
- , 1 AS DISP_ORD
- , NULL AS CLOC_PREFIX
- FROM DUAL
- UNION ALL
- SELECT
- 1700 AS CATE1_NO
- , '몰메인' AS CATE1_NM
- , NULL AS CATE2_NO
- , NULL AS CATE2_NM
- , NULL AS CATE3_NO
- , NULL AS CATE3_NM
- , NULL AS CATE4_NO
- , NULL AS CATE4_NM
- , 1 AS DISP_ORD
- , 'SMM' AS CLOC_PREFIX
- FROM DUAL
- UNION ALL
- SELECT
- 1711 AS CATE1_NO
- , '브랜드메인' AS CATE1_NM
- , NULL AS CATE2_NO
- , NULL AS CATE2_NM
- , NULL AS CATE3_NO
- , NULL AS CATE3_NM
- , NULL AS CATE4_NO
- , NULL AS CATE4_NM
- , 2 AS DISP_ORD
- , 'SBM' AS CLOC_PREFIX
- FROM DUAL
- UNION ALL
- SELECT
- CATE1_NO
- ,CATE1_NM
- ,CATE2_NO
- ,CATE2_NM
- ,NULL AS CATE3_NO
- ,NULL AS CATE3_NM
- ,NULL AS CATE4_NO
- ,NULL AS CATE4_NM
- ,3 AS DISP_ORD
- ,'SCM' AS CLOC_PREFIX
- FROM (
- SELECT 1712 AS CATE1_NO
- , '카테고리메인' AS CATE1_NM
- , A.CATE1_NO AS CATE2_NO
- , A.CATE1_NM AS CATE2_NM
- , RANK() OVER(ORDER BY A.CATE1_NO) AS DISP_ORD
- FROM TB_CATE_4SRCH A
- WHERE A.SITE_CD = 'G000_10'
- AND A.CATE_GB = 'G032_101'
- AND A.CATE_TYPE = 'G031_10'
- GROUP BY A.CATE1_NO, A.CATE1_NM
- ) A
- UNION ALL
- SELECT
- 1713 AS CATE1_NO
- , '아울렛메인' AS CATE1_NM
- , NULL AS CATE2_NO
- , NULL AS CATE2_NM
- , NULL AS CATE3_NO
- , NULL AS CATE3_NM
- , NULL AS CATE4_NO
- , NULL AS CATE4_NM
- , 4 AS DISP_ORD
- , 'SOM' AS CLOC_PREFIX
- FROM DUAL
- UNION ALL
- SELECT
- 1715 AS CATE1_NO
- , '광고상품메인' AS CATE1_NM
- , NULL AS CATE2_NO
- , NULL AS CATE2_NM
- , NULL AS CATE3_NO
- , NULL AS CATE3_NM
- , NULL AS CATE4_NO
- , NULL AS CATE4_NM
- , 6 AS DISP_ORD
- , 'SAD' AS CLOC_PREFIX
- FROM DUAL
- ) B
- ;
|
