| Subject | Re: [IBO] Explicit join doesn´t work with IB Query |
|---|---|
| Author | Helen Borrie |
| Post date | 2011-05-03T22:39:51Z |
At 06:15 AM 4/05/2011, Carlos Daniel wrote:
The SQL syntax in this query is not valid. It is now 7 years since developers were advised of the impending enforcement of correct syntax for joins. In Firebird 1.5 it would issue a warning through the Error Status Array, but would allow it. For Firebird 2 and higher versions it causes an exception. As far as I recall, at IBO 4.7 or 4.8, Jason made changes to the parser to take in these corrections in the Firebird parser. But perhaps you are still using InterBase...and I have no idea whether Borland or Embarcadero have corrected the IB parser.
In case you don't know what I am talking about, the standard (and Firebird 2 onwards) requires all referenced columns in joined queries to be fully qualified, using either table aliases or table identifiers, but not a mixture of both. A correct query could be (guessing at the location of your unqualified columns):
SELECT proc.PK_PROC
, proc.FG_CATEGORIA
, cat.DESCRICAO AS CAT_DESC
, cat.CODIGO
, proc.DESCRICAO
, proc.LOG_OPERADOR_I
, proc.LOG_OPERADOR_E
, proc.IMENTOS.DATA_INC
, proc.S.DATA_ALT
, cat.CARENCIA
, cat.PERIODICIDADE
, cat.RETORNO
, cat.UTILIZACAO
, cat.REMUNERACAO
, cat.CO_PARTICIP
, cat.BLOQUEADO
, cat.PROCEDIMENTOS.OBSERVACOES
FROM PROCEDIMENTOS proc
left join categorias cat
on proc.fg_categoria = cat.pk_categoria
Also, with a left join, you should be careful with your KeyLinks as you will potentially have NULLs in columns retrieved from CATEGORIAS. This is *not* a naturally updatable set and bad KeyLinks would cause the problem that you described.
Not a solution, I know, but possibly it might shed some light...
Helen
>Hi friends,There is no way to tell whether it is related to your display/refresh problem, as you do not say which version of DB server, client and IBO you have used here.
>
>I made this code on my ib_iquery
>
>SELECT PK_PROC
> , FG_CATEGORIA
> , CATEGORIAS.DESCRICAO AS CAT_DESC
> , CODIGO
> , PROCEDIMENTOS.DESCRICAO
> , PROCEDIMENTOS.LOG_OPERADOR_I
> , PROCEDIMENTOS.LOG_OPERADOR_E
> , PROCEDIMENTOS.DATA_INC
> , PROCEDIMENTOS.DATA_ALT
> , CARENCIA
> , PERIODICIDADE
> , RETORNO
> , UTILIZACAO
> , REMUNERACAO
> , CO_PARTICIP
> , BLOQUEADO
> , PROCEDIMENTOS.OBSERVACOES
>FROM PROCEDIMENTOS
>left join categorias on procedimentos.fg_categoria = categorias.pk_categoria
The SQL syntax in this query is not valid. It is now 7 years since developers were advised of the impending enforcement of correct syntax for joins. In Firebird 1.5 it would issue a warning through the Error Status Array, but would allow it. For Firebird 2 and higher versions it causes an exception. As far as I recall, at IBO 4.7 or 4.8, Jason made changes to the parser to take in these corrections in the Firebird parser. But perhaps you are still using InterBase...and I have no idea whether Borland or Embarcadero have corrected the IB parser.
In case you don't know what I am talking about, the standard (and Firebird 2 onwards) requires all referenced columns in joined queries to be fully qualified, using either table aliases or table identifiers, but not a mixture of both. A correct query could be (guessing at the location of your unqualified columns):
SELECT proc.PK_PROC
, proc.FG_CATEGORIA
, cat.DESCRICAO AS CAT_DESC
, cat.CODIGO
, proc.DESCRICAO
, proc.LOG_OPERADOR_I
, proc.LOG_OPERADOR_E
, proc.IMENTOS.DATA_INC
, proc.S.DATA_ALT
, cat.CARENCIA
, cat.PERIODICIDADE
, cat.RETORNO
, cat.UTILIZACAO
, cat.REMUNERACAO
, cat.CO_PARTICIP
, cat.BLOQUEADO
, cat.PROCEDIMENTOS.OBSERVACOES
FROM PROCEDIMENTOS proc
left join categorias cat
on proc.fg_categoria = cat.pk_categoria
Also, with a left join, you should be careful with your KeyLinks as you will potentially have NULLs in columns retrieved from CATEGORIAS. This is *not* a naturally updatable set and bad KeyLinks would cause the problem that you described.
Not a solution, I know, but possibly it might shed some light...
Helen