473,324 Members | 2,567 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,324 software developers and data experts.

MS-SQL Server equivalent to Oracle 9i?

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
Jul 20 '05 #1
7 4776
>> "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!
Jul 20 '05 #2
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)
Jul 20 '05 #3
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
Jul 20 '05 #4
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
Jul 20 '05 #5
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.washington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #6
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
--
Jul 20 '05 #7
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
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
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...
3
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...
18
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...
3
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...
2
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...
7
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...
33
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...
36
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...
4
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...
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.