> > Why do you think that your usage of EXISTS isn't legal? Do
> > you get any error message?
>
> I now believe the problem is the combination of the view and exists.
>
> The problem is that the procedure works only once after connecting to
> the db or if recompiled.
>
> I have attached a script to reproduce.
>
> replacing
> 'if (exists(select count(*) from test_view)) then begin'
> with
> 'select count(*) from test_view into :count_var' and
> 'if (count_var > 0) then begin' ...works every time.

[DDL snip]

I can confirm something strange here. Using Firebird 1.5 RC7 SS on
Windows 2000 Prof. SP3.

I've used your provided DDL, and the procedure TEST_PROC
looks like:


SET TERM ^^ ;
CREATE PROCEDURE TEST_PROC returns (
OUT VarChar(60))
AS
begin
if (exists(select * from test_view)) then begin
out = 'Test...';
suspend;
end
end
^^
SET TERM ; ^^


The following isql.exe trace:


D:\Programme\Firebird\Firebird_1_5_3050\bin>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect d:\daten\datenbanken\test.fdb user sysdba password masterkey;
Database: d:\daten\datenbanken\test.fdb, User: sysdba
SQL> select * from TEST_PROC;

OUT
============================================================

Test...

SQL> select * from TEST_PROC;
SQL>


That means. Connect to the database. Execute SELECT * FROM TEST_PROC
the first time. One record is returned. As expected.

Executing SELECT * FROM TEST_PROC the second time, an empty result
set is returned, which is wrong. Disconnect/Connect, the query will
work the first time, but will not afterwards.



Thomas