| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Test for exerts only!!!!
A copy of this was sent to tkyte_at_us.oracle.com (Thomas Kyte)
(if that email address didn't require changing)
On Tue, 08 Jun 1999 01:48:11 GMT, you wrote:
>A copy of this was sent to Chris Close <mrclose_at_flash.net>
>(if that email address didn't require changing)
>On Mon, 07 Jun 1999 19:58:39 GMT, you wrote:
>
>>--------- Oracle test ----------
>>
>>You have the following three tables:
>>
>
>you need to get new tables. east coast and west coast -- bad design. but
>anyway one way is:
>
>SQL> l
> 1 select last_name || ', ' || first_name name,
> 2 a.client_number,
> 3 cash,
> 4 chck "CHECK",
> 5 amt
> 6 from ( select client_number,
> 7 sum(decode( deposit_type, 'CASH', deposit_amount, 0 )) cash,
> 8 sum(decode( deposit_type, 'CHECK', deposit_amount, 0 )) chck,
> 9 sum(deposit_amount) amt
> 10 from ( select * from west_coast union all
> 11 select * from east_coast )
> 12 group by client_number
> 13 ) a, client_data b
> 14 where a.client_number = b.client_number
> 15* order by last_name asc
>SQL> /
>
>NAME CLIENT_NUMBER CASH CHECK AMT
>------------------------------ ------------- ---------- ---------- ----------
>Blass, Bill 100002 50.25 0 50.25
>Blass, Betty 100005 500 0 500
>Hunt, Kathy 100004 0 250.5 250.5
>Jones, Joe 100001 125.5 50.5 176
>Smith, Jane 100003 0 100 100
>
>5 rows selected.
>
>
>
>
>[snip]
>
>>
>>Create a single SQL query to join the three tables together and show the
>>
I guess if you really really want to join (and not union) you could of course do the following:
1 select last_name, last_name || ', ' || first_name name,
2 a.client_number, 3 sum(decode(b.deposit_type,'CASH',nvl(b.deposit_amount,0),0))/ 4 decode( count(distinct c.rowid), 0, 1, count(distinct c.rowid) ) + 5 sum(decode(c.deposit_type,'CASH',nvl(c.deposit_amount,0),0))/ 6 decode( count(distinct b.rowid), 0, 1, count(distinct b.rowid) ) 7 cash, 8 sum(decode(b.deposit_type,'CHECK',nvl(b.deposit_amount,0),0))/ 9 decode( count(distinct c.rowid), 0, 1, count(distinct c.rowid) ) + 10 sum(decode(c.deposit_type,'CHECK',nvl(c.deposit_amount,0),0))/ 11 decode( count(distinct b.rowid), 0, 1, count(distinct b.rowid) ) 12 "CHECK", 13 sum(decode(b.deposit_type,'CASH',nvl(b.deposit_amount,0),0))/ 14 decode( count(distinct c.rowid), 0, 1, count(distinct c.rowid) ) + 15 sum(decode(c.deposit_type,'CASH',nvl(c.deposit_amount,0),0))/ 16 decode( count(distinct b.rowid), 0, 1, count(distinct b.rowid) ) 17 + 18 sum(decode(b.deposit_type,'CHECK',nvl(b.deposit_amount,0),0))/ 19 decode( count(distinct c.rowid), 0, 1, count(distinct c.rowid) ) + 20 sum(decode(c.deposit_type,'CHECK',nvl(c.deposit_amount,0),0))/ 21 decode( count(distinct b.rowid), 0, 1, count(distinct b.rowid) ) 22 amt
NAME CLIENT_NUMBER CASH CHECK AMT ------------------------------ ------------- ---------- ---------- ---------- Blass, Betty 100005 500 0 500 Blass, Bill 100002 50.25 0 50.25 Hunt, Kathy 100004 0 250.5 250.5 Jones, Joe 100001 125.5 50.5 176 Smith, Jane 100003 0 100 100>>Order the query by last name of the client ascending.
>>amount of money deposited by cash, check and total in separate columns.
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |