| problem of query in a procedure [message #479372] |
Sat, 16 October 2010 07:53  |
narama87
Messages: 8
Registered: July 2010
|
Junior Member |
|
|
hello everybody,
i need your help ,
well , i have 2 items , a list ( id_sect) and a text_item (id_dde) ,and i used a trigger : when_list_changed ,
i have a table demand that contains the two values (id_dde a primary_key ) and (id_sect a foreign key) and i my goal is to affect the id_dde to the item id_dde when a value of id_sect is selected
but the query is not working
here is my code :
PROCEDURE pro_list_changed IS
id_dmde number;
BEGIN
if :DEMANDE.id_sect is not null AND :DEMANDE.id_dde is null then
select id_dde into id_dmde from demande where id_sect=:DEMANDE.ID_SECT;
:DEMANDE.ID_DDE:=id_dmde;
else if :DEMANDE.id_sect is not null and :DEMANDE.id_dde is not null then
:DEMANDE.id_dde:='';
:DEMANDE.ID_DDE:=id_dmde;
end if;
end if;
END;
i hope i had been clear enough ,
i'm using oracle developer suite 10 g ,
please help me with the not working query !!!!!
[mod-edit] color removed
[Updated on: Mon, 18 October 2010 13:02] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: problem of query in a procedure [message #479518 is a reply to message #479377] |
Mon, 18 October 2010 03:06  |
narama87
Messages: 8
Registered: July 2010
|
Junior Member |
|
|
hello,
thank you guys for answering ,
@Michel => the query is not working means that it has no output , i mean also that id_dmde is empty
@Irfan => the messages are not showing up , neither any alert , and the execution is stopped near this query
any ideas please ?
|
|
|
|
|
|
|
|
| Re: problem of query in a procedure [message #479552 is a reply to message #479550] |
Mon, 18 October 2010 05:33 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What is the point of using a loop to get a single value?
As for why the original wasn't working:
PROCEDURE pro_list_changed IS
id_dmde number;
BEGIN
if :DEMANDE.id_sect is not null AND :DEMANDE.id_dde is null then
select id_dde into id_dmde from demande where id_sect=:DEMANDE.ID_SECT;
:DEMANDE.ID_DDE:=id_dmde;
else if :DEMANDE.id_sect is not null and :DEMANDE.id_dde is not null then
:DEMANDE.id_dde:='';
:DEMANDE.ID_DDE:=id_dmde; ********This variable is null at this point*********
end if;
end if;
END;
|
|
|
|
| Re: problem of query in a procedure [message #479572 is a reply to message #479552] |
Mon, 18 October 2010 06:38 |
narama87
Messages: 8
Registered: July 2010
|
Junior Member |
|
|
i didn't find another way to use a query to get id_dde but the cursor method , it works perfectly.
and id_dmde that you said that is empty , it really is since the if not only the else , and that is the problem that still unresolved
|
|
|
|
| Re: problem of query in a procedure [message #479575 is a reply to message #479572] |
Mon, 18 October 2010 06:55 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
narama87 wrote on Mon, 18 October 2010 12:38i didn't find another way to use a query to get id_dde but the cursor method , it works perfectly.
If that cursor returns one row then you should be using select into like you did in the first place. If the cursor returns multiple rows then you're going to get indeterminate results.
narama87 wrote on Mon, 18 October 2010 12:38
it really is since the if not only the else
No idea what you mean there.
|
|
|
|
|
|
| Re: problem of query in a procedure [message #479614 is a reply to message #479612] |
Mon, 18 October 2010 09:35 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Then either:
1) The value of id_dde in the record you are selecting from the demande table is null. In which case your revised code is unlikely to fix the problem.
2) The original select was giving an error that you masked somehow. Most likely no_data_found or too_many_rows.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
