Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Advanced SQL query  RSS feed

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following relationship diagram

CLIENT(code,nom,prenom,localite,num_tel,categorie)

COMMANDE(num_cmd,#code,date_cmd)

ARTICLE(ref _article,designation,prix_HT, TVA)

LIGNE_CMD(#num_cmd,#ref_article,quantite,remise)

CHIFFRE_AFFAIRE(journee,montant)

write the SQL request that Display products that are ordered in all locations?
please someone can help me to write this request
 
Marshal
Posts: 61715
193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We don't hand out complete solutions or anything like that; please show us what you have tried and let's see if we can't improve it.

Since this question seems the same as your other question, please keep all discussion here.
 
emma roberts
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i try to write the request:

select ref_article,designation

from ligne_cmd,article,commande,client

where ligne_cmd.ref_article=article.ref_article and ligne_cmd.num_cmd=commande.num_cmd and commande.code=client.code

group by ref_article

having count(distinct localite)=(select count(distinct localite) from client);
 
Sheriff
Posts: 5446
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What happened when you executed that code?  Did you get an error?  What is the full error message?

Remember to TellTheDetails (that's a link).
 
emma roberts
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

i have this
group by ref_article
       *
ERROR at line 4:
error:ORA-00918 :column ambiguously defined
 
Campbell Ritchie
Marshal
Posts: 61715
193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does the Database program's handbook say about error 918? How have you defined the ref_article column? Does it appear twice? Is it used as a key?
 
Bartender
Posts: 19976
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:What does the Database program's handbook say about error 918? How have you defined the ref_article column? Does it appear twice? Is it used as a key?



This is Oracle. the manual probably says "column ambiguously defined". And lets you guess which column. Which can be loads of fun when you have a really complex statement.

It could be worse. Here's an IBM classic:

IBM MVS/OS Error Messages and Codes wrote:
ERROPT=ABE OR AN INVALID CODE



IBM's explanation:

IBM MVS/OS Error Messages and Codes wrote:
ABE or an invalid code was specified on the DCB.



Practical English translation:

Either A) You coded the wrong record length in your application (mainframes tended to like fixed-length records)

OR B) The tape drive is on fire.

The only actual way for the average programmer to tell which of the above applied - since app developers were usually not allowed in the computer room - was to examine the post-mortem memory dump and pick bits out of the I//O control structures. Unless the fire alarms went off, anyway.

Usually it was a wrong length record, though.

Later incarnations of this message changed the message lookup code from IEC141I to IEC000I. And dumped several other message texts under that heading. But that's another story.
 
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you put ora-00918 into Google you'll get a couple of definitions and explanations (Burleson always seems to come up first for me).
eg
http://www.dba-oracle.com/t_ora_00918_column_ambiguously_defined.htm
Or:
https://www.techonthenet.com/oracle/errors/ora00918.php

Essentially you have at least two tables with the same column name that you are referring to in your query.
 
Tim Holloway
Bartender
Posts: 19976
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:If you put ora-00918 into Google you'll get a couple of definitions and explanations (Burleson always seems to come up first for me).
eg
http://www.dba-oracle.com/t_ora_00918_column_ambiguously_defined.htm
Or:
https://www.techonthenet.com/oracle/errors/ora00918.php

Essentially you have at least two tables with the same column name that you are referring to in your query.



So they're leaving you to guess not only which column, but which tables.  
 
Dave Tolls
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Of course.
Where's the fun in actually being told where the problem is?



(I have never understood why Oracle is incapable of actually providing that information)
 
Tim Holloway
Bartender
Posts: 19976
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
IBM and Oracle are old-line IT companies. Their software was designed for behemoth mainframes where anything over 64 kilobytes of RAM was considered enormous (I remember the day we got an a 1MB module to replace the 256K unit we'd been using. Everyone was impressed).

Back then, hardware and CPU time was expensive, as was the disk space needed to store smarter logic and better error codes. When I was in school, I wanted to study PL/1, but the basic compiler would have taken 1 Megabyte of DASD space to install and the operations staff wouldn't do it unless I got enough people to demand a course. Yes, camels still roamed North America back then.

Also, when a mainframe cost a cool million or more back when Dr. Evil could demand a sum like that without being laughed at, paying teams of programmers $30-$60K annual salaries was not considered as objectionable as it is today, and the programmers weren't expected to re-create the Universe on a weekly basis. So spending a lot of time tracking down such messages was not considered a problem (by anyone but the programmers, anyway). Even though they might get only 2 shots a day at compiling and testing.

The problem is, they haven't done a whole lot of improvement in the product basics. It's like a skyscraper that was built on top of a Roman temple that was built on top of a mud hut. There's all sorts of advanced "gee-whiz" stuff, but the day-to-day support functions are still vintage 1960s. In fact, while MySQL and PostgreSQL can easily dump their guts to portable SQL text using the standard software packages, IBM's DB/2 requires a third-party solution.
 
Dave Tolls
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Having worked on even "old" Java code where new stuff has been bolted on top of dubious underlying code, I can imagine that the core of these old DBs has a big sign up along the lines of "here there be dragons".
 
Tim Holloway
Bartender
Posts: 19976
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Having worked on even "old" Java code where new stuff has been bolted on top of dubious underlying code, I can imagine that the core of these old DBs has a big sign up along the lines of "here there be dragons".



More like, "If you read this, you've already come too far". Most of us don't get to read that code, though. Even when the OS source was still public, DBMS's were object-code-only distributions.

IBM's Virtual Storage Access Method (VSAM) isn't a DBMS, but it's a highly-structured flexible data storage system and is in fact often used as the underpinning of actual database products. The logic manuals for VSAM were part of the standard client-distributed set and they were fun to read. Most IBM technical docs were stodgy and "businesslike", but VSAM's were more lighthearted. I've often felt that that's one reason VSAM was less problematic than some of their grimmer products.
 
Look ma! I'm selling my stuff!
Download Free Java APIs to Work with Office Files and PDF
htttp://www.e-iceblue.com/free-apis.html
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!