At 05:10 AM 1/07/2008, you wrote:

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

It does not work logically. The output of a UNION query does not return a set with names that can be mapped back to the search value. After that, IN() used against SELECT DISTINCT is illogical for an existence test.

With the IN(subquery) predicate, you only want to know if the searched value EXISTS in the subqueried set - and that is how the parser resolves the IN(subquery) expression. The logic needs to be:

for select p.id_producto, p.descripcion
from productos p
where p.id_familia_producto like :id_familia_producto_p /* see note! */
and p.id_producto like :id_producto_p /* see note 1 */
and (p.id_producto in
(
select 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 /* see note 1 */
and f.id_empleado like :id_empleado_p /* see note 1 */
and f.id_cliente like :id_cliente_p) ) /* see note 1 */
and (p.id_producto in
(
select 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 /* see note 1 */
and nc.id_empleado like :id_empleado_p /* see note 1 */
and nc.id_cliente like :id_cliente_p ) ) /* see note 1 */

into :id_producto_l, :detalle
do begin
...
end

The parser will resolve the IN(subquery) predicate to an EXISTS() predicate, which will return True immediately if a match is found and False otherwise. As far as I recall, in Fb 1.5.5, the parser will also employ "short" logic and ignore the second test if the first one returns False.

Two notes here:

1. The LIKE predicate needs "wildcard" characters to inform the parser about where to look for the string in the search argument. For example, LIKE '%pasta' (to search for 'pasta' at the right-hand side of the string) or LIKE 'pasta%' to search at the left-hand side or LIKE '%pasta%' to search anywhere inside the string. (Other wildcards are possible...) When the search values are parameterised, this can put a lot of extra responsibility into your application code and/or your SP code, to ensure that the query receives a valid search argument.

For LIKE 'pasta%', replace LIKE with STARTING WITH, which has the same effect but does not require a wildcard and is able to use an index. For LIKE '%pasta', use CONTAINING instead - it does not need wildcards, either.

There is no better substitute for LIKE '%pasta'. It is an inefficient search, period, and there is nothing the parser can do to redeem it.

N.B. Do not make the mistake of believing that a clause like

f.id_cliente LIKE :id_cliente_p

has the same meaning as

f.id_cliente = :id_cliente_p

They are quite different. When you want to test for equality, always use '='.

2. It is strongly recommended to avoid the old SQL-89 inner join syntax that you are using here:

select 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 /* see note 1 */
and f.id_empleado like :id_empleado_p /* see note 1 */
and f.id_cliente like :id_cliente_p /* see note 1 */

With this syntax it is very easy to make human errors that are hard to debug. It has been obsolete for almost two decades. Use the explicit syntax of SQL-92 - especially when using parameters - to make it clear to yourself and other humans which fields are JOIN criteria and which are search criteria.

select df.id_producto
from facturaciones f
JOIN detalle_facturaciones df
ON f.id_factura = df.id_factura /* JOIN criteria */
WHERE /* search criteria */
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 /* see note! */
and f.id_empleado like :id_empleado_p /* see note! */
and f.id_cliente like :id_cliente_p /* see note! */

./heLen