Oracle Tips and Tricks — David Fitzjarrell

June 26, 2012

Nullified Remains

Filed under: pl/sql — dfitzjarrell @ 18:58
Tags: , ,

Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite. It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs. I originally posted:

“Sadly Oracle Support may not do anything about this for two reasons:

1) It’s valid code.
2) It’s not causing another error to surface.

Amending my post to reflect the actual nature of the SR it’s now clear why this was opened — Oracle should never issue forms or production code using this exception ‘non-handler’ so it’s understandable why Oracle Support filed a bug report (bug number 14237626) for it. Why is coding “when others then null” not the best idea? Let’s look at some examples and see what could be hidden behind that glorious construct. Creating a table used in a previous post:

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table pktest(
2       pk number,
3       val1 number,
4       val2 number,
5       val3 number
6 );

Table created.

SQL>
SQL> --
SQL> -- Add the primary key
SQL> --
SQL> alter table pktest add constraint pktestpk primary key (pk);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> begin
2       for i in 1..25 loop
3             insert into pktest
4             values(i,i+1,i+2,i+3);
5       end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the data
SQL> --
SQL> commit;

Commit complete.

SQL>

Using this table/key combination let’s try some transactions that will generate errors; we’ll hide them by using “when others then null”:

SQL> --
SQL> -- Try and insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Intentionally obscure the original
SQL> -- error
SQL> --
SQL> begin
2       insert into pktest values (1,2, 3, 4);
3 exception
4       when others then
5             null;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>

An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely. The worst part of this is the code is declared to have run successfully; it appears that the insert was executed without error (which we know is NOT true) so the end user who ran this glorious piece of code has no idea his or her insert failed. These, of course, are not the only errors that can be hidden from view as other, more insidious errors can be ignored:

SQL>
SQL> --
SQL> -- Artificially generate some rather severe
SQL> -- errors
SQL> --
SQL> -- Ignore them in the exception handler
SQL> --
SQL> declare
  2          bad             exception;
  3          reallybad       exception;
  4          trulyreallybad  exception;
  5
  6          pragma exception_init(bad, -43); -- remote operation failed
  7          pragma exception_init(reallybad, -1019); -- unable to allocate memory on user side
  8          pragma exception_init(trulyreallybad, -1034); -- Oracle not available
  9  begin
 10          begin
 11                  raise bad;
 12          exception
 13                  when others then null;
 14          end;
 15          begin
 16                  raise reallybad;
 17          exception
 18                  when others then null;
 19          end;
 20          begin
 21                  raise trulyreallybad;
 22          exception
 23                  when others then null;
 24          end;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL>

Notice that even these errors were not reported, including the ‘Oracle not available’ error indicating connection to the database failed resulting in nothing being executed. Oracle reported that the PL/SQL ran successfully (meaning without error) all because of the ‘when others then null’ error mis-handler.

All of this transpires due to a lack of knowledge of what errors could be expected, a bout of laziness on the part of the developer or both — “Gee, I don’t know what other errors to expect but they can’t be serious so I’ll ignore them.” Any error is serious to the end user as it prevents work from completing, and if those errors are ignored and the code block execution looks successful then, when problems arise because of missing data, the issue is harder to troubleshoot. Absent an error message, no matter how trivial that error may seem to the developer, the end user has no indication that the insert/update/delete failed and has nothing to report to the help desk. Codng such mis-handlers also is a disservice to the developer/programmer as they won’t know what caused the problem any more than the user who ran the code. Not knowing the cause makes the solution that much more difficult to find. In that case everybody loses.

This is the third post on error handling and error reporting I’ve written, two on properly reporting error text so the end user can talk intelligently to the help desk on what went wrong, and this one, on making sure there IS an error message to report. All three should be read as a set (in my opinion) so that error messages are reported and the text that the end user sees provides useful information to the service desk personnel. This falls, really, on the developers and programmers as the error handlers they code directly affect what the end users see when errors arise, and the error messages should be clear enough for a non-technical Oracle user to describe to the help desk representatives with whom they speak. A little effort on the development side goes a long way in making troubleshooting easier if, or when, the time comes.

Third time is a charm.

Advertisements

February 27, 2012

Collecting Thoughts

Filed under: pl/sql — dfitzjarrell @ 20:21
Tags: , ,

Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them a collection/varray is defined as “an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.” The definition seems simple enough but it may be deceptively so. To make matters even more confusing to use a collection you must create a database type for it to reference; a varray requires a type as well but that type can be declared in the PL/SQL block. To clear the air a bit let’s go through some examples of defining and using collections and varrays: The first example uses a collection to store vendor ids and then process them for a report. The code builds two ‘tables’ and compares the contents of them by loading collections and comparing one collection to the other; output is displayed for the conditions of the two tables being equal and the two tables not being equal:


