sql server programming: Stumped by results -- sql server programming

Re: Stumped by results

Dan Guzman
1/11/2004 6:13:50 PM

sql server programming: The jpg is pretty but it would be more beneficial to post the query here too
so that we don't have to (mis)type it.

select caldate, IO_ID. IO_DateUntil from calandar inner join Inout ON
--Note that you have an OR here so the resultant row probably matched this
line
--You probably need to rearrange your parenthesis
((caldate >= IO_Date - 1) and (IO_Afternoon = 0)) or
((caldate >= IO_Date) and (IO_Afternoon = 1))
and
(((caldate > IO_DateUntil) and (IO_AfternoonUntil = 1) and (IO_ACIDUntil <>
1)) or ((caldate < IO_DateUntil - 1)))
and (IO_ACID = 1)
where IO_SNID = 7
order by caldate

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Re: Stumped by results

Steve Kass
1/11/2004 7:16:13 PM

Stijn,

Without working it through, I suspect it's because your query is

select ...
where A or B and C and D

and you are expecting this to mean
(A or B) and (C and D)
In fact, it means
A or (B and C and D)

When mixing AND with OR, it's safest never to leave out parentheses.

SK

[quoted text, click to view]

Re: Stumped by results

Steve Kass
1/11/2004 7:27:44 PM

[quoted text, click to view]

It could have been worse - sometimes I stare and stare and it needs 1 or
3. ;)

SK

[quoted text, click to view]

Stumped by results

Stijn Verrept
1/11/2004 11:46:32 PM

I have a very simple query but it the results just don't seem to match.
It seems to go wrong while doing datetime comparison.

caldate and IO_DateUntil are smalldatetime's.

I made a screenshot and highlighted the strange result and also the
line of the query which should be FALSE and thus not give this result.

http://www.entrysoft.com/query.jpg

What am I missing here?

--
Kind regards,

Re: Stumped by results

Stijn Verrept
1/12/2004 12:20:48 AM

[quoted text, click to view]

Aaargh, I cannot believe this. I have been staring myself blind at
this and it needed 2 extra parenthesis. Thanks a lot!!!

--
Kind regards,

Re: Stumped by results

Stijn Verrept
1/12/2004 12:21:52 AM

[quoted text, click to view]

Very true Steve! Thanks :) Works now.

--
Kind regards,

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.