Problem: In a product table, the answer for each product inquiry is stored in a separate column. We have something like this:
inquiry_date date product1 varchar2(1), product2 varchar2(1), product3 varchar2(1)
... and so on. We want to create a table where each product will be a separate LINE and show counts of 4 years from 1998. Count 1 if the value in column is "Y", else it would be 0. The output should look something like this:
products yr_1998 yr_1999 yr_2000 Yr_2001 product1 890 902 1002 3409 product2 897 1927 1232 3396
.... and so on.
Solution: I created a temporary table with one column (prodname) where all product names are individual rows. The product name fetched from this table is fed to dynamic SQL. The values returned by dynamic SQL are evaluated for appropriate year and a row is inserted into the reporting table. Here is the script:
=========================================================================== CREATE OR REPLACE procedure producttotals_proc as v_cursorid integer; v_selectstmt varchar2(1000); v_year varchar2(4); v_count number; v_product_name varchar2(30); v_dummy integer; -- PL/SQL table to hold values for 4 years. type year_count is table of number index by BINARY_INTEGER; var_year year_count; --- Cursor to hold all prodcut names (i.e. Columns ) cursor c_prodname is select product_name from temp_productnames; c_prodname_rec c_prodname%rowtype; begin
-- begin program open c_prodname; -- open the Product Name cursor loop -- for all Prodcut names fetched by cursor fetch c_prodname into c_prodname_rec; exit when c_prodname%notfound; -- define the cursor for Dynamic PL/SQL. v_cursorid := DBMS_SQL.OPEN_CURSOR; v_selectstmt := ' SELECT '|| ''''|| c_prodname_rec.product_name || '''' || ' ,to_char(inquiry_date,''rrrr''), sum(decode(' || c_prodname_rec.product_name || ',''Y'',1,0 )) from all_products where '|| ' to_char(inquiry_date,''rrrr'') in ''1998'',''1999'',''2000'',''2001'')'|| ' group by '|| c_prodname_rec.product_name || ' ,to_char(inquiry_date,''rrrr'')' ; -- parse the query. DBMS_SQL.PARSE(v_cursorid,v_selectstmt,DBMS_SQL.V7); -- define the output columns DBMS_SQL.DEFINE_COLUMN(v_cursorid,1,v_product_name,30); DBMS_SQL.DEFINE_COLUMN(v_cursorid,2,v_year,4); DBMS_SQL.DEFINE_COLUMN(v_cursorid,3,v_count); -- Initialize PL/SQL table. for j in 1..4 loop var_year(j) := 0; end loop; -- execute dynamic sql v_dummy := DBMS_SQL.EXECUTE(v_cursorid); loop IF DBMS_SQL.FETCH_ROWS(v_cursorid) = 0 then exit; END IF; DBMS_SQL.COLUMN_VALUE(v_cursorid,1,v_product_name); DBMS_SQL.COLUMN_VALUE(v_cursorid,2,v_year); DBMS_SQL.COLUMN_VALUE(v_cursorid,3,v_count); -- check the value returned for Year and put the count -- in appropriate bucket if v_year = '1998' then var_year(1) := v_count; elsif v_year = '1999' then var_year(2) := v_count; elsif v_year = '2000' then var_year(3) := v_count; elsif v_year = '2001' then var_year(4) := v_count; end if; end loop; DBMS_SQL.CLOSE_CURSOR(v_cursorid); insert into producttotals ( product, yr_1998, yr_1999, yr_2000, yr_2001 ) values ( v_product_name, var_year(1), var_year(2), var_year(3), var_year(4) ); commit; end loop; -- End loop for Product Name Cursor. close c_prodname; exception when others then DBMS_SQL.CLOSE_CURSOR(v_cursorid); RAISE; end; /
Leonard A. writes: This script is great, and useful for Oracle developers. Thanks!
Lucy L. writes: The use of PL/SQL, loops and cursors, dynamic SQL, etc. in place of perfectly good SQL is a hot button for me! I conduct a lot of code reviews that this would not have passed. The problem presented can be solved much more compactly and efficiently with straight SQL. Dynamic SQL has its uses, but it really ought to be a last resort.
In the solution below, assume NR is a table of integers 1 through some number (at least 3):
SELECT P.PNAME AS PRODUCTS, SUM(DECODE( PYEAR, '1998', PCOUNTER,0)) YR_1998, SUM(DECODE( PYEAR, '1999', PCOUNTER,0)) YR_1999, SUM(DECODE( PYEAR, '2000', PCOUNTER,0)) YR_2000, SUM(DECODE( PYEAR, '2001', PCOUNTER,0)) YR_2001 FROM (SELECT TO_CHAR(AP.INQUIRY_DATE,'RRRR') PYEAR, DECODE (NR.N, 1, 'PRODUCT 1', 2, 'PRODUCT 2', 'PRODUCT 3') PNAME, SUM( DECODE (NR.N, 1, INSTR(NVL(PRODUCT1,'N'),'Y'), 2, INSTR(NVL(PRODUCT2,'N'),'Y'), INSTR(NVL(PRODUCT3,'N'),'Y'))) PCOUNTER FROM ALL_PRODUCTS AP, NR WHERE NR.N <= 3 GROUP BY TO_CHAR(AP.INQUIRY_DATE,'RRRR'), DECODE (NR.N, 1, 'PRODUCT 1', 2, 'PRODUCT 2', 'PRODUCT 3')) P GROUP BY P.PNAME;
...gives the desired result. In order to build a table of integers, the following script is useful. There are many uses for a table of integers and I usually have one around with 64K or 128K rows. To build the table of integers:
-- script creates a table of integers from 1 to 64K CREATE TABLE NR ( N NUMBER NOT NULL, PRIMARY KEY ( N )) ORGANIZATION INDEX ; INSERT INTO NR VALUES(1); -- REPEAT UNTIL YOU HAVE THE DESIRED NUMBER OF ROWS. I FIND 64K OR 128K A USEFUL SIZE INSERT INTO NR SELECT N + (SELECT MAX(N) FROM NR) FROM NR; / / / / / / / / / / / / / / / COMMIT;
Brian Peasland. writes: As a general rule of thumb, if it can be done in SQL, do it in SQL. If not, then look towards PL/SQL to do the job. Lucy L. hit the nail right on the head in this regard. Kudos to her!
Mark G. writes: I agree with Lucy. A simple SQL solution is almost always preferable to a PL/SQL solution, at least from a performance perspective. The only exception I have seen is a PL/SQL function to return a single value (or null) from a table instead of an outer join. This is only when an outer join would be needed (because some records are missing in the "outer joined" table) and it varies with different versions of Oracle and with the optimizer settings.
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2002