Home » Developer & Programmer » Forms » wh is wrong in this code? n which trigger shld i use? (forms 6i)
wh is wrong in this code? n which trigger shld i use? [message #600336] Tue, 05 November 2013 00:40 Go to next message
sanjlaxmi
Messages: 24
Registered: October 2013
Junior Member
i want to fetch the a_name from applicant table if it is dere and want to show in name field(textbox) else it will b null..

and in which trigger shall i put this code so it will work
(after executing it will show output if the value is dere...so which trigger wiil b useful...)

declare
ct_no number :=0;

begin
select count(*) into :ct_no
from applicant
where appl_nmbr =:staff.ref.no;

if :ct_no > 0 then
:name :=a_name
else :name := null;
end if;

end;

pls give m esolution for this..
thanx in advance
Re: wh is wrong in this code? n which trigger shld i use? [message #600339 is a reply to message #600336] Tue, 05 November 2013 01:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your code is somewhat invalid; what is ":staff.ref.no"? Is it ":staff.ref_no" instead?

Do you really need to select COUNT? I suppose not.

I'd try POST-QUERY (and possibly WHEN-VALIDATE-ITEM) trigger.
begin
  select a.a_name
  into :block_name.name
  from applicant a
  where a.appl_nmbr = :staff.ref_no;

exception
  when no_data_found then
    null;
end;


In your next messages, please, write plain English. Avoid "wh", "dere", "b" and similar shortcuts. Also, spend no more than 10 seconds and view this to learn how to make your code easier to read.
Re: wh is wrong in this code? n which trigger shld i use? [message #600346 is a reply to message #600339] Tue, 05 November 2013 02:14 Go to previous messageGo to next message
sanjlaxmi
Messages: 24
Registered: October 2013
Junior Member
@Littlefoot
yes the field was staff.ref_no only.
and i tried in post-query trigger and its working but giving exception at the begining,
" post-query trigger raised unhandled exception ORA-01422"..
Re: wh is wrong in this code? n which trigger shld i use? [message #600348 is a reply to message #600346] Tue, 05 November 2013 02:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Too many rows, eh? It means that more than a single record is fetched from the APPLICANT table for one REF_NO. What do you want to do with these records? Display only one? If so, which one? Or ...?
Re: wh is wrong in this code? n which trigger shld i use? [message #600350 is a reply to message #600348] Tue, 05 November 2013 02:56 Go to previous messageGo to next message
sanjlaxmi
Messages: 24
Registered: October 2013
Junior Member
yes,i checked and there were more than one record for one ref no..
Re: wh is wrong in this code? n which trigger shld i use? [message #600351 is a reply to message #600350] Tue, 05 November 2013 02:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I know that, Oracle knows that, and now you know that. The question is: what will you do about it?
Re: wh is wrong in this code? n which trigger shld i use? [message #600390 is a reply to message #600351] Tue, 05 November 2013 23:35 Go to previous messageGo to next message
sanjlaxmi
Messages: 24
Registered: October 2013
Junior Member
Hi
post query trigger rasing exception if field have same values for one ref no
but i want to apply same no to another record i.e a.appl_nmbr = :staff.ref_no; and i want to update the first record with value null..
how to do this??

thanx in advance
Re: wh is wrong in this code? n which trigger shld i use? [message #600393 is a reply to message #600390] Wed, 06 November 2013 00:23 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Suppose that this is your APPLICANT table's contents (as far as APPL_NMBR equals 1):
APPL_NMBR   A_NAME
1           sanjlaxmi
1           Littlefoot

In a POST-QUERY trigger, you chose to select A_NAME from that table where APPL_NMBR equals value stored in :STAFF.REF_NO item. In this case, :STAFF.REF_NO = 1. So, which one of A_NAME values (sanjlaxmi? Littlefoot?) do you want to display on the screen?

You said you want to put NULL into the first record's NAME item. OK, that could be done (you'd have to check :SYSTEM.TRIGGER_RECORD value and decide what to do). But then you'll come to the second record and the same SELECT statement will fail, again, because there'll be another :STAFF.REF_NO = 1, and query would - again - return TOO-MANY-ROWS.

Therefore: either there's something wrong with the APPLICANT table (lack of primary/unique key/index which allowed two (or even more) same values in APPL_NMBR column), or you have to add another condition(s) into POST-QUERY's WHERE clause which would make sure that only one record is returned, or find some other way out of it.
Re: wh is wrong in this code? n which trigger shld i use? [message #600453 is a reply to message #600393] Wed, 06 November 2013 21:20 Go to previous message
sanjlaxmi
Messages: 24
Registered: October 2013
Junior Member
thanx a ton Smile
Previous Topic: upload file using oracle 11g forms
Next Topic: select value from dynamic list item..
Goto Forum:
  


Current Time: Thu Mar 28 04:03:14 CDT 2024