DROP TABLE ORDER_BY_CASE_TEST;

CREATE TABLE ORDER_BY_CASE_TEST
	(
    	IDX VARCHAR2(10) NOT NULL,
        PRODUCT VARCHAR2(50) NOT NULL,
        REG_DT VARCHAR2(8) NOT NULL,
        STATUS VARCHAR2(20) NOT NULL
    );

INSERT INTO ORDER_BY_CASE_TEST
VALUES(1, '°ÔÀÓÀ» Çϸé À̰ܾßÁö', '20180207', '¹è¼Û Áß');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(2, '¿ì¸®³ª¶ó ÁÁÀº ³ª¶ó', '20180210', 'ȯºÒ');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(3, '¿©Àǵµ º¢²É ³îÀÌ', '20180211', '¹è¼Û ¿Ï·á');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(4, 'Ƽ½ºÅ丮 ÆíÁý±â ºÒÆí', '20180213', '¹è¼Û ¿Ï·á');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(5, 'ºñÆ®ÄÚÀÎ ¶±»ó °¡Áî¾Æ', '20180215', 'ȯºÒ');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(6, '°³±×Äܼ­Æ®', '20180310', 'ȯºÒ');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(7, '½´ÆÛÆÇŸÁö¿ö', '20180314', '¹è¼Û ¿Ï·á');

INSERT INTO ORDER_BY_CASE_TEST
VALUES(8, '°ËÀº»ç¸· ¸ð¹ÙÀÏ', '20180316', '¹è¼Û Áß');

SELECT	TO_CHAR(TO_DATE(REG_DT, 'yyyyMMdd'), 'yyyy.MM.dd') AS BUY_DT
		, PRODUCT
        , STATUS
FROM 	ORDER_BY_CASE_TEST
ORDER BY DECODE(STATUS, '¹è¼Û ¿Ï·á', 1, 2), BUY_DT DESC;

SELECT	TO_CHAR(TO_DATE(REG_DT, 'yyyyMMdd'), 'yyyy.MM.dd') AS BUY_DT
		, PRODUCT
        , STATUS
FROM 	ORDER_BY_CASE_TEST
ORDER BY DECODE(STATUS, '¹è¼Û ¿Ï·á', 1, '¹è¼Û Áß', 2, 3), BUY_DT DESC;

SELECT	TO_CHAR(TO_DATE(REG_DT, 'yyyyMMdd'), 'yyyy.MM.dd') AS BUY_DT
		, PRODUCT
        , STATUS
FROM 	ORDER_BY_CASE_TEST
ORDER BY
		(
        	CASE STATUS
            WHEN '¹è¼Û ¿Ï·á' THEN 1
            WHEN '¹è¼Û Áß' THEN 2
            ELSE 3
            END
        ), BUY_DT DESC;

INSERT INTO ORDER_BY_CASE_TEST
VALUES(8, '°ËÀº»ç¸· ¸ð¹ÙÀÏ ¿î¿µ Æø¸Á', '20180316', 'ȯºÒ');

COMMIT;

SELECT	TO_CHAR(TO_DATE(REG_DT, 'yyyyMMdd'), 'yyyy.MM.dd') AS BUY_DT
		, PRODUCT
        , STATUS
FROM 	ORDER_BY_CASE_TEST
ORDER BY BUY_DT DESC, DECODE(STATUS, '¹è¼Û ¿Ï·á', 1, '¹è¼Û Áß', 2, 3);