SQL> 
SQL> set serveroutput on size 1000000
SQL> 
SQL> CREATE OR REPLACE type integer_table is table of integer;
  2  /

Type created.

SQL> 
SQL> DECLARE
  2  
  3  
  4   vendor_key_table   integer_table;
  5   vendor_key_table2   integer_table;
  6   CURSOR tst
  7   IS
  8      SELECT   purch_order, SUM (dollars),
  9        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 10          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 11           FROM DUAL
 12         UNION ALL
 13         SELECT 1 purch_order, 8 dollars, 123452 vendor_key
 14           FROM DUAL
 15         UNION ALL
 16         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 17           FROM DUAL
 18         UNION ALL
 19         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 20           FROM DUAL
 21         UNION ALL
 22         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 23           FROM DUAL)
 24      GROUP BY purch_order;
 25  
 26  
 27    CURSOR tst2
 28   IS
 29    SELECT purch_order, SUM (dollars),
 30        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 31          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 32           FROM DUAL
 33         UNION ALL
 34         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 35           FROM DUAL
 36         UNION ALL
 37         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 38           FROM DUAL
 39         UNION ALL
 40         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 41           FROM DUAL)
 42      GROUP BY purch_order;
 43   v_purch_order    NUMBER;
 44   v_dollars    NUMBER;
 45  
 46  
 47   mystr     VARCHAR2 (4000);
 48  
 49  
 50   v_purch_order2     NUMBER;
 51   v_dollars2     NUMBER;
 52  
 53  
 54   mystr2      VARCHAR2 (4000);
 55  BEGIN
 56   OPEN tst;
 57   open tst2;
 58  
 59  
 60   LOOP
 61      mystr := NULL;
 62      mystr2 := NULL;
 63  
 64  
 65      FETCH tst
 66       INTO v_purch_order, v_dollars, vendor_key_table;
 67  
 68  
 69      FETCH tst2
 70       INTO v_purch_order2, v_dollars2, vendor_key_table2;
 71  
 72  
 73      IF tst%NOTFOUND
 74      THEN
 75         EXIT;
 76      END IF;
 77  
 78  
 79      if vendor_key_table = vendor_key_table2 then
 80          dbms_output.put_line('equal');
 81      else
 82          dbms_output.put_line(' not equal');
 83      end if;
 84  
 85  
 86      -- loop through the collection and build a string so that
 87      -- we can display it and prove that it works
 88      FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
 89       FROM TABLE (vendor_key_table))
 90      LOOP
 91         mystr := mystr || ',' || cur1.vendor_key;
 92         -- /* based on the value of the sum, you can do something with each detail*/
 93         -- if v_dollars > 12 then
 94         --   UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
 95         -- end if;
 96      END LOOP;
 97  
 98  
 99      DBMS_OUTPUT.put_line (   'Purchase Order-> '
100       || TO_CHAR (v_purch_order)
101       || ' dollar total-> '
102       || TO_CHAR (v_dollars)
103       || ' vendorkey list-> '
104       || SUBSTR (mystr, 2));
105  
106  
107      -- loop throught the collection and build a string so that
108      -- we can display it and prove that it works
109      FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110       FROM TABLE (vendor_key_table2))
111      LOOP
112         mystr2 := mystr2 || ',' || cur2.vendor_key;
113      END LOOP;
114  
115  
116      DBMS_OUTPUT.put_line (   'Purchase Order-> '
117       || TO_CHAR (v_purch_order2)
118       || ' dollar total-> '
119       || TO_CHAR (v_dollars2)
120       || ' vendorkey list-> '
121       || SUBSTR (mystr2, 2));
122  
123  
124   END LOOP;
125   CLOSE tst;
126   CLOSE tst2;
127  END;
128  /
not equal
Purchase Order-> 1   dollar total-> 11   vendorkey list-> 435235,123452
Purchase Order-> 1   dollar total-> 3   vendorkey list-> 435235
equal
Purchase Order-> 2   dollar total-> 16   vendorkey list-> 433738,387118,383738
Purchase Order-> 2   dollar total-> 16   vendorkey list-> 383738,387118,433738

PL/SQL procedure successfully completed.

SQL> 

Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.

The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known ‘exit when cursor%notfound;’ directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:

 
SQL> 
SQL> set echo on linesize 150 trimspool on
SQL> 
SQL> create table emp_test as select * From emp where 0=1;

Table created.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when get_emp_data%notfound;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000

9 rows selected.

SQL> 
SQL> truncate table emp_test;

Table truncated.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when emp_c.count = 0;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000
      7844 TURNER                                          1500
      7876 ADAMS                                           1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES                                            950
      7902 FORD                                            3000
      7934 MILLER                                          1300

14 rows selected.

SQL> 

What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the ‘exit when cursor%notfound;’ statement terminated the loop BEFORE we could get the remaining 5 records inserted into our table, thus they were lost. Using the ‘exit when collection.count = 0;’ construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the ‘exit when cursor%NOTFOUND;’ would process the remaining records but that, to me, defeats the purpose of the conditional exit. As I see it we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.]

Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table (I believe this originally appeared in ‘Morgan’s Library’ on the psoug.org website). The second part is a bit contrived as it uses a collection to process deletes from a table — deletes that could have easily been executed with a single SQL statement — but it does show the power of using collections and varrays:


SQL> 
SQL> set echo on timing on
SQL> 
SQL> create table temp_stg(
  2        usrname varchar2(30),
  3        usrjob varchar2(20),
  4        usrsal number
  5  );

Table created.

SQL> 
SQL> begin
  2        for i in 1..1000000 loop
  3        insert into temp_stg
  4        values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
  5        end loop;
  6  
  7        commit;
  8  
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> create table temp_ld(
  2        usrname varchar2(30),
  3        usrjob varchar2(20),
  4        usrsal number
  5  );

Table created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE bulk_load IS
  2  
  3  TYPE dfarray IS TABLE OF temp_stg%ROWTYPE;
  4  ld_dat dfarray;
  5  
  6  CURSOR stg IS
  7  SELECT *
  8  FROM temp_stg;
  9  
 10  BEGIN
 11   OPEN stg;
 12   LOOP
 13     FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
 14  
 15     FORALL i IN 1..ld_dat.COUNT
 16        INSERT INTO temp_ld VALUES ld_dat(i);
 17  
 18     EXIT WHEN ld_dat.count=0;
 19    END LOOP;
 20    COMMIT;
 21    CLOSE r;
 22  END bulk_load;
 23  /

Procedure created.

SQL> 
SQL> exec bulk_load;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from temp_stg where usrname like '%99999%';

USRNAME                        USRJOB                   USRSAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

USRNAME                        USRJOB                   USRSAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL> 

Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:


SQL> CREATE OR REPLACE PROCEDURE data_del IS
  2  
  3  TYPE dfarray IS TABLE OF temp_stg.usrname%TYPE;
  4  ld_dat dfarray;
  5  
  6  CURSOR stg IS
  7  SELECT usrname
  8  FROM temp_stg
  9  where usrname like '%9999%';
 10  
 11  BEGIN
 12   OPEN stg;
 13   LOOP
 14     FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
 15  
 16     FORALL i IN 1..ld_dat.COUNT
 17        delete from temp_ld where usrname = ld_dat(i);
 18  
 19     EXIT WHEN ld_dat.count=0;
 20    END LOOP;
 21    COMMIT;
 22    CLOSE stg;
 23  END data_del;
 24  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> exec data_del;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from temp_ld where usrname like '%99999%';

no rows selected

SQL> 

Collections can be a real timesaver for bulk processing of data; they may not be applicable in every situation but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.

Collections, anyone?

October 27, 2011

That’s Your Problem

Filed under: General — dfitzjarrell @ 17:13
Tags: ,

Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being ‘locked into’ a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let’s look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.

Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It’s gone through several iterations since its original offering and we’ll consider the most recent of those here. The problem:

Two mathematicians met at their college reunion. Wanting to keep current they started discussing their lives as only mathematicians can:

Mathematician #1: So, do you have any children?
Mathematician #2: Yes, three girls.
Mathematician #1: Wonderful! What are their ages?
Mathematician #2: I’ll give you a clue: the product of their ages is 36.
Mathematician #1: Hmmm, good clue but not nearly enough information.
Mathematician #2: Well, the sum of their ages is the number of people in this room.
Mathematician #1: (After looking about the room) That’s still not enough information.
Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
Mathematician #1: I have it now — say, are the twins identical?

Given that all of the information needed to solve the problem is in the problem, what are the ages of the three girls?

The problem seems unsolvable at first glance but there is more information available than is originally seen. Let’s state what we know from the problem:

1 — There are three girls
2 — Their ages, multiplied together, have a product of 36
3 — The sum of their ages is (to us, anyway) an undisclosed number
4 — The oldest daughter has a hamster with a wooden leg

A strange collection of facts, some might say. But, looking deeper into the problem we can find some logic and answers not obvious from casual inspection. Let’s start with the product of the ages:

  
SQL> --
SQL> -- Generate an age list for the girls
SQL> --
SQL> -- Maximum age is 36
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  )
  6  select *
  7  from age_list;

       AGE                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
         4                                                                      
         5                                                                      
         6                                                                      
         7                                                                      
         8                                                                      
         9                                                                      
        10                                                                      
        11                                                                      

       AGE                                                                      
----------                                                                      
        12                                                                      
        13                                                                      
        14                                                                      
        15                                                                      
        16                                                                      
        17                                                                      
        18                                                                      
        19                                                                      
        20                                                                      
        21                                                                      
        22                                                                      

       AGE                                                                      
----------                                                                      
        23                                                                      
        24                                                                      
        25                                                                      
        26                                                                      
        27                                                                      
        28                                                                      
        29                                                                      
        30                                                                      
        31                                                                      
        32                                                                      
        33                                                                      

       AGE                                                                      
----------                                                                      
        34                                                                      
        35                                                                      
        36                                                                      

36 rows selected.

SQL> 
SQL> --
SQL> -- Return only the age groupings whose product
SQL> -- is 36
SQL> --
SQL> -- Return, also, the sum of the ages
SQL> --
SQL> -- This restricts the set of values needed to
SQL> -- solve the problem
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  ),
  6  product_check as (
  7  	     select
  8  	     age1.age as youngest,
  9  	     age2.age as middle,
 10  	     age3.age as oldest,
 11  	     age1.age+age2.age+age3.age as sum,
 12  	     age1.age*age2.age*age3.age as product
 13  	     from age_list age1, age_list age2, age_list age3
 14  	     where age2.age >= age1.age
 15  	     and age3.age >= age2.age
 16  	     and age1.age*age2.age*age3.age = 36
 17  )
 18  select *
 19  from product_check
 20  order by 1,2,3;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         1          1         36         38         36                          
         1          2         18         21         36                          
         1          3         12         16         36                          
         1          4          9         14         36                          
         1          6          6         13         36                          
         2          2          9         13         36                          
         2          3          6         11         36                          
         3          3          4         10         36                          

8 rows selected.

SQL> 

Notice we return not only the product of the ages but also the sums of the various combinations, as we’ll need this information later on in the problem. Now another ‘fact’ emerges:

5 — Knowing the sum of the ages doesn’t help matters much

This reveals that there is more than one combination of ages which produce the same sum:

  
SQL> --
SQL> -- Find, amongst the acceptable values,
SQL> -- those sets where the summed value is
SQL> -- the same
SQL> --
SQL> -- This is necessary as providing the sum
SQL> -- was of little direct help in solving the
SQL> -- problem
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  ),
  6  product_check as (
  7  	     select
  8  	     age1.age as youngest,
  9  	     age2.age as middle,
 10  	     age3.age as oldest,
 11  	     age1.age+age2.age+age3.age as sum,
 12  	     age1.age*age2.age*age3.age as product
 13  	     from age_list age1, age_list age2, age_list age3
 14  	     where age2.age >= age1.age
 15  	     and age3.age >= age2.age
 16  	     and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19  	     select youngest, middle, oldest, sum, product
 20  	     from (
 21  		     select youngest, middle, oldest, sum, product,
 22  		     count(*) over (partition by sum) ct
 23  		     from product_check
 24  	     )
 25  	     where ct > 1
 26  )
 27  select *
 28  from summed_check;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          
         1          6          6         13         36                          

SQL> 

Now we know the number of people in the room and why the sum wasn’t enough information to solve the problem. The final ‘nail in the coffin’ (so to speak) is the owner of the hamster with the wooden leg; the problem states:

Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.

