시처럼 음악처럼

중복 할인 금액 구하기 이번 퀴즈로 배워보는 SQL 시간에는 여러 할인 쿠폰을 중복으로 적용하여 최종 할인 금액을 구하는 쿼리 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자. [문제] 원본리스트(할인쿠폰) WITH t AS ( SELECT 1 seq, 2000 amt, NULL rat FROM dual UNION ALL SELECT 2, NULL, 10 FROM dual UNION ALL SELECT 3, 3000, NULL FROM dual UNION ALL SELECT ..

Recursive Subquery Factoring의 한가지 활용 예오라클/SQL 튜닝 2010/12/01 17:46 Oracle 11gR2부터 Recursive Subquery Factoring이라는 기능을 제공합니다. 이 기능을 이용하면 Connect By 구문을 대신할 수 있죠. 아래에 간단한 사용법이 있습니다.SQL> select lpad(' ', 2 * level - 2, ' ') || ename as ename 2 , empno 3 , mgr 4 , level 5 from emp 6 connect by mgr = prior empno 7 start with mgr is null 8 / SQL> with emps (ename,empno,mgr,lvl) as 2 ( select ename 3 , ..

Recursive WITH clause - to implement Connect By featuresORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause. This blog entry shows how to implement all CONNECT BY features in Recursive WITH clause. CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]): rajesh@ORA11G> select empno,ename,mgr 2 from ..

/* Pivot */WITH wdata AS( SELECT '1001' user_id, 'USE' org, 'Boss' position, 'EN' lang, 100 qty, 25000 amt FROM dual UNION ALL SELECT '1001' user_id, 'USD' org, 'Bossa' position, 'FI' lang, 200 qty, 30000 amt FROM dual UNION ALL SELECT '1002' user_id, 'GWR' org, 'Dim' position, 'SV' lang, 300 qty, 35000 amt FROM dual UNION ALL SELECT '1003' user_id, 'GGA' org, 'DCS' position, 'FI' lang, 400 qty,..

developerWorks Technical topics Information Management Technical library Port CONNECT BY to DB2 A recipe to map recursive queries from Oracle to DB2 using recursive common table expressions Using examples, this article describes step-by-step how to map CONNECT BY syntax and its associated pseudo columns to common table expressions that work in DB2® Universal Database™ (DB2 UDB) for Linux®, UNIX®..

/* Oracle ==> http://blog.naver.com/hyelee96/140073401311 */ WITH t AS ( SELECT 'A' gbn, '001' val FROM dual UNION ALL SELECT 'A' gbn, '002' val FROM dual UNION ALL SELECT 'A' gbn, '003' val FROM dual UNION ALL SELECT 'A' gbn, '004' val FROM dual UNION ALL SELECT 'A' gbn, '005' val FROM dual UNION ALL SELECT 'A' gbn, '011' val FROM dual UNION ALL SELECT 'B' gbn, '007' val FROM dual UNION ALL SEL..

Oracle 11g: Unpivot multiple columns and include column name Unpivot table with multiple columns and dynamic column names

pivot and unpivot queries in 11gPivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. Pivoting is a common technique, especially for reporting, and it has been possible to generate pivoted resultsets with SQL for many years and Oracle versions. However, the release of 11g includes explicit pivot-query support for the fi..

from : http://www.dba-oracle.com/plsql/t_plsql_pipelining.htm Table Functions and Pipelining Oracle Tips by Burleson Consulting The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:Functions that return collections of rows are known as table functions. They can be used like database ta..

from : http://sqlandplsql.com/2013/04/30/5-different-ways-to-test-oracle-ref-cursor-results/ 5 different ways to test Oracle Ref Cursor resultsPosted on April 30, 2013by sqlandplsqlIn the previous posts we have covered Ref Cursors and Ref Cursor examples. Now we will examine how Ref Cursor examples are tested.For all the examples I have used the package we have created in Ref Cursor Example post..