SELECT con SUBSTRING en Firebird

[Gregorio Cíber]

Esto:
LEFT JOIN Bancos H ON SUBSTRING(A.Iban FROM 5 FOR 4) = H.Codigo AND A.IdCuenta = 'A'

¿No debería ir así?
LEFT JOIN Bancos H ON (SUBSTRING(A.Iban FROM 5 FOR 4) = H.Codigo) AND (A.IdCuenta = 'A')

[Casimiro Noteví]

Código SQL [-]
SELECT SUBSTRING(field FROM 5 FOR 15) AS x FROM table;

[[RONPABLO]]

Hay que tener en cuenta que existe unas UDF que implementan el substring y lo usan de una forma diferente, así que es bueno ver que en los códigos del UDF no se encuentre creado substring, también sría bueno saber que versión de Firebird usa? H.Codigo es un string o un int?
Por último y aunque no creo que afecte mucho podrías cambiar el orden a lo siguiente:

Código SQL [-]
LEFT JOIN Bancos H ON H.Codigo = SUBSTRING(A.Iban FROM 5 FOR 4) AND A.IdCuenta = 'A'
LEFT JOIN Bancos I ON I.Codigo = SUBSTRING(A.Iban FROM 1 FOR 4) AND A.IdCuenta = 'B'

[Angel.Matilla]

Gracias por las respuestas.

Empezado por Gregorio Cíber

¿No debería ir así?
LEFT JOIN Bancos H ON (SUBSTRING(A.Iban FROM 5 FOR 4) = H.Codigo) AND (A.IdCuenta = 'A')

El resultado, en cualquier caso, sería el mismo.

Empezado por Casimiro Notevi

Código SQL [-]SELECT SUBSTRING(field FROM 5 FOR 15) AS x FROM table;

Esto no le he probado, pero no creo que cambie el resultado; curiosamente donde indica el error (line 6, char 43) es donde está la n de Iban.

Empezado por RONPABLO

Hay que tener en cuenta que existe unas UDF que implementan el substring y lo usan de una forma diferente, así que es bueno ver que en los códigos del UDF no se encuentre creado substring, también sría bueno saber que versión de Firebird usa? H.Codigo es un string o un int?

La versión de FB es la 2.5 y... ¡GRACIAS! Acabo de darme cuenta al leer tu mensaje que he cambiado la estructura de la tabla y el código ahora es numérico y antes era varchar.

[Angel.Matilla]

¡Mi gozo en un pozo!

Al margen de que, efectivamente, había renombrado alguna columna y se me había olvidado el problema no era esa. Sigue dándome el mismo error; por si acaso he actualizado a la versión 2.5.4 de Firebird.

Voy más allá: Estoy probando este otro código:

Código SQL [-]
SELECT * FROM Bancos WHERE SUBSTRING(Bic FROM 5 FOR 2) NOT IN (SELECT Codigo FROM Paises)

Las tablas son estas:

Bancos
CREATE TABLE BANCOS (
ENTIDAD VARCHAR(5) CHARACTER SET NONE NOT NULL,
NOMBRE VARCHAR(66) CHARACTER SET NONE,
BIC VARCHAR(12) CHARACTER SET NONE);

Países
CREATE TABLE PAISES (
CODIGO VARCHAR(3) CHARACTER SET NONE NOT NULL,
NOMBRE VARCHAR(51)CHARACTER SET NONE);

En el código BIC las posiciones 5 y 6 del mismo identifican el país y se referencian a la tabla ISO 3166-1 alpha-2 (por ejemplo en Códigos oficialmente asignados).

Hasta donde veo el código es correcto; de hecho en una BB.DD. Paradox funciona sin ningún problema. Sin embargo usando tanto IBManager como IBExpert me da el mismo error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 2, char 19.
FROM.

Ya no sé por donde pillarlo y me tiene bloqueada la adaptación de una aplicación desde Paradox a Firebird.

[Angel.Matilla]

Si hace falta y alguien me puede ayudar le mando las tablas implicadas.

[Casimiro Noteví]

Es que por lo visto tiene que ser "select substring" no en un "where".
Puedes crearte un Stored Procedure, algo asi como:

Código SQL [-]
for select substring(bic from 5 to 2) from bancos into :valor do
  select codigo from tbpaises where codigo= :valor
  suspend;
end

[ecfisa]

Hola Angel.Matilla.

Algo mas debe haber en juego. Reproduje la situación como la planteaste, ejecutando la misma consulta y no me dá ningún error.

Ambiente:

Windows 7 32 bit.
Firebird 2.5.0
IBExpert 2015.6.17.1

Tabla PAISES:

CODIGO	NOMBRE
----------------
156	CHINA
840	E.E.U.U.
124	CANADA

Tabla BANCOS:

ENTIDAD	NOMBRE	                BIC
--------------------------------------------
00001	ICBC	                000015600000
00002	Wells Fargo & Co	000084000000
00003	Bank of Nova Scotia	000012400000

Consulta:

Código SQL [-]
SELECT *
FROM BANCOS
WHERE SUBSTRING(BIC FROM 5 FOR 2) NOT IN (SELECT CODIGO FROM PAISES)

Resultado:

ENTIDAD	NOMBRE	                BIC
--------------------------------------------
00001	ICBC	                000015600000
00002	Wells Fargo & Co	000084000000
00003	Bank of Nova Scotia	000012400000

(Como dato a tomar en cuenta, los países son representados por tres dígitos)

Saludos

[[RONPABLO]]

A mi también me corre bien el query así tal cual está, con esos tipos de datos (osea BIC varchar(12) y CODIGO varchar(3),

por otro lado el query no es optimo y con muchos registros en ambas tamblas podría tomar mucho tiempo, lo puedes cambiar a algo similar a esto:

Código Delphi [-]
SELECT b.* FROM Bancos b WHERE exists (SELECT p.Codigo FROM Paises p where p.codigo = SUBSTRING(b.Bic FROM 5 FOR 2))

[Angel.Matilla]

Gracias a todos por las respuestas.

Empezado por Casimiro Notevi

Es que por lo visto tiene que ser "select substring" no en un "where".

Pues yo lo tengo usado tal como lo he puesto con tablas Paradox y en otras aplicaciones con FB. Además, el error que da es que la palabra FROM es desconocida. Si en lugar de se código pongo, por ejemplo, este:
Código SQL [-]SELECT SUBSTRING(Bic FROM 5 FOR 2) FROM Bancos

me da el mismo error

Empezado por Casimiro Notevi

Puedes crearte un Stored Procedure, algo asi como:

Código SQL [-]for select substring(bic from 5 to 2) from bancos into :valor do select codigo from tbpaises where codigo= :valor suspend; end

¿Y como hago para meter esto en un CREATE VIEW o en un UPDATE de una tabla?

Empezado por ecfisa

Hola Angel.Matilla.

Algo mas debe haber en juego. Reproduje la situación como la planteaste, ejecutando la misma consulta y no me dá ningún error.

Sí, pero no soy capaz de verlo.

Empezado por ecfisa

Tabla PAISES:

CODIGO    NOMBRE
----------------
156    CHINA
840    E.E.U.U.
124    CANADA

Tabla BANCOS:

ENTIDAD    NOMBRE                    BIC
--------------------------------------------
00001    ICBC                    000015600000
00002    Wells Fargo & Co    000084000000
00003    Bank of Nova Scotia    000012400000

Consulta:
Código SQL [-]SELECT * FROM BANCOS WHERE SUBSTRING(BIC FROM 5 FOR 2) NOT IN (SELECT CODIGO FROM PAISES)


Resultado:

ENTIDAD    NOMBRE                    BIC
--------------------------------------------
00001    ICBC                    000015600000
00002    Wells Fargo & Co    000084000000
00003    Bank of Nova Scotia    000012400000

Pues algo no hace bien tu prueba. Fíjate que busco los que no estén en la tabla. Con la prueba que has hecho no tendría que salir ninguna fila del select.

Empezado por RONPABLO

por otro lado el query no es optimo y con muchos registros en ambas tamblas podría tomar mucho tiempo, lo puedes cambiar a algo similar a esto:

SELECT b.* FROM Bancos b WHERE exists (SELECT p.Codigo FROM Paises p where p.codigo = SUBSTRING(b.Bic FROM 5 FOR 2))

Me da el mismo error: TOKEN UNKNOW FROM.

[Angel.Matilla]

A lo mejor digo una tontería: ¿Puede tener algo que ver con la definición de la base de datos? Es que no se me ocurre otra cosa. La definición, que se hace por código, es esta:

IBDatabase1->Params->Clear();
IBDatabase1->DatabaseName = "Afiliados.gdb";
IBDatabase1->Params->Add("USER 'sysdba'");
IBDatabase1->Params->Add("PASSWORD 'masterkey'");
IBDatabase1->Params->Add("PAGE_SIZE 4096");
IBDatabase1->SQLDialect = 3;
IBDatabase1->CreateDatabase();