Subject Re: [IBO] Master detail with master and group by problem: is it ok?
Author Helen Borrie
At 10:31 AM 10/02/2004 +0000, you wrote:
>IBO 4.3Aa, WI-V6.2.908 Firebird 1.0
>I've a master query with some joins and a group by clause. If in the
>detail table I use (in MasterParamLinks, or in MasterLinks) the form
>TABLE.MASTERFIELD to specify the master field I get the following error:
>---------------------------
>Debugger Exception Notification
>---------------------------
>Project ammwine2000.exe raised exception class EIB_StatementError with
>message 'FieldName: CONDOMINI.CONDOMINIO_ID not found'. Process
>stopped. Use Step or Run to continue.
>---------------------------
>
>If, instead, I use the form MASTERFIELD (no table qualify), it works.
>But, since joins are involved, there could be the possibility of
>having two fields with the same name from different tables and I want
>to link only to the one from table A, not B. A workaround is use
>column alias.
>Seems that with Goup By the table information is lost.
>Is it the intended behaviour?
>
>Here the master query:
>SELECT CONDOMINI.CONDOMINIO_ID,
> CONDOMINI.DESCRIZIONEDISP,
> SUM(RITENUTE_MOV.RITENUTA) AS TOT_RITENUTA
>FROM CONDOMINI
>INNER JOIN RITENUTE ON (RITENUTE.CONDOMINIO_ID = CONDOMINI.CONDOMINIO_ID)
>INNER JOIN RITENUTE_MOV ON (RITENUTE.RITENUTA_ID =
>RITENUTE_MOV.RITENUTA_ID)
>WHERE (CONDOMINI.GESTIONE_FISCO = 'S') AND
> (EXTRACT(YEAR FROM RITENUTE_MOV.DATA_PAGAMENTO_COMPENSO) =
>:P_ANNO_PAGAMENTI) AND
> (EXTRACT(MONTH FROM RITENUTE_MOV.DATA_PAGAMENTO_COMPENSO) =
>:P_MESE_PAGAMENTI) AND
> (ABS(RITENUTE_MOV.RITENUTA) > 1)
>GROUP BY CONDOMINI.CONDOMINIO_ID, CONDOMINI.DESCRIZIONEDISP
>
>
>Here the detail table, using MasterParamLinks
>SELECT RITENUTA_MODPAG_ADD_ID
> , CONDOMINIO_ID
> , COMPETENZA_ANNO
> , COMPETENZA_MESE
> , COD_TRIBUTO
> , IMPORTO
>FROM RITENUTE_MODPAG_ADD
>WHERE (RITENUTE_MODPAG_ADD.CONDOMINIO_ID = :P_CONDOMINIO_ID)
>
>Using:
>P_CONDOMINIO_ID=CONDOMINI.CONDOMINIO_ID
>the error is risen, while using:
>P_CONDOMINIO_ID=CONDOMINIO_ID
>it works ok
>
>If I change the detail query to be used with MasterLinks, I have the
>same problem:
>
>this does not work:
>RITENUTE_MODPAG_ADD.CONDOMINIO_ID=CONDOMINI.CONDOMINIO_ID
>this works:
>RITENUTE_MODPAG_ADD.CONDOMINIO_ID=CONDOMINIO_ID

Marco,
It gets down to SQL logic.

A grouping field implies multiple "master" records, which, if referred back
to a table, cannot be supported as a master-detail (1:Many) structure
(CONDOMINIO.CONDOMINIO_ID is implicitly non-unique).. Removing the table
identifier solves the problem, because it doesn't require/enable the
relationship to be linked to that non-existent database relationship. In
the client-side dataset, CONDOMINIO_ID is unique. The grouped query isn't
updatable, of course.

In situations like this, if you're concerned about the doubling of
fieldnames, you can either output your master fields as run-time fields (AS
GR_CONDOMINIO_ID, etc.) or define a view with alternative output fieldnames.

Helen