Home » SQL & PL/SQL » SQL & PL/SQL » cursor & decode
cursor & decode [message #192642] Wed, 13 September 2006 02:35 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
There are 2 column in the table

amount_dr amount_cr


now there is always two records exist in the table for a particular combination like

id	amount_dr    amount_cr		
1	100		0		
2	0		100
3	0		500
4 	500		0
5	400		0
6	600		98
7	0		400
8	98		600



like wise

I know only one line info ie line , then i want to get the corresponding line of based out of the amount.

I have written 2 cursor, first to select the particular row than second to get correspoing counter line..

My first cursor is fine and in second cursor i am passing additional parameter as amount.

cursor1 ..
select id,amount_dr,amount_cr
     from x
     where id =1;
     
cursor 2(p_amount number)
   select id,amount_dr,amount_cr
          from x
     where amountdr =decode(p_amount,0,amount_cr,amount_dr)
     
_
here i want to get the corresponding record based out of amount field.

I am bit confused to use of Decode Function , how to get the adject line ie for 1 get 2
..

Can anyone help me where to pass the decode in query , so do we need to manipulate some where else..

Thanks in advance.
Re: cursor & decode [message #192658 is a reply to message #192642] Wed, 13 September 2006 03:12 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Sanjit,
Please refer the link:
http://www.oreview.com/9603cop.htm

You will get enough information on DECODE with examples.


Thanks,
Mona
Re: cursor & decode [message #192708 is a reply to message #192658] Wed, 13 September 2006 05:50 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
what is your expected output???
Re: cursor & decode [message #192761 is a reply to message #192708] Wed, 13 September 2006 08:34 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
i want to get the row select which is amount couterpart

if first cursor pick row#1, then want to pick second row which is row#2, similary
for row#6 selected then want rownum#8 like wise
so i am not able to swap the value in the second cursor
thats why i am looking some help

i have tried couple of option but no luck
Re: cursor & decode [message #192770 is a reply to message #192761] Wed, 13 September 2006 08:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect you've got a problem, because if you've got two lines for the same amount, how are you planning to tell the difference between them.
Still, that's your problem, not mine Cool

create table temp_cr_db (id  number, dr number, cr number);

insert into temp_cr_db values (1,	100	,	0		);
insert into temp_cr_db values (2,	0	,	100);
insert into temp_cr_db values (3,	0	,	500);
insert into temp_cr_db values (4,	500	,	0);
insert into temp_cr_db values (5,	400	,	0);
insert into temp_cr_db values (6,	600	,	98);
insert into temp_cr_db values (7,	0	,	400);
insert into temp_cr_db values (8,	98	,	600);

select t1.id,t1.dr,t1.cr,t2.id,t2.dr,t2.cr
from   temp_cr_db t1, temp_cr_db t2
where  t1.dr > 0
and    t1.dr = t2.cr
order by t1.id

        ID         DR         CR         ID         DR         CR
---------- ---------- ---------- ---------- ---------- ----------
         1        100          0          2          0        100
         4        500          0          3          0        500
         5        400          0          7          0        400
         6        600         98          8         98        600
         8         98        600          6        600         98
Previous Topic: Procedure Reference by
Next Topic: Logical Delete
Goto Forum:
  


Current Time: Tue Dec 06 06:33:39 CST 2016

Total time taken to generate the page: 0.28041 seconds