mysql 인라인뷰 질문

Question

(1) CASE WHEN 사용SELECT aa.DISPLAY_CATEGORY_ID, aa.PRODUCT_ID, aa.SHOP_NO, aa.SORT_NO FROM ( SELECT( CASE WHEN SUBSTRING(dg.DISP_NO,1,6) &#61; &#34;001001&#34; AND SUBSTRING(dg.DISP_NO,10,1) !&#61; &#34;&#34; THEN CONCAT(SUBSTRING(dg.DISP_NO,1,9), &#34;00&#34;&#43;truncate(cc.SORT_NO,0), SUBSTR(dg.DISP_NO,10)) WHEN SUBSTRING(dg.DISP_NO,1,6) &#61; &#34;001005&#34; AND SUBSTRING(dg.DISP_NO,10,1) !&#61; &#34;&#34; THEN CONCAT(SUBSTRING(dg.DISP_NO,1,9), &#34;00&#34;&#43;truncate(cc.SORT_NO,0), SUBSTR(dg.DISP_NO,10)) WHEN SUBSTRING(dg.DISP_NO,1,6) &#61; &#34;001006&#34; AND SUBSTRING(dg.DISP_NO,10,1) !&#61; &#34;&#34; THEN CONCAT(SUBSTRING(dg.DISP_NO,1,9), &#34;00&#34;&#43;truncate(cc.SORT_NO,0), SUBSTR(dg.DISP_NO,10)) WHEN SUBSTRING(dg.DISP_NO,1,6) &#61; &#34;001008&#34; AND SUBSTRING(dg.DISP_NO,10,1) !&#61; &#34;&#34; THEN CONCAT(SUBSTRING(dg.DISP_NO,1,9), &#34;00&#34;&#43;truncate(cc.SORT_NO,0), SUBSTR(dg.DISP_NO,10)) ELSE dg.DISP_NO END )AS DISPLAY_CATEGORY_ID, pp.PRODUCT_ID AS PRODUCT_ID, dg.SHOP_NO AS SHOP_NO, MIN(dg.DISP_PRIR_SEQ) AS SORT_NO FROM BATCH_DATA_TSHOPDISPGOODS dg INNER JOIN PMS_SALEPRODUCT psp ON psp.C21_GOODS_NO &#61; dg.GOODS_NO INNER JOIN PMS_PRODUCT pp ON pp.PRODUCT_ID &#61; psp.PRODUCT_ID INNER JOIN CCS_CODE cc ON cc.cd &#61; pp.GENDER_TYPE_CD WHERE pp.STORE_ID &#61; dg.SHOP_NO GROUP BY DISPLAY_CATEGORY_ID, PRODUCT_ID ) as aa WHERE EXISTS ( SELECT 1 FROM DMS_DISPLAYCATEGORY dcp WHERE dcp.DISPLAY_CATEGORY_ID &#61; aa.DISPLAY_CATEGORY_ID AND dcp.LEAF_YN &#61; &#34;Y&#34; AND dcp.STORE_ID &#61; aa.SHOP_NO ) (2) FUNCTION 사용 CREATE FUNCTION GenderMappingDISPNO(DISP_NO VARCHAR(63), GENDER_TYPE VARCHAR(100)) RETURNS VARCHAR(63) DETERMINISTIC BEGIN DECLARE genderNum VARCHAR(3); DECLARE result VARCHAR(63); IF GENDER_TYPE &#61; 'GENDER_TYPE_CD.MALE' THEN SET genderNum &#61; '001'; ELSEIF GENDER_TYPE &#61; 'GENDER_TYPE_CD.FEMALE' THEN SET genderNum &#61; '002'; ELSEIF GENDER_TYPE &#61; 'GENDER_TYPE_CD.JUNIOR' THEN SET genderNum &#61; '003'; ELSEIF GENDER_TYPE &#61; 'GENDER_TYPE_CD.UNISEX' THEN SET genderNum &#61; '004'; ELSEIF GENDER_TYPE &#61; 'GENDER_TYPE_CD.NONE' THEN SET genderNum &#61; '004'; END IF; IF (SUBSTRING(DISP_NO,1,6) &#61; '001001' AND SUBSTRING(DISP_NO,10,1) !&#61; '') THEN SET result &#61; CONCAT(SUBSTRING(DISP_NO,1,9), genderNum, SUBSTR(DISP_NO,10)); ELSEIF (SUBSTRING(DISP_NO,1,6) &#61; '001005' AND SUBSTRING(DISP_NO,10,1) !&#61; '') THEN SET result &#61; CONCAT(SUBSTRING(DISP_NO,1,9), genderNum, SUBSTR(DISP_NO,10)); ELSEIF (SUBSTRING(DISP_NO,1,6) &#61; '001006' AND SUBSTRING(DISP_NO,10,1) !&#61; '') THEN SET result &#61; CONCAT(SUBSTRING(DISP_NO,1,9), genderNum, SUBSTR(DISP_NO,10)); ELSEIF (SUBSTRING(DISP_NO,1,6) &#61; '001008' AND SUBSTRING(DISP_NO,10,1) !&#61; '') THEN SET result &#61; CONCAT(SUBSTRING(DISP_NO,1,9), genderNum, SUBSTR(DISP_NO,10)); ELSE SET result &#61; DISP_NO; END IF; RETURN result; END SELECT aa.DISPLAY_CATEGORY_ID, aa.PRODUCT_ID, aa. DISPLAY_YN, aa.SORT_NO, aa.SHOP_NO FROM ( SELECT GenderMappingDISPNO(dg.DISP_NO,pp.GENDER_TYPE_CD) AS DISPLAY_CATEGORY_ID, pp.PRODUCT_ID AS PRODUCT_ID, &#34;Y&#34; AS DISPLAY_YN, MIN(dg.DISP_PRIR_SEQ) AS SORT_NO, dg.SHOP_NO AS SHOP_NO FROM BATCH_DATA_TSHOPDISPGOODS dg INNER JOIN PMS_SALEPRODUCT psp ON psp.C21_GOODS_NO &#61; dg.GOODS_NO INNER JOIN PMS_PRODUCT pp ON pp.PRODUCT_ID &#61; psp.PRODUCT_ID INNER JOIN CCS_CODE cc ON cc.cd &#61; pp.GENDER_TYPE_CD WHERE pp.STORE_ID &#61; dg.SHOP_NO GROUP BY DISPLAY_CATEGORY_ID, PRODUCT_ID ) AS aa WHERE EXISTS ( SELECT 1 FROM DMS_DISPLAYCATEGORY dcp WHERE dcp.DISPLAY_CATEGORY_ID &#61; aa.DISPLAY_CATEGORY_ID AND dcp.LEAF_YN &#61; &#34;Y&#34; AND dcp.STORE_ID &#61; aa.SHOP_NO ) 왜 결과 값이 틀릴까요 ...인라인뷰안의 데이터 값 수량은은 똑같이 나오고있는데,인라인뷰의 데이터를 밖에서 where이랑 비교하니깐 달라요. function을 사용한것이 타당한 데이터가 나오고, case when은 틀린 데이터가 나옵니다. case when을 사용하고싶은이유는 function을 select에 사용하니깐 너무 느려져서입니다. 도와주세요

mysql 인라인뷰 질문

0개의 답변

Q&A 목록