[Bug?] Where clause in SQLSERVER7 doesn't return results where it really should

Hi group,
I found the following behaviour really surprising:
[SQLServer 7]
consider following query:
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08') AND (logdate <= '2006-02-08'))
ORDER BY logdate;
This query returns 0 results.
If I remove one part of the where-clause, it works just fine:
Like this
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate <= '2006-02-08'))
ORDER BY logdate;
or
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08'))
ORDER BY logdate;
(These queries only differ in the >= or <= in the whereclause.)
Both queries return some rows!
(As expected by me because I know the table does have rows with logdate
'2006-02-08')
It seems that SQLServer is unable to find ANY records if I use both
whereclauses at the same time.
The reason this weird situation is needed is because I need to assemble the
query dynamically based on some form-info posted by a user.
Can anybody explain this?
Is this a known bug?
Or is maybe something going on that has to do with my (limmited)
understanding of SQLServer/SQL ?
I can of course code around this issue by checking in my script if the dates
are the same and in that case make only whereclause (logdate='2006-02-08'),
but I am curious what is going on.
Thanks for your time!
Regards,
Erwin Moller
Erwin Moller wrote:
Hi, I made the situation even simpler, and found the reason for my problem.
I removed the join, and left only 1 where-clause.
So the following query:
SELECT logid, logdate, vragensetid, dooruserid, incidentid, action
FROM tbllog
WHERE(logdate <= '2006-02-08') ORDER BY logdate;
Resulted in NO results.
Where the database contains records on this date.
Reason is: I am a simpleminded idiot.
I do not tell the database how many hours/minutes/seconds....
So while there are records on 2006-02-08, they are ALL after midnight.
:-)
Sorry for the noise!
Regards,
Erwin Moller

[Bug?] Where clause in SQLSERVER7 doesn't return results where it really should

Hi group,
I found the following behaviour really surprising:
[SQLServer 7]
consider following query:
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08') AND (logdate <= '2006-02-08'))
ORDER BY logdate;
--
This query returns 0 results.
If I remove one part of the where-clause, it works just fine:
Like this
--
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate <= '2006-02-08'))
ORDER BY logdate;
--
or
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08'))
ORDER BY logdate;
--
(These queries only differ in the >= or <= in the whereclause.)
Both queries return some rows!
(As expected by me because I know the table does have rows with logdate
'2006-02-08')
It seems that SQLServer is unable to find ANY records if I use both
whereclauses at the same time.
The reason this weird situation is needed is because I need to assemble the
query dynamically based on some form-info posted by a user.
Can anybody explain this?
Is this a known bug?
Or is maybe something going on that has to do with my (limmited)
understanding of SQLServer/SQL '
I can of course code around this issue by checking in my script if the dates
are the same and in that case make only whereclause (logdate='2006-02-08'),
but I am curious what is going on.
Thanks for your time!
Regards,
Erwin MollerErwin Moller wrote:
Hi, I made the situation even simpler, and found the reason for my problem.
I removed the join, and left only 1 where-clause.
So the following query:
SELECT logid, logdate, vragensetid, dooruserid, incidentid, action
FROM tbllog
WHERE(logdate <= '2006-02-08') ORDER BY logdate;
--
Resulted in NO results.
Where the database contains records on this date.
Reason is: I am a simpleminded idiot.
I do not tell the database how many hours/minutes/seconds....
So while there are records on 2006-02-08, they are ALL after midnight.
:-)
Sorry for the noise!
Regards,
Erwin Moller

@variable in SELECT ... WHERE ... IN clause

Is there a way to create a query that can be like:

DECLARE @.group_id_list varchar(100)

SET @.group_id_list='100,101,150'

SELECT * FROM abc WHERE abc_id IN (@.group_id_list)

I get the error "Syntax error converting the varchar value '100,101,150' to a column of data type int.

Do I need to resort to a dynamic SQL statement?

You do.|||Not if you don't want to. In many cases I prefer to use a udf that I've created that takes a comma-delimited varchar and returns a table. Then you can either join on the table to limit your results, or you can use IN (SELECT id FROM Split(@.param,DEFAULT) alias1) in your where clause. The second param into my Split UDF is what the separator is (default is comma).|||

Thank you. I actually ended up doing something similar (never thought of using a UDF):

DECLARE @.groups TABLE (group_id int)

I ran a while loop inserting the values into the @.groups table, then used:

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @.groups)

I don't know if it's efficient, but my dynamic SQL statement was going to exceed 14k in length!