Problem with SQL query

From: Nicolas Payre <nicpayre_at_sympatico.ca>
Date: Wed, 16 Jul 2003 15:20:59 -0700
Message-ID: <qAhRa.6008$104.567613_at_news20.bellglobal.com>

Hi,
I have the following SQL that I want to use to update a table. It doesn't work ! Does someone knows why?

• I Know it could be done easy with a CURSOR FOR LOOP, but still...

Thanks for your help.

update XSORA1A.XS0011T_STATISTIQUES_UNIX a

   set a.ID_APPLICATION = (

                                                select b.ID_APPLICATION
                                                 from (
                                                        select
c.ID_APPLICATION, count(*) as nbr
                                                          from
XSORA1A.XS0011T_STATISTIQUES_UNIX c
                                                         where
c.XS007_NOM_PHYSIQUE_CUBE = a.XS007_NOM_PHYSIQUE_CUBE
                                                        group by
c.ID_APPLICATION
                                                        order by nbr desc
                                                         ) b
                                                where rownum = 1
                                )




where c.XS007_NOM_PHYSIQUE_CUBE = a.XS007_NOM_PHYSIQUE_CUBE
                                             *

ERROR at line 7:
ORA-00904: invalid column name

SQL> desc XSORA1A.XS0011T_STATISTIQUES_UNIX;

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
-
 XS001_STARTDATE                           NOT NULL DATE
 XS002_START_MILISECOND                             NUMBER(4)
 XS003_ENDDATE                                      DATE
 XS004_END_MILISECOND                               NUMBER(4)
 XS005_SESSION_ID                          NOT NULL NUMBER(6)
 XS006_REQUEST_ID                          NOT NULL VARCHAR2(4)
 XS007_NOM_PHYSIQUE_CUBE                            VARCHAR2(255)
 XS008_NOM_LOGIQUE_CUBE                             VARCHAR2(128)
 XS009_SERVEUR                                      VARCHAR2(16)
 XS010_NOM_USAGER                                   VARCHAR2(32)
 XS011_TEMPS_MS                                     NUMBER(10)
 XS013_CODE_ERREUR                                  VARCHAR2(1)
 XS014_MESSAGE_ERREUR                               VARCHAR2(2000)
 ID_APPLICATION                                     VARCHAR2(60)
 DATE_LOAD                                          DATE

==

I also tried the following...

SQL> select *
  2 from XSORA1A.XS0011T_STATISTIQUES_UNIX a

  3     where a.ID_APPLICATION = (
  4                     select b.ID_APPLICATION
  5                       from (
  6                             select c.ID_APPLICATION, count(*) as nbr
  7                               from XSORA1A.XS0011T_STATISTIQUES_UNIX c
  8                              where c.XS007_NOM_PHYSIQUE_CUBE =
b.XS007_NOM_PHYSIQUE_CUBE
  9                              group by c.ID_APPLICATION
 10                              order by nbr desc
 11                             ) b
 12                       where b.XS007_NOM_PHYSIQUE_CUBE =
a.XS007_NOM_PHYSIQUE_CUBE
 13                         and rownum = 1
 14  );
     where c.XS007_NOM_PHYSIQUE_CUBE = b.XS007_NOM_PHYSIQUE_CUBE
                                                                         *

ERROR at line 8:
ORA-00904: invalid column name

==

And then... But no sucess.

select *
  from XSORA1A.XS0011T_STATISTIQUES_UNIX a    where a.ID_APPLICATION = (

                                        select b.ID_APPLICATION
                                  from XSORA1A.XS0011T_STATISTIQUES_UNIX b
                                         where b.XS007_NOM_PHYSIQUE_CUBE =
a.XS007_NOM_PHYSIQUE_CUBE
                                         group by b.ID_APPLICATION
                                         having count(*) = (
                                                             select
max(count(*))
                                                                    from
XSORA1A.XS0011T_STATISTIQUES_UNIX c
                                                                   where

c.XS007_NOM_PHYSIQUE_CUBE = b.XS007_NOM_PHYSIQUE_CUBE

                                                                 group by
c.ID_APPLICATION
                                                            )
                                   )

ERROR at line 11:
ORA-00979: not a GROUP BY expression Received on Thu Jul 17 2003 - 00:20:59 CEST