--- In firebird-support@yahoogroups.com, "william_yuwei"
<william_yuwei@y...> wrote:

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

IBExpert is a simulator not a true debugger. There is no true debugger
for Firebird stored procedures. That means it may behave differently
in some cases, and in some cases, may behave "better" than Firebird does.

> 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?
>

I had a problem before attempting to combine parameters with execute
statement. Also make sure your variable is long enough to handle the
entire query.


Adam