Subject RE: [firebird-support] Select stored in a table
Author Bogdan Mihalache
This works just fine.

Thank you!(multumesc!)


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




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.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 =

Left Join nom_contracte_pret ac on aa.id_criteriu_pret =

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



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



Execute Statement Sql Into :lnCantitate ;

lnCantitate = coalesce(:lnCantitate, 0) ;

valoare = lnCantitate * val1 ;



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?


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