Report ignores WHERE clause in SPROC

Question

Report ignores WHERE clause in SPROC

thottle

Old Hand

Points: 353
More actions
November 10, 2014 at 8:36 am

#300932

I've got a strange one in SQL Svr 2008R2.
I wrote a report that pulls datasets from two different SPROCs.
Dataset 1 has the following code (sanitized to protect the guilty, of course):
SELECT f.batchDate, f.orderNum, b.productNum, b.prodDescription, f.orderQty
FROM foo f
INNER JOIN bar b ON f.ProductID = b.ProductID
WHERE f.shipDate BETWEEN (GetDate() - 7) AND GetDate()
AND b.prodDescription NOT IN (widgetA, widgetB)
Dataset 2 is the opposite.
SELECT f.batchDate, f.orderNum, b.productNum, b.prodDescription, f.orderQty
FROM foo f
INNER JOIN bar b ON f.ProductID = b.ProductID
WHERE f.shipDate BETWEEN (GetDate() - 7) AND GetDate()
AND b.prodDescription IN (widgetA, widgetB)
In BIDS, the report filters exactly as you would expect these queries to respond. (Also tested the resulting SPROCs in Mgt. Studio.)
When I deploy them to production, the second dataset still works fine. The first one for some reason ignores "AND b.prodDescription NOT IN (widgetA, widgetB)". However, it does process the date range normally.
Any clues as to why the published version would ignore part of one SPROC and not the other?
--- Remember, if you don't document your work, Apollo 13 doesn't come home.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

thottle (11/10/2014)
I've got a strange one in SQL Svr 2008R2.
I wrote a report that pulls datasets from two different SPROCs.
Dataset 1 has the following code (sanitized to protect the guilty, of course):
SELECT f.batchDate, f.orderNum, b.productNum, b.prodDescription, f.orderQty
FROM foo f
INNER JOIN bar b ON f.ProductID = b.ProductID
WHERE f.shipDate BETWEEN (GetDate() - 7) AND GetDate()
AND b.prodDescription NOT IN (widgetA, widgetB)
Dataset 2 is the opposite.
SELECT f.batchDate, f.orderNum, b.productNum, b.prodDescription, f.orderQty
FROM foo f
INNER JOIN bar b ON f.ProductID = b.ProductID
WHERE f.shipDate BETWEEN (GetDate() - 7) AND GetDate()
AND b.prodDescription IN (widgetA, widgetB)
In BIDS, the report filters exactly as you would expect these queries to respond. (Also tested the resulting SPROCs in Mgt. Studio.)
When I deploy them to production, the second dataset still works fine. The first one for some reason ignores "AND b.prodDescription NOT IN (widgetA, widgetB)". However, it does process the date range normally.
Any clues as to why the published version would ignore part of one SPROC and not the other?

Are you absolutely sure that something other than WidgetA and WidgetB have data for the last 7 days?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thottle Old Hand Points: 353 More actions · Answer 2

Are you absolutely sure that something other than WidgetA and WidgetB have data for the last 7 days?

Yes. If it doesn't, dataset 2 tossed up a blank report, which is fine.

It's dataset one that's supposed to filter out widgetA and widgetB. So I'm getting more data than I'm supposed to in the first one, not less.

And again, doesn't do it in BIDS, just in production.

Something cached maybe?

--- Remember, if you don't document your work, Apollo 13 doesn't come home.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

thottle (11/10/2014)
Are you absolutely sure that something other than WidgetA and WidgetB have data for the last 7 days?
Yes. If it doesn't, dataset 2 tossed up a blank report, which is fine.
It's dataset one that's supposed to filter out widgetA and widgetB. So I'm getting more data than I'm supposed to in the first one, not less.
And again, doesn't do it in BIDS, just in production.
Something cached maybe?

That's what I was asking... about dataset 1...

Sounds like you may have come up with a bug (or I haven't yet had enough coffee) but I can't see your data to verify. I have seen this type of behavior as a bug way back in SQL Server 2000 SP2 on an Update but haven't seen the likes of such a thing since they fixed it.

I do know that simple reversal of logic in the relationships isn't always the opposite query but that doesn't look to be the part here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thottle Old Hand Points: 353 More actions · Answer 4

OK. I'll see if I can move the criteria up into the join (which is pretty ugly, but it works, and with a not-entirely-painful execution plan) and see if that makes a difference.

Unfortunately, I don't know the exact specs on our SSRS hosting, or I could follow that path of reasoning a little closer.

Inglorious results to follow this afternoon.

--- Remember, if you don't document your work, Apollo 13 doesn't come home.

thottle Old Hand Points: 353 More actions · Answer 5

Missed the obvious solution.

Changed...

WHERE f.shipDate BETWEEN (GetDate() - 7) AND GetDate()

AND b.prodNumber NOT IN (widgetA, widgetB)

to...

WHERE f.shipDate BETWEEN (GetDate() - 7) AND GetDate()

AND b.prodDescription NOT LIKE (%'widget'%)

Doh!

--- Remember, if you don't document your work, Apollo 13 doesn't come home.