CODEWITHSUNDEEP
coldfusionqoq
Jan
Jan

Reputation: 85

Query of Query returning me full return instead of filtered ones

I have this query derived from original query, original query has 50 records and the qoq should give me 30 rows, but it is giving me full 50 rows,

what i am doing here

here is my Query of Query

<cfquery name="qryResults" dbtype="query">
    Select *
    From qryResults
    Where sDate> {d '2015-01-01'}
    OR (
    userID = 22
    AND isActive = 1
    )
    OR (
    userID = 8
    AND isActive = 0
    )
    OR (
    userID = 7
    AND isActive = 1
    ) 
</cfquery>

what i am doing wrong in here

Upvotes: 1

Views: 72

Answers (2)

Shawn
Shawn

Reputation: 4786

To add a little bit to the previous answer:

Check your filters are doing what you expect them to do. Working with OR can easily show you unexpected results. SQL reads the WHERE part of the query essentially sequentially (though the optimizer can change the order how it needs to), so your query above says to give you the rows where:

1) `sDate > '2015-01-01'`
OR
2) `userID = 22 AND isActive = 1`
OR
3) `userID = 8 AND isActive = 0`
OR
4) `userID = 7 AND isActive = 1`

So if sDate <= '2015-01-01' then it'll move on to checking userID and isActive. For me, it helps to explicitly block out OR conditions so that I can see what I'm trying to do a little easier`

WHERE sDate > '2015-01-01`
    AND (
        ( userID = 22 AND isActive = 1 )
        OR
        ( userID = 8 AND isActive = 0 )
        OR        
        ( userID = 7 AND isActive = 1 )
    )

That will make sDate a required condition of your query.

I'd also point out that when it comes to dates, you have to pay attention to your boundaries. Your query above will exclude January 1, 2015 at 12:00 AM (if sDate includes a time component, or the whole day if it doesn't). So you might have been looking for >=. Also, pay attention to the precision of your date type. For example, with SQL Server, a datetime is only precise to the thousandths of a second, so if an event occurs on Dec 31, 2015 at 23:59:59.998, it'll appear to SQL as if it happened on Jan 1, 2015 at 00:00:00.000. Especially with dates, watch the edge cases.

And if you wanted to shorten your conditions a little, you could also use:

WHERE sDate > '2015-01-01'
    AND (
        ( isActive = 0 AND userID = 8 )
        OR
        ( isActive = 1 AND userID IN (22,7) )
    )

Saying userID IN (22,7) is shorthand for userID=22 OR userID=7.

Upvotes: 0

user12031119
user12031119

Reputation: 1228

This question needs more detail about the result set retrieved and what's contained in the original 50 row result set. Without knowing those details, it's hard to determine why no rows are being filtered out. However, considering you're using the OR for all of your operator connecting conditions, then what happens is that only one condition needs to satisfy returning all 50 rows. I'm going to take a stab at this and presume the culprit is the date comparison since the rest are more specific about requiring the correct combination of userID and isActive. My best "guess" is that you likely need to change the first OR in your where clause into an AND with the final result looking like this.

<cfquery name="qryResults" dbtype="query">
    Select *
    From qryResults
    Where sDate> {d '2015-01-01'}
    AND (
    userID = 22
    AND isActive = 1
    )
    OR (
    userID = 8
    AND isActive = 0
    )
    OR (
    userID = 7
    AND isActive = 1
    )
</cfquery>

Upvotes: 3

Related Questions