SQL Query comparing Float to Decimal Help

Hello all,

I want to make sure I am doing this the most efficient way.   I am comparing in a query a nullable float amount to a nullable decimal amount.  Right now I seem to have to do this in order for the compare to work correct.

AND LTRIM(RTRIM(ISNULL(CFS.Amount,0))) = LTRIM(RTRIM(CAST(ISNULL(CDC.Amount,0) AS decimal(18,2))))

The trimming seemed to be needed as well as latest I realized I needed an ISNULL check to 0 as well.   Is there a better way to do this?  No idea why I needed the trim but it seemed to need it or things were being missed.   I have the values coming from two different sources one is using a float and one a decimal(18,2).

Thanks all
sbornstein2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Why using LTRIM and RTRIM when you have FLOAT and DECIMAL/NUMERIC? These are string functions..

Then: Comparing float values for equality is, well, error prone due to the nature of floats. Under normal circumstances you need to test for ABS(float1- float2) < σ. σ (sigma) is a boundary where you consider two floats to be equal. So you need to choose it carefully.

E.g.

DECLARE @CdcAmount DECIMAL(18, 4) = 1 / 3.14159265 + 1 / 2.71828183;
DECLARE @CfsAmount FLOAT = 1 / 3.14159265 + 1 / 2.71828183;
DECLARE @SIGMA FLOAT = 0.0001;

SELECT  @CdcAmount ,
        @CfsAmount ,
        IIF(@CdcAmount = @CfsAmount, 1, 0) ,
        ABS(@CdcAmount - @CfsAmount) ,
        IIF(ABS(@CdcAmount - @CfsAmount) < @SIGMA, 1, 0);

SET @SIGMA = 0.00000001;

SELECT  @CdcAmount ,
        @CfsAmount ,
        IIF(@CdcAmount = @CfsAmount, 1, 0) ,
        ABS(@CdcAmount - @CfsAmount) ,
        IIF(ABS(@CdcAmount - @CfsAmount) < @SIGMA, 1, 0);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbornstein2Author Commented:
Not sure why but I know without the trims for some reason comparing both in some cases was not matching.
0
ste5anSenior DeveloperCommented:
Without actual case showing that you need that TRIMs drop them. E.g.

SELECT  LTRIM(STR(98235.12, 10, 2)) ,
        '"' + STR(98235.12, 10, 2) + '"';

Open in new window


But as you said your types are FLOAT and DECIMAL, then it does not apply.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

John EsraeloDatabase / SQL DeveloperCommented:
compare_data_types.pngSee attached, I have made couple of comparisons.
those are nullable fields, however, did not make any of those as null to compare..
but, that's something that you can play with and see how far you can take that ..
0
ste5anSenior DeveloperCommented:
@John, please post code, not images..
0
John EsraeloDatabase / SQL DeveloperCommented:
I believe that typing 6 lines of code is not that cumbersome..
I get your point though.. perhaps in the near future postings.
0
sbornstein2Author Commented:
Thanks guys I am going through these this AM.
0
sbornstein2Author Commented:
Hey guys so this is what I am seeing.  Notice the .8 and .80 in the data.  Also notice the NULL issue with the AmountUSD Null compare.

Sample:

  CREATE TABLE #TempCustodianMVData
	   (
		  PortfolioID int not null,
		  MVDate datetime not null,
		  Amount float null,
		  AmountUSD float null,
		  ReconcileFlag char(1) null,
		  CurrencyID int not null
	   )

INSERT INTO #TempCustodianMVData
(
PortfolioID, 
MVDate, 
Amount,
AmountUSD, 
ReconcileFlag,
CurrencyID
)
VALUES
(
1775,
'5/31/2015',
39619139.8,
NULL,
'F',
8
)

 CREATE TABLE #TempCustodianMVSnap
	   (
		  PortfolioID int not null,
		  MVDate datetime not null,
		  Amount decimal(18,2) null,
		  AmountUSD decimal(18,2) null,
		  ReconcileFlag char(1) null,
		  CurrencyID int not null
	   )
	   
  INSERT INTO #TempCustodianMVSnap
