davydrey wrote:

> when a execute this SQL query, i have this error
> 'invalid request BLR at offset 100.
> Implementation limit exceeded.
> block size exceeds implementation restriction.'
> and It works on SQLSERVER !
>
> here is my query
>
> "SELECT DISTINCT SCHEMAGAMME.*, GAMME_CODE, LIBGAMME.LIBDATA_LIB
> GAMME_LIB, LIBGAMME2.LIBDATA_LIB GAMME_DESC, RUBGAMME_CODE,
> LIBRUBGAMME.LIBDATA_LIB RUBGAMME_LIB, SCHEMA_FICHIER,
> LIBSCHEMA.LIBDATA_LIB SCHEMA_LIB, LIBFAMILLE.LIBDATA_LIB FAMILLE_LIB
> FROM SCHEMAGAMME, FAMILLE, GAMME, RUBGAMME, UNSCHEMA, LIBDATA
> LIBGAMME, LIBDATA LIBGAMME2, LIBDATA LIBRUBGAMME, LIBDATA
> LIBSCHEMA,LIBDATA LIBFAMILLE
> WHERE SCHEMAGAMME_GAMME_ID = GAMME_ID
> AND LIBGAMME.LIBDATA_LNG_CODE='FR'
> AND LIBGAMME.LIBDATA_TABLE = 'GAMME'
> AND LIBGAMME.LIBDATA_CHAMP = 'GAMME_LIB'
> AND LIBGAMME.LIBDATA_TABLE_ID = GAMME_ID
> AND LIBGAMME2.LIBDATA_LNG_CODE='FR'
> AND LIBGAMME2.LIBDATA_TABLE = 'GAMME'
> AND LIBGAMME2.LIBDATA_CHAMP = 'GAMME_DESC'
> AND LIBGAMME2.LIBDATA_TABLE_ID = GAMME_ID
> AND SCHEMAGAMME_RUBGAMME_ID = RUBGAMME_ID
> AND LIBRUBGAMME.LIBDATA_LNG_CODE='FR'
> AND LIBRUBGAMME.LIBDATA_TABLE = 'RUBGAMME'
> AND LIBRUBGAMME.LIBDATA_CHAMP = 'RUBGAMME_LIB'
> AND LIBRUBGAMME.LIBDATA_TABLE_ID = RUBGAMME_ID
> AND SCHEMAGAMME.SCHEMAGAMME_ID = 671
> AND SCHEMA_CODE=SCHEMAGAMME_SCHEMA_CODE
> AND LIBSCHEMA.LIBDATA_LNG_CODE='FR'
> AND LIBSCHEMA.LIBDATA_TABLE = 'SCHEMA'
> AND LIBSCHEMA.LIBDATA_CHAMP = 'SCHEMA_LIB'
> AND LIBSCHEMA.LIBDATA_TABLE_ID = SCHEMA_ID
> AND FAMILLE_ID = GAMME_FAMILLE_ID
> AND LIBFAMILLE.LIBDATA_LNG_CODE='FR'
> AND LIBFAMILLE.LIBDATA_TABLE = 'FAMILLE'
> AND LIBFAMILLE.LIBDATA_CHAMP = 'FAMILLE_LIB'
> AND LIBFAMILLE.LIBDATA_TABLE_ID = FAMILLE_ID
> ORDER BY SCHEMAGAMME_ORDRE"
>
> Can you help me ?
>
> davyd rey

Hi Davyd!

Actually, I don't know whether I can help you or not. That is, I find
your query difficult to read and I can give you some hints to clean it
up, but I don't know what made Firebird complain.

There are three things I generally react to:

Use of SQL-89: SQL-92 is to be preferred, change from joining in the
WHERE clause to joining in the JOIN clause. SQL-92 is easier for both
the reader and the optimizer.

Qualifying fields. You should qualify all fields with its alias to show
where every field belong. The optimizer gives an error if you don't
qualify a field that may belong to more than one alias.

Use of *. It is better to write all fields rather than using <table name>.*.

So we get something like (having guessed on those fields I don't know
where belongs to):

SELECT DISTINCT SCHEMAGAMME.FIELDA, SCHEMAGAMME.FIELDB,
SCHEMAGAMME.FIELDC, GAMME.GAMME_CODE, LIBGAMME.LIBDATA_LIB GAMME_LIB,
LIBGAMME2.LIBDATA_LIB GAMME_DESC, RUBGAMME.RUBGAMME_CODE,
LIBRUBGAMME.LIBDATA_LIB RUBGAMME_LIB, UNSCHEMA.SCHEMA_FICHIER,
LIBSCHEMA.LIBDATA_LIB SCHEMA_LIB, LIBFAMILLE.LIBDATA_LIB FAMILLE_LIB
FROM SCHEMAGAMME
JOIN GAMME ON SCHEMAGAMME.SCHEMAGAMME_GAMME_ID = GAMME.GAMME_ID
JOIN FAMILLE ON FAMILLE.FAMILLE_ID = GAMME.GAMME_FAMILLE_ID
JOIN RUBGAMME
ON SCHEMAGAMME.SCHEMAGAMME_RUBGAMME_ID = RUBGAMME.RUBGAMME_ID
JOIN UNSCHEMA
ON SCHEMAGAMME.SCHEMA_CODE=UNSCHEMA.SCHEMAGAMME_SCHEMA_CODE
JOIN LIBDATA LIBGAMME ON LIBGAMME.LIBDATA_TABLE_ID = GAMME.GAMME_ID
JOIN LIBDATA LIBGAMME2 ON LIBGAMME2.LIBDATA_TABLE_ID = GAMME.GAMME_ID
JOIN LIBDATA LIBRUBGAMME
ON LIBRUBGAMME.LIBDATA_TABLE_ID = RUBGAMME.RUBGAMME_ID
JOIN LIBDATA LIBSCHEMA
ON LIBSCHEMA.LIBDATA_TABLE_ID = SCHEMAGAMME.SCHEMA_ID
JOIN LIBDATA LIBFAMILLE
ON LIBFAMILLE.LIBDATA_TABLE_ID = FAMILLE.FAMILLE_ID
WHERE LIBGAMME.LIBDATA_LNG_CODE='FR'
AND LIBGAMME.LIBDATA_TABLE = 'GAMME'
AND LIBGAMME.LIBDATA_CHAMP = 'GAMME_LIB'
AND LIBGAMME2.LIBDATA_LNG_CODE='FR'
AND LIBGAMME2.LIBDATA_TABLE = 'GAMME'
AND LIBGAMME2.LIBDATA_CHAMP = 'GAMME_DESC'
AND LIBRUBGAMME.LIBDATA_LNG_CODE='FR'
AND LIBRUBGAMME.LIBDATA_TABLE = 'RUBGAMME'
AND LIBRUBGAMME.LIBDATA_CHAMP = 'RUBGAMME_LIB'
AND SCHEMAGAMME.SCHEMAGAMME_ID = 671
AND LIBSCHEMA.LIBDATA_LNG_CODE='FR'
AND LIBSCHEMA.LIBDATA_TABLE = 'SCHEMA'
AND LIBSCHEMA.LIBDATA_CHAMP = 'SCHEMA_LIB'
AND LIBFAMILLE.LIBDATA_LNG_CODE='FR'
AND LIBFAMILLE.LIBDATA_TABLE = 'FAMILLE'
AND LIBFAMILLE.LIBDATA_CHAMP = 'FAMILLE_LIB'
ORDER BY SCHEMAGAMME.SCHEMAGAMME_ORDRE

Does Firebird still complain if you change your code in a way similar to
this?

HTH,
Set