Hi Carlos!

Your English is fine, but in my opinion you SQL could be improved.

I'd rather use something like

for select p.id_producto, p.descripcion
from productos p
where p.id_familia_producto like :id_familia_producto_p
and p.id_producto like :id_producto_p
and (exists(select * from facturaciones f
join detalle_facturaciones df on f.id_factura = df.id_factura
where p.id_producto = df.id_producto
and f.fecha_documento between :fecha_inicio_l and :fecha_fin_l
and f.id_campanha_x_rubro_agricola = :id_campanha_x_rubro_agricola_l
and f.id_zona like :id_zona_p
and f.id_empleado like :id_empleado_p
and f.id_cliente like :id_cliente_p)
or exists(select * from notas_creditos dnc
join detalle_notas_creditos dnc on nc.id_nota_credito = dnc.id_nota_credito
where p.id_producto = nc.id_producto
and nc.fecha between :fecha_inicio_l and :fecha_fin_l
and nc.id_campanha_x_rubro_agricola = :id_campanha_x_rubro_agricola_l
and nc.id_zona like :id_zona_p
and nc.id_empleado like :id_empleado_p
and nc.id_cliente like :id_cliente_p))
into :id_producto_l, :detalle
do begin
...
End

I do not know why your original SQL doesn't work, myself I've avoided using IN (subselect) since Firebird 0.9.4 (or similar). Logically, the subselect is calculated for each potential row, and it used to make things a lot slower. At some point (I don't know when), Firebird began to automatically change such queries into EXISTS (when it was able to), but for unknown reason it has problems doing so with your statement (maybe it is because you use DISTINCT - parenthesis - UNION - DISTINCT - parenthesis, DISTINCT and parenthesis are at least unnecessary and maybe Firebird gets confused).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Carlos A. Caballero Yunis
Sent: 30. juni 2008 21:11
To: firebird-support@yahoogroups.com
Subject: [firebird-support] using union in a subquery within an IN condition does not work in FB 1.5.5 ?

hi all. (sorry for my bad english)

1) this SP, does not compile only when using UNION.

for select p.id_producto, p.descripcion
from productos p
where p.id_familia_producto like :id_familia_producto_p
and p.id_producto like :id_producto_p
and p.id_producto in
(
select distinct(df.id_producto)
from facturaciones f, detalle_facturaciones df
where f.id_factura = df.id_factura
and f.fecha_documento between :fecha_inicio_l and :fecha_fin_l
and f.id_campanha_x_rubro_agricola = :id_campanha_x_rubro_agricola_l
and f.id_zona like :id_zona_p
and f.id_empleado like :id_empleado_p
and f.id_cliente like :id_cliente_p
UNION /* this union, Parsing Error*/
select distinct(dnc.id_producto)
from notas_creditos nc, detalle_notas_creditos dnc
where nc.id_nota_credito = dnc.id_nota_credito
and nc.fecha between :fecha_inicio_l and :fecha_fin_l
and nc.id_campanha_x_rubro_agricola = :id_campanha_x_rubro_agricola_l
and nc.id_zona like :id_zona_p
and nc.id_empleado like :id_empleado_p
and nc.id_cliente like :id_cliente_p
)
into :id_producto_l, :detalle
do begin
...
end


2) but this UNION, works fine:

select distinct(df.id_producto)
from facturaciones f, detalle_facturaciones df
where f.id_factura = df.id_factura
and f.fecha_documento between :fecha_inicio_l and :fecha_fin_l
and f.id_campanha_x_rubro_agricola = :id_campanha_x_rubro_agricola_l
and f.id_zona like :id_zona_p
and f.id_empleado like :id_empleado_p
and f.id_cliente like :id_cliente_p
UNION
select distinct(dnc.id_producto)
from notas_creditos nc, detalle_notas_creditos dnc
where nc.id_nota_credito = dnc.id_nota_credito
and nc.fecha between :fecha_inicio_l and :fecha_fin_l
and nc.id_campanha_x_rubro_agricola = :id_campanha_x_rubro_agricola_l
and nc.id_zona like :id_zona_p
and nc.id_empleado like :id_empleado_p
and nc.id_cliente like :id_cliente_p
------

using union in a subquery within an IN condition does not work in FB 1.5.5 ?

--
Best Regards.
Carlos Caballero.