SELECT con SUBSTRING en Firebird

[Angel.Matilla]

Tengo este query:

Código SQL [-]
SELECT A.Apellidos, A.Nombre, A.Nif, A.F_nacim, A.Domicilio, A.Cpostal, A.Municipio, A.Provincia, A.Telefono_1, A.Telefono_2, A.Telefono_3, A.C_electronico, 
A.Profesion CodProfesion, A.Sexo, A.Correo, A.Lo1599, A.Situacion, A.Afiliacion, A.Junta, A.N_afiliado, A.F_alta, A.ForPago, A.PerPago, A.Referencia, A.Cuota, 
A.IdCuenta, A.Iban, A.Bic, B.Nombre, B.Pais, C.Nombre, D.Nombre, E.Nombre, F.Nombre, G.Nombre, H.Nombre, H.Bic, I.Nombre, I.Bic
FROM Persona A
LEFT JOIN Bancos H ON SUBSTRING(A.Iban FROM 5 FOR 4) = H.Codigo AND A.IdCuenta = 'A'
LEFT JOIN Bancos I ON SUBSTRING(A.Iban FROM 1 FOR 4) = I.Codigo AND A.IdCuenta = 'B'
LEFT JOIN Poblacion B ON A.CodPrv = B.CodPrv AND A.Municipio = B.Codigo
LEFT JOIN Provincia C ON A.Provincia = C.Codigo
LEFT JOIN Paises D ON B.Pais = D.Codigo
LEFT JOIN Profesion E ON A.CodPrv = E.CodPrv AND A.Profesion = E.Codigo
LEFT JOIN Afiliacion F ON A.CodPrv = F.CodPrv AND A.Afiliacion = F.Codigo
LEFT JOIN Junta G  ON A.CodPrv = G.CodPrv AND A.Junta = G.Codigo

A la hora de tratar de ejecuatrlo, tanto desde programa como con IBManager, me da este error:

Error: "Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 6, char 43. FROM."

El error hace referencia a la claúsula FROM de los SUBSTRING. He estado comprobando la sintaxis de la instrucción y es correcta y todas las columnas existen en las tablas respectivas.

[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 Notevi]

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 Notevi]

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();

[ecfisa]

Hola Angel.Matilla

Empezado por 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:

Lo dudo mucho... Tal vez podrías agregar,

Código PHP:

  IBDatabase1->Params->Add("lc_ctype=ISO8859_1"); 


pero no creo que haga ninguna diferencia.

Empezado por Angel.Matilla

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.

Bueno... En realidad es copia fiél del código de tu consulta :

Empezado por Angel.Matilla

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

Debido a eso es que puse puse la aclaración al final de mi mensaje anterior:

Empezado por ecfisa

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

Dado que los paises se identifican por un código de tres dígitos, tu consulta hubiera dado el resultado esperado si la hubieras formulado de este modo:

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

¿ Has intentado reinstalar Firebird ?

Saludos

[[fjcg02]]

Hola

divide y vencerás.

Yo no veo nada raro en la query.

Si haces
SELECT SUBSTRING(BIC FROM 5 FOR 3)
FROM BANCOS

tiene que darte los códigos de los países.

Si haces
SELECT CODIGO FROM PAISES

tiene que darte los códigos también.

si haces
SELECT SUBSTRING(B.BIC FROM 5 FOR 3)
FROM BANCOS B
inner join PAISES P ON ( SUBSTRING(B.BIC FROM 5 FOR 3) = P.codigo)

tiene que darte los códigos de los países que reconcilian en las dos tablas.

Si has llegado hasta aqui, no vas mal.

Si en lugar de INNER JOIN pones LEFT JOIN.... y añades una pequeña condición... tienes tu consulta.

Esto debe funcionar desde la versión 0 de FB, no lo aseguro pero me apostaría un Kas de limón.

Ya nos contarás. Postea tus resultados.

Un saludo

[Casimiro Notevi]

Empezado por fjcg02

Esto debe funcionar desde la versión 0 de FB, no lo aseguro pero me apostaría un Kas de limón.

Yo me apuesto una Mirinda

[[fjcg02]]

Empezado por Casimiro Notevi

Yo me apuesto una Mirinda

O una Konga naranja

Cuánto ha llovido.... sniff

[Angel.Matilla]

Gracias por vuestras respuestas. El Kas, la Mirinda o la Konga la pago yo si arreglo el follón .

Empezado por ecfisa

IBDatabase1->Params->Add("lc_ctype=ISO8859_1");

Está añadfido y, efectivamente, no tiene ningún efecto.

En la definición de las tablas me he dado cuenta que se me olvidó aclarar una cosa: Los campos VARCHAR están dimensionados con exceso; es decir: tuve que poner en toos los VARCHAR de la base de datos una posición más de las necesarias porque, y tampocó sé la razón, si los ponía justos al hacer un SELECT del tipo LIKE '99%' me daba error. Así pues el código de país realmente tiene dos posiciones: ES para España, FR para Francia, US para los Estados Unidos, etc. En ese sentido ecfisa tiene razón en que el resultado de su query es coherente; ¡error mío!

Empezado por ecfisa

¿ Has intentado reinstalar Firebird ?

Sí, actualizándolo a la última versión estable, la 2.5.4

Empezado por fjcg02

SELECT SUBSTRING(BIC FROM 5 FOR 3)
FROM BANCOS

El problema es que me dice que la palabra reservada FROM le es desconocida, y es esto lo que me tiene perplejo. Ponga donde ponga el SUBSTRING (como parte del SELECT o como parte del WHERE) me da siempre el mismo error:

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

Y lo más gordo de todo es cuando hago las pruebas, tanto IBManager como IBExpert, la ayuda da este mesaje:

<expression> FROM pos [ FOR count ]

¡Y ahora sí que no le entiendo!

[[fjcg02]]

Hola,
sin mucha convicción te lo digo. Has eliminado la dll de firebird de donde proceda?. fbclient.dll . Búscala, bórrala y sustituyela por la incluida en la última versión que hayas instalado.

Podría ser que tengas mezcladas versiones de fb y la librería cliente.

Saludos

[Casimiro Notevi]

Desde luego que "normal" no es eso.

[Angel.Matilla]

Parece ser que todo es debido a una instalación anterior de Interbase XE que juraría había desinstalado. Aunque no estaba conectado el servidor, la base de datos se ha creado como si fuera de Interbase o eso es lo que me han indicado desde EMS:

As we see from your screenshot your database ODS version is 15.0. It means the database was created on InterBase XE. Please check one more time that you are connected to Firebird (not InterBase) server. If you do not have InterBase server installed, please note that the database can be correctly moved to another server by backup/restore only.

Voy a terminar de eliminar totalmente y probaré de nuevo, tal como me indican. Ya os diré.