Home » SQL & PL/SQL » SQL & PL/SQL » Kindly help me out !!!
Kindly help me out !!! [message #239451] Tue, 22 May 2007 08:01 Go to next message
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 #239461 is a reply to message #239451] Tue, 22 May 2007 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- What is your Oracle version?
- What did you already try?
- What is blocking you?

Regards
Michel
Re: Kindly help me out !!! [message #239472 is a reply to message #239451] Tue, 22 May 2007 08:25 Go to previous messageGo to next message
Bill B
Messages: 1484
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 Go to previous messageGo to next message
Maaher
Messages: 7062
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 #239480 is a reply to message #239472] Tue, 22 May 2007 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: Kindly help me out !!! [message #239494 is a reply to message #239480] Tue, 22 May 2007 08:57 Go to previous messageGo to next message
Bill B
Messages: 1484
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 Go to previous messageGo to next message
Bill B
Messages: 1484
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 #239642 is a reply to message #239505] Tue, 22 May 2007 23:06 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
thanks a lot...

it worked out. Thanks for all ur replies.
Re: Kindly help me out !!! [message #239664 is a reply to message #239642] Wed, 23 May 2007 00:38 Go to previous messageGo to next message
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 Go to previous message
Maaher
Messages: 7062
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
Previous Topic: Need help in creating materialized view for the data extracted from LONG datatype.
Next Topic: Functions..
Goto Forum:
  


Current Time: Thu Dec 08 06:02:22 CST 2016

Total time taken to generate the page: 0.11290 seconds