All,
Oracle 9i provides a "USING" clause option for inner joins, that
allows me to say:
SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
for our software make use of, but we also support SQL Server. There
is no USING option available, and
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
causes an ambiguous column error on KeyColumn.
Is there any equivalent to this Oracle functionality on SQL Server?
KingGreg 7 4793
>> "SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
causes an ambiguous column error on KeyColumn <<
And the USING clause is limited to equi-joins. But the real problem is
that good SQL programmers do not use "SELECT *" in production code. It
changes at run time and is too unclear and dangerous.
NATURAL JOIN and USING were two of the worst ideas we put into SQL-92.
I hope they get deprecated soon.
--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
On 14 May 2004 13:02:13 -0700, KingGreg wrote: All,
Oracle 9i provides a "USING" clause option for inner joins, that allows me to say:
SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable for our software make use of, but we also support SQL Server. There is no USING option available, and
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
causes an ambiguous column error on KeyColumn.
I can't reproduce this error:
create table TBL1 (KeyColumn int not null primary key)
create table TBL2 (KeyColumn int not null primary key)
insert TBL1 (KeyColumn)
values(1)
insert TBL1 (KeyColumn)
values(2)
insert TBL2 (KeyColumn)
values(1)
insert TBL2 (KeyColumn)
values(3)
SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
drop table TBL1
drop table TBL2
KeyColumn KeyColumn
----------- -----------
1 1
(1 row(s) affected)
Can you post the actual SQL that returns this error, as I assume there is
an error somewhere in the query.
Is there any equivalent to this Oracle functionality on SQL Server?
No, there isn't.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I understand that I was not clear because you have to be using derived
table. See below: create table TBL1 (KeyColumn int not null primary key) create table TBL2 (KeyColumn int not null primary key) insert TBL1 (KeyColumn) values(1) insert TBL1 (KeyColumn) values(2) insert TBL2 (KeyColumn) values(1) insert TBL2 (KeyColumn) values(3) SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn drop table TBL1 drop table TBL2
KeyColumn KeyColumn ----------- ----------- 1 1
(1 row(s) affected)
Try :
1 SELECT KeyColumn
2 FROM (
3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
4 ) TBL
Gives error : Column 'KeyColumn' specified multiple times for TBL
As you noted it does not fail when running line 3 by itself.
I guess I must be a bad SQL programmer, but this is nonetheless the
direction I am pursuing because of numerous other limitations in SQL
Server or Oracle that prevents using some other solution.
KingGreg
Can you be more specific?
I agree with Joe that USING and NATURAL JOIN are undesiravel features,
especially since their only purpose in life seems to be to add convenience.
Obviously you are of a different opinion. As a developer I (and quite
likely MS folks listening in) am curious to learn where you see the
value ad.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
KingGreg wrote: I understand that I was not clear because you have to be using derived table. See below: create table TBL1 (KeyColumn int not null primary key) create table TBL2 (KeyColumn int not null primary key) insert TBL1 (KeyColumn) values(1) insert TBL1 (KeyColumn) values(2) insert TBL2 (KeyColumn) values(1) insert TBL2 (KeyColumn) values(3) SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn drop table TBL1 drop table TBL2
KeyColumn KeyColumn ----------- ----------- 1 1
(1 row(s) affected)
Try :
1 SELECT KeyColumn 2 FROM ( 3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn 4 ) TBL
Gives error : Column 'KeyColumn' specified multiple times for TBL
As you noted it does not fail when running line 3 by itself.
I guess I must be a bad SQL programmer, but this is nonetheless the direction I am pursuing because of numerous other limitations in SQL Server or Oracle that prevents using some other solution.
KingGreg
If in Oracle ... I suspect what you are trying to do is:
SELECT KeyColumn
FROM (
SELECT *
FROM TBL1
WHERE TBL1.KeyColumn = TBL2.KeyColumn) ;
Using ISO standard syntax. If in 9i or above you could also use
ANSI standard syntax.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
As you have an INNER JOIN it doesn't matter which value of keycolumn you
reference as long as you specify an alias. It's best to avoid using SELECT *
in production code anyway (except in an EXISTS subquery). Try this:
SELECT keycolumn
FROM
(SELECT Tbl1.keycolumn
FROM Tbl1 JOIN Tbl2
ON Tbl1.keycolumn = Tbl2.keycolumn) TBL
--
David Portas
SQL Server MVP
--
Joe Celko wrote: "SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
causes an ambiguous column error on KeyColumn <<
And the USING clause is limited to equi-joins. But the real problem is that good SQL programmers do not use "SELECT *" in production code. It changes at run time and is too unclear and dangerous.
It's not dangerous if your client code accesses the return fields by
name, and not by number. It is, however, generally returning more
data than you need, so it's a waster of resources, and you still
shouldn't do it.
Bill This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Comcast Mail |
last post by:
|
by: Don Wash |
last post by:
Hi All!
I do not find any relevant newsgroups to post this question so I just posted
to this VB DotNet and General DotNet groups.
I'm trying to create a VB.NET application that will scan newsgroups.
What I like to know is if there are any different between a MS newsgroup
(microsoft.public.dotnet.general) and a UseNet newsgroup (comp.software)
apart from their topic? Because as a NNTP newsreader user, it is totally
|
by: sridevi |
last post by:
Hello
How to export data from ms-access database to excel worksheet using ASP.
mainly i need to export data to multiple worksheets. it is very urgent to
us.
i have a sample code which works only exporting to single worksheet. but i
need to export data to multiple worksheets.
it is very urgent to us. so please help me in code.
|
by: Rob R. Ainscough |
last post by:
MS Visual Studio Ad contained in VS Magazine.
Two developers in "hip" clothing diagramming out a huge flow chart on a
beach.
I could NOT stop laughing at the stupidity of the ad -- "Let your ideas run
free"....only to be washed up by the waves when the tide comes in -- this is
exactly so so so Microsoft. Re-invent the dev tools every 3-5 years in the
name of progress and hope developers don't notice -- good revenue generation
for MS,...
|
by: mehrdad |
last post by:
Hi,
I have a fully functional MS-Access database.
May I build such an "exe" file from a MS-Access
"mdb" file which one who has not MS-Access
installed on his/her system can take advantage of the
database? is it possible to have an standalone
MS-Access application with no need to use the MS-Access
environment itself ?
regds,
a. nasseh
| |
by: Daniel |
last post by:
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I...
|
by: Ronnie |
last post by:
I'm curious if anyone has any insights into what Microsoft has in
store for ACCESS in current or future releases? I'm currently working
on Access 2000 and haven't seen the newer versions. I'm curious if
Microsoft will keep VBA or move ACCESS over to VB.NET or C#?
Thanks.
|
by: Uwe Range |
last post by:
Hi to all!
A customer of mine told me some days ago that her IT-people told her
ACCESS would not be such a good idea for continuing with our project,
because Access will not be continued in the future. I haven't heard of
this. Does anybody know more about it?
The IT-People usually prefer Oracle. If they really want to go in this
direction, could our Access-application (if continued) be used as a
front end with an Oracle back end? Does...
|
by: Alex Martelli |
last post by:
So, I thought I'd tool up to let me build and test Python extensions on
Windows (as well as Mac and Linux) -- I'm trying out Parallels
Workstation beta on my new Macbook Pro (and so far it seems to work very
well), I bought and installed a Win2000 Pro on it (since according to
the grapevine it works better than XP in various kinds of virtual
machines, and almost all SW supports w2k anyway -- I also found out that
one exception is the...
|
by: PabsBath |
last post by:
Hello, help please.
I have been pulling my hair out for a few weeks now and been looking on the web for answers but not managed to find anything!!
I'm currently operating a small (2mb - approx 4500 records) MS Access database on my new laptop that has MS Vista. I'm also using MS Office (with MS Access) 2003. I'm the only user on the laptop.
The database was design and still is in MS Access 2000 version because it needs to be used on...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |