Just before the execute statement, insert sql_text into an external
table (so it won't be removed when the SP rolls back) to confirm what
SQL is being generated by the non-debugger version of the SP.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of william_yuwei
Sent: Tuesday, December 20, 2005 5:14 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Execute Statement Problem

Hi, All

I have the following code in one of my store procedure
REPORT_TARIFF(divisionid, excludeinvoices):

sql_text = 'select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY),
sum(id.AMOUNT) '
|| 'from INVOICE i ' ||
'inner join INVOICE_DETAIL id on id.INVOICE_ID = i.ID and '
||
'id.PART_ID is null and id.ADJUSTINVENTORY <> ''B'' '||
'inner join INVENTORY iv on iv.ID = id.INVENTORY_ID ' ||
'inner join COUNTRY c on c.ID = iv.COUNTRY_ID ' ||
'inner join MATERIAL m on m.ID = iv.MATERIAL_ID ' ||
'inner join TARIFF t on t.ID = m.TARIFF_ID ';

if (divisionid > 0) then
sql_text = sql_text ||
'where i.DIVISION_ID = ' || divisionid || ' and ' ||
'i.POSTED = ''Y'' and i.INVOICE_NO > ' || :last_tariff_invoiceno;
else
sql_text = sql_text ||
'where i.POSTED = ''Y'' and i.INVOICE_NO > ' ||
:last_tariff_invoiceno;


if (excludeinvoices <> '') then
sql_text = sql_text ||
' and i.INVOICE_NO not in (' || excludeinvoices || ') ';

sql_text = sql_text ||
' group by m.DESCRIPTION, t.CODE, c.CODE';

/*
for select m.DESCRIPTION, t.CODE, c.CODE, sum(id.QTY), sum(id.AMOUNT)
from INVOICE i
inner join INVOICE_DETAIL id on id.INVOICE_ID = i.ID and
id.PART_ID is null and id.ADJUSTINVENTORY <> 'B'
inner join INVENTORY iv on iv.ID = id.INVENTORY_ID
inner join COUNTRY c on c.ID = iv.COUNTRY_ID
inner join MATERIAL m on m.ID = iv.MATERIAL_ID
inner join TARIFF t on t.ID = m.TARIFF_ID
where ((:divisionid <= 0) or (i.DIVISION_ID = :divisionid)) and
i.POSTED = 'Y' and i.INVOICE_NO > :last_tariff_invoiceno
group by m.DESCRIPTION, t.CODE, c.CODE
*/

for execute statement sql_text
into :DESCRIPTION, :TARIFF_CODE, :COUNTRY_CODE, :QTY,
:INVOICE_AMOUNT
do
begin
suspend;
end

1)What is strange is that, when I debug this store procedure under
IBExpert, everything work just fine, but when I try to run the store
procedure in the SQL editor, select * from report_tariff(-1, ''), I
got: Unsuccessful execute caused by an unavailable resource. Variable
type (position 3) in EXECUTE STATMENT 'select m.DESCRIPTION, t.CODE,
c.CODE, sum(id.QYT),' INTO does not match return.

2)When I using my application against "select * from
report_tariffjournal(-1, '')" I got error like: unexpected end of
command, Dyanamic SQL Error, Error Code: 249(100000) Invalid token SQL
Error code = -104"

What am I doing wrong?

Thanks

William










------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet
Life.
http://us.click.yahoo.com/KIlPFB/vlQLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links