Where Clause Error - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Where Clause Error

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-09-27 : 09:14:44

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]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-27 : 09:24:25

try SUM(ISNULL(BF.GLOBAL_FEE_TAKEN,0.00)) instead.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-09-27 : 09:29:40

I still get the same results, "BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT" doesnt return data at all.
"ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00) < ISNULL(BF.FEE_AMOUNT, 0.00)" returns data but with only zero values for [AmountPaid]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-27 : 09:50:21

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
AND BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT

--------------------------
http://connectsql.blogspot.com/

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-09-27 : 09:58:07

I'm getting zeros for all MB.POLICY_ID and MB.TOTAL_AMOUNT which is wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 10:29:45

show some sample data from your tables and explain what you want as output from them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources