where clause error

Question

where clause error

hoseam

SSCertifiable

Points: 5533
More actions
September 27, 2012 at 7:13 am

#262219

Hi
I have
SELECT
[PolicyId]= MB.POLICY_ID
,[SwitchValue]= ISNULL(SUM(MB.TOTAL_AMOUNT), 0.00)
,[RequestedAmount]= ISNULL(SUM(BF.FEE_AMOUNT), 0.00)
,[AmountPaid]= ISNULL(SUM(BF.GLOBAL_FEE_TAKEN), 0.00)
FROM PR_MONEY_BFN MB
LEFT JOIN PR_BFN_FEES BF WITH (NOLOCK)
ON MB.POLICY_ID = BF.POLICY_ID
WHERE
-- ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00) < ISNULL(BF.FEE_AMOUNT, 0.00)
--AND BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT
I don't get any data when I try to use: "BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT"
but when I use: "ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00) < ISNULL(BF.FEE_AMOUNT, 0.00)" I get data back but with zero values for [AmountPaid]

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

What is the value in the two columns in the where clause, I am guessing that one or both are NULL due to you wrapping them in ISNULL in the SELECT and therefore you are running into the UNKNOWN clause.

The where predicate needs to return TRUE or FALSE and as you are comparing NULLS you get UNKNOWN as it might be TRUE it might be FALSE so SQL leaves them out.

But when you put the ISNULL clause around them as you are setting the values to a actual value SQL will always return TRUE or FALSE not UNKNOWN

hoseam SSCertifiable Points: 5533 More actions · Answer 2

[RequestedAmount] [AmountPaid]

32700 10200

17000 0

150 0

2425 1425

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

So 0 for a Ammount paid, so is that 0 due to the ISNULL or is that 0 due to the sum of the values?

hoseam SSCertifiable Points: 5533 More actions · Answer 4

I changed ny code to

,[SwitchValue]= SUM(MB.TOTAL_AMOUNT)

,[RequestedAmount]= SUM(BF.FEE_AMOUNT)

,[AmountPaid]= SUM(BF.GLOBAL_FEE_TAKEN)

to I get the NULL values where I was getting Zero values, so it was due to ISNULL

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Well there you go then, you where running into the UNKNOWN section of the predicate and in turn it was filtering them out as if a value is NULL SQL cannot say if NULL < 100 = TRUE or FALSE, therefore SQL removes it from the data set.

Adding the ISNULL to the where clause made it say 0.00 < 100 = TRUE so the row is returned.

hoseam SSCertifiable Points: 5533 More actions · Answer 6

So then, I have a situation where I have 10200 < 32700, why don't I get both value?

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Your where clause is not SUMming the data, so while 10200 < 32700 = TRUE, that is not what is actually being checked in the where.

hoseam SSCertifiable Points: 5533 More actions · Answer 8

How do I then filter with values where [AmountPaid] is less than [RequestedAmount]

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

WHERE

ISNULL(SUM(BF.GLOBAL_FEE_TAKEN), 0.00) < ISNULL(SUM(BF.FEE_AMOUNT), 0.00)

hoseam SSCertifiable Points: 5533 More actions · Answer 10

hoseam

SSCertifiable

Points: 5533

September 27, 2012 at 8:05 am

#1542915

Thank you.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

anthony.green (9/27/2012)
WHERE
ISNULL(SUM(BF.GLOBAL_FEE_TAKEN), 0.00) < ISNULL(SUM(BF.FEE_AMOUNT), 0.00)

Shouldn't that be on the HAVING clause (after the GROUP BY)?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

You can't put a SUM into a where clause.

HAVING ISNULL(SUM(BF.GLOBAL_FEE_TAKEN), 0.00) < ISNULL(SUM(BF.FEE_AMOUNT), 0.00)

With the appropriate group by, of course.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ant-Green SSC Guru Points: 113507 More actions · Answer 13

Yep thanks both, case of the afternoon slump, need more coffee to keep going.

hoseam SSCertifiable Points: 5533 More actions · Answer 14

I have already figured that one out, so I have

HAVING SUM(ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00)) < SUM(ISNULL(BF.FEE_AMOUNT, 0.00))