Re: SQL, Group by expressions

From: Peter Chang <p-chang_at_ti.com>
Date: Fri, 18 Sep 1998 08:55:58 -0500
Message-ID: <3602666E.D685AEE1_at_ti.com>


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  TO_CHAR(T.date,'YYYY') ,
                    TO_CHAR(T.date,'MM') ,
                    a.descr

Emmanuel Baechler wrote:

> Hi,
>
> 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 - 15:55:58 CEST

Original text of this message