Subject | Master detail with master and group by problem: is it ok? |
---|---|
Author | Marco Menardi |
Post date | 2004-02-10T10:31:25Z |
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
thanks a lot
Marco Menardi
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
thanks a lot
Marco Menardi