\n| — Data insert script<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, ‘CLARK ‘, TO_DATE(‘19810609’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, ‘KING’, TO_DATE(‘19811117’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(10, ‘MILLER’, TO_DATE(‘19820123’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘ADAMS’, TO_DATE(‘19830112’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘FORD’, TO_DATE(‘19811203’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘JONES’, TO_DATE(‘19810402’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘SCOTT’, TO_DATE(‘19821209’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(20, ‘SMITH’, TO_DATE(‘19801217’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘ALLEN’, TO_DATE(‘19810220’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘BLAKE’, TO_DATE(‘19810501’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘JAMES’, TO_DATE(‘19811203’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘MARTIN’, TO_DATE(‘19810928’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘TURNER’, TO_DATE(‘19810908’, ‘YYYYMMDD’));<\/p>\n INSERT INTO EMP(DEPTNO, ENAME, HIREDATE) VALUES(30, ‘WARD’, TO_DATE(‘19810222’, ‘YYYYMMDD’));<\/p>\n COMMIT;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n n \ub370\uc774\ud130 \ud655\uc778 \uc2a4\ud06c\ub9bd\ud2b8<\/p>\n \n\n\n| — Query EMP<\/p>\n SQL) SELECT DEPTNO,ENAME,TO_CHAR(HIREDATE, ‘YYYY-MM-DD’) HIREDATE<\/p>\n FROM EMP;<\/p>\n DEPTNO ENAME HIREDATE<\/p>\n ———— ———– —————–<\/p>\n 10 CLARK 1981-06-09<\/p>\n 10 KING 1981-11-17<\/p>\n 10 MILLER 1982-01-23<\/p>\n 20 ADAMS 1983-01-12<\/p>\n 20 FORD 1981-12-03<\/p>\n 20 JONES 1981-04-02<\/p>\n 20 SCOTT 1982-12-09<\/p>\n 20 SMITH 1980-12-17<\/p>\n 30 ALLEN 1981-02-20<\/p>\n 30 BLAKE 1981-05-01<\/p>\n 30 JAMES 1981-12-03<\/p>\n 30 MARTIN 1981-09-28<\/p>\n 30 TURNER 1981-09-08<\/p>\n 30 WARD 1981-02-22<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n\n| — Query aggregated ename in 10g version<\/p>\n col deptno for 999,999,999<\/p>\n col aggregated_enames for a50<\/p>\n SELECT DEPTNO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME, ‘, ‘)), 2) AGGREGATED_ENAMES<\/p>\n FROM (SELECT DEPTNO, ENAME, TO_CHAR(HIREDATE, ‘YYYY-MM-DD’) HIREDATE,<\/p>\n ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) CNT<\/p>\n FROM EMP) T<\/p>\n CONNECT BY PRIOR CNT=CNT-1 AND PRIOR DEPTNO = DEPTNO<\/p>\n START WITH CNT = 1<\/p>\n GROUP BY DEPTNO<\/p>\n ORDER BY 1;<\/p>\n — RESULT —<\/p>\n DEPTNO AGGREGATED_ENAMES<\/p>\n ———— ———————————————-<\/p>\n 10 CLARK, KING, MILLER<\/p>\n 20 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n n DEPTNO \uac12\uc744 grouping \ud558\uae30\uc704\ud574 ROWNUM() \ud568\uc218\ub97c \uc0ac\uc6a9\ud55c\ud6c4 connect by prior\ub97c \uc774\uc6a9\ud558\uc5ec<\/p>\n \uc5f0\uacb0\ud55c\ub4a4, SYS_CONNECT_BY_PATH \ud568\uc218\ub97c \uc774\uc6a9\ud558\uc5ec \uac12\uc744 \uac00\ub85c\ub85c \ub098\uc5f4\ud558\ub294 \ubc29\ubc95<\/p>\n \ud558\uc9c0\ub9cc 11g \ubd80\ud130\ub294 \ubcf5\uc7a1\ud558\uac8c \uad6c\ud604\ud560 \ud544\uc694\ud558\uc9c0 \uc54a\uace0 LISTAGG Function \uc0ac\uc6a9\ud568\uc73c\ub85c\uc11c<\/p>\n \uac04\ub2e8\ud788 \uad6c\ud604\ud560\uc218 \uc788\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n\n| Syntax : LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]<\/p>\n The following elements are mandatory:<\/p>\n 1) the column or expression to be aggregated;<\/p>\n 2) the WITHIN GROUP keywords;<\/p>\n 3) the ORDER BY clause within the grouping<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n n LISTAGG \ud568\uc218 \uc0ac\uc6a9\ud558\uc5ec \uac00\ub85c\ub85c \uc815\ub82c<\/p>\n \n\n\n| SELECT DEPTNO, LISTAGG(ENAME, ‘, ‘) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES<\/p>\n FROM EMP<\/p>\n GROUP BY DEPTNO;<\/p>\n — RESULT —<\/p>\n DEPTNO AGGREGATED_ENAMES<\/p>\n ——– ———————————————-<\/p>\n 10 CLARK, KING, MILLER<\/p>\n 20 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 30 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n – detpno \uceec\ub7fd\uc73c\ub85c \uadf8\ub8f9\uc73c\ub85c \ubb36\uc740 \ud6c4, \uac00\ub85c\ub85c \ub098\uc5f4\ud558\uace0\uc790 \ud558\ub294 \uceec\ub7fc ename\uc744 LISTAGG()\uc5d0 \uba85\uc2dc\ud574\uc8fc\uba74<\/p>\n \ub41c\ub2e4. WITHIN GROUP() \ud568\uc218\uc5d0\uc11c\ub294 \uac00\ub85c\ub85c \ub098\uc5f4\ud558\uace0\uc790 \ud558\ub294 \uc21c\uc11c\ub97c order by\ub85c \uc9c0\uc815\ud558\uba74 \ub05d\ub09c\ub2e4.<\/p>\n \uc704\uc758 10g\uc758 \uacb0\uacfc\uac12\uacfc \ub3d9\uc77c\ud55c \uacb0\uacfc\uac12\uc744 \uac00\uc9c8 \uc218 \uc788\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n\n| — Query<\/p>\n SELECT DEPTNO,ENAME,HIREDATE, LISTAGG(ENAME, ‘, ‘) WITHIN GROUP (ORDER BY HIREDATE) OVER(PARTITION BY DEPTNO) AS AGGREGATED_ENAMES FROM EMP;<\/p>\n — RESULT —<\/p>\n DEPTNO ENAME HIREDATE AGGREGATED_ENAMES<\/p>\n ———- ————— ——————- ——————————————-<\/p>\n 10 CLARK 09-JUN-81 CLARK, KING, MILLER<\/p>\n 10 KING 17-NOV-81 CLARK, KING, MILLER<\/p>\n 10 MILLER 23-JAN-82 CLARK, KING, MILLER<\/p>\n 20 SMITH 17-DEC-80 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 20 JONES 02-APR-81 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 20 FORD 03-DEC-81 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 20 SCOTT 09-DEC-82 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 20 ADAMS 12-JAN-83 SMITH, JONES, FORD, SCOTT, ADAMS<\/p>\n 30 ALLEN 20-FEB-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n 30 WARD 22-FEB-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n 30 BLAKE 01-MAY-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n 30 TURNER 08-SEP-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n 30 MARTIN 28-SEP-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/p>\n 30 JAMES 03-DEC-81 ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n\n| SELECT DEPTNO, LISTAGG(ENAME) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES<\/p>\n FROM EMP<\/p>\n GROUP BY DEPTNO;<\/p>\n DEPTNO AGGREGATED_ENAMES<\/p>\n ——- ——————————–<\/p>\n 10 CLARKKINGMILLER<\/p>\n 20 SMITHJONESFORDSCOTTADAMS<\/p>\n 30 ALLENWARDBLAKETURNERMARTINJAMES<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n\n| SELECT DEPTNO, LISTAGG(ENAME, ‘, ‘) WITHIN GROUP () AS AGGREGATED_ENAMES FROM EMP<\/p>\n GROUP BY DEPTNO;<\/p>\n — RESULT —<\/p>\n LISTAGG(ENAME, ‘, ‘) WITHIN GROUP () AS AGGREGATED_ENAMES<\/p>\n *<\/p>\n ERROR at line 2:<\/p>\n ORA-30491: missing ORDER BY clause<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n n LISTAGG \ud568\uc218\uc758 2\ubc88\uc9f8 \ud30c\ub77c\ubbf8\ud130\uc5d0 ROWNUM \uac19\uc740 \uc608\uc57d\uc5b4\ub294 \uc0ac\uc6a9 \ubd88\uac00\ud568<\/p>\n \n\n\n| SELECT DEPTNO, LISTAGG(ENAME, ‘(‘ || ROWNUM || ‘)’) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO;<\/p>\n — RESULT —<\/p>\n LISTAGG(ENAME, ‘(‘ || ROWNUM || ‘)’) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES<\/p>\n *<\/p>\n ERROR at line 2:<\/p>\n ORA-30497: Argument should be a constant or a function of expressions in GROUP BY.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n n LISTAGG \ud568\uc218\uc758 2\ubc88\uc9f8 \ud30c\ub77c\ubbf8\ud130\uc5d0 \uc608\uc57d\uc5b4\uac00 \uc544\ub2cc CHR() \ud568\uc218\ub97c \uc0ac\uc6a9\ud560 \uacbd\uc6b0, \uc5d0\ub7ec \uc5c6\uc774 \ucd9c\ub825\uc774 \uac00\ub2a5<\/p>\n \n\n\n| SELECT DEPTNO, LISTAGG(ENAME, ‘(‘ || CHR(DEPTNO+55) || ‘)’) WITHIN GROUP (ORDER BY HIREDATE) AS AGGREGATED_ENAMES FROM EMP GROUP BY DEPTNO;<\/p>\n — RESULT —<\/p>\n DEPTNO AGGREGATED_ENAMES<\/p>\n —— ———————————————-<\/p>\n 10 CLARK(A)KING(A)MILLER<\/p>\n 20 SMITH(K)JONES(K)FORD(K)SCOTT(K)ADAMS<\/p>\n 30 ALLEN(U)WARD(U)BLAKE(U)TURNER(U)MARTIN(U)JAMES<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n n LISTAGG \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uc5ec String \uac12\uc744 \uac00\uc838\uc62c \ub54c \ub108\ubb34 \uae38\uc774\uac00 \uae34 \uac12\uc740 \uac00\uc838\uc62c \uc218 \uc5c6\uc74c\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub294 \uc608\uc81c\uc774\ub2e4.<\/p>\n n 4000 bytes \ubcf4\ub2e4 \ud070\uac12\uc744 \ubd99\uc5ec\uc11c \ucd9c\ub825\ud560\ub824\ub2e4 \ubcf4\ub2c8 ORA-1489 \ubc1c\uc0dd<\/p>\n \n\n\n| SELECT LISTAGG(OBJECT_NAME) WITHIN GROUP (ORDER BY NULL)<\/p>\n FROM ALL_OBJECTS;<\/p>\n — RESULT —<\/p>\n FROM ALL_OBJECTS<\/p>\n *<\/p>\n ERROR at line 2:<\/p>\n ORA-01489: result of string concatenation is too long<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":" \u00a0 1 \ubaa9\uc801 \uc810\ucc28\uc801\uc73c\ub85c 10g \uc5d0\uc11c 11g\ub85c \ubc84\uc804\uc5c5\uc774 \ub418\uace0 \uc788\ub294 \uc2dc\uc7a5\uc5d0\uc11c Oracle 11g\uc5d0\uc11c \uc0c8\ub86d\uac8c \uc18c\uac1c\ub41c \uae30\ub2a5\uc744 \uc0b4\ud3b4\ubcf4\uace0\uc790 \ud55c\ub2e4. SQL\ubb38\uc744 \uc0ac\uc6a9\ud558\ub2e4\ubcf4\uba74 \uceec\ub7fc\ubcc4\ub85c \uac00\ub85c\ub85c \uc815\ub9ac\ud574\uc57c \ud560\ub54c\uac00 \uc788\ub2e4 10g \ubc84\uc804\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub358 SYS_CONNECT_BY_PATH \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uc5ec \ubcf5\uc7a1\ud588\ub358 SQL\uc774 LISTAGG Function\uc744 \uc0ac\uc6a9\ud558\uc5ec \uac04\ub2e8\ud558\uba70 \ud6a8\uacfc\uc801\uc778 SQL\ubb38\uc744 \uc791\uc131\uc744 \ud558\uae30 \uc704\ud55c \ubaa9\uc801\uc785\ub2c8\ub2e4. 2 TEST \ud658\uacbd\uad6c\uc131 n \ubc84\uc804\uc815\ubcf4 \ud655\uc778 \uc2a4\ud06c\ub9bd\ud2b8 — Check Oracle Version — […]<\/p>\n","protected":false},"author":1,"featured_media":3627,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[1141,1142],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/03\/sql1.jpg?fit=489%2C345","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4400"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4400"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4400\/revisions"}],"predecessor-version":[{"id":4402,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4400\/revisions\/4402"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3627"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4400"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4400"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4400"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | | | | | | | | | | |