Author |
Topic |
Markuu
Starting Member
9 Posts |
Posted - 2007-11-09 : 19:38:26
|
I made a page for a baseball league a couple of years ago using PHP and MySQL, and decided this winter to switch it to ASP.Net and SQL Server Express that comes with VWD 2005 Express Edition.Anyways, I ran into a problem when trying to convert the query I made with MySQL into SQL Server. My old code was something like this:SELECT homeScore, visitorScore, ( homeScore > visitorScore ) AS homeWins, ( visitorScore > homeScore ) AS visitorWins FROM ScheduleThis worked fine in MySQL, and returned the home team's score, visiting team's score, and then returned a 1 in homeWins if the home team won. Now, when I try running this query in SQL Server Express, I get an error saying "Invalid column name 'visitorScore > homeScore' ". Can anyone please help me with what I should be doing differently so this works in SQL Server Express?! Thanks in advance!!! |
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-09 : 19:51:03
|
[code]SELECT homeScore, visitorScore, CASE WHEN homeScore > visitorScore THEN 1 ELSE 0 END AS visitorWins FROM Schedule[/code]Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
Markuu
Starting Member
9 Posts |
Posted - 2007-11-09 : 20:31:14
|
wow, thanks! Didn't know that new syntax!!! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-09 : 23:09:52
|
T-SQL has different syntax from MySql. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-12 : 02:04:23
|
Yes they both have different syntaxesThis will work in MySQL and not in SQL Serverselect * from( select 10 as number union all select -234) as twhere 1 MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 03:34:18
|
What about ties? E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-12 : 03:47:26
|
quote: Originally posted by Peso What about ties? E 12°55'05.25"N 56°04'39.16"
Ties?The above query would return all the rows when run in MySQLMadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 03:51:49
|
With ties I meant when the game was a tie. HomeScore = 10 and VisitorScore = 10... E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-12 : 04:38:11
|
quote: Originally posted by Peso With ties I meant when the game was a tie. HomeScore = 10 and VisitorScore = 10... E 12°55'05.25"N 56°04'39.16"
Condition failed so it would return 0MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 05:09:26
|
Yes. And how do OP think to handle that case?In baseball there can never be a tie? E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-12 : 05:17:25
|
quote: Originally posted by Peso Yes. And how do OP think to handle that case?In baseball there can never be a tie? E 12°55'05.25"N 56°04'39.16"
I dont know about basebasll but in Cricket there is tie MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 06:21:18
|
From Wikipediaquote: In Major League Baseball, games end with tie scores only because conditions have made it impossible to continue play. A tie game does not count as an official game in the standings unless it is finished later or replayed; however, individual player statistics from tie games are counted. Inclement weather may also shorten games, but at least five innings must be played for the game to be considered official; four-and-a-half innings are enough if the home team is ahead. Previously, curfews and the absence of adequate lighting caused more ties and shortened games. Also, with more modern playing surfaces better able to handle light rains, the process for calling or shortening a game due to weather has changed; it is more common than in the past to delay a game as much as 2 hours before a cancellation; also, a delay usually does not occur anymore until the rain is moderate-heavy and/or there is standing water on some part of the playing field.In Japan's Nippon Professional Baseball, if the score remains tied after nine innings, up to three extra innings may be played before the game is called a tie. Some youth or amateur leagues will end a game early if one team is ahead by ten or more runs, a practice known as the "mercy rule" or "slaughter rule". Rarely, a game can also be won or lost by forfeit.
E 12°55'05.25"N 56°04'39.16" |
|
|
arorarahul.0688
Posting Yak Master
125 Posts |
Posted - 2007-11-12 : 07:07:02
|
quote: Originally posted by dinakar
SELECT homeScore, visitorScore, CASE WHEN homeScore > visitorScore THEN 1 ELSE 0 END AS visitorWins FROM Schedule Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
SELECT homeScore, visitorScore,CASE WHEN homeScore > visitorScore THEN 1 ELSE 0 END AS homeWins ,case WHEN homeScore < visitorScore THEN 1 ELSE 0 END AS VisitorsWinsFROM ScheduleRahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 08:05:46
|
Same thing as Dinakar wrote 3 days ago... E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-12 : 08:18:03
|
quote: Originally posted by Peso Same thing as Dinakar wrote 3 days ago... E 12°55'05.25"N 56°04'39.16"
I think he corrected alias name MadhivananFailing to plan is Planning to fail |
|
|
arorarahul.0688
Posting Yak Master
125 Posts |
Posted - 2007-11-13 : 06:55:21
|
quote: Originally posted by arorarahul.0688
quote: Originally posted by dinakar
SELECT homeScore, visitorScore, CASE WHEN homeScore > visitorScore THEN 1 ELSE 0 END AS visitorWins here should be homeWins FROM Schedule Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
SELECT homeScore, visitorScore,CASE WHEN homeScore > visitorScore THEN 1 ELSE 0 END AS homeWins ,case WHEN homeScore < visitorScore THEN 1 ELSE 0 END AS VisitorsWinsFROM ScheduleRahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE
thanks madhivanan Rahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-13 : 07:25:08
|
quote: Originally posted by madhivanan I think he corrected alias name
I think he wants a software bundle package. E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-13 : 08:26:44
|
quote: Originally posted by Peso
quote: Originally posted by madhivanan I think he corrected alias name
I think he wants a software bundle package. E 12°55'05.25"N 56°04'39.16"
I think what you think is what he thinks MadhivananFailing to plan is Planning to fail |
|
|
|