It’s not the hamster, it is the fact that the oldest daughter (there’s only one) exists. Knowing that last piece of information provides the final answer:

  
SQL> --
SQL> -- Return the one set of values meeting all of
SQL> -- the criteria:
SQL> --
SQL> -- Product of 36
SQL> -- Sum of some unknown number
SQL> -- Oldest child exists
SQL> --
SQL> with age_list as (
  2  	     select rownum age
  3  	     from all_objects
  4  	     where rownum <= 36
  5  ),
  6  product_check as (
  7  	     select
  8  	     age1.age as youngest,
  9  	     age2.age as middle,
 10  	     age3.age as oldest,
 11  	     age1.age+age2.age+age3.age as sum,
 12  	     age1.age*age2.age*age3.age as product
 13  	     from age_list age1, age_list age2, age_list age3
 14  	     where age2.age >= age1.age
 15  	     and age3.age >= age2.age
 16  	     and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19  	     select youngest, middle, oldest, sum, product
 20  	     from (
 21  		     select youngest, middle, oldest, sum, product,
 22  		     count(*) over (partition by sum) ct
 23  		     from product_check
 24  	     )
 25  	     where ct > 1
 26  )
 27  select *
 28  from summed_check
 29  where oldest > middle;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          

SQL> 

The ages of the girls are 9, 2 and 2 which also clarifies the question of identical twins.

The problem was solved in a systematic and (to me, at least) logical way by breaking the problem down into workable pieces.

So you don’t encounter such problems at college reunions or parties (what a dull life that must be); you may encounter them at work. This next problem was presented in the Oracle PL/SQL group:

Hi,

I have 3 columns of data

Column 1:subscription
Column 2: invoice number
Column 3: Service

I need to seperate the subscription types into new, renewals and additional
which is fine but the next bit i am having trouble

Each invoice number can have 1 or more service

e.g.
Invoice Number Service
123 Photocopying
123 Printing
123 Scan & Store
234 Photocopying
234 Scan & Store
345 Photocopying
345 Printing

I apply a rate for each service e.g.
photocopying = 1.5
printing = 1.7

but if Scan and store is in an invoice with photocopying we charge an extra
1.5

but if printing is a service with the scan and store a different rate
applies 1.7

so i can’t just count scan and store and apply a rate i have to figure out
if it is with photocopying or with printing and then apply the rate

What I want to be able to do is creat a table with columns that calculates
this
so i get a 4 columns:

Service usage rate total
photocopying 3 1.5 4.5
Printing 2 1.7 3.4
Scan & Store 1 1.5 1.5
Scan & Store w/Print 1 1.7 1.7

The problem comes in when i’m trying to count scan and store wit/without
printing. I can’t figure it out.

I import the report from an excel spreadsheet into acces and want to run a
query that does all this…

thanks in advance,
ainese

With this problem I decided to change the table a bit and add a numeric SERVICE_CD column:

  
SQL> Create table subscription(
  2          subscr_type varchar2(15),
  3          invoice number,
  4          service varchar2(40),
  5          service_cd number
  6  );

Table created.

SQL>
SQL> insert all
  2  into subscription
  3  values('RENEWAL',123,'Photocopying',0)
  4  into subscription
  5  values('RENEWAL',123,'Printing',2)
  6  into subscription
  7  values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
  8  into subscription
  9  values('ADDITIONAL',234,'Photocopying',0)
 10  into subscription
 11  values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
 12  into subscription
 13  values('NEW',345,'Photocopying',0)
 14  into subscription
 15  values('NEW',345,'Printing',2)
 16  select * From dual;

7 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Using the wm_concat() function and the BITAND operator produced results that will make the final solution easier to code; using BITAND allows Oracle to generate a result based upon the sum of the SERVICE_CD values and by properly choosing those SERVICE_CD entries make it easier to isolate the various combinations:

  
SQL> select subscr_type, invoice, services,
  2         bitand(service_cds, 0) col1,
  3         bitand(service_cds, 2) col2,
  4         bitand(service_cds, 7) col3
  5  from
  6  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  7  from subscription
  8  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                       COL1       COL2       COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW                    345 Photocopying,Printing                             0          2          2
RENEWAL                123 Photocopying,Printing,Scan & Store                0          2          7
ADDITIONAL             234 Photocopying,Scan & Store                         0          0          5

SQL>

Knowing which BITAND results indicate which chargeable combinations allows using DECODE to produce a version of the desired results:

  

SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum  of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
  2         decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
  3         decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
  4         decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
  5         decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
  6  from
  7  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  8  from subscription
  9  group by subscr_type,invoice);

SUBSCR_TYPE INVOICE SERVICES                           PHOTOCOPY PRINTING SCAN_STORE SCAN_STORE_PRT
----------- ------- ---------------------------------- --------- -------- ---------- --------------
NEW             345 Photocopying,Printing                    1.5      1.7          0              0
RENEWAL         123 Photocopying,Printing,Scan & Store       1.5      1.7          0            1.7
ADDITIONAL      234 Photocopying,Scan & Store                1.5        0        1.5              0
                                                       --------- -------- ---------- --------------
