Subject | Re: IBO and union (problem) |
---|---|
Author | svein_erling |
Post date | 2002-08-14T17:45:55Z |
Carlos,
you're right in that it doesn't sound very sensible that setting
RecordCountAccurate or AutoFetchAll should solve this problem, but I
am surprised that your query is allowed!
I thought that with a left join only the table(s) to the left of the
join where allowed in the where clause and that a query like yours
would have to be rewritten
select p.Cod_MatBibl, e.Num_Ex, p.Per_Titulo, e.Per_Volume,
e.Per_Fasc, e.Per_MesPub, e.Per_AnoPub
from Periodic p
left outer join Exemplar e on (p.Cod_MatBibl=e.Cod_MatBibl) and
(e.Ex_IndEtiq='S')
where (P.Cod_Matbibl >= :inicio) and P.Cod_Matbibl <= :fim)
union
select cast (9999999 as integer) as Cod_MatBibl,
cast(0 as smallint) as Num_ex,
f_strblob(' ') as Per_Titulo,
cast(0 as smallint) as Per_Volume,
cast('' as varchar(10)) as Per_Fasc,
cast('' as char(3)) as Per_MesPub,
cast(0 as smallint) as Per_AnoPub
from rdb$database
Does it still produce the error if you do this?
Set
you're right in that it doesn't sound very sensible that setting
RecordCountAccurate or AutoFetchAll should solve this problem, but I
am surprised that your query is allowed!
I thought that with a left join only the table(s) to the left of the
join where allowed in the where clause and that a query like yours
would have to be rewritten
select p.Cod_MatBibl, e.Num_Ex, p.Per_Titulo, e.Per_Volume,
e.Per_Fasc, e.Per_MesPub, e.Per_AnoPub
from Periodic p
left outer join Exemplar e on (p.Cod_MatBibl=e.Cod_MatBibl) and
(e.Ex_IndEtiq='S')
where (P.Cod_Matbibl >= :inicio) and P.Cod_Matbibl <= :fim)
union
select cast (9999999 as integer) as Cod_MatBibl,
cast(0 as smallint) as Num_ex,
f_strblob(' ') as Per_Titulo,
cast(0 as smallint) as Per_Volume,
cast('' as varchar(10)) as Per_Fasc,
cast('' as char(3)) as Per_MesPub,
cast(0 as smallint) as Per_AnoPub
from rdb$database
Does it still produce the error if you do this?
Set