Home » SQL & PL/SQL » SQL & PL/SQL » need logic with decode
need logic with decode [message #252526] Thu, 19 July 2007 02:06 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i have a table


create table alerts(md date,nd number(4),status varchar2(1),id number(4))


now i have a piece of code, which verifies the status of
each id...it calculates the cd(current date) or md(modified
date) + number of days and compares it with sysdate,

if condition is met, it makes the status as inactive

the code is


DECLARE
cursor c1 is select id,md,nd from alerts;
begin

for r1 in c1 loop

	IF MD IS NOT NULL THEN

  		IF R1.MD + R1.ND <=SYSDATE THEN
   			update alerts set status='I' where id=r1.id;

 		END IF;

  		IF R1.STATUS=0 THEN

  			EXIT;

  		END IF;
	ELSE

               --nobody has modified, use createddate
  

		if r1.CD + r1.nd <= sysdate then
			update alerts set status='I' where id=r1.id;

		 end if;


		IF R1.STATUS=0 THEN

		EXIT;

		END IF;

END IF;


 end loop;

 EXCEPTION

 WHEN OTHERS THEN

 NULL;
 --- some extra code is also there
 end;




now i have to use decode instead of all these things
logic is

if modified date is null, use created date to calculate things,
and immediately check the status of the id..if its 0..

then further processing should NOT happen and control should

stop..same thing in the else part

i have 2 doutbs

1) how to use decode to minimize my code above..cause the code
is repeated in else part

2) is exit the keyword to stop execution and not allow to
continue? cause i have to stop if the status is 0..
and the code i am providing is only a part of a big proc..
so processing should stop if status is 0

can anyone please help me on this?

Re: need logic with decode [message #252529 is a reply to message #252526] Thu, 19 July 2007 02:10 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
somethign like

decode(md,null,dosomething,default)

dosomething means to go to the else part of my code

also we need to verify after updating the status immediately
whether the status is 0 or not

should i go for 2 decodes?

Re: need logic with decode [message #252532 is a reply to message #252526] Thu, 19 July 2007 02:14 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i forgot to include cd in the cursor definition

please include that too
Re: need logic with decode [message #252557 is a reply to message #252526] Thu, 19 July 2007 03:00 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
decode(md,null,decode(status,r1_rec.cd+r1_rec.nd<=sysdate,0),
decode(status,r1_rec.md+r1_rec.nd<=sysdate,0)




##############################################################################################################



decode(md,null,decode(r1_rec.cd+r1_rec.nd, <=sysdate, status=0),
decode(r1_rec.md+r1_rec.nd,<=sysdate,status=0)

something like this?

Re: need logic with decode [message #252559 is a reply to message #252526] Thu, 19 July 2007 03:02 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update alerts
set status = 'I'
where nvl(md,cd)+nd <= sysdate
/

Regards
Michel
Previous Topic: Help: Need to convert to PL/SQL
Next Topic: datatype INTEGER to datatype DATE
Goto Forum:
  


Current Time: Sat Dec 03 04:12:36 CST 2016

Total time taken to generate the page: 0.12622 seconds