Thomas Steinmaurer wrote:

> Do you want to query what relations where created after the
> current month? ;-)

Yeah.. sure :)

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


--
BR,
Lauri

----------

/******************************************************************************/
/**** Generated by IBExpert 2.5.0.61 18.11.2003 11:58:58 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1257;

CREATE DATABASE 'C:\VIEW_TEST.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1257;



SET TERM ^ ;



/******************************************************************************/
/**** Stored Procedures ****/
/******************************************************************************/

CREATE PROCEDURE TEST_PROC
RETURNS (
OUT VARCHAR(60))
AS
BEGIN
EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE CHEQUE (
ID INTEGER NOT NULL,
CLIENT_ID BIGINT NOT NULL,
TIME_ TIMESTAMP NOT NULL
);


CREATE TABLE CLIENT (
ID BIGINT NOT NULL,
NAME VARCHAR(60) NOT NULL,
COMPANY BIGINT NOT NULL,
COMPANY_ID BIGINT
);





/******************************************************************************/
/**** Views ****/
/******************************************************************************/


/* View: TEST_VIEW */
CREATE VIEW TEST_VIEW(
ID,
NAME,
YEAR_,
MONTH_,
COMPANY,
COMPANY_ID)
AS
select cl.id, cl.name, extract(year from cq.time_), extract(month from cq.time_), cl.company, cl.company_id
from client cl join cheque cq on
exists(select * from client cl2 where cl2.company_id = cl.id and cq.client_id = cl2.id)
or ((cq.client_id = cl.id))
group by 1, 2, 3, 4, 5, 6;


INSERT INTO CHEQUE (ID, CLIENT_ID, TIME_) VALUES (1, 1, '2003-11-18 00:00:00');
INSERT INTO CHEQUE (ID, CLIENT_ID, TIME_) VALUES (2, 2, '2003-11-18 00:00:00');
INSERT INTO CHEQUE (ID, CLIENT_ID, TIME_) VALUES (3, 3, '2003-11-18 00:00:00');
INSERT INTO CHEQUE (ID, CLIENT_ID, TIME_) VALUES (4, 4, '2003-11-18 00:00:00');

COMMIT WORK;

INSERT INTO CLIENT (ID, NAME, COMPANY, COMPANY_ID) VALUES (0, 'Client 1', 1, NULL);
INSERT INTO CLIENT (ID, NAME, COMPANY, COMPANY_ID) VALUES (1, 'Sub-Client 1', 0, 0);
INSERT INTO CLIENT (ID, NAME, COMPANY, COMPANY_ID) VALUES (2, 'Sub-Client 2', 0, 0);
INSERT INTO CLIENT (ID, NAME, COMPANY, COMPANY_ID) VALUES (3, 'Client 2', 0, NULL);
INSERT INTO CLIENT (ID, NAME, COMPANY, COMPANY_ID) VALUES (4, 'Client 3', 1, NULL);

COMMIT WORK;



/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/

ALTER TABLE CHEQUE ADD CONSTRAINT PK_CHEQUE PRIMARY KEY (ID);
ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (ID);


/******************************************************************************/
/**** Stored Procedures ****/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE TEST_PROC
RETURNS (
OUT VARCHAR(60))
AS
begin
if (exists(select * from test_view)) then begin
out = 'Test...';
suspend;
end
end
^


SET TERM ; ^


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