How to select a column to compare two values in it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to select a column to compare two values in it

Author

Topic

DBAstudent123
Starting Member

5 Posts

Posted - 2015-04-24 : 00:19:42

How to select a column to compare two values in it with AND

Select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
  from employee a, driver b, trip c
  where a.e#=b.e#
  and b.l#=c.l#
  and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;

error no rows selected
I want c.reg# for the two values inclusive

Kristen
Test

22859 Posts

Posted - 2015-04-24 : 03:53:31

This is an MS SQL forum, you are unlikely to find people here who will be able to answer Oracle questions and you would be better off posting on an Oracle forum

Which is what you were told in your previous post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=200692

DBAstudent123
Starting Member

5 Posts

Posted - 2015-04-24 : 04:46:09

Hi, sorry. I know but it is quite similar in the semantics...I just wish to know the semantics of AND in SQL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 09:23:05

For starters, your where clause contains:


and (c.reg#='PKR3344'AND c.reg#='WWF1155')

which is impossible, of course. No row in your table can have two different values for the same column

Kristen
Test

22859 Posts

Posted - 2015-04-24 : 10:28:57

quote:
Originally posted by DBAstudent123

Hi, sorry. I know but it is quite similar in the semantics...I just wish to know the semantics of AND in SQL

Sure, but "quite similar" won't necessarily work on Oracle.

You can't have column names ending in "#" on MS SQL (unless you escape them), so I can't type this into my MS SQL test database to try it ... or I can escape the column names and then the code I give you definitely won't work on Oracle.

Or maybe the trailing "#" has some special significance on Oracle ...

I know that to_char() is Oracle syntax, but it definitely doesn't work under MS SQL ... so I can't include that in any test that I do.

You are not using JOIN syntax, but old fashioned joins which included all the comparison operators in the WHERE clause. That has been deprecated in MS SQL for several versions - I don't think it is even supported any more. I have no idea if it is supported in Oracle still, so I can't advvise you if you MUST change or only if you SHOULD change - if it is still supported, and the JOIN syntax is also support (you see, I have no idea whether it is supported in Oracle or not ...) then my view would be that you SHOULD be using the new JOIN syntax; but someone skilled in Oracle might well tell you differently.

If you want an OUTER JOIN using the old WHERE clause style its

WHERE COl1 *= COl2

but its so long since I used it that I cannot remember which side the "*" goes on.

So, in short, you are wasting your time and our time and IMHO you would get a much better and more useful answer on an Oracle forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources