Re: Problem with SQL query

From: Nicolas Payre <nicpayre[junk]_at_sympatico.ca>
Date: Thu, 17 Jul 2003 18:14:34 -0700
Message-ID: <mdFRa.5031$eP6.768719_at_news20.bellglobal.com>

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0307170803.25c47db0_at_posting.google.com... > "Nicolas Payre" <nicpayre_at_sympatico.ca> wrote in message  news:<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
> >

>
> You can't reference alias c at the update statement level, it's not
> visible here. You can use this condition inside of the subquery, if
> that's what you want to do.

I'am not sure of what you mean. To me, the problem seems to be that the inline-view (inner most statement) doesn't see the alias a, or refuse to execute with it?

Thanks,

update TABLE a

     set a.ID = ( select b.ID
                         from (  select c.ID, count(*) as nbr
                                      from TABLE c
                                    where c.NOM = a.NOM
                                     group by c.ID
                                     order by nbr desc
                                    ) b
                       where rownum = 1
                         )

> >
> >
> >
> >
> >
> > 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
> >

>
> Same thing here.
>

> >
> >
> > ==
> >
> > 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
> > )
> > )
> >

>
> You're using two nested group functions (max and count). Either use
> inline view or grouping function to do double grouping.
>

> >
> >
> > ERROR at line 11:
> > ORA-00979: not a GROUP BY expression
Received on Fri Jul 18 2003 - 03:14:34 CEST