Home » Developer & Programmer » Forms » how to handle multi row select statement.
how to handle multi row select statement. [message #492959] Thu, 03 February 2011 22:57 Go to next message
helloworld_28
Messages: 60
Registered: October 2010
Member
hi,

iam doing simple project, where i need to check date(form which we give in run time) with database column date.problem i have writte n the code for program now i found that select statement is return multi rows. what is best way to do this please help.
code is 

when_button_pressed trigger.

declare
	 fdcode dmas.dcode%type;
	 fcodno dmas.codno%type;
	 famtrs dmas.amtrs%type;
	 fdate donres.rdate%type;
	 begin
	select dcode,codno,amtrs into fdcode,fcodno,famtrs  from dmas where descr=:BLOCK10.FOODTYPE;
	select rdate into fdate from donres;
	loop 
		CASE fdcode
	WHEN 'f' then 
		if  fdate = :BLOCK10.REDATE  AND (fdcode = 'f' or fdcode='b' or fdcode='l' or fdcode='d' or fcodno='s') then
			message( 'FOOD ALREADY BOOKED');
		ELSE
			:BLOCK10.AMOUNT := famtrs;
			exit;
		end if;
		when 'b' then
			 if fdate=:BLOCK10.REDATE AND (fdcode ='f' or fcodno='b') then
			 		message( 'FOOD ALREADY BOOKED');
			 else
			 	:BLOCK10.AMOUNT := famtrs;
			 	exit;	
			 end if;
		when 'l' then
			 if fdate =:BLOCK10.REDATE AND (fdcode='f' or fcodno='l') then
			 	message( 'FOOD ALREADY BOOKED');
			 else
			 	:BLOCK10.AMOUNT := famtrs;
			 	exit;
			 end if;
		 when 'd' then
			 if fdate =:BLOCK10.REDATE AND (fdcode='f' or fcodno='d') then
			 	message( 'FOOD ALREADY BOOKED');
			 else
			 	:BLOCK10.AMOUNT := famtrs;
			 	exit;
			 end if;
		 when 's' then 	 
			 if fdate=:BLOCK10.REDATE AND (fdcode='f' or fcodno='s') then
			 	message( 'FOOD ALREADY BOOKED');
			 else
			 	:BLOCK10.AMOUNT := famtrs;
			 	exit;
			 end if;
	end case;
	 end loop;
exception
	when too_many_rows then
	message('error too many ');
		end;
Re: how to handle multi row select statement. [message #492972 is a reply to message #492959] Fri, 04 February 2011 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
what is best way to do this

It depends on what is true.

If you expect it to return more than a single record, then handling it with an exception is the right way.

If you need to select only one record, you must ensure that WHERE clause is restrictive enough so that it really returns just one record.

If you are sure that WHERE clause - as it is written - is correct but it still returns many records, then the problem is in data (there are many rows, but there should have been only one. It might be because uniqueness is violated, which - furthermore - means that you (or whoever did that) didn't pay enough attention while designing tables you use).
Re: how to handle multi row select statement. [message #492989 is a reply to message #492972] Fri, 04 February 2011 02:09 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
	select rdate into fdate from donres;
	loop 

You've got a single row select immediately followed by a loop.
Are you expecting to loop over the results of that select?
If so you need to use a for loop. I strongly suggest you read up on the basics of select, cursors and loops.
The PL/SQL User's Guide and Reference would be the place to start.
Re: how to handle multi row select statement. [message #493106 is a reply to message #492972] Fri, 04 February 2011 22:45 Go to previous messageGo to next message
helloworld_28
Messages: 60
Registered: October 2010
Member
hi,

thanks for replying.

you mean to say that i have to use cursor because i thought about even i give where clause it restricts some ,most multi row will be there.if it then i have to create a cursor along with a loop,open the cursor then fetch value check using if clause and close cursur that have tobe done for each case statement. that what you say.
Re: how to handle multi row select statement. [message #493128 is a reply to message #493106] Sat, 05 February 2011 04:46 Go to previous message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
I say Read up on for loops in the documentation. It has examples.
Previous Topic: Auti increamnet using seq. in form level
Next Topic: Multilingual Form
Goto Forum:
  


Current Time: Tue Sep 17 16:18:27 CDT 2024