June 9, 2010 at 9:31 am
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!
June 9, 2010 at 9:47 am
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.
June 9, 2010 at 9:56 am
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
June 9, 2010 at 10:02 am
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.
June 9, 2010 at 10:10 am
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.
June 9, 2010 at 11:01 am
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
June 10, 2010 at 8:49 am
Dan, Wayne and Ron
thanks for the help, i learned a few things 🙂
have a nice weekend..
June 10, 2010 at 11:47 am
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
June 11, 2010 at 10:22 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply