Re: Problem with decode function with pl/sql cursor in function

Nicolas Rancourt wrote:
>
> Hi..
>
> Oracle version 7.1.3.......etc...
>
> I've a problem with the decode function with this context :
>
> select cos.cos_amount,
> decode(cos.cot_code,'LAN',1,'FPR',2,'FPF',3)
> from cost cos
> where cos.cot_code in ('LAN','FPR','FPF')
> order by 2 desc;
>
> this sqlcode work properly in sql but when I put it on a pl/sql cursor in
> function,
> and when I execute this function, sql return this message
>
> ora-001413 no_data_found...
>
> Anybody have solution for my problem other than put in alphabetic order
>
> thank's
>
> --
> Nicolas.Rancourt_at_videotron.ca
> Groupe Progestic ( www.groupe-progestic.com )
> ---------------------------------------------------------------------
> Conseillers en gestion et en informatique
> Bureau : 514-842-7995
> Fax : 514-842-1602

I have had a similar problem and it seems that the problem is likely to be in the use of ORDER BY, rather than in the decode. There seems to be a limitation (which I can't find documented -- anyone??) that a PL/SQL function used in a SELECT statement is not allowed to have one of these, whatever values are assigned to the restrict_references pragma.

(In any case, if order by / decode *is* allowed, your statement could equally well have been:
select cos.cos_amount
from cost cos
where cos.cot_code in ('LAN','FPR','FPF') order by decode(cos.cot_code,'LAN',3,'FPR',2,'FPF',1); )

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"