데이터베이스 질문과 답변
ANSI 쿼리를 오라클 쿼리로 바꿨는데 두개 데이터수가 다르게 나와서요 ㅠㅠㅠ 도와주셔여~!
==========================기존쿼리=========================
SELECT count(*)
FROM (SELECT DISTINCT T1.PROD_CD AS PROD_CD,
T1.PROD_NM AS PROD_NM,
T1.PROD_REG_STS_CD AS PROD_REG_STS_CD,
T1.PROD_REG_STS_NM AS PROD_REG_STS_NM,
T1.LGF_PROD_YN AS LGF_PROD_YN,
T1.MD_ID AS RSPSB_MD_ID,
T3.MGR_NM AS RSPSB_MD_NM,
T1.FORMAL_GB_CD AS FORMAL_GB_CD,
T1.FORMAL_GB_NM AS FORMAL_GB_NM,
CASE
WHEN T1.LGF_PROD_YN = 'N' THEN '90'
ELSE
CASE
WHEN T1.FORMAL_GB_CD IN ('{',
'}') THEN T1.FORMAL_GB_CD
WHEN T1.FORMAL_GB_CD IN ('10',
'30') THEN '10'
WHEN T1.FORMAL_GB_CD IN ('20',
'40',
'50') THEN '20'
ELSE '}'
END
END AS FORMAL_CTGR_CD,
CASE
WHEN T1.LGF_PROD_YN = 'N' THEN '입점'
ELSE
CASE
WHEN T1.FORMAL_GB_CD = '{' THEN '-'
WHEN T1.FORMAL_GB_CD IN ('10',
'30') THEN '정상'
WHEN T1.FORMAL_GB_CD IN ('20',
'40',
'50') THEN '이월'
ELSE '미정의'
END
END AS FORMAL_CTGR_NM,
CASE
WHEN T1.PROD_REG_STS_CD = '90'
AND T1.PCHS_PSB_YN = 'Y'
AND T1.PROD_TYPE_CD = '10' THEN 'Y'
ELSE 'N'
END AS DSPL_PROD_YN,
T1.BRND_CD AS BRND_CD,
T1.BRND_NM AS BRND_NM,
Nvl(T1.SAP_BRND_CD, '{') AS SAP_BRND_CD,
CASE
WHEN T1.SAP_BRND_CD IS NULL
OR T1.SAP_BRND_CD = '{' THEN '-'
ELSE
CASE
WHEN T2.ZBRAND IS NULL THEN '미정의'
ELSE T2.ZBRTEXT
END
END AS SAP_BRND_NM,
T1.ITEM_KIND_CD AS ITEM_KIND_CD,
T1.ITEM_KIND_NM AS ITEM_KIND_NM,
T1.STD_PROD_CTGR_ID AS STD_PROD_CTGR_ID,
T1.STD_PROD_CTGR_NM AS STD_PROD_CTGR_NM,
T1.STD_PROD_LCTGR_ID AS STD_PROD_LCTGR_ID,
T1.STD_PROD_LCTGR_NM AS STD_PROD_LCTGR_NM,
T1.STD_PROD_MCTGR_ID AS STD_PROD_MCTGR_ID,
T1.STD_PROD_MCTGR_NM AS STD_PROD_MCTGR_NM,
T1.STD_PROD_SCTGR_ID AS STD_PROD_SCTGR_ID,
T1.STD_PROD_SCTGR_NM AS STD_PROD_SCTGR_NM,
T1.PROD_TYPE_CD AS PROD_TYPE_CD,
T1.PROD_TYPE_NM AS PROD_TYPE_NM,
T1.STYL_YY AS STYL_YY,
T1.SESN_CD AS SESN_CD,
T1.SESN_NM AS SESN_NM,
T1.CLR_CD AS CLR_CD,
T1.CLR_NM AS CLR_NM,
T1.CLR_TONE_CD AS CLR_TONE_CD,
T1.CLR_TONE_NM AS CLR_TONE_NM,
T1.PLAN_GRAD_CD AS PLAN_GRAD_CD,
T1.STCK_GRAD_CD AS STCK_GRAD_CD,
T1.PROD_FST_CUST_PRC AS PROD_FST_CUST_PRC,
T1.CRNT_SALE_PRC AS CRNT_SALE_PRC,
T1.PRC_CHNG_DT AS PRC_CHNG_DT,
T1.CHNG_BFR_PRC AS CHNG_BFR_PRC,
T1.PROD_PRC_RNG_CD AS PROD_PRC_RNG_CD,
T1.PROD_PRC_RNG_NM AS PROD_PRC_RNG_NM,
T1.VUCH_YN AS VUCH_YN,
T1.SET_PROD_YN AS SET_PROD_YN,
T1.DC_RATE AS DC_RATE,
T1.BASE_DLVR_FEE AS BASE_DLVR_FEE,
T1.IMDT_DC_MILG_USE_PSB_YN AS IMDT_DC_MILG_USE_PSB_YN,
T1.LMILG_ACL_RATE AS LMILG_ACL_RATE,
T1.ACLM_ACL_RATE AS ACLM_ACL_RATE,
T1.SALE_FEE_RATE AS SALE_FEE_RATE,
T1.MBIL_LMILG_ACL_RATE AS MBIL_LMILG_ACL_RATE,
T1.MBIL_ACLM_ACL_RATE AS MBIL_ACLM_ACL_RATE,
T1.MBIL_IMDT_DC_MILG_USE_PSB_YN AS MBIL_IMDT_DC_MILG_USE_PSB_YN,
T1.RTN_DLVR_FREE_YN AS RTN_DLVR_FREE_YN,
T1.EXCH_DLVR_FREE_YN AS EXCH_DLVR_FREE_YN,
T1.PRC_EXPS_YN AS PRC_EXPS_YN,
T1.OPT_SETP_YN AS OPT_SETP_YN,
T1.ITEM_ORD_PSB_YN AS ITEM_ORD_PSB_YN,
T1.CPNT_YN AS CPNT_YN,
T1.PCHS_PSB_YN AS PCHS_PSB_YN,
T1.PROD_SEX_GB_CD AS PROD_SEX_GB_CD,
T1.PROD_SEX_GB_NM AS PROD_SEX_GB_NM,
T1.NLINE_EXCSV_PROD_GB_CD AS NLINE_EXCSV_PROD_GB_CD,
T1.NLINE_EXCSV_PROD_GB_NM AS NLINE_EXCSV_PROD_GB_NM,
T1.ACL_RATE_LGC_SCRN_YN AS ACL_RATE_LGC_SCRN_YN,
T1.FLINE_SHOP_MEND_PSB_YN AS FLINE_SHOP_MEND_PSB_YN,
T1.ORD_MUFT_YN AS ORD_MUFT_YN,
T1.EXCH_PSB_YN AS EXCH_PSB_YN,
T1.RTN_PSB_YN AS RTN_PSB_YN,
T1.KC_CERT_GB_CD AS KC_CERT_GB_CD,
T1.KC_CERT_GB_NM AS KC_CERT_GB_NM,
T1.DLVR_MTHD_CD AS DLVR_MTHD_CD,
T1.DLVR_MTHD_NM AS DLVR_MTHD_NM,
T1.MTRL_DESC AS MTRL_DESC,
T1.SPLY_CO_CD AS SPLY_CO_CD,
T1.SPLY_CO_NM AS SPLY_CO_NM,
T1.SPLY_CO_PROD_CD AS SPLY_CO_PROD_CD,
T1.MUFT_NM AS MUFT_NM,
T1.ORGN_NM AS ORGN_NM,
T1.REG_ID AS REG_ID,
T1.REG_DT AS REG_DT,
T1.UP_ID AS UP_ID,
T1.UP_DT AS UP_DT
FROM (SELECT DISTINCT T1.PROD_CD AS PROD_CD,
T1.PROD_NM AS PROD_NM,
Decode(T1.PROD_STS_CD, NULL, '{', Decode(T2.CD_ID, NULL, '}', T2.CD_ID)) AS PROD_REG_STS_CD,
Decode(T1.PROD_STS_CD, NULL, '-', Decode(T2.CD_ID, NULL, '미정의', T2.CD_NM)) AS PROD_REG_STS_NM,
Decode(T1.PROD_LGF_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS LGF_PROD_YN,
T1.MD_ID AS MD_ID,
Decode(T1.FORMAL_GB, NULL, '{', Decode(T3.CD_ID, NULL, '}', T3.CD_ID)) AS FORMAL_GB_CD,
Decode(T1.FORMAL_GB, NULL, '-', Decode(T3.CD_ID, NULL, '미정의', T3.CD_NM)) AS FORMAL_GB_NM,
Decode(T1.BRAND_CD, NULL, '{', Decode(T4.BRAND_CD, NULL, '}', T4.BRAND_CD)) AS BRND_CD,
Decode(T1.BRAND_CD, NULL, '-', Decode(T4.BRAND_CD, NULL, '미정의', T4.BRAND_NM)) AS BRND_NM,
CASE
WHEN Substr(T1.PROD_CD, 1, 3) = 'LGT' THEN '{'
WHEN T1.ATTR_C3 IS NULL THEN '{'
ELSE
CASE
WHEN T1.PROD_LGF_YN = 'Y'
AND T1.SET_YN = 'N' THEN
CASE
WHEN T5.ZRBRAND IN ('7O',
'9Y')
AND Substr(T1.PROD_CD, 1, 2) IN ('OU',
'OC') THEN T6.ZBRAND_ORG
ELSE T5.ZBRAND
END
ELSE T1.ATTR_C3
END
END AS SAP_BRND_CD,
Decode(T1.ITEMKIND_CD, NULL, '{', Decode(T7.ITEMKIND_CD, NULL, '}', T7.ITEMKIND_CD)) AS ITEM_KIND_CD,
Decode(T1.ITEMKIND_CD, NULL, '-', Decode(T7.ITEMKIND_CD, NULL, '미정의', T7.ITEMKIND_NM)) AS ITEM_KIND_NM,
T1.STD_PROD_CTGR_ID AS STD_PROD_CTGR_ID,
T1.STD_PROD_CTGR_NM AS STD_PROD_CTGR_NM,
T1.STD_PROD_LCTGR_ID AS STD_PROD_LCTGR_ID,
T1.STD_PROD_LCTGR_NM AS STD_PROD_LCTGR_NM,
T1.STD_PROD_MCTGR_ID AS STD_PROD_MCTGR_ID,
T1.STD_PROD_MCTGR_NM AS STD_PROD_MCTGR_NM,
T1.STD_PROD_SCTGR_ID AS STD_PROD_SCTGR_ID,
T1.STD_PROD_SCTGR_NM AS STD_PROD_SCTGR_NM,
Decode(T1.PROD_TYPE, NULL, '{', Decode(T8.CD_ID, NULL, '}', T8.CD_ID)) AS PROD_TYPE_CD,
Decode(T1.PROD_TYPE, NULL, '-', Decode(T8.CD_ID, NULL, '미정의', T8.CD_NM)) AS PROD_TYPE_NM,
T1.STYLE_YY AS STYL_YY,
Decode(T1.SEASON_CD, NULL, '{', Decode(T9.CD_ID, NULL, '}', T9.CD_ID)) AS SESN_CD,
Decode(T1.SEASON_CD, NULL, '-', Decode(T9.CD_ID, NULL, '미정의', T9.CD_NM)) AS SESN_NM,
Decode(T1.COLOR_1_CD, NULL, '{', Decode(T10.COLOR_1_CD, NULL, '}', T10.COLOR_1_CD)) AS CLR_CD,
Decode(T1.COLOR_1_CD, NULL, '-', Decode(T10.COLOR_1_CD, NULL, '미정의', T10.COLOR_HNM)) AS CLR_NM,
Decode(T1.TONE_CD, NULL, '{', Decode(T11.CD_ID, NULL, '}', T11.CD_ID)) AS CLR_TONE_CD,
Decode(T1.TONE_CD, NULL, '-', Decode(T11.CD_ID, NULL, '미정의', T11.CD_NM)) AS CLR_TONE_NM,
Decode(T1.ATTR_C1, NULL, '{', T1.ATTR_C1) AS PLAN_GRAD_CD,
Decode(T1.ATTR_C2, NULL, '{', T1.ATTR_C2) AS STCK_GRAD_CD,
T1.LIST_PRICE AS PROD_FST_CUST_PRC,
T1.PROD_PRICE AS CRNT_SALE_PRC,
Decode(F_chk_date(To_char(T1.PRICEUP_DT, 'YYYYMMDD')), 0, To_date(NULL, 'YYYYMMDD'), T1.PRICEUP_DT) AS PRC_CHNG_DT,
T1.OLD_PRICE AS CHNG_BFR_PRC,
Decode(T1.PRICE_CD, NULL, '{', Decode(T12.CD_ID, NULL, '}', T12.CD_ID)) AS PROD_PRC_RNG_CD,
Decode(T1.PRICE_CD, NULL, '-', Decode(T12.CD_ID, NULL, '미정의', T12.CD_NM)) AS PROD_PRC_RNG_NM,
Decode(T1.VOUCHER_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS VUCH_YN,
Decode(T1.SET_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS SET_PROD_YN,
Cast(T1.DC_RATE AS NUMERIC(10, 2)) AS DC_RATE,
T1.DELIVERY_FEE AS BASE_DLVR_FEE,
Decode(T1.PRE_LPOINT_USEGB, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS IMDT_DC_MILG_USE_PSB_YN,
Cast(T1.LPOINT_RATE AS NUMERIC(10, 2)) AS LMILG_ACL_RATE,
Cast(T1.SPOINT_RATE AS NUMERIC(10, 2)) AS ACLM_ACL_RATE,
Cast(T1.SELL_FEE_RATE AS NUMERIC(10, 2)) AS SALE_FEE_RATE,
Cast(T1.LPOINT_MRATE AS NUMERIC(10, 2)) AS MBIL_LMILG_ACL_RATE,
Cast(T1.SPOINT_MRATE AS NUMERIC(10, 2)) AS MBIL_ACLM_ACL_RATE,
Decode(T1.PRE_LPOINT_MUSEGB, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS MBIL_IMDT_DC_MILG_USE_PSB_YN,
Decode(T1.RETURN_FEE_FREE_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS RTN_DLVR_FREE_YN,
Decode(T1.CHANGE_FEE_FREE_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS EXCH_DLVR_FREE_YN,
Decode(T1.PRICE_DP_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS PRC_EXPS_YN,
Decode(T1.OPTION_SET_YN, NULL, '{', 'Y', 'Y', 'N', 'N', 'y', 'Y', 'n', 'N', '}') AS OPT_SETP_YN,
Decode(T1.ITEM_ORD_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS ITEM_ORD_PSB_YN,
Decode(T1.GS_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS CPNT_YN,
Decode(T1.BUY_ABLEYN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS PCHS_PSB_YN,
Decode(T1.PROD_SEX_GB, NULL, '{', Decode(T13.CD_ID, NULL, '}', T13.CD_ID)) AS PROD_SEX_GB_CD,
Decode(T1.PROD_SEX_GB, NULL, '{', Decode(T13.CD_ID, NULL, '}', T13.CD_NM)) AS PROD_SEX_GB_NM,
Decode(T1.ONLINE_PROD_GB, NULL, '{', Decode(T14.CD_ID, NULL, '}', T14.CD_ID)) AS NLINE_EXCSV_PROD_GB_CD,
Decode(T1.ONLINE_PROD_GB, NULL, '-', Decode(T14.CD_ID, NULL, '미정의', T14.CD_NM)) AS NLINE_EXCSV_PROD_GB_NM,
Decode(T1.POINT_RATE_LINK_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS ACL_RATE_LGC_SCRN_YN,
Decode(T1.OFFLINE_MEND_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS FLINE_SHOP_MEND_PSB_YN,
Decode(T1.ORDER_MAKING_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS ORD_MUFT_YN,
Decode(T1.CHANGE_ABLEYN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS EXCH_PSB_YN,
Decode(T1.RETURN_ABLEYN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS RTN_PSB_YN,
Decode(T1.KC_GB, NULL, '{', Decode(T15.CD_ID, NULL, '}', T15.CD_ID)) AS KC_CERT_GB_CD,
Decode(T1.KC_GB, NULL, '{', Decode(T15.CD_ID, NULL, '}', T15.CD_NM)) AS KC_CERT_GB_NM,
Decode(T1.DELIVERY_METHOD, NULL, '{', Decode(T16.CD_ID, NULL, '}', T16.CD_ID)) AS DLVR_MTHD_CD,
Decode(T1.DELIVERY_METHOD, NULL, '{', Decode(T16.CD_ID, NULL, '}', T16.CD_NM)) AS DLVR_MTHD_NM,
Trim(T1.MATERIAL_DESC) AS MTRL_DESC,
Decode(T17.SUPPLY_ENTR_CD, NULL, '{' , Decode(T18.SUPPLY_ENTR_CD, NULL, '}', T18.SUPPLY_ENTR_CD)) AS SPLY_CO_CD,
Decode(T17.SUPPLY_ENTR_CD, NULL, '-' , Decode(T18.SUPPLY_ENTR_CD, NULL, '미정의', T18.SUPPLY_ENTR_NM)) AS SPLY_CO_NM,
T1.SUPPLY_PROD_CD AS SPLY_CO_PROD_CD,
T17.MAKE_NM AS MUFT_NM,
T17.NATIVE_NM AS ORGN_NM,
T1.REG_ID AS REG_ID,
T1.REG_DT AS REG_DT,
T1.UP_ID AS UP_ID,
T1.UP_DT AS UP_DT
FROM (SELECT T1.PROD_CD,
T1.PROD_NM,
T1.VOUCHER_YN,
T1.PROD_LGF_YN,
T1.BRAND_CD,
T1.ITEMKIND_CD,
T1.STYLE_YY,
T1.SEASON_CD,
T1.COLOR_1_CD,
T1.SET_YN,
T1.LIST_PRICE,
T1.PROD_PRICE,
T1.DC_RATE,
T1.PRICE_CD,
T1.MATERIAL_DESC,
T1.PROD_STS_CD,
T1.SUPPLY_PROD_CD,
T1.PRICEUP_DT,
T1.OLD_PRICE,
T1.DELIVERY_FEE,
T1.MD_ID,
T1.LPOINT_RATE,
T1.SPOINT_RATE,
T1.SELL_FEE_RATE,
T1.FORMAL_GB,
T1.CHANGE_ABLEYN,
T1.RETURN_ABLEYN,
T1.RETURN_FEE_FREE_YN,
T1.CHANGE_FEE_FREE_YN,
T1.PRE_LPOINT_USEGB,
T1.PRICE_DP_YN,
T1.OPTION_SET_YN,
T1.ITEM_ORD_YN,
T1.TONE_CD,
T1.PROD_TYPE,
T1.GS_YN,
T1.BUY_ABLEYN,
T1.LPOINT_MRATE,
T1.SPOINT_MRATE,
T1.PRE_LPOINT_MUSEGB,
T1.PROD_SEX_GB,
T1.ONLINE_PROD_GB,
T1.POINT_RATE_LINK_YN,
T1.OFFLINE_MEND_YN,
T1.ATTR_C1,
T1.ATTR_C2,
T1.ATTR_C3,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE T2.STD_PROD_CTGR_ID
END
END AS STD_PROD_CTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE T2.STD_PROD_CTGR_NM
END
END AS STD_PROD_CTGR_NM,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE
CASE
WHEN T2.STD_PROD_LCTGR_ID IS NULL THEN 0
ELSE T2.STD_PROD_LCTGR_ID
END
END
END AS STD_PROD_LCTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE
CASE
WHEN T2.STD_PROD_LCTGR_ID IS NULL THEN '-'
ELSE T2.STD_PROD_LCTGR_NM
END
END
END AS STD_PROD_LCTGR_NM,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE
CASE
WHEN T2.STD_PROD_MCTGR_ID IS NULL THEN 0
ELSE T2.STD_PROD_MCTGR_ID
END
END
END AS STD_PROD_MCTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE
CASE
WHEN T2.STD_PROD_MCTGR_ID IS NULL THEN '-'
ELSE T2.STD_PROD_MCTGR_NM
END
END
END AS STD_PROD_MCTGR_NM,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE
CASE
WHEN T2.STD_PROD_SCTGR_ID IS NULL THEN 0
ELSE T2.STD_PROD_SCTGR_ID
END
END
END AS STD_PROD_SCTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE
CASE
WHEN T2.STD_PROD_SCTGR_ID IS NULL THEN '-'
ELSE T2.STD_PROD_SCTGR_NM
END
END
END AS STD_PROD_SCTGR_NM,
T1.DELIVERY_METHOD,
T1.ORDER_MAKING_YN,
T1.KC_GB,
T1.REG_ID,
T1.REG_DT,
T1.UP_ID,
T1.UP_DT
FROM WLGF_STB_PRODUCT T1 /* WLGF_상품 */
LEFT JOIN D_STD_PROD_CTGR_LST T2 /* 기준상품분류내역 */
ON Nvl(T1.STANDARDCATEGORYID, 0) = T2.STD_PROD_CTGR_ID) T1 LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE SRC_SYS_GB_CD = 'M'
AND CD_GRP_ID = 'G210'
AND CD_USE_YN = 'Y') T2 ON T1.PROD_STS_CD = T2.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE SRC_SYS_GB_CD = 'M'
AND CD_GRP_ID = 'C120'
AND CD_USE_YN = 'Y') T3 ON T1.FORMAL_GB = T3.CD_ID LEFT JOIN WLGF_STB_BRAND T4 /* WLGF_브랜드 */
ON T1.BRAND_CD = T4.BRAND_CD LEFT JOIN WLGF_LST_SAP_BRAND_MASTER T5 /* WLGF_SAP 브랜드 마스터 */
ON T1.ATTR_C3 = T5.ZBRAND LEFT JOIN WLGF_LST_SAP_BRAND_MASTER T6 /* WLGF_SAP 브랜드 마스터 */
ON T1.ATTR_C3 = T6.ZBRAND
AND Substr(T1.PROD_CD, 1, 2) = T6.ZBRAND_DTL
AND Substr(T1.PROD_CD, 7, 1) = T6.ZBRAND_OU LEFT JOIN WLGF_STB_ITEMKIND T7 /* WLGF_브랜드별품목 */
ON T1.BRAND_CD = T7.BRAND_CD
AND T1.ITEMKIND_CD = T7.ITEMKIND_CD LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G200'
AND CD_USE_YN = 'Y') T8 ON T1.PROD_TYPE = T8.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G201'
AND CD_USE_YN = 'Y') T9 ON T1.SEASON_CD = T9.CD_ID LEFT JOIN WLGF_STB_COLOR T10 /* WLGF_색상 */
ON T1.COLOR_1_CD = T10.COLOR_1_CD LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'C000'
AND CD_USE_YN = 'Y') T11 ON T1.TONE_CD = T11.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G290'
AND CD_USE_YN = 'Y') T12 ON T1.PRICE_CD = T12.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G222'
AND CD_USE_YN = 'Y') T13 ON T1.PROD_SEX_GB = T13.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G215'
AND CD_USE_YN = 'Y') T14 ON T1.ONLINE_PROD_GB = T14.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G954'
AND CD_USE_YN = 'Y') T15 ON T1.KC_GB = T15.CD_ID LEFT JOIN (SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G953'
AND CD_USE_YN = 'Y') T16 ON T1.DELIVERY_METHOD = T16.CD_ID LEFT JOIN WLGF_STB_ITEM T17 /* WLGF_단품 */
ON T1.PROD_CD = T17.ITEM_CD LEFT JOIN WLGF_STB_SUPPLY_ENTR T18 /* WLGF_입점업체 */
ON T17.SUPPLY_ENTR_CD = T18.SUPPLY_ENTR_CD) T1 LEFT JOIN (SELECT DISTINCT ZBRAND, ZBRTEXT
FROM WLGF_LST_SAP_BRAND_MASTER /* WLGF_SAP 브랜드 마스터 */) T2 ON T1.SAP_BRND_CD = T2.ZBRAND LEFT JOIN WLGF_LST_MANAGER T3 /* WLGF_관리자.신규관리자등록에의한발생 */
ON T1.MD_ID = T3.MGR_ID) T1 LEFT OUTER JOIN D_PROD_DEPT_LST T2 ON T1.PROD_CD = T2.PROD_CD
AND T1.FORMAL_CTGR_CD = T2.FORMAL_CTGR_CD
==================================수정 후 쿼리
/* INSERT SQL */
SELECT count(*)
FROM (SELECT DISTINCT T1.PROD_CD AS PROD_CD,
T1.PROD_NM AS PROD_NM,
T1.PROD_REG_STS_CD AS PROD_REG_STS_CD,
T1.PROD_REG_STS_NM AS PROD_REG_STS_NM,
T1.LGF_PROD_YN AS LGF_PROD_YN,
T1.MD_ID AS RSPSB_MD_ID,
T3.MGR_NM AS RSPSB_MD_NM,
T1.FORMAL_GB_CD AS FORMAL_GB_CD,
T1.FORMAL_GB_NM AS FORMAL_GB_NM,
CASE
WHEN T1.LGF_PROD_YN = 'N' THEN '90'
ELSE
CASE
WHEN T1.FORMAL_GB_CD IN ('{',
'}') THEN T1.FORMAL_GB_CD
WHEN T1.FORMAL_GB_CD IN ('10',
'30') THEN '10'
WHEN T1.FORMAL_GB_CD IN ('20',
'40',
'50') THEN '20'
ELSE '}'
END
END AS FORMAL_CTGR_CD,
CASE
WHEN T1.LGF_PROD_YN = 'N' THEN '입점'
ELSE
CASE
WHEN T1.FORMAL_GB_CD = '{' THEN '-'
WHEN T1.FORMAL_GB_CD IN ('10',
'30') THEN '정상'
WHEN T1.FORMAL_GB_CD IN ('20',
'40',
'50') THEN '이월'
ELSE '미정의'
END
END AS FORMAL_CTGR_NM,
CASE
WHEN T1.PROD_REG_STS_CD = '90'
AND T1.PCHS_PSB_YN = 'Y'
AND T1.PROD_TYPE_CD = '10' THEN 'Y'
ELSE 'N'
END AS DSPL_PROD_YN,
T1.BRND_CD AS BRND_CD,
T1.BRND_NM AS BRND_NM,
Nvl(T1.SAP_BRND_CD, '{') AS SAP_BRND_CD,
CASE
WHEN T1.SAP_BRND_CD IS NULL
OR T1.SAP_BRND_CD = '{' THEN '-'
ELSE
CASE
WHEN T2.ZBRAND IS NULL THEN '미정의'
ELSE T2.ZBRTEXT
END
END AS SAP_BRND_NM,
T1.ITEM_KIND_CD AS ITEM_KIND_CD,
T1.ITEM_KIND_NM AS ITEM_KIND_NM,
T1.STD_PROD_CTGR_ID AS STD_PROD_CTGR_ID,
T1.STD_PROD_CTGR_NM AS STD_PROD_CTGR_NM,
T1.STD_PROD_LCTGR_ID AS STD_PROD_LCTGR_ID,
T1.STD_PROD_LCTGR_NM AS STD_PROD_LCTGR_NM,
T1.STD_PROD_MCTGR_ID AS STD_PROD_MCTGR_ID,
T1.STD_PROD_MCTGR_NM AS STD_PROD_MCTGR_NM,
T1.STD_PROD_SCTGR_ID AS STD_PROD_SCTGR_ID,
T1.STD_PROD_SCTGR_NM AS STD_PROD_SCTGR_NM,
T1.PROD_TYPE_CD AS PROD_TYPE_CD,
T1.PROD_TYPE_NM AS PROD_TYPE_NM,
T1.STYL_YY AS STYL_YY,
T1.SESN_CD AS SESN_CD,
T1.SESN_NM AS SESN_NM,
T1.CLR_CD AS CLR_CD,
T1.CLR_NM AS CLR_NM,
T1.CLR_TONE_CD AS CLR_TONE_CD,
T1.CLR_TONE_NM AS CLR_TONE_NM,
T1.PLAN_GRAD_CD AS PLAN_GRAD_CD,
T1.STCK_GRAD_CD AS STCK_GRAD_CD,
T1.PROD_FST_CUST_PRC AS PROD_FST_CUST_PRC,
T1.CRNT_SALE_PRC AS CRNT_SALE_PRC,
T1.PRC_CHNG_DT AS PRC_CHNG_DT,
T1.CHNG_BFR_PRC AS CHNG_BFR_PRC,
T1.PROD_PRC_RNG_CD AS PROD_PRC_RNG_CD,
T1.PROD_PRC_RNG_NM AS PROD_PRC_RNG_NM,
T1.VUCH_YN AS VUCH_YN,
T1.SET_PROD_YN AS SET_PROD_YN,
T1.DC_RATE AS DC_RATE,
T1.BASE_DLVR_FEE AS BASE_DLVR_FEE,
T1.IMDT_DC_MILG_USE_PSB_YN AS IMDT_DC_MILG_USE_PSB_YN,
T1.LMILG_ACL_RATE AS LMILG_ACL_RATE,
T1.ACLM_ACL_RATE AS ACLM_ACL_RATE,
T1.SALE_FEE_RATE AS SALE_FEE_RATE,
T1.MBIL_LMILG_ACL_RATE AS MBIL_LMILG_ACL_RATE,
T1.MBIL_ACLM_ACL_RATE AS MBIL_ACLM_ACL_RATE,
T1.MBIL_IMDT_DC_MILG_USE_PSB_YN AS MBIL_IMDT_DC_MILG_USE_PSB_YN,
T1.RTN_DLVR_FREE_YN AS RTN_DLVR_FREE_YN,
T1.EXCH_DLVR_FREE_YN AS EXCH_DLVR_FREE_YN,
T1.PRC_EXPS_YN AS PRC_EXPS_YN,
T1.OPT_SETP_YN AS OPT_SETP_YN,
T1.ITEM_ORD_PSB_YN AS ITEM_ORD_PSB_YN,
T1.CPNT_YN AS CPNT_YN,
T1.PCHS_PSB_YN AS PCHS_PSB_YN,
T1.PROD_SEX_GB_CD AS PROD_SEX_GB_CD,
T1.PROD_SEX_GB_NM AS PROD_SEX_GB_NM,
T1.NLINE_EXCSV_PROD_GB_CD AS NLINE_EXCSV_PROD_GB_CD,
T1.NLINE_EXCSV_PROD_GB_NM AS NLINE_EXCSV_PROD_GB_NM,
T1.ACL_RATE_LGC_SCRN_YN AS ACL_RATE_LGC_SCRN_YN,
T1.FLINE_SHOP_MEND_PSB_YN AS FLINE_SHOP_MEND_PSB_YN,
T1.ORD_MUFT_YN AS ORD_MUFT_YN,
T1.EXCH_PSB_YN AS EXCH_PSB_YN,
T1.RTN_PSB_YN AS RTN_PSB_YN,
T1.KC_CERT_GB_CD AS KC_CERT_GB_CD,
T1.KC_CERT_GB_NM AS KC_CERT_GB_NM,
T1.DLVR_MTHD_CD AS DLVR_MTHD_CD,
T1.DLVR_MTHD_NM AS DLVR_MTHD_NM,
T1.MTRL_DESC AS MTRL_DESC,
T1.SPLY_CO_CD AS SPLY_CO_CD,
T1.SPLY_CO_NM AS SPLY_CO_NM,
T1.SPLY_CO_PROD_CD AS SPLY_CO_PROD_CD,
T1.MUFT_NM AS MUFT_NM,
T1.ORGN_NM AS ORGN_NM,
T1.REG_ID AS REG_ID,
T1.REG_DT AS REG_DT,
T1.UP_ID AS UP_ID,
T1.UP_DT AS UP_DT
FROM (SELECT DISTINCT T1.PROD_CD AS PROD_CD,
T1.PROD_NM AS PROD_NM,
Decode(T1.PROD_STS_CD, NULL, '{', Decode(T2.CD_ID, NULL, '}', T2.CD_ID)) AS PROD_REG_STS_CD,
Decode(T1.PROD_STS_CD, NULL, '-', Decode(T2.CD_ID, NULL, '미정의', T2.CD_NM)) AS PROD_REG_STS_NM,
Decode(T1.PROD_LGF_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS LGF_PROD_YN,
T1.MD_ID AS MD_ID,
Decode(T1.FORMAL_GB, NULL, '{', Decode(T3.CD_ID, NULL, '}', T3.CD_ID)) AS FORMAL_GB_CD,
Decode(T1.FORMAL_GB, NULL, '-', Decode(T3.CD_ID, NULL, '미정의', T3.CD_NM)) AS FORMAL_GB_NM,
Decode(T1.BRAND_CD, NULL, '{', Decode(T4.BRAND_CD, NULL, '}', T4.BRAND_CD)) AS BRND_CD,
Decode(T1.BRAND_CD, NULL, '-', Decode(T4.BRAND_CD, NULL, '미정의', T4.BRAND_NM)) AS BRND_NM,
CASE
WHEN Substr(T1.PROD_CD, 1, 3) = 'LGT' THEN '{'
WHEN T1.ATTR_C3 IS NULL THEN '{'
ELSE
CASE
WHEN T1.PROD_LGF_YN = 'Y'
AND T1.SET_YN = 'N' THEN
CASE
WHEN T5.ZRBRAND IN ('7O',
'9Y')
AND Substr(T1.PROD_CD, 1, 2) IN ('OU',
'OC') THEN T6.ZBRAND_ORG
ELSE T5.ZBRAND
END
ELSE T1.ATTR_C3
END
END AS SAP_BRND_CD,
Decode(T1.ITEMKIND_CD, NULL, '{', Decode(T7.ITEMKIND_CD, NULL, '}', T7.ITEMKIND_CD)) AS ITEM_KIND_CD,
Decode(T1.ITEMKIND_CD, NULL, '-', Decode(T7.ITEMKIND_CD, NULL, '미정의', T7.ITEMKIND_NM)) AS ITEM_KIND_NM,
T1.STD_PROD_CTGR_ID AS STD_PROD_CTGR_ID,
T1.STD_PROD_CTGR_NM AS STD_PROD_CTGR_NM,
T1.STD_PROD_LCTGR_ID AS STD_PROD_LCTGR_ID,
T1.STD_PROD_LCTGR_NM AS STD_PROD_LCTGR_NM,
T1.STD_PROD_MCTGR_ID AS STD_PROD_MCTGR_ID,
T1.STD_PROD_MCTGR_NM AS STD_PROD_MCTGR_NM,
T1.STD_PROD_SCTGR_ID AS STD_PROD_SCTGR_ID,
T1.STD_PROD_SCTGR_NM AS STD_PROD_SCTGR_NM,
Decode(T1.PROD_TYPE, NULL, '{', Decode(T8.CD_ID, NULL, '}', T8.CD_ID)) AS PROD_TYPE_CD,
Decode(T1.PROD_TYPE, NULL, '-', Decode(T8.CD_ID, NULL, '미정의', T8.CD_NM)) AS PROD_TYPE_NM,
T1.STYLE_YY AS STYL_YY,
Decode(T1.SEASON_CD, NULL, '{', Decode(T9.CD_ID, NULL, '}', T9.CD_ID)) AS SESN_CD,
Decode(T1.SEASON_CD, NULL, '-', Decode(T9.CD_ID, NULL, '미정의', T9.CD_NM)) AS SESN_NM,
Decode(T1.COLOR_1_CD, NULL, '{', Decode(T10.COLOR_1_CD, NULL, '}', T10.COLOR_1_CD)) AS CLR_CD,
Decode(T1.COLOR_1_CD, NULL, '-', Decode(T10.COLOR_1_CD, NULL, '미정의', T10.COLOR_HNM)) AS CLR_NM,
Decode(T1.TONE_CD, NULL, '{', Decode(T11.CD_ID, NULL, '}', T11.CD_ID)) AS CLR_TONE_CD,
Decode(T1.TONE_CD, NULL, '-', Decode(T11.CD_ID, NULL, '미정의', T11.CD_NM)) AS CLR_TONE_NM,
Decode(T1.ATTR_C1, NULL, '{', T1.ATTR_C1) AS PLAN_GRAD_CD,
Decode(T1.ATTR_C2, NULL, '{', T1.ATTR_C2) AS STCK_GRAD_CD,
T1.LIST_PRICE AS PROD_FST_CUST_PRC,
T1.PROD_PRICE AS CRNT_SALE_PRC,
Decode(F_chk_date(To_char(T1.PRICEUP_DT, 'YYYYMMDD')), 0, To_date(NULL, 'YYYYMMDD'), T1.PRICEUP_DT) AS PRC_CHNG_DT,
T1.OLD_PRICE AS CHNG_BFR_PRC,
Decode(T1.PRICE_CD, NULL, '{', Decode(T12.CD_ID, NULL, '}', T12.CD_ID)) AS PROD_PRC_RNG_CD,
Decode(T1.PRICE_CD, NULL, '-', Decode(T12.CD_ID, NULL, '미정의', T12.CD_NM)) AS PROD_PRC_RNG_NM,
Decode(T1.VOUCHER_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS VUCH_YN,
Decode(T1.SET_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS SET_PROD_YN,
Cast(T1.DC_RATE AS NUMERIC(10, 2)) AS DC_RATE,
T1.DELIVERY_FEE AS BASE_DLVR_FEE,
Decode(T1.PRE_LPOINT_USEGB, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS IMDT_DC_MILG_USE_PSB_YN,
Cast(T1.LPOINT_RATE AS NUMERIC(10, 2)) AS LMILG_ACL_RATE,
Cast(T1.SPOINT_RATE AS NUMERIC(10, 2)) AS ACLM_ACL_RATE,
Cast(T1.SELL_FEE_RATE AS NUMERIC(10, 2)) AS SALE_FEE_RATE,
Cast(T1.LPOINT_MRATE AS NUMERIC(10, 2)) AS MBIL_LMILG_ACL_RATE,
Cast(T1.SPOINT_MRATE AS NUMERIC(10, 2)) AS MBIL_ACLM_ACL_RATE,
Decode(T1.PRE_LPOINT_MUSEGB, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS MBIL_IMDT_DC_MILG_USE_PSB_YN,
Decode(T1.RETURN_FEE_FREE_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS RTN_DLVR_FREE_YN,
Decode(T1.CHANGE_FEE_FREE_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS EXCH_DLVR_FREE_YN,
Decode(T1.PRICE_DP_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS PRC_EXPS_YN,
Decode(T1.OPTION_SET_YN, NULL, '{', 'Y', 'Y', 'N', 'N', 'y', 'Y', 'n', 'N', '}') AS OPT_SETP_YN,
Decode(T1.ITEM_ORD_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS ITEM_ORD_PSB_YN,
Decode(T1.GS_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS CPNT_YN,
Decode(T1.BUY_ABLEYN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS PCHS_PSB_YN,
Decode(T1.PROD_SEX_GB, NULL, '{', Decode(T13.CD_ID, NULL, '}', T13.CD_ID)) AS PROD_SEX_GB_CD,
Decode(T1.PROD_SEX_GB, NULL, '{', Decode(T13.CD_ID, NULL, '}', T13.CD_NM)) AS PROD_SEX_GB_NM,
Decode(T1.ONLINE_PROD_GB, NULL, '{', Decode(T14.CD_ID, NULL, '}', T14.CD_ID)) AS NLINE_EXCSV_PROD_GB_CD,
Decode(T1.ONLINE_PROD_GB, NULL, '-', Decode(T14.CD_ID, NULL, '미정의', T14.CD_NM)) AS NLINE_EXCSV_PROD_GB_NM,
Decode(T1.POINT_RATE_LINK_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS ACL_RATE_LGC_SCRN_YN,
Decode(T1.OFFLINE_MEND_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS FLINE_SHOP_MEND_PSB_YN,
Decode(T1.ORDER_MAKING_YN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS ORD_MUFT_YN,
Decode(T1.CHANGE_ABLEYN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS EXCH_PSB_YN,
Decode(T1.RETURN_ABLEYN, NULL, '{', 'Y', 'Y', 'N', 'N', '}') AS RTN_PSB_YN,
Decode(T1.KC_GB, NULL, '{', Decode(T15.CD_ID, NULL, '}', T15.CD_ID)) AS KC_CERT_GB_CD,
Decode(T1.KC_GB, NULL, '{', Decode(T15.CD_ID, NULL, '}', T15.CD_NM)) AS KC_CERT_GB_NM,
Decode(T1.DELIVERY_METHOD, NULL, '{', Decode(T16.CD_ID, NULL, '}', T16.CD_ID)) AS DLVR_MTHD_CD,
Decode(T1.DELIVERY_METHOD, NULL, '{', Decode(T16.CD_ID, NULL, '}', T16.CD_NM)) AS DLVR_MTHD_NM,
Trim(T1.MATERIAL_DESC) AS MTRL_DESC,
Decode(T17.SUPPLY_ENTR_CD, NULL, '{' , Decode(T18.SUPPLY_ENTR_CD, NULL, '}', T18.SUPPLY_ENTR_CD)) AS SPLY_CO_CD,
Decode(T17.SUPPLY_ENTR_CD, NULL, '-' , Decode(T18.SUPPLY_ENTR_CD, NULL, '미정의', T18.SUPPLY_ENTR_NM)) AS SPLY_CO_NM,
T1.SUPPLY_PROD_CD AS SPLY_CO_PROD_CD,
T17.MAKE_NM AS MUFT_NM,
T17.NATIVE_NM AS ORGN_NM,
T1.REG_ID AS REG_ID,
T1.REG_DT AS REG_DT,
T1.UP_ID AS UP_ID,
T1.UP_DT AS UP_DT
FROM (SELECT T1.PROD_CD,
T1.PROD_NM,
T1.VOUCHER_YN,
T1.PROD_LGF_YN,
T1.BRAND_CD,
T1.ITEMKIND_CD,
T1.STYLE_YY,
T1.SEASON_CD,
T1.COLOR_1_CD,
T1.SET_YN,
T1.LIST_PRICE,
T1.PROD_PRICE,
T1.DC_RATE,
T1.PRICE_CD,
T1.MATERIAL_DESC,
T1.PROD_STS_CD,
T1.SUPPLY_PROD_CD,
T1.PRICEUP_DT,
T1.OLD_PRICE,
T1.DELIVERY_FEE,
T1.MD_ID,
T1.LPOINT_RATE,
T1.SPOINT_RATE,
T1.SELL_FEE_RATE,
T1.FORMAL_GB,
T1.CHANGE_ABLEYN,
T1.RETURN_ABLEYN,
T1.RETURN_FEE_FREE_YN,
T1.CHANGE_FEE_FREE_YN,
T1.PRE_LPOINT_USEGB,
T1.PRICE_DP_YN,
T1.OPTION_SET_YN,
T1.ITEM_ORD_YN,
T1.TONE_CD,
T1.PROD_TYPE,
T1.GS_YN,
T1.BUY_ABLEYN,
T1.LPOINT_MRATE,
T1.SPOINT_MRATE,
T1.PRE_LPOINT_MUSEGB,
T1.PROD_SEX_GB,
T1.ONLINE_PROD_GB,
T1.POINT_RATE_LINK_YN,
T1.OFFLINE_MEND_YN,
T1.ATTR_C1,
T1.ATTR_C2,
T1.ATTR_C3,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE T2.STD_PROD_CTGR_ID
END
END AS STD_PROD_CTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE T2.STD_PROD_CTGR_NM
END
END AS STD_PROD_CTGR_NM,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE
CASE
WHEN T2.STD_PROD_LCTGR_ID IS NULL THEN 0
ELSE T2.STD_PROD_LCTGR_ID
END
END
END AS STD_PROD_LCTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE
CASE
WHEN T2.STD_PROD_LCTGR_ID IS NULL THEN '-'
ELSE T2.STD_PROD_LCTGR_NM
END
END
END AS STD_PROD_LCTGR_NM,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE
CASE
WHEN T2.STD_PROD_MCTGR_ID IS NULL THEN 0
ELSE T2.STD_PROD_MCTGR_ID
END
END
END AS STD_PROD_MCTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE
CASE
WHEN T2.STD_PROD_MCTGR_ID IS NULL THEN '-'
ELSE T2.STD_PROD_MCTGR_NM
END
END
END AS STD_PROD_MCTGR_NM,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN 0
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN-1
ELSE
CASE
WHEN T2.STD_PROD_SCTGR_ID IS NULL THEN 0
ELSE T2.STD_PROD_SCTGR_ID
END
END
END AS STD_PROD_SCTGR_ID,
CASE
WHEN T1.STANDARDCATEGORYID IS NULL THEN '-'
ELSE
CASE
WHEN T2.STD_PROD_CTGR_ID IS NULL THEN '미정의'
ELSE
CASE
WHEN T2.STD_PROD_SCTGR_ID IS NULL THEN '-'
ELSE T2.STD_PROD_SCTGR_NM
END
END
END AS STD_PROD_SCTGR_NM,
T1.DELIVERY_METHOD,
T1.ORDER_MAKING_YN,
T1.KC_GB,
T1.REG_ID,
T1.REG_DT,
T1.UP_ID,
T1.UP_DT
FROM WLGF_STB_PRODUCT T1 /* WLGF_상품 */
,D_STD_PROD_CTGR_LST T2 /* 기준상품분류내역 */
WHERE Nvl(T1.STANDARDCATEGORYID, 0) = T2.STD_PROD_CTGR_ID) T1,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE SRC_SYS_GB_CD = 'M'
AND CD_GRP_ID = 'G210'
AND CD_USE_YN = 'Y') T2,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE SRC_SYS_GB_CD = 'M'
AND CD_GRP_ID = 'C120'
AND CD_USE_YN = 'Y') T3,
WLGF_STB_BRAND T4, /* WLGF_브랜드 */
WLGF_LST_SAP_BRAND_MASTER T5, /* WLGF_SAP 브랜드 마스터 */
WLGF_LST_SAP_BRAND_MASTER T6, /* WLGF_SAP 브랜드 마스터 */
WLGF_STB_ITEMKIND T7, /* WLGF_브랜드별품목 */
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G200'
AND CD_USE_YN = 'Y') T8,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G201'
AND CD_USE_YN = 'Y') T9,
WLGF_STB_COLOR T10, /* WLGF_색상 */
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'C000'
AND CD_USE_YN = 'Y') T11,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G290'
AND CD_USE_YN = 'Y') T12,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G222'
AND CD_USE_YN = 'Y') T13,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G215'
AND CD_USE_YN = 'Y') T14,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G954'
AND CD_USE_YN = 'Y') T15,
(SELECT CD_ID, CD_NM
FROM D_CD_LST
WHERE CD_GRP_ID = 'G953'
AND CD_USE_YN = 'Y') T16,
WLGF_STB_ITEM T17, /* WLGF_단품 */
WLGF_STB_SUPPLY_ENTR T18 /* WLGF_입점업체 */
WHERE T1.PROD_STS_CD = T2.CD_ID(+)
AND T1.FORMAL_GB = T3.CD_ID(+)
AND T1.BRAND_CD = T4.BRAND_CD(+)
AND T1.ATTR_C3 = T5.ZBRAND(+)
AND T1.ATTR_C3 = T6.ZBRAND(+)
AND T1.BRAND_CD = T7.BRAND_CD(+)
AND T1.ITEMKIND_CD = T7.ITEMKIND_CD(+)
AND T1.PROD_TYPE = T8.CD_ID(+)
AND T1.SEASON_CD = T9.CD_ID(+)
AND T1.COLOR_1_CD = T10.COLOR_1_CD(+)
AND T1.TONE_CD = T11.CD_ID(+)
AND T1.PRICE_CD = T12.CD_ID(+)
AND T1.PROD_SEX_GB = T13.CD_ID(+)
AND T1.ONLINE_PROD_GB = T14.CD_ID(+)
AND T1.KC_GB = T15.CD_ID(+)
AND T1.DELIVERY_METHOD = T16.CD_ID(+)
AND T1.PROD_CD = T17.ITEM_CD(+)
AND T17.SUPPLY_ENTR_CD = T18.SUPPLY_ENTR_CD(+)
AND Substr(T1.PROD_CD, 1, 2) = T6.ZBRAND_DTL(+)
AND Substr(T1.PROD_CD, 7, 1) = T6.ZBRAND_OU(+)
) T1,
(SELECT DISTINCT ZBRAND, ZBRTEXT
FROM WLGF_LST_SAP_BRAND_MASTER /* WLGF_SAP 브랜드 마스터 */) T2,
WLGF_LST_MANAGER T3 /* WLGF_관리자.신규관리자등록에의한발생 */
WHERE T1.SAP_BRND_CD = T2.ZBRAND(+)
AND T1.MD_ID = T3.MGR_ID(+)) T1,
D_PROD_DEPT_LST T2
WHERE T1.PROD_CD = T2.PROD_CD(+)
AND T1.FORMAL_CTGR_CD = T2.FORMAL_CTGR_CD(+)
제일 첫번째 조인이 이너조인에서 아우터조인으로 바뀌었네요.
- 오류 : LEFT JOIN D_STD_PROD_CTGR_LST T2
- 수정 : INNER JOIN D_STD_PROD_CTGR_LST T2
아! 제가 거꾸로 답변했네요. 기존쿼리가 아우터조인인데 이너조인으로 바뀐거네요.
- 오류 : WHERE Nvl(T1.STANDARDCATEGORYID, 0) = T2.STD_PROD_CTGR_ID
- 수정 : WHERE Nvl(T1.STANDARDCATEGORYID, 0) = T2.STD_PROD_CTGR_ID(+)
) 버튼을 클릭하여 작성 하시면 됩니다.