Home » Developer & Programmer » Forms » list_item selection problem (form developer 10g)
| list_item selection problem [message #567573] |
Wed, 03 October 2012 06:56  |
 |
oraclehi
Messages: 41
Registered: July 2012
Location: India
|
Member |
|
|
Hi
i am facing a problem in functionality of a form as there are different search parameters:
date from: text item-->mandatory
date to: text item---->mandatory
house type: list item-->optional
form_status: list item->optional
order by: list item---->optinal
untill i don't select house type by list item everything is working fine and gives desired result, even with house type if i select any house type result is fine but as i select last list item of house type which is null as i want result without house type then it gives no any data as data is present in database.
for example:
date from: 20-05-2009
date to: 21-05-2009
with these two i got the correct result, then i select
house type: 3
i got the correct result and when i change list item to any value like: 1,2,3.....
it is working fine but as i want result without house type by selecting the null value in list item as:
date from: 20-05-2009
date to: 21-05-2009
it is not picking any data from database. but the same search criteria in 1st case as i described above giving result.
declare
qry varchar2(5000);
n number;
alert number;
v_order varchar2(500);
cursor c2 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where regno= :control.v_regno or form_no= :control.v_form_no ;
Begin
if :control.rg='B' then
if :control.REC_FROM is not null and :control.REC_TO is not null and :control.HOUSE_TYPE is not null then
IF :control.v_form_status is null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO
and qtr_type=:control.house_type ';
v_order:=:control.status;
set_block_property('ol_registration', default_where, qry);
set_block_property('ol_registration',order_by, v_order);
go_block('ol_registration');
clear_block(no_validate);
execute_query;
END IF;
IF :control.v_form_status is not null then
qry := ' RECD_ON between :control.REC_FROM and :control.REC_TO
and qtr_type=:control.house_type and form_status=:control.v_form_status ';
v_order:=:control.status;
set_block_property('ol_registration', default_where, qry);
set_block_property('ol_registration',order_by, v_order);
go_block('ol_registration');
clear_block(no_validate);
execute_query;
END IF;
end if;
--------------------------------------------------------------
IF :control.REC_FROM is not null and :control.REC_TO is not null and :control.HOUSE_TYPE is null then
call_cur;
END IF;
elsif :control.rg='S' then
if :control.v_regno is not null or :control.v_form_no is not null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c2;
loop
fetch c2 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c2%notfound;
next_record;
end loop;
close c2;
first_record;
end if;
end if;
end;
---------------------
-----------coading on call_cur procedure------------
PROCEDURE call_cur IS
BEGIN
declare
---------------------------------for bulk radio button----------------------------------
cursor c1 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
order by edt,name,regno,form_no;
--------------------------------------------------------------------------------------
cursor c4 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
order by :control.STATUS;
cursor c5 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
order by name;
cursor c6 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
order by form_no;
cursor c7 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
order by recd_on;
--------------------------------------------------------------------------------------- */
cursor c3 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
and form_status= :control.v_form_status ORDER BY edt,name,form_no,form_status ;
---------------------------------------------------------------------------------------
cursor c8 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
and form_status= :control.v_form_status ORDER BY :control.STATUS;
cursor c9 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
and form_status= :control.v_form_status ORDER BY name ;
cursor c10 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
and form_status= :control.v_form_status ORDER BY form_no ;
cursor c11 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
and form_status= :control.v_form_status ORDER BY recd_on ;
------------------------------------------------------------------------------------------
Begin
if :control.rg='B' then
if :control.REC_FROM is not null and :control.REC_TO is not null then
IF :control.v_form_status is null and :control.status is null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c1;
loop
fetch c1 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c1%notfound;
next_record;
end loop;
close c1;
first_record;
END IF;
-------------------
-------------------
IF :control.v_form_status is not null and :control.status is null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c3;
loop
fetch c3 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c3%notfound;
next_record;
end loop;
close c3;
first_record;
END IF;
--------------------
IF :control.v_form_status is null and :control.status='edt' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c4;
loop
fetch c4 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c4%notfound;
next_record;
end loop;
close c4;
first_record;
END IF;
------------------
IF :control.v_form_status is null and :control.status='name' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c5;
loop
fetch c5 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c5%notfound;
next_record;
end loop;
close c5;
first_record;
END IF;
----------------
IF :control.v_form_status is null and :control.status='form_no' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c6;
loop
fetch c6 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c6%notfound;
next_record;
end loop;
close c6;
first_record;
END IF;
-------------------------------
IF :control.v_form_status is null and :control.status='recd_on' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c7;
loop
fetch c7 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c7%notfound;
next_record;
end loop;
close c7;
first_record;
END IF;
---------------------------------*/
IF :control.v_form_status is not null and :control.status='edt' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c8;
loop
fetch c8 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c8%notfound;
next_record;
end loop;
close c8;
first_record;
END IF;
----------------------------------------------------------------------------------
IF :control.v_form_status is not null and :control.status='name' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c9;
loop
fetch c9 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c9%notfound;
next_record;
end loop;
close c9;
first_record;
END IF;
--------------------------------------------------------------------
IF :control.v_form_status is not null and :control.status='form_no' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c10;
loop
fetch c10 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c10%notfound;
next_record;
end loop;
close c10;
first_record;
END IF;
---------------------------------------------------------------------------------
IF :control.v_form_status is not null and :control.status='recd_on' then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c11;
loop
fetch c11 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c11%notfound;
next_record;
end loop;
close c11;
first_record;
END IF;
-----------------------------------------------------------------------------------
else
message('Please fill Received from and Received to date(s).'); message('.');
raise form_trigger_failure;
end if;
end if;
exception
when others then
null;
END;
END;
|
|
|
|
| Re: list_item selection problem [message #567582 is a reply to message #567573] |
Wed, 03 October 2012 07:29  |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's a lot of badly formatted code.
You need to debug it, because we can't - since we don't have your tables or data or form.
Use messages to work out which select is being run when the list item is blank. That should allow you to work out what the problem is.
If you'll still stuck tell us which select is being run and we'll go from there.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jul 05 21:40:13 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