sum                                                          4.5      3.4        1.5            1.7

SQL>

All services in this example are charged the appropriate rates, including the adjustments made for certain combinations of service. For those using Oracle 12.1.0.x, where wm_concat doesn’t appear to be available, or for those not wanting to use wm_concat here is the same solution using listagg:

  

SQL>
SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum  of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
  2         bitand(service_cds, 0) col1,
  3         bitand(service_cds, 2) col2,
  4         bitand(service_cds, 7) col3
  5  from
  6  (select subscr_type, invoice, listagg(service,',') within group (order by subscr_type, invoice) services, sum(service_cd) service_cds
  7  from subscription
  8  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                       COL1       COL2       COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW                    345 Photocopying,Printing                             0          2          2
RENEWAL                123 Photocopying,Printing,Scan & Store                0          2          7
ADDITIONAL             234 Photocopying,Scan & Store                         0          0          5


SQL>
SQL> select subscr_type, invoice, services,
  2         decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
  3         decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
  4         decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
  5         decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
  6  from
  7  (select subscr_type, invoice, listagg(service,',') within group (order by subscr_type, invoice) services, sum(service_cd) service_cds
  8  from subscription
  9  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                  PHOTOCOPY   PRINTING SCAN_AND_STORE SCAN_AND_STORE_W_PRT
--------------- ---------- ---------------------------------------- ---------- ---------- -------------- --------------------
NEW                    345 Photocopying,Printing                           1.5        1.7              0                    0
RENEWAL                123 Photocopying,Printing,Scan & Store              1.5        1.7              0                  1.7
ADDITIONAL             234 Photocopying,Scan & Store                       1.5          0            1.5                    0
                                                                    ---------- ---------- -------------- --------------------
sum                                                                        4.5        3.4            1.5                  1.7




SQL>

One last problem is one found often on the web:

Display the second highest salary in the employee table
Display the employee id, first name, last name and salary for employees earning the second highest salary

Depending on which question is asked several solutions present themselves. The first is the ‘obvious’ solution:

  
SQL> select salary
  2  from
  3  (select salary from employees order by 1 desc)
  4  where rownum = 2;

no rows selected

SQL>

which doesn’t work because ROWNUM is never set to 1 so it can’t get to 2. A modest rewrite produces:

  
SQL> select salary
  2  from
  3  (select rownum rn, salary from
  4  (select salary from employees order by 1 desc))
  5  where rn = 2;

    SALARY
----------
     17000

1 row selected.

SQL>

which still might not be the correct answer as more than one person may have the same salary, including the highest. Another rewrite, using DENSE_RANK() provides the solution:

  
SQL> select salary from
  2  (select salary, dense_rank() over (order by salary desc) rk
  3          from employees)
  4  where rk=2;

    SALARY
----------
     17000
     17000

2 rows selected.

SQL>

To produce more information the above query needs a small modification:

  
SQL> select employee_id, first_name, last_name, salary
  2  from
  3  (select employee_id, first_name, last_name, salary, rank() over (order by salary desc) rk
  4   from employees)
  5  where rk =2;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000

2 rows selected.

SQL>

RANK() and DENSE_RANK() do just what they’re named — rank the requested values — but only DENSE_RANK() will not skip ranking numbers when duplicate values exist:

  
SQL> select salary, rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          4
     13500          5
     13000          6
     12000          7
     12000          7
     12000          7
     11500         10
...

Notice that the third highest salary is ranked 4 with RANK(); not so with DENSE_RANK():

  
SQL> select salary, dense_rank() over (order by salary desc) rk
  2          from employees;    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          3
     13500          4
     13000          5
     12000          6
     12000          6
     12000          6
     11500          7
     11000          8
     11000          8
     11000          8
     10500          9
     10500          9
     10000         10
     10000         10
     10000         10
     10000         10
...

which is why DENSE_RANK() was used to solve the problem.

Problem solving, when given a little thought, isn’t a terrible chore if you know how to read the problem and extract known information. From that you can eventually arrive at a solution (and, yes, multiple solutions can exist depending upon how you think about the problem). The above are examples to get you started thinking in the ‘right’ direction. As always, some practice at solving problems is recommended so take these problems, work them through, change data, work them through again (and you may find holes in my solutions that I didn’t consider). The more you practice, the more you learn.

A train leaves station A at 3:30 PM and travels west at 50 miles per hour …

Blog at WordPress.com.