SQL, Group by expressions

From: Emmanuel Baechler <ebaechle_at_hospvd.ch>
Date: Fri, 18 Sep 1998 14:22:03 +0200
Message-ID: <3602506B.5262_at_hospvd.ch>

Hi,

[Quoted] [Quoted] I did recently notice a behavior that looks rather strange to me:

Recently, I got an error when I tried to execute the following request, that looked correct to me:

select TO_CHAR(T.date,'YYYY') As YEAR,

        TO_CHAR(T.date,'MM') As MONTH,
        A.DESCR,
        COUNT (*),
from    Details D,
        Articles A,
        Transactions T
where   D.No_Article =A.No and
        D.No_transact=T.No and
	-- A few more criteria for filtering

group by Year, Month, A.Descr;

The error message was:

ORA-00904: invalid colum name
the substring: 'Month, A.Descr' was highlighted

Now, I do not understand why the column name is invalid.

Second, I turned around the problem, by creating a view on top of the transaction table, adding two columns: the year and the month field. With that view, the request works fine.

But why is it necessary to include the first two fields in a view to have the request working?

Any help will be appreciated

Best regards

Emmanuel Baechler
LES HOSPICES CANTONAUX
Office Informatique
Bugnon 21
1005 Lausanne
Switzerland

select  T.data_annee,
        T.data_mois,
        A.DESCRIZIONE,
        COUNT (*),
        SUM (D.QT_PESO)
from    DETTAGLI D,
        ARTICOLI A,
        TESTATE_extended T
where   D.PROGR_ARTICOLO=A.PROGR and
        D.PROGR_TESTATA=T.PROGR and
        A.CODICE in ('5011', '5012', '5013', '5014', '5015', '5016',
'5017', '5018') and
        T.TIPO_PAGAMENTO in (1, 2, 6, 7) and
        TO_NUMBER( TO_CHAR(T.DATA ,'MM'))>=1 and
        TO_NUMBER( TO_CHAR(T.DATA ,'MM'))<=6

group by T.data_Annee, T.data_mois, A.Descrizione; order by T.data_Annee asc, T.data_mois asc, A.Codice ASC; Received on Fri Sep 18 1998 - 14:22:03 CEST