Ayuda Con Sql

[marcial]

Hola a todos:
Uso Delphi 5, FibsPlus y Firebird 2.1 y tengo el siguiente problema:
Tengo una tabla que contiene los campos FechaEmision (Date), ImporteRecibo (Float) y Concepto (String). En un DBGrid asociado a la Tabla saco los datos y tengo un Edit para filtrar la consulta de manera que en el OnChange del Edit segun voy introduciendo caracteres pues hace una consulta a la tabla y sacando los datos en el DBGrid.

El formato de la FechaEmision es dd.mm.yyyy (o tambien he probado con dd/mm/yyyy), y el formato numérico de ImporteRecibo es ##,###0.00

Si introduzco en el Edit por elejmplo el 2, todo perfecto, luego el 4, perfecto (24), pero cuando introduzco el "." o "/" no obtiene ningún resultado en el campo FechaEmision. Lo mismo ocurre si quiero buscar un ImporteRecibo que sea por ejemplo: 31.24, cuando introduzco el 31 perfecto, pero al introducir en el edit "31." ya no obtiene ningún resultado. Parece como si el "." o "/" no pudiera interpretarlos.

He probado con %LIKE% y CONTAINING y el resultado es el mismo.

Código SQL [-]
  SQL :=  'SELECT * FROM HISTORICO WHERE ' +
                       '(Upper(FECHAEMISION) LIKE ' + '''%' +UpperCase(Edit2.Text)+ '%'') OR ' +
                       '(Upper(IMPORTERECIBO) LIKE ' + '''%' +UpperCase(Edit2.Text)+ '%'') OR ' +
                       '(Upper(CONCEPTO) LIKE ' + '''%' +UpperCase(Edit2.Text)+ '%'') ' +
                       ' ORDER BY FechaEmision';
                       CONSULTA;
                       end;

Os agradecería que me pudiérais indicar qué estoy haciendo mal.
Saludos.

[[kuan-yiu]]

Varias cosas.
Los datos tipo float se almacenan internamente de forma muy diferente a como los ves por pantalla por lo que pretender encontrar "algo como 31." no es posible porque "31." no es un decimal válido, como mucho entenderá que "31", "31." y "31.0" son lo mismo.
Las fechas internamente se almacenan como un float, así que más de lo mismo.
Si ya tienes todos los datos cargados y en el grid lanzar la consulta cada vez que filtra no me parece lo más cómodo, para eso sería mejor que usases componentes tipo DataSet, ClientDataSet, DataSetProvider... según la estructura que más se adecue y filtres en el componente, o sea solo muestres lo que te interesa. De ese modo puedes tener una versión de esas columnas en formato string y buscar en ellas siguiedo la lógica que estás indicando.

Si de todos modos quieres hacerlo a través de una consulta tendrás que construirla de forma diferente.
Si por ejemplo se intruduce la fecha: "01/01" Supondrías que es el uno de enero de cualquier año y deberías construir la consulta así:

Código Delphi [-]
   miSql.close;
   with miSql.SQL do
   begin
      Clear;
      Text := 'SELECT * FROM HISTORICO WHERE';
      Add('month(FECHAEMISION)=:mes and day(FECHAEMISION)=:dia');
      //Todas las otras condiciones que necesites
      Add('ORDER BY FechaEmision');
   end;
   miSql.ParamByName('mes').AsInteger:= miMes;
   miSql.ParamByName('dia').AsInteger:= miDia;

Pero esto aporta una serie de problemas a la hora de construir la consulta que yo personalmente evitaría.

[marcial]

Muchas gracias por tu respuesta. Por desgracia no he podido aplicarla porque nunca he trabajado con parámetros y he tenido problemas; de todas formas interpreto que en tu código hay que introducir el dia y el mes. Entonces, lo que podría hacer es introducir la fecha completa a buscar y solamente comparar por Igual, Mayor que o Menor que, ya que el Like y Containing no me funcionan.
Sin embargo, aunque no sepa aplicar tu código, la explicación dada en el primer párrafo la desconocía y me ha sido muy útil.
Gracias de nuevo.

[[kuan-yiu]]

Parametrizar las consultas en importante, fundamental diría.
Copia el código que usas, los errores que salen y te ayudo.

[mamcx]

Siguiendo con lo que dice kuan-yiu: Tu lógica interna DEBE usar tipos de datos correctos y MAS estrictos que tus entradas (que pueden venir como sea). Usa tipos fecha para fecha, floats para números imprecisos, decimal para monedas/números mas precisos, booleanos para si/no, etc. Y asegurate en mantener la disciplina a lo largo de toda la cadena del software (como es el esquema de datos). Eso no solo ahorra un MONTON de errores, también habilita optimizaciones desde el punto de vista del compilador y expande las capacidades de lo que haces.

Uno de los MAYORES errores al programar es ser perezoso con los tipos. Ademas, estas en delphi: Un lenguaje de tipos estáticos. Usalos.

[marcial]

Hola de nuevo y gracias por el ofrecimiento.
El código que utilizo para filtrar la consulta es este:

Código Delphi [-]
                SQL := 'SELECT * FROM EXTRACTOS WHERE ' +
                       '(Upper(SOCIO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(NRORECIBO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(NOMBRE) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(IMPORTEENTREGA) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(IMPORTERECIBO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(FECHAAPUNTE) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(PAGADO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(CONCEPTO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(CUENTACONTABLECOBRO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(NROCHEQUE) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(FECHAVENCIMIENTOCHEQUE) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'') OR ' +
                       '(Upper(USUARIO) LIKE ' + '''%' +UpperCase(Edit6.Text)+ '%'')' +

                       ' ORDER BY FechaApunte';

