Re: sql query, help needed urgently, please

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 22 Apr 1999 11:58:54 GMT
Message-ID: <371f0e34.3789869@192.86.155.100>

A copy of this was sent to pgunda_at_aol.com (PGunda) (if that email address didn't require changing) On 21 Apr 1999 23:50:07 GMT, you wrote:

>I have a record like this
>Emp_id Week_end_date Monday Tuesday wednesday thurday Friday
>111 25-apr-99 8 8 8
>
>
>The fields Monday, Tuesday etc holds no_of_hours worked
>
>This one record from the table, in a view I have to show like this
>
>Emp_id Date Hours
>111 19-apr-99 8
>111 20-apr-99 0
>111 21-apr-99 8
>111 22-apr-99 8
>111 23-apr-99 0
>
>Is it possible to get it through a sql.
>
>Oracle experts please respond
>
>Thanks in advance
>GR

by using a table with the numbers 3, 4, 5, 6, 7 in it to join to and the decode function, yes we can. We will join every row in your table to every row in the other table (cartesian product) so that every row in your table will be output 5 times. We can use the numbers 3..7 to adjust the week_end_date to the correct day (your week_end_date appears to be Sunday, Sunday - 3 = Friday, Sunday - 7 = Monday). We can use the numbers 3..7 to pick off the right column for that given day using decode. Here is an example:

SQL> create table t ( emp_id number,

  2                   week_end_date date,
  3                   monday number,
  4                   tuesday number,
  5                   wednesday number,
  6                   thursday number,
  7                   friday number );

Table created.

SQL> insert into t values ( 111, '25-apr-99', 8, 0, 8, 8, 0 );
SQL> insert into t values ( 222, '25-apr-99', 0, 8, 0, 8, 8 );
SQL> insert into t values ( 111, '18-apr-99', 0, 5, 4, 8, 9 );
SQL> insert into t values ( 222, '18-apr-99', 0, 9, 0, 0, 8 );

SQL> break on emp_id skip 1

SQL> select emp_id,

  2         week_end_date - index_rows.i + 1 theDate,
  3         decode( index_rows.i, 7, monday,
  4                               6, tuesday,
  5                               5, wednesday,
  6                               4, thursday,
  7                               3, friday,
  8                              null ) hours

9 from t, ( select rownum+2 i from all_users where rownum < 6 ) index_rows 10 order by 1, 2
11 /

EMP_ID THEDATE HOURS

---------- --------- ----------
       111 12-APR-99          0
           13-APR-99          5
           14-APR-99          4
           15-APR-99          8
           16-APR-99          9
           19-APR-99          8
           20-APR-99          0
           21-APR-99          8
           22-APR-99          8
           23-APR-99          0

       222 12-APR-99          0
           13-APR-99          9
           14-APR-99          0
           15-APR-99          0
           16-APR-99          8
           19-APR-99          0
           20-APR-99          8
           21-APR-99          0
           22-APR-99          8
           23-APR-99          8

20 rows selected.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Apr 22 1999 - 06:58:54 CDT