Case clause in query doesnt consider condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case clause in query doesnt consider condition

Author

Topic

MS4711
Starting Member

4 Posts

Posted - 2011-07-27 : 02:34:12

Hi Everyone,
Please consider the query below -

quote:

SELECT TD.T_ID,
CASE WHEN TI.STD_QTY > 1 THEN
((SUM((TD.ORG_QTY % TI.STD_QTY))) / TI.STD_QTY)
ELSE
1
END
as TOTAL_DEMAND
FROM
TMP_DTL TD, TMP_ITEM TI
WHERE TI.T_ID = TD.T_ID
GROUP BY TD.T_ID, TI.STD_QTY

There are few items in TMP_ITEM with TI.STD_QTY = 0 /1
which when encountered into the above query execution, I wanted to put some scalar value and hence I included CASE clause

quote:

CASE WHEN TI.STD_QTY > 1 THEN
((SUM((TD.ORG_QTY % TI.STD_QTY))) / TI.STD_QTY)
ELSE
1
END

But whenever I try to execute it, I get following ERROR -

quote:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

This I am not able to understand, when I have already filtered records with value 0, it should not enter into the line

quote:
((SUM((TD.ORG_QTY % TI.STD_QTY))) / TI.STD_QTY)

I am struggling to eliminate this error, anyone if can suggest or provide some idea on this .. would be really appreciable ..

Thanks in Advance
Regards,
Vivek

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-27 : 03:12:59

Hey MS,

You said you filtered for values greater than 0, but actually, you did not. You set your case expression to handle values greater than 0 (greater than 1 actually), but the query will still pull 0 and/or null values if applicable. To filter the "0"s out, you'd have to say either something like:

Where T1.STD_QTY>0 or a having clause if you're filtering on an aggregate like this:

Having sum(T1.STD_QTY)>0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-27 : 03:17:42

A workaround:
CASE WHEN TI.STD_QTY > 1 THEN
((SUM((TD.ORG_QTY % TI.STD_QTY))) / nullif(TI.STD_QTY,0))
ELSE
1
END

No, you're never too old to Yak'n'Roll if you're too young to die.

MS4711
Starting Member

4 Posts

Posted - 2011-07-27 : 06:36:20

@flamblaster

I know am not filtering records with SQ as 0/1 (neither I wanted it to be) infact I would assume some scalar value whenever particular record having 0/1 as SQ value, since it doesn't make any sense to divide by 0 and MOD with 1 result in 0
But I definitely want that scalar value in such scenario

@webfred

I did try the WR, but i believe for values with 0/1 it still goes to the line -

quote:

((SUM((TD.ORG_QTY % TI.STD_QTY))) / TI.STD_QTY)

and just discard that record with NULL value and I still miss my Scalar Value which I wanted instead ...

Question here is why is the CASE clause is not able distinguish and put the Scalar Value for the scenario ??

Sorry for the late reply though ..

Anyways thanks for the some inputs .. more ideas are most invited...

Regards,
Vivek

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-27 : 06:51:31

Please make a test using the ELSE part.
In the ELSE part return a value like 10000 or so then you can see in the query result if there are values coming from the ELSE part.
I believe the error is a result from the parse and should not hit the result at query runtime.

Don't know how to explain my thoughts better with my limited english...

No, you're never too old to Yak'n'Roll if you're too young to die.

MS4711
Starting Member

4 Posts

Posted - 2011-07-27 : 07:13:20

Hi webfred,

You explained quite decently ... :) thanks
anyways I have already tried as below -

CASE WHEN TI.STD_QTY > 1 THEN
99999
ELSE
88888
END
as TOTAL_DEMAND ...

and it fetches me result 55 recs .. in total

with Condition ** TI.STD_QTY != 0
Fetches 35 recs

that is .. there are 20 recs with value = 0

Now when I run work around as per your kind suggession in original query
It only returns with 35 recs ??? which should be 55 infact

Hence it is discarding the records with TI.STD_QTY = 0
Infact it should write the scalar value for these :(

I think that explains ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-27 : 07:59:58

That's not really possible.
A CASE expression in the SELECT list isn't able to suppress rows in a resultset.
Maybe your are not showing the real query to us?

No, you're never too old to Yak'n'Roll if you're too young to die.

MS4711
Starting Member

4 Posts

Posted - 2011-07-27 : 08:08:21

I am really sorry webfred I cannot post the real query, but the query i posted is kind of mirror image only few status conditions added and table names changed nothing else.

Well even I am surprised why CASE clause is not working as expected. Will let you know once if I find the reason... but there is nothing to hide... after all I got registered to get the answer and not to fool the people.

Sorry for any inconvenience ...

Regards

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-27 : 09:13:42

MS, my example was to illustrate that you are not filtering out the 0's as you indicated in the original problem. Like webfred said, a case expression in the select won't filter anything out; it will just display results in a specific format based on the conditions you set up.

I would think that a nullif or coalesce in the case expression would return the results you're looking for as per webfred's earlier example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources