Problema con select first en Firebird

[[egostar]]

Hola amigos,

Necesito hacer estadísticas de cierto número de registros, viendo otros hilos encontré que con la instrucción FIRST puedo obtener la cantidad que deseo.

Todo bien si la cantidad la coloco fija, es decir

Código SQL [-]
select first 30 Prefijo, Numero, count(numero) as numlla from llamadas

pero si le agrego un parámetro de entrada (E_Limite) me muestra el siguiente error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 13, column 18.
:.

La instrucción que me da error es esta

Código SQL [-]
select first :E_limite Prefijo, Numero, count(numero) as numlla from llamadas

Alguien sabe porque no me acepta esta estructura?

Muchas gracias

Salud OS.

Edito: oops, parezco nuevo, Uso Firebird.

[[gluglu]]

No te estoy dando una explicación a tu pregunta, pero te propongo otro método para que funcione.

Código SQL [-]
IBQuery1.SelectSQL.Clear;
IBQuery1.SelectSQL.Add('Select first '+QuotedStr(IntToStr(E_limite)));
IBQuery1.SelectSQL.Add('Prefijo, Numero, count(numero) as numlla from llamadas');
IBQuery1.SelectSQL.Open;

La verdad es que no puedo concluir si el valor de First se puede o no pasar como parámetro.

[[gluglu]]

Por cierto, ahora que me doy cuenta leyendo de nuevo tu post anterior ... el error que indicas es

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 13, column 18

No estarás equivocándote en algún otro lado ?? Porque te está dando error en la línea 13 ! que, entiendo por tu código, no tienes.

Estás creando la sentencia SQL de manera dinámica en tiempo de ejecución ?? Si es así, te has asegurado de que estás borrando correctamente la anterior ??

[[egostar]]

Muchas gracias gluglu, precisamente eso estaba pensado en este preciso momento, pero estoy con el tema de hacer todas mis consultas con procedimientos almacenados, una mas de mis aferraciones

Edito:

Si, perdon el procedimiento almacenado es este

Código SQL [-]
ALTER PROCEDURE REPNUM (
    e_limite integer = 1,
    e_inicial date,
    e_final date)
returns (
    s_prefijo varchar(3),
    s_numero varchar(20),
    s_llamadas integer)
as
begin
  /* Procedure Text */
  FOR
    select first :E_limite Prefijo, Numero, count(numero) as numlla from llamadas
    where fecha between :E_Inicial and :E_Final
    group by prefijo, numero
    order by prefijo, numlla desc
  INTO :S_Prefijo, :S_Numero, :S_Llamadas DO
  suspend;
end

Trato de evitar el error de que pueda pasar un dato 0, que leí en algún lado produce error.
Salud OS.

[jhonny]

Puedes intentar usar el Execute Statement, nunca lo he usado con parámetros de fechas pero me supongo que asi debería funcionar:

Código SQL [-]
 
CREATE PROCEDURE REPNUM (
    E_LIMITE INTEGER = 1,
    E_INICIAL DATE,
    E_FINAL DATE)
RETURNS (
    S_PREFIJO VARCHAR(3),
    S_NUMERO VARCHAR(20),
    S_LLAMADAS INTEGER)
AS
begin
  FOR
  execute statement 'select first ' || CAST(E_limite AS CHAR(1)) || 'Prefijo , Numero, count(numero) as numlla from llamadas where fecha between '||
  CAST(E_Inicial AS CHAR(11)) || ' and '||
  CAST(E_Final AS CHAR(11)) || ' group by prefijo, numero order by prefijo, numlla desc'
  INTO :S_Prefijo, :S_Numero, :S_Llamadas DO
  suspend;
end

Pruebalo y si te funciona, espero te sirva .

[jhonny]

Acabo de darme cuenta que en una base de datos que tengo a mano, ya había hecho lo mismo con parámetros de fechas y toda la cosa, analizándolo recordé que la cuestión debe funcionarte asi:

Código SQL [-]
 
CREATE PROCEDURE REPNUM (
    E_LIMITE INTEGER = 1,
    E_INICIAL DATE,
    E_FINAL DATE)
RETURNS (
    S_PREFIJO VARCHAR(3),
    S_NUMERO VARCHAR(20),
    S_LLAMADAS INTEGER)
