Parametros opcionales en SELECTs

[Gydba]

Buena, buenas.

Paso a comentar rapidamente un problema que estoy teniendo:

Tengo una especie de wizard para permiten parametrizar determinadas consultas. El tema es que el usuario puede o no definir los valores para los parámetros, es decir el usuario bien podría darle un valor determinado o dejarlo simplemente en nulo y la consulta ignorará el filtrado.

Hasta aca todo parece simple. Las consultas se guardan en una tabla de la BD por lo que no se deberían armar dinamicamente y otros detalles que no vienen al caso.

Mi problema es el siguiente, hasta el momento yo utilizaba una SP para chequear que el valor pasado no sea nulo y en caso contrario continuaba con un OR dentro del WHERE, pero por diversos problemas no podemos seguir utilizando esto. Creo que la estoy complicando

Bueno llendo al punto con algo simple:

SELECT
    CLT.ID, CLT.NAME
FROM
    CLT_MASTER CLT
WHERE
    (COALESCE(:PARAM, -1) = -1) OR CLT.ID = :PARAM
    AND
    (COALESCE(:PARAM2, '') = '') OR CLT.NAME = :PARAM2

En el caso de PARAM anda 10 puntos al ser un tipo entero, pero en PARAM2 que es un varchar(60) me tira un error del tipo:

Incompatible column/host variable data type.
Dynamic SQL Error.
SQL error code = -303.
arithmetic exception, numeric overflow, or string truncation.

Tengo que aclarar que ya intente un par de "CASTs" pero me sigue tirando errores.
¿A alguien le pasa algo similar?
¿Cómo reconoce FB el tipo de parámetro que se le pasa?

Saben que desde ya les doy las gracias

PD: Las pruebas fueron realizadas con FB1.5 e IBExpert PE

[jachguate]

En casos como este yo he tenido que darle varias vueltas al asunto y no ha podido ser con null's... asi que lo que he hecho es crear valores "bandera" que me indican como actuar... algo como

where (:TodosLosClientes = 'S' or IdCliente = :IdCliente) and
        (:TodosLosTipos = 'S' or idTipo = :idTipo)

Es bastante engorroso, porque luego en delphi tenes que estar condicionando las asignaciones... pero al final, vas a obtener el resultado deseado:

  q1.ParamByName('TodosLosClientes').AsString := IfThenElse(eCliente.Text = '', 'S', 'N');
  q1.ParamByName('idCliente').AsInteger := IntToStr(eCliente.Text);  
  // esto en un try/except o alguna función que devuelva cualquier valor al elevarse la excepción... 
  q1.ParamByName('TodosLosTipos').AsString := IfThenElse(eTipo.Text = '', 'S', 'N');
  q1.ParamByName('idTipos').AsString := eTipo.Text;

etc, etc...

Hasta luego.

[Gydba]

Si, eso fué lo que se planteó en un principio, pero el hecho de enviar dobles parametrós no nos agradó lo suficiente y apareció esta SP que mencionó que evaluaba contra un OR.

Gracias de todas formas, ahora que tengo un poco de tiempo voy a investigar un poco más a ver que vuelta le encuentro.

[Gydba]

Bueno, recien estoy teniendo tiempo para realizar algunas pruebas. He llegado a esto:

SELECT
    CLT.ID, CLT.NAME
FROM
    CLT_MASTER CLT
WHERE
    CLT.ID = Coalesce(:PARAM1, CLT.ID)
    AND
    CLT.NAME = Coalesce(:PARAM2, CLT.NAME)

Claro que de esta manera si el usuario no ingresa nada en el TEdit se deberá pasar un nulo como valor del parámetro si no se quiere inconvenientes.

Me surgió esta idea cuando alguien me comentó un problema de optimización de índices de algunos motores (como MSSQL) cuando tiene que realizar algo similar con el operador OR.

Hasta el momento lo probé con integers, varchars y dates como quería y no me fallo. Tengo que aclarar que utilizo la ultima versión de FB, es decir la 1.5.

[jachguate]

Interesante... si ya no te dió problema con el tipo de dato, creo que es lo que estabamos esperando...

Hasta luego.