Link to home
Start Free TrialLog in
Avatar of fahVB
fahVB

asked on

select stmt multiply function

This multiplication in select stmt is working but when one of the value is zero then the required result in Risk_Score becomes zero...

select LTV_Score*FICO_Score*ST_Score*Balloon*Interest_Only as Risk_Score

In the example below, this second account should score 12 [2*1*1*2*3=12] but because of the 0 in FICO score, it came out 0 [2*0*1*2*3=0]

LTV_Score      FICO_Score      ST_Score      Balloon      Interest_Only      Risk_Score
1      1      1      2      3      6
2      0      1      2      3      0
2      1      1      0      3      0
1      1      1      0      3      0
2      3      1      0      3      0
1      1      2      0      3      0
1      3      1      0      3      0


is there a way to ignore zer0's and continue with the multiplication step
select LTV_Score*FICO_Score*ST_Score*Balloon*Interest_Only as Risk_Score
from
(select 
case when current_ltv < 85.01 then 1 when current_ltv < 95.01 then 2 else 3 end as LTV_Score,
case when FICO < 640 then 3 when FICO < 700 then 2 when FICO IS NULL then 0 else 1 end as FICO_Score,
case when State in ('CA','FL','AZ','NV') then 3 when State = 'NJ' then 2 else 1 end as ST_Score,
case when Balloon_Date > '1/1/1900' then 2 when Balloon_Date = '1/1/1900' then 0 when Balloon_Date IS NULL then 0 else 1 end as Balloon,
case when Interest_Only_Flag = 'Y' then 3 else 1 end as Interest_Only
From First_Mortages
 ) A

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
..what a good idea.  ;)
GENIUS.... PURE GENIUS!
Maybe they're right..maybe we are twins.
Siamese twins: cheesemandew
:)  I actually laughed out loud on that one...
Avatar of fahVB
fahVB

ASKER

Aight, let me resolve the dispute between twins
Not a dispute... we're just having fun at the expense of cluttering up a thread!

:)
Avatar of fahVB

ASKER

I know, I am just being funny....
Avatar of fahVB

ASKER

Thank you for your suggestions, twins....