AS
begin
  FOR
  execute statement 'select first ' || CAST(E_limite AS CHAR(1)) ||
  ' Prefijo, Numero, count(numero) as numlla from llamadas where fecha between '''||
  :E_INICIAL||''' and '''||:E_FINAL||''' group by prefijo, numero order by prefijo, numlla desc'
  INTO :S_Prefijo, :S_Numero, :S_Llamadas DO
  suspend;
end

[[egostar]]

Perfecto, me funcionó de maravilla, solo cambié el AS CHAR(1) del E_Limite por AS INTEGER, porque pueden ser mas de 1 dígito y no sea que se les ocurra solicitar, 9, 30, 120, 1000, etc...

Como lo diria nuestro buen amigo Carlos Alberto, muchas gracias maestro.

Salud OS.

[jhonny]

Final Feliz¡¡¡ Que bueno

[[Al González]]

¿Cuál final feliz?

Tenía planeado hacer varios procedimientos con esa característica en Firebird 1.5, pero según veo ahora tendré que recurrir a la instrucción Execute Statement para que sea posible.

¿No lo hace eso un poco más lento? Hablo de unos 20 mil registros, por ejemplo.

Por otro lado, ¿cómo es que sí funciona una sentencia como la siguiente en el SQL Editor de IB Expert?

Select First :C * From Empleados

Me hace suponer que SQL Editor también utiliza Execute Statement, o bien, transforma los parámetros armando una nueva sentencia "plana" (formateo) antes de ejecutarla.

Pero no hay como estar seguros, ¿alguien puede mirar qué hace internamente el SQL Editor de IB Expert? Por favor.

Un abrazo sacado de onda.

Al González.

[[egostar]]

Hola Alberto, pues exactamente yo tengo alrrededor de 20,000 registros en esta prueba que consta de solo dos dias de información, estos reportes los sacarán por mes, así que por simple operacion matemática estamos hablando de alrrededor de los 300,000 registros al mes.

Y tienes razón, en el Editor SQL de IBExpert funciona sin problema, el tiempo a mi parecer es el mismo, a lo más 1 segundo en realizar la consulta, tanto en el SP como en el Editor SQL.

Lo que yo pretendo es que quien trabaje es la base de datos y no el lado de cliente, por eso estoy utilizando SP's.

En este aspecto, me gustaría que ustedes los que llevan tiempo con Firebird me comentaran si esto que "soñe" es lo más idóneo o mejor sería hacerlo a través de Queries en los programas como lo hacia con Paradox.

Salud OS.

[[Al González]]

¡Hola a todos!

Empezado por egostar

...quien trabaje es la base de datos y no el lado de cliente, por eso estoy utilizando SP's...es lo más idóneo o mejor sería hacerlo a través de Queries en los programas...

En la normativa de la empresa seguimos la regla de que si una operación es sobre registros, es tarea del servidor de la base de datos o cuando mucho del servidor de aplicaciones.

Todas las sentencias Select que llevan uniones horizontales (Join), uniones verticales (Union), columnas calculadas (Case, concatenaciones, operaciones matemáticas, etc.), agrupación (Group By), subconsultas (Select dentro del Select) o más de una condición en su cláusula Where, son colocadas dentro de vistas o procedimientos almacenados (en estos últimos cuando hay necesidad de parametrizar la consulta).

Es decir, los únicos Selects directos fuera de la capa de la base de datos son aquellas sentencias simples tipo "Select Campo1, Campo2, Campo3 From Tabla Where Campo4 = Valor Oder By Campo2" (normalmente son típicos "Select * From Tabla Order By Campo"). Bajo el entendido de que el programador operativo (capa cliente) o de lógica de negocios (capa intermedia) no necesariamente cuenta con el conocimiento de un especialista en bases de datos. Es el AD (administrador de datos o "DBA") quien se encarga de la programación SQL de mediana y alta complejidad a nivel de la base de datos. Además, con esto se consigue centralizar muchos procesos que no son simples, evitando que desde la base de datos crezca una enredadera de código SQL hacia las demás capas.

Con respecto al uso de Execute Statement, me inquieta un poco que tengo sentencias Select de varias decenas de líneas que ahora tendré que armar como una cadena de caracteres concatenada dentro de los procedimientos. Espero no toparme con un límite de 1024 caracteres o una cosa rara de esas que a veces pasan porque un programador de un país lejano decidió ahorrar unos cuantos bits. Hay que pensar positivo.

Un abrazo selecto.

Al González.

[jhonny]

Empezado por Al González

Con respecto al uso de Execute Statement, me inquieta un poco que tengo sentencias Select de varias decenas de líneas que ahora tendré que armar como una cadena de caracteres concatenada dentro de los procedimientos. Espero no toparme con un límite de 1024 caracteres o una cosa rara de esas que a veces pasan porque un programador de un país lejano decidió ahorrar unos cuantos bits. Hay que pensar positivo.

Si necesitas usar la primera sintaxis que describe Alex Peshkoff en http://fresh.t-systems-sfr.com/unix/...cute_statement estarías limitado a 32765 caracteres que es el tamaño máximo de un tipo VARCHAR o 32767 si decides usar un CHAR, lo que no me queda claro aun, es lo que pasaría al respecto para los casos de las sintaxis 2 y 3 que muestra dicho articulo.

[jhonny]

Ahora, cuando trato de hacer esto, en un procedimiento almacenado:

Código SQL [-]
execute statement /*Y escribo 32767 veces la letra 'A'.*/

Desde el IbExpert, este me advierte que el procedimiento es mas grande de 64K y que eso podría causarme problemas de compilación y/o de ejecución posteriormente.

[[Al González]]

¡Hola de nuevo!

Ya me había ido a dormir, pero de pronto, entre el barullo de mis últimos pensamientos del día, reflexioné:

----------------------------------------------------
¿Por qué será que no se permite parametrizar un First?

Ah, pero podría "armar" el First n en el Select que llama al procedimiento (Select First n * From Procedimiento...). Después de todo, un procedimiento almacenado que devuelve un cursor hace un Suspend por cada fila, y si desde afuera le digo "First 10", pues sólo deberá traerme 10 y suspender definitivamente la obtención de las filas restantes.

Quizá por eso los creadores de InterBase / Firebird no permiten usar una variable o parámetro para indicar la cantidad en el interior de un procedimiento. Pareciera una forma sutil de decirnos, "¿pa' qué quieres el First parametrizado aquí? Tú pide desde afuera la cantidad que requieras." Aunque esto no suena muy convincente, quizá haya otra razón.

Ah, ¿pero qué pasaría si a ese Select exterior le añado una cláusula Order By? Temo que se va a traer todo el cursorote y ya luego tomará los First n del orden indicado, discriminando una gran cantidad de filas que ya se trajo. Eso lo volvería muy lento...
----------------------------------------------------

Entonces recordé que tenía una barra de chocolate con almendras en el congelador y aquello fue la gota que derramó la tentación.

Me levanté a la media noche para compartir estas reflexiones con ustedes y de paso hacer algunas pruebas adicionales con IB Expert y Delphi. Los resultados de esas pruebas fueron que:

1. Efectivamente, se puede utilizar First en el Select exterior para evitar el empleo de Execute Statement dentro del procedimiento.
2. Ese First sí puede tener un parámetro.
3. Por alguna razón no se permiten cláusulas First sin un valor literal dentro de procedimientos almacenados y disparadores.
4. Como era de esperarse, una cláusula Order By en el Select que llama al procedimiento hace que se obtengan todas las filas posibles de éste antes de aplicar el First de dicha sentencia Select, lo cual no es óptimo.

Conclusión para mi caso: Usaré First parametrizados en los Selects que llaman a los procedimientos. Dentro de éstos aplicaré un Order By acorde a ese First de afuera. En esos Selects exteriores evitaré el uso de Order By; con la ventaja de que, al estar utilizando CDSs (client data sets) conectados a los queries, puedo reordenar las filas en memoria según convenga para cada caso.

Esta solución podría ser útil en otros casos; todo depende de la cantidad de registros, los componentes utilizados y de qué tanto queramos variar el orden del cursor.

Empezado por jhonny

Si necesitas usar la primera sintaxis que describe Alex Peshkoff en http://fresh.t-systems-sfr.com/unix/...cute_statement estarías limitado a 32765 caracteres que es el tamaño máximo de un tipo VARCHAR o 32767 si decides usar un CHAR...

Gracias por el enlace Jhonny. Al final de ese documento se explican otras desventajas de Execute Statement (como el mayor consumo de tiempo). Veo muy útil la instrucción, pero creo que sólo la emplearé en casos muy especiales.

Un abrazo chocolatoso.

Al González.

[RolphyReyes]

Saludos.

Al González podrias si te es posible subir los select's de pruebas que utilizaste? .

Gracias de antemano.

[[egostar]]

Bueno, el hecho de usar el FIRST, solo lo considero para obtener estadísticas, no para recuperar filas de las tablas, mi caso es por ejemplo, quiero saber los N números más marcados mas no las primeras N filas, eso no me hace sentido y lo haría con validaciones WHERE, HAVING.

Salud OS.

[ariefez]

Tu sentencia esta bien solo le falta unos parentesis

Código SQL [-]
    SELECT FIRST (:PAGE_NF) SKIP (:PAGE_NF * (:PAGE_NRO - 1))

donde:
PAGE_NF Numero de registros por pagina
PAGE_NRO el numero de pagina

este es el codigo q uso, para darte un ejemplo

Código SQL [-]
/* CLIENTE - DISPONIBLES */

SET TERM ^ ;

CREATE OR ALTER PROCEDURE CLI_SELECT (
  CLIENTE TYPE OF D_CLI_NOM,
  DNI TYPE OF D_RUC,
  PAGE_NF INTEGER,
  PAGE_NRO INTEGER,
  IN_BLOCKED SMALLINT)
RETURNS (
  CLI_ID INTEGER,
  CLI_NOM VARCHAR(60),
  RUC VARCHAR(15),
  BLOCKED SMALLINT)
AS
BEGIN
  FOR
    SELECT FIRST (:PAGE_NF) SKIP (:PAGE_NF * (:PAGE_NRO - 1))
      CLI_ID, CLI_NOM, RUC, BLOCKED
    FROM
      CLIENTES
    WHERE
      0 < CLI_ID
      AND (:IN_BLOCKED = 1 OR BLOCKED = 0)
      AND (:CLIENTE IS NULL OR CLI_NOM LIKE '%' || :CLIENTE || '%')
      AND (NI IS NULL OR RUC LIKE '%' || NI || '%')
    ORDER BY CLI_NOM, RUC, CLI_ID
    INTO :CLI_ID, :CLI_NOM, :RUC, :BLOCKED
  DO SUSPEND;
END^

SET TERM ; ^

Agrego: probado en firebird 2.0 y 2.1 beta

[jhonny]

Vaya, Vaya, Vaya¡¡¡

[[egostar]]

, Funcionó perfectamente.

Muchas gracias ariefez, pues nada, cada día se aprende algo nuevo.

Así quedo finalmente mi SP

Código SQL [-]
SET TERM ^ ;

CREATE PROCEDURE NEW_PROCEDURE (
    e_limite integer,
    e_inicial date,
    e_final date)
returns (
    s_prefijo varchar(3),
    s_numero varchar(20),
    s_llamadas integer,
    s_duracion numeric(15,0))
as
begin
  /* Procedure Text */
  FOR
    select first (:e_limite) Prefijo, Numero, COUNT(*) as NumLla,
       SUM(DurEntera) as TotDur from llamadas
    where fecha between :e_inicial and :e_final
    group by prefijo, numero order by NumLla desc
  INTO :S_Prefijo, :s_numero, :s_duracion, s_llamadas DO
  suspend;
end^

SET TERM ; ^

GRANT SELECT ON LLAMADAS TO PROCEDURE NEW_PROCEDURE;

GRANT EXECUTE ON PROCEDURE NEW_PROCEDURE TO SYSDBA;

Solo hago notar que la funcion SUM() requiere de un tipo de dato Numeric, intentaba asignarlo como Integer y me daba un error de casting.

Salud OS.

[[Al González]]

¡Hola a todos!

Empezado por RolphyReyes

...podrias si te es posible subir los select's de pruebas que utilizaste?...

No sé si sirvan de mucho, y menos después de la tremenda solución dada por Ariefez, pero son los siguientes.

Select que detiene definitivamente el For Select del procedimiento spParte tras obtener las primeras 10 filas construidas por el procedimiento:

Código SQL [-]
-- Versión rápida
Select First 10 * From spParte (Null, Null, Null, Null, Null, Null)

Select que, por tener Order By, espera a que se realice todo el For Select del procedimiento spParte, para luego devolver solo 10 de las filas que construyó el procedimiento:

Código SQL [-]
-- Versión lenta
Select First 10 * From spParte (Null, Null, Null, Null, Null, Null) Order By Numero

Empezado por egostar

...usar el FIRST, solo lo considero para obtener estadísticas, no para recuperar filas de las tablas, mi caso es por ejemplo, quiero saber los N números más marcados mas no las primeras N filas, eso no me hace sentido...

¡Hey, hey, hey! Este no es mi vaso de Batman. Eliseo: En tu procedimiento sí usas First para obtener las primeras n filas. Sí, las primeras n filas que construye en el aire el propio For Select del procedimiento con ayuda de Suspend. No dije que fueran las filas (registros) de una tabla.

Empezado por ariefez

...Tu sentencia esta bien solo le falta unos parentesis

Código SQL [-]
    SELECT FIRST (:PAGE_NF)...

Agrego: probado en firebird 2.0 y 2.1 beta

Empezado por jhonny

Vaya, Vaya, Vaya¡¡¡

Otros tres vayaes . Me sorprendió de la misma forma y lo mejor de todo es que ¡también funciona en Firebird 1.5!

Gracias por el tip Ariefez, creo que has dado con la mejor solución para los casos planteados. Y además he aprendido aquello del Order By en llamadas a procedimientos.


Un primer abrazo.

Al González.