473,287 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Problem with SQL query

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
3 4650
"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
"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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
8
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
6
by: lenny | last post by:
Hi, I've been trying to use a Sub or Function in VBA to connect to a database, make a query and return the recordset that results from the query. The connection to the database and the query...
3
by: StBond | last post by:
Hi everyone, I am new to Access and Visual Basic so things my be getting across a bit cloudy. I only started using VB for one week. I am having a little problem with the database that I am...
3
by: Andy_Khosravi | last post by:
I have been trying to build a user friendly search engine for a small database I have created. I'm having some particular problems with one of my date fields. Here's the setup: I'm using...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
0
by: mjsterz | last post by:
I've been working with VB .NET for less than a year and this is the first time I've posted on one of these groups, so let me apologize beforehand if I'm being unclear, not posting my issue...
3
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows:...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.