Help needed!!!

Question

Help needed!!!

rea_wag

Old Hand

Points: 305
More actions
June 9, 2010 at 9:31 am

#140774

i wrote this query:
SELECT PlayerID,Username
FROM dbo.Players
WHERE PlayMode='p' and CasinoID=5 and (RegistrationDate between 2010-01-01 and 2010-03-31)
it shows no records in the result but i know there supposed to be thousands of records, what did i do wrong?
also, i wrote the query below
SELECT CountryName
FROM (SELECT PlayersName, count(Players.CountryID)as Deposits#
FROM Players,Countries
WHERE CasinoID=12 and DepositsCount<>0
GROUP BY Players.CountryID)
WHERE Deposits#=max(Deposits#)
and got the error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'WHERE'.
when i excute the inner query alone, everything is fine, but when i try to excute it like shown above i get the error, why?
thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

Without seeing any sample data it would be pretty hard to tell why the first query is not returning any results. I could only offer that you might want to check case. For example is the field is case sensative then a lower case p would return nothing if the field is stored with an upper case P.

The second query is a syntaxe error becuase you can not have two seperate where clauses. you would need to combine the second where clasue with the first.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

WayneS SSC Guru Points: 95460 More actions · Answer 2

Dan.Humphries (6/9/2010)
Without seeing any sample data it would be pretty hard to tell why the first query is not returning any results. I could only offer that you might want to check case. For example is the field is case sensative then a lower case p would return nothing if the field is stored with an upper case P.
The second query is a syntaxe error becuase you can not have two seperate where clauses. you would need to combine the second where clasue with the first.

For the second query, the problem is that you have a subquery being used as a table, but it doesn't have a table alias.

change it to:

SELECT CountryName

FROM (SELECT PlayersName, count(Players.CountryID)as Deposits#

FROM Players,Countries

WHERE CasinoID=12 and DepositsCount<>0

GROUP BY Players.CountryID) TableAlias

WHERE Deposits#=max(Deposits#)

I think the next problem that you're going to have is with "WHERE Deposits#=max(Deposits#)" - I think you will need to predetermine the max(Deposits#) and put it into a variable.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

yup I did not even see the subquery. my eyes when straight to the where clause.

Sorry about that.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

rea_wag
but i know there supposed to be thousands of records

Might I suggest that you trouble shoot your query... first run this code:

SELECT PlayerID,Username

FROM dbo.Players

WHERE PlayMode='p'

Do you get rows returned? If so then run:

SELECT PlayerID,Username

FROM dbo.Players

WHERE CasinoID=5

Do you get rows returned? If so then run:

SELECT PlayerID,Username

FROM dbo.Players

WHERE PlayMode='p' and CasinoID=5

Do you get rows returned? If so then run:

SELECT PlayerID,Username

FROM dbo.Players

WHERE RegistrationDate between 2010-01-01 and 2010-03-31

Again do you get rows returned?

If so then combine your PlayMode = 'p' with the RegistrationDate clause, and so on as so forth ...

If this does not identify the problem please repost with

the table definition, some sample data as outlined in the first link in my

signature block and some one will further assist you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Along with the above solutions,

Please check the format of the field RegistrationDate is same as the hard coded value? else please do proper casting.

And are you able to use that Max() in the WHERE clause ? WHERE wont support aggregation right ? you might have to use HAVING

Thanks & Regards,
MC

rea_wag Old Hand Points: 305 More actions · Answer 6

Dan, Wayne and Ron

thanks for the help, i learned a few things 🙂

have a nice weekend..

myheadhurts Ten Centuries Points: 1262 More actions · Answer 7

try enclosing the dates in quotes:

WHERE RegistrationDate between '2010-01-01' and '2010-03-31'

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

rea_wag Old Hand Points: 305 More actions · Answer 8

the problam was that the date field was of this format

####-##-## 00:00

the gap was records that were updated during the last day within that range, had to up it one day..

thanks,

Rea.