Home » Developer & Programmer » Forms » list_item selection problem (form developer 10g)
list_item selection problem [message #567573] Wed, 03 October 2012 06:56 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
Re: list_item selection problem [message #567648 is a reply to message #567582] Thu, 04 October 2012 00:02 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you are on 10g, you might run the form in debug mode. That's a helpful feature.
Re: list_item selection problem [message #567661 is a reply to message #567648] Thu, 04 October 2012 02:14 Go to previous messageGo to next message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
thanks for your suggestion as i'm going through debug mode it is showing an error
frm-40602:cannot insert into or update data in a view.
Re: list_item selection problem [message #567664 is a reply to message #567661] Thu, 04 October 2012 02:24 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you'll need to create INSTEAD OF database triggers which will handle these operations.
Re: list_item selection problem [message #567668 is a reply to message #567664] Thu, 04 October 2012 03:21 Go to previous message
oraclehi
Messages: 41
Registered: July 2012
Location: India
Member
thanks to all for your great help, now it is working fine as i read some previous posts on this suggested

Set the key mode property to Unique in the block properties. If this doesn't work, set the key mode property to non-updatable and assign atleast one column as a primary key. Make sure that column is the actual primary key in the underlying table.
thanks everybody for your help Smile Smile
Previous Topic: FRM-40209 : Field must be of form XX,XX,XXX coming several times
Next Topic: Date field gets disabled when a date format is given
Goto Forum:
  


Current Time: Thu Dec 18 13:41:43 CST 2014

Total time taken to generate the page: 0.13167 seconds