At 02:17 AM 11-08-02 +0000, you wrote:

>Hi,
>
>The following SQL statment gives an error with UNION key word. If
>I remove union and make
>it to two statment it works! And UNION works without the INSERT
>statment also! Is this an
>expected behaviour of FB ?

Not just the expected behaviour of Fb, but also the expected behaviour of
SQL. Only a set with a live cursor can be used as input to an INSERT in
this fashion because the engine forms the parameters for the inserts row by
row, as each row is output row is fetched, not on the finished set (similar
to what happens when you "do stuff" in a for..select loop in a SP):

for select <output columns>
from <tables>
where <???>
into <varlist>
do begin
<do stuff>
...
end <--- loops back to <do stuff> until there are no more output rows to fetch

For the record (though it doesn't help your current scenario) when UNIONing
two sets from a single table, you need to use table aliases to ensure that
the query has two distinct sets of cursors, viz.

SELECT ab1.entry_date, ah1.account_nm,
ab1.narration,
ab1.debit, ab1.credit,
account_book.journal_no
FROM account_book ab1, account_heads ah1
WHERE ab1.l_account_id = ah1.id
AND ab1.entry_date >= :dFrom
AND ab1.entry_date <= :dTo
AND account_book.account_id = :dAcID
UNION
SELECT ab2.entry_date, ah2.account_nm,
ab2.narration,
ab2.credit, ab2.debit,
ab2.journal_no
FROM account_book ab2, account_heads ah2
WHERE ab2.account_id = ah2.id
AND ab2.entry_date >= :dFrom
AND ab2.entry_date <= :dTo
AND ab2.l_account_id = :dAcID

Either run two queries; or move this operation into a stored procedure
where you can operate on the two sets consecutively with no bother.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________