Implicit conversion varchar to money
-
need help Guys., how can i convert money to varchar, to add some prefix. having problem with the below statement. Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount], The Complete Sproc is USE FastLoan_Commissions GO set ANSI_NULLS OFF set QUOTED_IDENTIFIER ON GO
ALTER PROC [dbo].[upExceptions_GetPending] @UserID varchar(10) AS BEGIN SELECT E.ExceptionID, I.IntroducerID, I.TradingName, E.LoanID, '' [LoanIncrease], --tooltip A.ShortName, LS12.StageDate [SettlementDate], CRT.CommissionRunType, CR.CommissionRunDate, ET.ExceptionType [Reason], Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount], ISNULL(L.CurrentBalance, 0) [CurrentBalance], E.ExceptionRate, NULL [ExceptionDays], E.ExceptionAmount, NULLIF(E.ExceptionGST, 0) [ExceptionGST], E.ExceptionTotal FROM tblCommissionRun CR INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12) INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID AND LS12.LoanStageTypeID = 6 LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID WHERE CR.Pending = 1 AND CR.CommissionRunTypeID IN (1,2) AND E.LoanIncreaseID IS NULL UNION ALL --UPFRONT LOAN INCREASES SELECT E.ExceptionID, I.IntroducerID, I.TradingName, E.LoanID, 'Loan Increase' [LoanIncrease], --tooltip A.ShortName, LIS12.StageDate [SettlementDate], CRT.CommissionRunType, CR.CommissionRunDate, ET.ExceptionType [Reason], LI.IncreaseAmount [Amount], ISNULL(L.CurrentBalance, 0) [CurrentBalance], E.ExceptionRate, NULL [ExceptionDays], E.ExceptionAmount, NULLIF(E.ExceptionGST, 0) [ExceptionGST], E.ExceptionTotal FROM tblCommissionRun CR INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E. -
need help Guys., how can i convert money to varchar, to add some prefix. having problem with the below statement. Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount], The Complete Sproc is USE FastLoan_Commissions GO set ANSI_NULLS OFF set QUOTED_IDENTIFIER ON GO
ALTER PROC [dbo].[upExceptions_GetPending] @UserID varchar(10) AS BEGIN SELECT E.ExceptionID, I.IntroducerID, I.TradingName, E.LoanID, '' [LoanIncrease], --tooltip A.ShortName, LS12.StageDate [SettlementDate], CRT.CommissionRunType, CR.CommissionRunDate, ET.ExceptionType [Reason], Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount], ISNULL(L.CurrentBalance, 0) [CurrentBalance], E.ExceptionRate, NULL [ExceptionDays], E.ExceptionAmount, NULLIF(E.ExceptionGST, 0) [ExceptionGST], E.ExceptionTotal FROM tblCommissionRun CR INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12) INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID AND LS12.LoanStageTypeID = 6 LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID WHERE CR.Pending = 1 AND CR.CommissionRunTypeID IN (1,2) AND E.LoanIncreaseID IS NULL UNION ALL --UPFRONT LOAN INCREASES SELECT E.ExceptionID, I.IntroducerID, I.TradingName, E.LoanID, 'Loan Increase' [LoanIncrease], --tooltip A.ShortName, LIS12.StageDate [SettlementDate], CRT.CommissionRunType, CR.CommissionRunDate, ET.ExceptionType [Reason], LI.IncreaseAmount [Amount], ISNULL(L.CurrentBalance, 0) [CurrentBalance], E.ExceptionRate, NULL [ExceptionDays], E.ExceptionAmount, NULLIF(E.ExceptionGST, 0) [ExceptionGST], E.ExceptionTotal FROM tblCommissionRun CR INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E. -
it seems like to me that you would do something like this instead Case When Isnull(LP.RefinanceAmount, '')='' Then cast(L.LoanAmount as varchar(20)) Else 'N.F' + cast(LP.RefinanceAmount as varchar(20)) END [Amount],