Kindly help me out !!! [message #239451] |
Tue, 22 May 2007 08:01  |
cutsmartprem
Messages: 62 Registered: November 2006
|
Member |
|
|
Hi All,
Request you to kindly help me out....
I have a table as shown below
month currency amount
may USD 1000
may USD 1000
may USD 0
may EUR 0
may EUR 0
may GBP 0
may GBP 0
i have to delete the row if the amount is zero. with the condition that
atleast a single row must be there for a currency even if both
the amount is zero.
The result i need is
month currency amount
may USD 1000
may USD 1000
may EUR 0
may GBP 0
Thanks in advance
|
|
|
|
Re: Kindly help me out !!! [message #239472 is a reply to message #239451] |
Tue, 22 May 2007 08:25   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Try the following. Check the table before you commit. **Warning, this is not tested code**
delete from my_table a
where rowid >
(select min(b.rowid)
from my_table b
where a.currency = b.currency
and a.currency_rate = b.currency_rate
and a.currency_month = b.currency_month)
and a.currency_rate = 0;
|
|
|
Re: Kindly help me out !!! [message #239476 is a reply to message #239461] |
Tue, 22 May 2007 08:34   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
A combination of DECODE/CASE and DENSE_RANK() could help you out:
DECODE will take the DENSE_RANK if the value is zero or else it will take the value 1. Once we have that we can add a where clause saying that only the records that have our decode value 1 are accepted:
WITH yourtable AS
(SELECT 'may' themonth
, 'USD' thecur
, 1000 theval
FROM DUAL
UNION ALL
SELECT 'may' themonth
, 'USD' thecur
, 1000 theval
FROM DUAL
UNION ALL
SELECT 'may' themonth
, 'USD' thecur
, 0 theval
FROM DUAL
UNION ALL
SELECT 'may' themonth
, 'EUR' thecur
, 0 theval
FROM DUAL
UNION ALL
SELECT 'may' themonth
, 'EUR' thecur
, 0 theval
FROM DUAL
UNION ALL
SELECT 'may' themonth
, 'GBP' thecur
, 0 theval
FROM DUAL
UNION ALL
SELECT 'may' themonth
, 'GBP' thecur
, 0 theval
FROM DUAL)
SELECT themonth
, thecur
, theval
FROM ( SELECT themonth
, thecur
, theval
, DECODE ( theval
, 0, DENSE_RANK () OVER (PARTITION BY themonth, thecur ORDER BY theval DESC, rownum)
, 1
) rn
FROM yourtable
) v
WHERE v.rn = 1
MHE
|
|
|
|
Re: Kindly help me out !!! [message #239494 is a reply to message #239480] |
Tue, 22 May 2007 08:57   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 22 May 2007 08:42 | Bill,
Your query does not work for the USD in the example (several rows with at least one with amount 0): it does not remove all the 0 (or by accident if there is only 1).
Regards
Michel
|
Rats... My mistake, I forgot about the USD example...
|
|
|
Re: Kindly help me out !!! [message #239505 is a reply to message #239451] |
Tue, 22 May 2007 09:32   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Here is the corrected code. I had to remove the join on the currency amount and every thing works fine.
xe>select * from my_table;
CUR CUR CURRENCY_RATE
--- --- -------------
may USD 1000
may USD 1000
may USD 0
may EUR 0
may EUR 0
may GBP 0
may GBP 0
7 rows selected.
xe>edit
Wrote file afiedt.buf
1 delete from my_table a
2 where rowid >
3 (select min(b.rowid)
4 from my_table b
5 where a.currency = b.currency
6 and a.currency_month = b.currency_month)
7* and a.currency_rate = 0
whb@xe>/
3 rows deleted.
xe>select * from my_table;
CUR CUR CURRENCY_RATE
--- --- -------------
may USD 1000
may USD 1000
may EUR 0
may GBP 0
|
|
|
|
Re: Kindly help me out !!! [message #239664 is a reply to message #239642] |
Wed, 23 May 2007 00:38   |
cutsmartprem
Messages: 62 Registered: November 2006
|
Member |
|
|
The answer you helped out is correct only for specific conditions
of the data in the tables.
One of the inconsistency i faced is shown below
SQL> SELECT ROWID,UPDAT,CURR,AMOUNT FROM CHK;
ROWID UPDAT CUR AMOUNT
------------------ --------- --- ----------
AADPJrAAaAAAKqkAAA 20-MAY-07 USD 0
AADPJrAAaAAAKqkAAB 20-MAY-07 USD 1000
AADPJrAAaAAAKqkAAC 20-MAY-07 USD 0
AADPJrAAaAAAKqkAAD 20-MAY-07 USD 0
SQL> delete from chk a
2 where rowid > (select min(rowid) from chk b where a.updat=b.updat and a.curr=b.curr)
3 and a.amount = 0;
2 rows deleted.
SQL> SELECT ROWID,UPDAT,CURR,AMOUNT FROM CHK;
ROWID UPDAT CUR AMOUNT
------------------ --------- --- ----------
AADPJrAAaAAAKqkAAA 20-MAY-07 USD 0
AADPJrAAaAAAKqkAAB 20-MAY-07 USD 1000
You can see that the first row for USD exists with amount =0
How can i get the solution for all cases.
|
|
|
Re: Kindly help me out !!! [message #239678 is a reply to message #239664] |
Wed, 23 May 2007 01:12  |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
cutsmartprem wrote on Wed, 23 May 2007 07:38 | How can i get the solution for all cases.
|
Didn't I tackle this one? Perhaps you got confused by the 'with clause'. That was included to simulate your data. Try the select alone:
SELECT updat
, cur
, amount
FROM ( SELECT updat
, cur
, amount
, DECODE ( amount
, 0, DENSE_RANK () OVER (PARTITION BY updat, cur ORDER BY amount DESC, rownum)
, 1
) rn
FROM yourtable
) v
WHERE v.rn = 1
MHE
|
|
|