how to handle multi row select statement. [message #492959] |
Thu, 03 February 2011 22:57 |
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 #493106 is a reply to message #492972] |
Fri, 04 February 2011 22:45 |
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.
|
|
|
|