Re: Problem with SQL query

"Nicolas Payre" <nicpayre[junk]_at_sympatico.ca> wrote in message news:<4j_Ra.6506$eP6.908834_at_news20.bellglobal.com>...
> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0307180739.780144a2_at_posting.google.com...
> > "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! My mistake, my 1st post wasn't clear. There is no WHERE clause at
> UPDATE statement level, the line is echo by SQL*Plus to show what cause the
> error...
>
> However, we agree on the part of the statement that cause the error! But I
> don't undestand why I can't use inline-view the same way I would use table
> in a correlated-subquery ??
>

'cause Oracle said so. No other reason.

> Thanks Alex.
>
> >
> >
> > > 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