Re: Problem with SQL query

"Nicolas Payre" <nicpayre[junk]_at_sympatico.ca> wrote in message news:<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?
>

You're right. It's another problem with your query. You can't use references to main query aliases in the inline view. However, the WHERE clause at UPDATE statement level is wrong too, on the reason I stated above.

> 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 - 17:39:33 CEST