IF Statement - 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

General SQL Server Forums

New to SQL Server Programming

IF Statement

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2010-01-14 : 22:50:25

I need to create a query that will look at the repaymenttype and then based on that data run one of 2 processes. So IF RepaymentType is 'iv' then Select XXXXXX else Select YYYYY

I have tried the following

use livereporting
go
if (select RepaymentType from dbo.Loan_Amortisation ) = 'IV'
begin
select PV, InterestRateStandard, RepaymentType,repaymentfrequency,pmt,Repaypermonth, pmtfixed,RepaypermonthFixed,
nper,AccountName,
-(pv*-1)*(power((1+InterestRateStandard/1200000),nper))-pmt*((power((1+InterestRateStandard/1200000),nper))-1)
/(InterestRateStandard/1200000) as EndBalance
from dbo.Loan_Amortisation
end
else
select PV, InterestRateStandard, RepaymentType,repaymentfrequency,pmt,Repaypermonth, pmtfixed,RepaypermonthFixed,
nper,AccountName,
-(pv*-1)*(power((1+InterestRateStandard/1200000),3))-pmt*((power((1+InterestRateStandard/1200000),3))-1)
/(InterestRateStandard/1200000) as EndBalance
from dbo.Loan_Amortisation

and get the error message

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone point me in the right direction?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-14 : 23:06:23

Use a CASE expression.

select PV, InterestRateStandard, RepaymentType,repaymentfrequency,pmt,Repaypermonth, pmtfixed,RepaypermonthFixed,
nper,AccountName,
CASE WHEN RepaymentType = 'IV' THEN
-(pv*-1)*(power((1+InterestRateStandard/1200000),nper))-pmt*((power((1+InterestRateStandard/1200000),nper))-1)
/(InterestRateStandard/1200000) 
ELSE
-(pv*-1)*(power((1+InterestRateStandard/1200000),3))-pmt*((power((1+InterestRateStandard/1200000),3))-1)
/(InterestRateStandard/1200000) 
END
as EndBalance
from dbo.Loan_Amortisation

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-14 : 23:15:17

It is complaining about

if (select RepaymentType from dbo.Loan_Amortisation ) = 'IV'

The select statement will return as many rows as there are in the dbo.Loan_Amortisation table or view. SQL does not know which of those values it should pick and compare to see if it equals 'IV'.

I am guessing that for each row in your table, you want to calculate the EndBalance depending on the RepaymentType. In that case, do the test for Repayment type within the select statement as follows:

select 
   PV, InterestRateStandard, RepaymentType,repaymentfrequency,pmt,Repaypermonth, pmtfixed,RepaypermonthFixed,nper,AccountName,
   case when RepaymentType = 'IV' then
      -(pv*-1)*(power((1+InterestRateStandard/1200000),nper))-pmt*((power((1+InterestRateStandard/1200000),nper))-1)/(InterestRateStandard/1200000) 
   else
      -(pv*-1)*(power((1+InterestRateStandard/1200000),3))-pmt*((power((1+InterestRateStandard/1200000),3))-1)/(InterestRateStandard/1200000)
   end as EndBalance
from dbo.Loan_Amortisation

Also, you may want to put explicit decimal points where you want the numbers to be treated as floats as in 1.0 + InterestRateStandard/1200000.0 etc.

Les.61
Starting Member

49 Posts

Posted - 2010-01-14 : 23:17:10

Many thanks. It worked fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources