This works just fine.



Thank you!(multumesc!)



Bogdan





From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Virna Constantin
Sent: Tuesday, March 4, 2014 1:03 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Select stored in a table





:sql=replace(:sql,':lcIdContract',:lcIdContract);

:sql=replace(:sql,':lcIdRamura',:lcIdRamura);

...

On Tuesday, March 4, 2014 12:24 PM, Bogdan Mihalache <bogdan.mihalache@...> wrote:



I have this Sql statement strored in a table :

Select sum(secunde_fact) as cantitate From agenda Where id_contract = :lcIdContract and id_ramura = :lcIdRamura And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela And :ldPanala ) And facturabil = 1 and deleted = 0



I wanna used in a stored procedure, like this :

lcIdClient = id_client ;

lcIdContract = id_contract ;

lcIdRamura = id_ramura ;

lcIdSubramura = id_subramura ;

ldDela = lcDela ;

ldPanala = lcPanala ;

lnAn = Extract (Year From ldDela) ;

lnLuna = Extract (Month From ldDela) ;

For Select ab.um, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, aa.int_start,

aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, aa.id_pret_um,

aa.felpret, aa.ispretfix, ac.id_subramura

From contracte_det_pret aa

Left Join nom_contracte_pret_um ab on aa.id_pret_um = ab.id

Left Join nom_contracte_pret ac on aa.id_criteriu_pret = ac.id

Where aa.id_contract = :lcIdContract And ac.id_subramura = :lcIdSubramura

Into :um, :sql, :val1, :val2, :val3, :val4, :int_start,

:int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm,

:felpret, :ispretfix, :lcIdSubramura

do

begin

if (lower(:felpret) = 'pretunitar')

then

begin

Execute Statement Sql Into :lnCantitate ;

lnCantitate = coalesce(:lnCantitate, 0) ;

valoare = lnCantitate * val1 ;

suspend;

end





I am using IBExpert as a tool to view my database. In debug mode it works just fine. But if i hit play, i`m receivind an error, that he can`t fiind column lcIdContract, at this line(when he is trying to execute the sql statement) : Execute Statement Sql Into :lnCantitate ;





If i remove the variables(:lcIdContract, :lcIdRamura, etc.) from the sql statement, that is stored in the table, it`s working, but I really need to keep them, and keep the sql in the table. Does anyone has a solution to my problem?



Bogdan MIHALACHE









[Non-text portions of this message have been removed]