Home » Developer & Programmer » Forms » show all records. (form 6i)
show all records. [message #605033] Tue, 07 January 2014 01:44 Go to next message
shahzad-ul-hasan
Messages: 229
Registered: August 2002
Senior Member
Master Table Structure: Student (Class, Section, stuid, name, f_name,mob)
Detail Table Structure: Student (class,section,stuid,name,f_name,mob)
please see the attached file.i want to show all records based on the combo value. if the class value is prep then it shows me the only record of "Prep". please advised. i have used cusor.but show nothing.in this picture the combo values or query only. and in detail table is display items.
please advised../fa/11574/0/
Re: show all records. [message #605048 is a reply to message #605033] Tue, 07 January 2014 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 10572
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you using a cursor at all?
Why not use the standard execute query functionality?
Re: show all records. [message #605054 is a reply to message #605048] Tue, 07 January 2014 03:56 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
Yes @cookiemonster is right go through standard execute_query instead o cursor b'coz u have already done master_detail relation so no need for any cursor.


Thanks
Mughal
Re: show all records. [message #605056 is a reply to message #605054] Tue, 07 January 2014 04:07 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 229
Registered: August 2002
Senior Member
pls advised me the trigger how i can write...
Re: show all records. [message #605066 is a reply to message #605056] Tue, 07 January 2014 04:30 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The reason you got nothing in your form is the error caused in code you wrote. It raises (unhandled) TOO-MANY-ROWS error (see the status line at the bottom of the screenshot, it says ORA-01422). Therefore, you should first fix that. How? Copy that code into SQL*Plus, debug it, fix the error. Then copy/paste it back into a form trigger.

Besides, what kind of a data model is that? The first message you posted suggests that both master and detail tables are exactly the same. What's the purpose of such a decision? Sounds VERY strange to me.

Finally, I'd also suggest you to use standard, built-in Forms functionality which enables you to easily perform query operations - in that case, you need only one block and no code at all.
Re: show all records. [message #605070 is a reply to message #605056] Tue, 07 January 2014 04:39 Go to previous messageGo to next message
cookiemonster
Messages: 10572
Registered: September 2008
Location: Rainy Manchester
Senior Member
shahzad-ul-hasan wrote on Tue, 07 January 2014 10:07
pls advised me the trigger how i can write...


You don't need any triggers for standard forms query functionality
Re: show all records. [message #605071 is a reply to message #605066] Tue, 07 January 2014 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 10572
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot wrote on Tue, 07 January 2014 10:30
The reason you got nothing in your form is the error caused in code you wrote. It raises (unhandled) TOO-MANY-ROWS error (see the status line at the bottom of the screenshot, it says ORA-01422). Therefore, you should first fix that. How? Copy that code into SQL*Plus, debug it, fix the error. Then copy/paste it back into a form trigger.

I suspect the select will work just fine in sqlplus.
I suspect its a select to get all rows data and fetchs into a single set of datablock items.
Re: show all records. [message #605073 is a reply to message #605066] Tue, 07 January 2014 04:48 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 229
Registered: August 2002
Senior Member
DECLARE
	A VARCHAR2(200);
	B VARCHAR2(22);
BEGIN
	SELECT DISTINCT CLASS,SECTION INTO A,B FROM STUDENT;
	IF :CL=A AND :SEC=B THEN
		EXECUTE_QUERY;
	ELSE
		GO_BLOCK('STUDENT');
	END IF;
	END;

Please see the attached code..
select distinct stuid,mob from student
where class='PREP' and Section='A';
....
15 Rows Selected.

[Updated on: Tue, 07 January 2014 04:50]

Report message to a moderator

Re: show all records. [message #605079 is a reply to message #605073] Tue, 07 January 2014 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 10572
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're selecting multiple values into scaler variables - that'll always result in ORA-1422.
Again - why are you not using default query functionality?
Re: show all records. [message #605081 is a reply to message #605079] Tue, 07 January 2014 04:57 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 229
Registered: August 2002
Senior Member
advised me the default query..
Re: show all records. [message #605084 is a reply to message #605071] Tue, 07 January 2014 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
cookiemonster wrote on Tue, 07 January 2014 11:41

I suspect the select will work just fine in sqlplus.
I suspect its a select to get all rows data and fetchs into a single set of datablock items.

I was having on mind a subquery which causes the error, such as
select ...
from ...
where id = (select id        --> this!
            from ...
           )

Now it is obvious; you were right.



"Default query": you don't have to do anything. Create a new form, using Data Block wizard. Run the form. Enter query mode. Enter search criteria. Execute query.

That's all.
Re: show all records. [message #605130 is a reply to message #605084] Tue, 07 January 2014 08:55 Go to previous message
shahzad-ul-hasan
Messages: 229
Registered: August 2002
Senior Member
thanks for your help....
Previous Topic: clear_block
Next Topic: running error in form
Goto Forum:
  


Current Time: Thu Apr 17 08:21:16 CDT 2014

Total time taken to generate the page: 0.06252 seconds