By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

Problem with SQL query

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Nicolas Payre" <ni******@sympatico.ca> wrote in message news:<qA*******************@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.


ERROR at line 11:
ORA-00979: not a GROUP BY expression

Jul 19 '05 #2

P: n/a
"Nicolas Payre" <nicpayre[junk]@sympatico.ca> wrote in message news:<md*******************@news20.bellglobal.com> ...
"Alex Filonov" <af******@yahoo.com> wrote in message
news:33**************************@posting.google.c om...
"Nicolas Payre" <ni******@sympatico.ca> wrote in message news:<qA*******************@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

Jul 19 '05 #3

P: n/a

"Alex Filonov" <af******@yahoo.com> wrote in message
news:33**************************@posting.google.c om...
"Nicolas Payre" <nicpayre[junk]@sympatico.ca> wrote in message news:<md*******************@news20.bellglobal.com> ...
"Alex Filonov" <af******@yahoo.com> wrote in message
news:33**************************@posting.google.c om...
"Nicolas Payre" <ni******@sympatico.ca> wrote in message

news:<qA*******************@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 ??

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

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.