SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 problem: MySQL query doesn't work with SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Markuu
Starting Member

9 Posts

Posted - 11/09/2007 :  19:38:26  Show Profile  Visit Markuu's Homepage  Reply with Quote
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 Schedule

This 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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 11/09/2007 :  19:51:03  Show Profile  Visit dinakar's Homepage  Reply with Quote

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/
Go to Top of Page

Markuu
Starting Member

9 Posts

Posted - 11/09/2007 :  20:31:14  Show Profile  Visit Markuu's Homepage  Reply with Quote
wow, thanks! Didn't know that new syntax!!!
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 11/09/2007 :  23:09:52  Show Profile  Reply with Quote
T-SQL has different syntax from MySql.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2007 :  02:04:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Yes they both have different syntaxes

This will work in MySQL and not in SQL Server


select * from
(
	select 10 as number union all
	select -234
) as t
where 1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/12/2007 :  03:34:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What about ties?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2007 :  03:47:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 MySQL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/12/2007 :  03:51:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2007 :  04:38:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 0

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/12/2007 :  05:09:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2007 :  05:17:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/12/2007 :  06:21:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
From Wikipedia
quote:
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"
Go to Top of Page

arorarahul.0688
Posting Yak Master

India
125 Posts

Posted - 11/12/2007 :  07:07:02  Show Profile  Reply with Quote
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 VisitorsWins
FROM Schedule

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/12/2007 :  08:05:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Same thing as Dinakar wrote 3 days ago...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/12/2007 :  08:18:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

arorarahul.0688
Posting Yak Master

India
125 Posts

Posted - 11/13/2007 :  06:55:21  Show Profile  Reply with Quote
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 VisitorsWins
FROM Schedule

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE



thanks madhivanan

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/13/2007 :  07:25:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/13/2007 :  08:26:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000