Mailing List
Home
Forum Home
MySQL General - General MySQL discussion
MySQL++ - Programming with the C++ API to MySQL
MaxDB - Everything about MaxDB, formerly known as SAP DB
ODBC - ODBC with the MySQL Connector/ODBC driver
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• mysql openssl Question
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• Update one field with more fields from another table
• Getting Identity after INSERT
• ERROR 2002: Can 't connect to local MySQL server through socket
• mysql test 4 1 fails with the gis test
• MySQL Cluster Software
• Downgrade Mysql from 4 to 3 23
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Can 't access mysql after kernel upgrade
• Executing MySQL Commands From Within C Program
• Comparing and writing out BLOBS
• Preventing Duplicate Entries
• FULLTEXT query format question
• Strange behavior, Table Level Permission
• Does the binary log enabling affect the MySQL performances?
• mysql:it 's a db not a dbms how it 's possible?!
• mysql have same function mthod as Oracle decode()
 
Problem with subqueries

Problem with subqueries

2007-06-14       - By =?utf-8?Q?"Fr=C3=A9d=C3=A9ric DEMILLY"?=

 Back
Hello All,
I'me using MaxDb 7.6.00.32 on Windows 2000 Server.
I've a strange problem with subqueries. This query work fine:
select distinct article.art_c_ref from torpedo.article,torpedo.niv_clfct_mar as
metier,torpedo.niv_clfct_mar as famille where  exists (select * from torpedo
.encours_sto_art where torpedo.encours_sto_art.Art_c_ref = torpedo.article.art_c
_ref and  torpedo.encours_sto_art.sto_c_code = '2ANGE' and torpedo.encours_sto
_art.ESA_F_STOTHEO > 0) and  art_l_actif = true and  article.art_c_ref = metier
.art_c_ref and metier.cm_i_num = 1 and metier.cm_i_rang = 2 and metier.cm_c_code
= '50' and  article.art_c_ref = famille.art_c_ref and famille.cm_i_num = 1 and
famille.cm_i_rang = 3 and famille.cm_c_code = '010' and  article.art_c_type_ens
= '0' and article.art_c_ref not in(select art_c_ref from torpedo.etb_int where
torpedo.etb_int.etb_c_code = 'ANGE') and article.art_c_ref not in (select art_c
_ref from torpedo.sku where torpedo.sku.col_c_code = 'EXO')

But this one return an error:
select distinct article.art_c_ref from torpedo.article,torpedo.niv_clfct_mar as
metier,torpedo.niv_clfct_mar as famille where  exists (select * from torpedo
.encours_sto_art where torpedo.encours_sto_art.Art_c_ref = torpedo.article.art_c
_ref and  torpedo.encours_sto_art.sto_c_code = '2ANGE' and torpedo.encours_sto
_art.ESA_F_STOTHEO > 0) and  art_l_actif = true and  article.art_c_ref = metier
.art_c_ref and metier.cm_i_num = 1 and metier.cm_i_rang = 2 and metier.cm_c_code
= '50' and  article.art_c_ref = famille.art_c_ref and famille.cm_i_num = 1 and
famille.cm_i_rang = 3 and famille.cm_c_code = '010' and  article.art_c_type_ens
= '0' and article.art_c_ref not in(select art_c_ref from torpedo.etb_int where
torpedo.etb_int.etb_c_code = 'ANGE' union select art_c_ref from torpedo.sku
where torpedo.sku.col_c_code = 'EXO')

General error;-7011 POS(241) Correlated subquery not allowed
...torpedo.encours_sto_art.Art_c_ref = torpedo.article.art_c_ref...

Why does the error is on the first subquery, since I've change the last one ?
TIA

Fred.

P.S. I know I can do more optimisation on the query, but it's generated by an
application, depending on which criteria are chosen by the user.

P.P.S sorry for my poor English, French native ;)



--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=mysql@(protected)