Hi,

I am using FB 1.0 for a project. I am converting a paradox application to Firebird dialect 1.
I was doing fine until I tried to create a particular view.

This view has 24 unions ( 2 for each month of the year ). I will not place the entired code here,
but it goes like this:

--------------------------------------------------------------------
create view VW_ASSOCIADOS_ATIVOS_1998 ( Mes, Periodo, Tipo, Ativos )
as
select 1 as Mes, 'JAN-98' as Periodo,'D' as Tipo, count(*) as Ativo
from PDX_DEPENDE
where (extract (YEAR FROM DATA_INCLUSAO) = 1998) and
(Extract (MONTH from DATA_INCLUSAO) = 1)
and ( (DATA_CANCELAMENTO is NULL ) or (DATA_CANCELAMENTO >= '02/01/1998') )

union

select 2 as Mes,'FEV-98' as Periodo,'D' as Tipo, count(*) as Ativo
from PDX_DEPENDE
where (extract (YEAR FROM DATA_INCLUSAO) = 1998) and
(Extract (MONTH from DATA_INCLUSAO) between 1 and 2)
and ( (DATA_CANCELAMENTO is NULL ) or (DATA_CANCELAMENTO >= '03/01/1998') )

union

select 3 as Mes,'MAR-98' as Periodo,'D' as Tipo, count(*) as Ativo
from PDX_DEPENDE
where (extract (YEAR FROM DATA_INCLUSAO) = 1998) and
(Extract (MONTH from DATA_INCLUSAO) between 1 and 3)
and ( (DATA_CANCELAMENTO is NULL ) or (DATA_CANCELAMENTO >= '04/01/1998') )

union
--------------------------------------------------------------------

... This goes on for PDX_DEPENDE and PDX_TITULAR ( kind of Master Account (Titular) and Sub
accounts (Dependente).
When I reach the 19th select, when I execute: select * from VW_ASSOCIADOS_ATIVOS_1998
FB returns me an "Unknown error".

If I run the query by itself, everything works as expected.

I broke down the view into 2 smaller ones (12 unions each). One for TITULAR the other for DEPENDE.
They work fine isolated. Then I tried to UNION them :

CREATE VIEW VW_ASSOCIADOS_ATIVOS_1998
as
SELECT * FROM VW_TITULARES_ATIVOS_1998
union
SELECT * FROM VW_DEPENDENTES_ATIVOS_1998

Once again the same "Unknow error" happens... Is there a limit I am unaware?

Best regards,
Clément