The quiry you posted in Soln to Challenge #2 is syntatically wrong

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

SQL Server 2000 Forums

Transact-SQL (2000)

The quiry you posted in Soln to Challenge #2 is syntatically wrong

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-06 : 09:42:56

Oleg writes "select g1.Player,
g1.hcap,
Rank = (select count(*) + 1
from SQLTeam_Golfers g2
where g1.hcap < g2.hcap)
from SQLTeam_Golfers g1
order by Rank

1) Rank is undefined column

2) I don't understand how can
Rank = (select count(*) + 1
from SQLTeam_Golfers g2
where g1.hcap < g2.hcap)
do the work?

Would you please translate in programming terms?

P.S I copied directly this after I create table."

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-05-06 : 10:32:23

quote:

The quiry you posted in Soln to Challenge #2 is syntatically wrong

How ?

You didn't even run it so how can you say that.

Damian

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-05-06 : 11:05:35

SQL server allows the syntax


select rank = a + b from t order by rank

meaning that rank can be used as a alias for the expression a+b. This behaviour is not in accordance with the SQL standard where


  rank = a + b

shall be treated as a boolean expression. If rank and a + b have comparable types the statement would be semantically correct and would return records with values either being TRUE or FALSE.

So the query


select g1.Player,
g1.hcap,
Rank = (select count(*) + 1
from SQLTeam_Golfers g2
where g1.hcap < g2.hcap)
from SQLTeam_Golfers g1
order by Rank

is valid SQL in a DBMS that supports boolean. When used in DBMS (other than SQL server and Sybase) which does not suport boolean, the query would certainly result in a syntax error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources