|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-16 : 08:04:01
|
| Gary writes "I'm imported the query from Access (where it worked perfectly) to SQL View where the "Iif" statements caused an issue.Here's a SMALL portion of the original:SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, [TransBuyerPropSalePrice]*(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, ......Below, are my changes so far, but I'm stuck. SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, [TransBuyerPropSalePrice]*If([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],If([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA]/100)) AS VAFunding, ........It appears that I may not be able to put an IF into a SQL SELECT. If not, how can I get the same effect ? Thanks. GaryListed Below is the Full Access Query:SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, tblTransactionBuyer.TransBuyerEntryDate, qryUserAlphaList.UserName, qryUserAlphaList.UserAreaCode, qryUserAlphaList.UserPhone, tblTransactionBuyer.TransBuyerName, tblTransactionBuyer.TransBuyerVaEligible, tblTransactionBuyer.TransBuyerPropAddress, tblTransactionBuyer.TransBuyerPropCity, tblTransactionBuyer.TransBuyerPropSalePrice, ([TransBuyerPropSalePrice]*([DefBasePercentFHA])/100) AS LoanBaseFHA, ([TransBuyerPropSalePrice]*([DefBasePercentCONV])/100) AS LoanBaseCONV, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, ([TransBuyerPropSalePrice]*([DefBasePercentJumbo])/100) AS LoanBaseJumbo, [LoanBaseFHA]*[DefMipFHA]/100 AS MipFHA, [LoanBaseCONV]*[DefMipCONV]/100 AS MipCONV, [LoanBaseJumbo]*[DefMipJumbo]/100 AS MipJumbo, [TransBuyerPropSalePrice]*(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, [LoanBaseFHA]-[DownOverMinFHA]+[MipFHA] AS LoanTotalFHA, [LoanBaseCONV]-[DownOverMinCONV]+[MipCONV] AS LoanTotalCONV, [LoanBaseJumbo]+[MipJumbo]-[DownPaymentJumbo] AS LoanTotalJumbo, [LoanBaseVA]+[VAFunding]-[DownPaymentVA] AS LoanTotalVA, [TransBuyerPropSalePrice]*([DefMinDownPayFHA]/100) AS MinDownFHA, [TransBuyerPropSalePrice]*[DefMinDownPayCONV]/100 AS MinDownCONV, [TransBuyerPropSalePrice]*[DefMinDownPayVA]/100 AS MinDownVA, [TransBuyerPropSalePrice]*[DefMinDownPayJumbo]/100 AS MinDownJumbo, tblTransactionBuyer.TransBuyerIntRate, tblTransactionBuyer.TransBuyerLoanYears, tblTransactionBuyer.TransBuyerHazardIns, tblTransactionBuyer.TransBuyerFloodIns, tblTransactionBuyer.TransBuyerClosingDate, IIf(([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice]>[TransBuyerConvDownPayCash],([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice],[TransBuyerConvDownPayCash]) AS ConvDownPay, tblDefaults.DefBuyAppraisal, tblDefaults.DefBuyCreditReport, tblDefaults.DefBuyJumboUnderwriting, tblDefaults.DefBuyVAUnderwriting, tblDefaults.DefBuyFHAUnderwriting, tblDefaults.DefBuyCONVUnderwriting, tblDefaults.DefBuyJumboTaxService, tblDefaults.DefBuyVATaxService, tblDefaults.DefBuyFHATaxService, tblDefaults.DefBuyCONVTaxService, tblDefaults.DefBuyFloodCert, tblDefaults.DefBuyAbstractTitle, tblDefaults.DefBuyMiscFedex, tblDefaults.DefBuyRecording, tblDefaults.DefBuySurvey, tblDefaults.DefBuyAttorney, tblDefaults.DefBasePercentFHA, tblDefaults.DefBasePercentCONV, tblDefaults.DefBasePercentJumbo, tblDefaults.DefBasePercentVA, [TransBuyerPropSalePrice]*[DefPropTaxRate] AS PropTax, [LoanBaseFHA]*[DefBuyOrigFHA]/100 AS OrigFHA, [LoanBaseCONV]*[DefBuyOrigCONV]/100 AS OrigCONV, [LoanBaseVA]*[DefBuyOrigVA]/100 AS OrigVA, [LoanBaseJumbo]*[DefBuyOrigJumbo]/100 AS OrigJumbo, [DefTitleInsDollar]*([TransBuyerPropSalePrice]/[DefTitleInsPerX]) AS TitleInsurance, [OrigFHA]+[DefBuyAppraisal |
|
Trouble translating query from Access to SQL Server 