(
PortfolioID, 
MVDate, 
Amount,
AmountUSD, 
ReconcileFlag,
CurrencyID
)
VALUES
(
1775,
'5/31/2015',
39619139.80,
NULL,
'F',
8
)

SELECT PortfolioID, 
			   MVDate, 
			   Amount,
			   AmountUSD, 
			   ReconcileFlag,
			   CurrencyID	   
		FROM #TempCustodianMVData CMV
		WHERE CMV.ReconcileFlag IN ('F','R') AND NOT EXISTS
			  (SELECT * FROM #TempCustodianMVSnap MVS 
						WHERE MVS.MVDate = CMV.MVDate
							  AND LTRIM(RTRIM(MVS.Amount)) = LTRIM(RTRIM(CAST(CMV.Amount AS decimal(18,2))))
							  AND LTRIM(RTRIM(MVS.AmountUSD)) = LTRIM(RTRIM(CAST(CMV.AmountUSD AS decimal(18,2))))
							  AND MVS.ReconcileFlag = CMV.ReconcileFlag
							  AND MVS.CurrencyID = CMV.CurrencyID)

Open in new window


This is returning a record where it should not.  If I take out the AmountUSD compare then it works where I don't return a record.   If I do this it works and does not return a record:

AND LTRIM(RTRIM(ISNULL(MVS.AmountUSD,0))) = LTRIM(RTRIM(CAST(ISNULL(CMV.AmountUSD,0) AS decimal(18,2))))

The trims also were needed for another issue I saw but can't remember where.
0
ste5anSenior DeveloperCommented:
.8 is equal to .80. Thus it's correct.

You cannot compare NULL with the equal operator. Unknown cannot be equal to Unknown. You need the IS NULL operator.

Don't use the TRIMs when you don't know why you need it..

E.g.

SET DATEFORMAT MDY;

DECLARE @TempCustodianMVData TABLE
    (
      PortfolioID INT NOT NULL ,
      MVDate DATETIME NOT NULL ,
      Amount FLOAT NULL ,
      AmountUSD FLOAT NULL ,
      ReconcileFlag CHAR(1) NULL ,
      CurrencyID INT NOT NULL
    );

INSERT  INTO @TempCustodianMVData
VALUES  ( 1775, '5/31/2015', 39619139.8, NULL, 'F', 8 );

DECLARE @TempCustodianMVSnap TABLE
    (
      PortfolioID INT NOT NULL ,
      MVDate DATETIME NOT NULL ,
      Amount DECIMAL(18, 2) NULL ,
      AmountUSD DECIMAL(18, 2) NULL ,
      ReconcileFlag CHAR(1) NULL ,
      CurrencyID INT NOT NULL
    );
	   
INSERT  INTO @TempCustodianMVSnap
VALUES  ( 1775, '5/31/2015', 39619139.80, NULL, 'F', 8 );

SELECT  PortfolioID ,
        MVDate ,
        Amount ,
        AmountUSD ,
        ReconcileFlag ,
        CurrencyID
FROM    @TempCustodianMVData CMV
WHERE   NOT EXISTS ( SELECT *
                     FROM   @TempCustodianMVSnap MVS
                     WHERE  MVS.MVDate = CMV.MVDate
                            AND MVS.Amount = CMV.Amount
                            AND ( MVS.AmountUSD = CMV.AmountUSD
                                  OR MVS.AmountUSD IS NULL
                                  OR CMV.AmountUSD IS NULL
                                )
                            AND MVS.ReconcileFlag = CMV.ReconcileFlag
                            AND MVS.CurrencyID = CMV.CurrencyID );

Open in new window

0
sbornstein2Author Commented:
ste5an its okay to just do ISNULL(X,0) right on both sides?
0
ste5anSenior DeveloperCommented:
It depends on whether you want

 MVS.AmountUSD = CMV.AmountUSD
 OR MVS.AmountUSD IS NULL
 OR CMV.AmountUSD IS NULL

Open in new window


or

 MVS.AmountUSD = CMV.AmountUSD
 OR (
    MVS.AmountUSD IS NULL 
    AND 
    CMV.AmountUSD IS NULL 
)

Open in new window


In the first case it's equivalent.
0
sbornstein2Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.