Re: Problem with SQL query

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

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

>