EXTRACTOS.Close;
if pFIBTransaction1.InTransaction then pFIBTransaction1.CommitRetaining;
pFIBTransaction1.StartTransaction;
EXTRACTOS.SelectSQL.Clear;
EXTRACTOS.SelectSQL.Add(SQL);
EXTRACTOS.QSelect.ExecQuery;
pFIBtransaction1.CommitRetaining;
EXTRACTOS.Open;

El motivo de utilizar este sistema de búsqueda es mas visual que efectivo, lo sé. Cuando se va digitando en Edit6 cada carácter, llamo a una rutina (en el OnDrawColumCell) para que pinte de otro color en el DBGrid aquellas apariciones coincidentes con el valor de Edit6.
En realidad no aparecen errores, lo que ocurre es que en los campos DATE o FLOAT, como son: IMPORTEENTREGA, IMPORTERECIBO, FECHAAPUNTE Y FECHAVENCIMIENTOCHEQUE no aparecen resultados al utilizar LIKE o CONTAINING cuando pulso "/" para las fechas o "." para los decimales de los numericos. Sin Embargo, cuando uso Igual, Menor, Mayor, Menor o Igual o Mayor o Igual SI obtiene registros cuando edit6 vale por ejempo "22/01/2020" o "31.60".

En el Select del código, si dejo el UPPER del campo con el LIKE no da error pero los resultados de la consulta no son correctos, si quito el UPPER del campo, al comparar por IGUAL la fecha da el error "Overflow occurred during data type conversion. Conversion error from string 2".

Espero haber sido claro en la explicación y gracias por tu tiempo.

[cloayza]

Creo que los filtros están mal...

Que sentido tiene evaluar un campo fecha como mayusculas: upper(fechaEmision)?
Que sentido tiene evaluar un campo float como mayusculas : Upper(ImporteRecibo)?

Te sugiero que si estas filtrando por fecha utilices las siguientes estrategias, siendo f1 y f2 parámetros introducidos a la consulta:

1) FechaEmision = :f1
2) FechaEmision between :f1 and :f2 es equivalente a (FechaEmision>=:f1 and FechaEmision<= :f2)
3) FechaEmision <= :f1
4) FechaEmision >= :f1

En cuanto a filtro de valores numéricos (enteros, reales), sigue la misma sugerencia:

1) ImporteRecibo=:v1
2) ImporteRecibo between :v1 and :v2 es equivalente a (ImporteRecibo>=:v1 and ImporteRecibo<= :v2)
3) ImporteRecibo<=:v1
3) ImporteRecibo>=:v1

Dicho lo anterior, te propongo la siguiente modificación:
Parámetros de la consulta:
f1 y f2: Valores de fechas mínimo y máxima
v1 y v2: Valores de importe mínimo y máximo

Código SQL [-]
SQL :=  'SELECT * FROM HISTORICO WHERE ' +
            '(FECHAEMISION Between :f1 and :f2) OR ' +
            '(IMPORTERECIBO Between :v1 and v2) OR ' +
            '(Upper(CONCEPTO) LIKE ' + '''%' +UpperCase(Edit2.Text)+ '%'') ' +
            ' ORDER BY FechaEmision';
             CONSULTA;

Código Delphi [-]
       SQL.Add('SELECT * '); {Es necesario traer todos los campos? }
       SQL.Add('FROM HISTORICO WHERE ');
       SQL.Add('(FECHAEMISION Between :f1 and :f2) OR');
       SQL.Add('(IMPORTERECIBO Between :v1 and v2) OR');
       SQL.Add('(Upper(CONCEPTO) LIKE :concepto');
       SQL.Add(' ORDER BY FechaEmision');

       ParamByName('F1').AsValue:=Date(); //Fecha actual
       ParamByName('F2').AsValue:=Date(); //Fecha actual

       ParamByName('V1').AsValue:=1000; //Valor mínimo
       ParamByName('V2').AsValue:=1000; //Valor máximo

       ParamByName('Concepto').AsValue:='%' +UpperCase(Edit2.Text)+ '%') ;

       {Abrir consulta...}

Saludos cordiales

[cloayza]

Me demoré mucho en escribir y aparecieron 3 mensajes antes que el mío...

[marcial]

Gracias por tu respuesta. Perdona, pero me puedes decir dónde defino f1, f2, v1 y v2?, puede ser el edit que utilizo para introducir la fecha?. Lo siento pero nunca he trabajado con parámetros. Hay alguna forma de evitar los parámetros?.
Muchas gracias

[cloayza]

Empezado por marcial

...dónde defino f1, f2, v1 y v2?

Los parámetros los defines en el sql y deben ser antecedidos por dos puntos ":Variable", es mas pueden ser llamados igual que el nombre de campo que quieres filtrar.

Por ejemplo, si utilizas TpFIBDataset o TpFIBQuery

Código Delphi [-]
       {Si usas TpFIBDataset} 
       SQL.Select.add('Select Nombre, apellido');
       SQL.Select.add('from clientes');
       SQL.Select.add('where apellido = :apellido and edad>= :edad');

       {Si usas TpFIBQuery}
       SQL.add('Select Nombre, apellido');
       SQL.add('from clientes');
       SQL.add('where apellido = :apellido and edad>= :edad');

        {Para asignar un valor al parametro debes hacerlo como sigue...}
        .ParamByName('Apellido').AsString:='Marcial'; 
        .ParamByName('Eda').AsInteger:= 45; 

        .ParamByName('Apellido').AsString:=editApellido.text; //Usando valor de TEdit...
        .ParamByName('Edad').AsInteger:= strtoint(editEdad.text); //Usando valor de TEdit...

Yo te recomiendo utilizar parámetros para aplicar filtros en las consultas, es mucho mas claro.

Ademas cuando los parámetros son de tipo string, solo se encarga de asignar las comillas y ya no te preocupas de ese tema...

Código SQL [-]
 'apellido=' + ''' +UpperCase(EditApellido.Text)+ ''') ' +

v/s

Código SQL [-]
apellido = :apellido

Espero haber aclarado en algo tus dudas...

[Casimiro Notevi]

Empezado por marcial

Hay alguna forma de evitar los parámetros?

No lo evites, al contrario, es lo mejor.

[ecfisa]

Empezado por marcial

Hay alguna forma de evitar los parámetros?.

¿ Por qué motivo harias eso ?

Como te indican, siempre que se pueda, es la mejor opción.

Saludos

[marcial]

Desde luego que siempre estáis ahi para ayudar. Muchas gracias por vuestras recomendaciones y consejos. Lo probará como decís y ya que no tiene demasiada complicación seguro que solucionaré el problema.
Gracias de nuevo.

[mamcx]

Para que tengas claro la importancia de usar parametros:

https://es.wikipedia.org/wiki/Inyección_SQL

[lbuelvas]

Saludos, el problema de manejar las fechas de esa manera es que el motor Firebird las maneja en formato mm/dd/aaaa y en mi país las fechas se deben representar dd/mm/yyy. Para manejar las fechas sin el uso de parámetros me toco elaborar (no me acuerdo si fue copiada de algún la lado) una función para que tome un TDate y lo devuelva una cadena.

Código Delphi [-]
function _fecha_delimitada (fecha: TDateTime; caracter_delimitador: string) : string;
var
  anno, mes, dia: word;
begin
  DecodeDate(fecha,anno,mes,dia);
  if mes < 10 then
    if dia < 10 then
      result:=caracter_delimitador+'0'+InttoStr(mes)+'/'+'0'+InttoStr(dia)+'/'+InttoStr(anno)+caracter_delimitador
    else
      result:=caracter_delimitador+'0'+InttoStr(mes)+'/'+    InttoStr(dia)+'/'+InttoStr(anno)+caracter_delimitador
  else
    if dia < 10 then
      result:=caracter_delimitador+    InttoStr(mes)+'/'+'0'+InttoStr(dia)+'/'+InttoStr(anno)+caracter_delimitador
    else
      result:=caracter_delimitador+    InttoStr(mes)+'/'+    InttoStr(dia)+'/'+InttoStr(anno)+caracter_delimitador
end;

[Casimiro Notevi]

No, firebird no guarda los campos fecha de esa manera, los campos fecha y los fechahora, son números, ejemplo: 34248,17238173917

La parte entera 34248 son los días transcurridos desde 31/12/1895 (creo recordar, no recuerda la fecha exacta) y la parte decimal es la hora, cuando llega a 34248.999999999999 pasa a 34249.00000000000 que es un día más.

[lbuelvas]

Hola Casimiro, no me refiero a la representación interna sino a la forma en que Firebird muestra los campos Date al usuario

[[kuan-yiu]]

No necesitas hacer eso, Dephi tiene funciones que lo hacen por ti, concretamente FormatDateTime.
Y aquí tienes listada un montón más de funciones con fechas:
https://delphiallimite.blogspot.com/...cha-y.html?m=0

[Casimiro Notevi]

Empezado por lbuelvas

Hola Casimiro, no me refiero a la representación interna sino a la forma en que Firebird muestra los campos Date al usuario

Firebird los presenta como tú quieras. Firebird es un gestor de bases de datos "servidor", le da igual la presentación, eso lo deja al "cliente".

[mamcx]

En otras palabras, internamente, no tienes porque hacer "malabares" si usas los tipos de datos correctos. Unicamente haces conversiones en tus entradas/salidas del/al "exterior". Eso significa que ademas, "alinea" el uso de tus tipos con lo que te da el lenguage.

En concreto, Delphi tiene de planta y por terceros un MONTON de controles y librerias que felizmente te permiten usar DateTime como DateTimes, Monedas como monedas, horas como horas, sin pasar por conversiones manuales a texto.

Esto siginifica que piensa asi:

Problema: Tengo que usar fechas


Hay un tipo fecha en delphi y/o BD y/ lo que sea?

• Si, lo uso (y por lo tanto tiene que existir rutinas ya probadas para manipularlos)
• No, y hago mi tipo concreto que abstrae como hacerlo

Hay un CONTROL para fechas?

• Si, lo uso
• No, y hago mi tipo concreto que abstrae como hacerlo

En este instante veo una vble o campo que se supone es fecha. USA EL TIPO CORRECTO?

• Si, eres un buen programador, date una palmadita
• No, eres un buen programador, y por lo tanto cambia al tipo correcto

Ahora, si la fuente de entrada/salida no tiene ese tipo "nativo" (ej: Lees de un archivo de csv) entonces:

Conviertes TODOS los campos al tipo correcto JUNTO con la lectura

Código Delphi [-]
convertir_desde(archivo:TCSV):TCuentas

y al final lo devuelves:

Código Delphi [-]
convertir_hacia(cuentas: TCuentas): TCSV

eso solamente debe existir A LA ENTRADA/SALIDA del mundo exterior. En TODOS los demas lugares usas el tipo correcto!

----
Esto se extiende. Si estas facturando y tienes unos estados como "Borrador", "En Proceso", "Pagado", entonces lo correcto es crear un tipo:

Código Delphi [-]
type
   TFacturaEstado = (Borrador, Proceso, Pagado); 

convertir_desde(texto:String): TFacturaEstado
convertir_hacia(estado: TFacturaEstado): String

Aunque no lo parezca, esto reduce un MONTON de codigo repetido (ej: Estar chequeando por todos lados si el "estado" esta vacio, o preocuparse si esta o no en mayusculas, etc. Con un tipo, todo eso desaparece). Junto con hacer test redundantes (sera que si guarda el estado que es?).

Y si hay un error en el parseo de datos, no tienes que ir a chequear en cientos de lugares. Solo hay 2 opciones: convertir_desde, convertir_hacia.