Am I asking for trouble putting a the ROUND function in my SQL Query

[ulicki]

Hi Crystal Reports Version 7 MS SQL Server

I was having problems with my query not returning correct data because the way my option price field was stored in my datebase(as floats). Storing values as floats can cause the values to differ slightly for the same value stored. NOTE: Option price is stored in the grecap_view and the FAS123 table. Hence the join grecap_view.Option_Price *= FAS123.OPT_PRC does not bring back the correct information; because the values are slightly different there is no match so Nulls are returned. So as you can see below I put in the round function right into the sql query and it works. So my question is, am I asking for trouble and should I just use a record selection formula instead? Also I cannot change the float datatype, I am stuck with it.

SELECT
grecap_view.NUF_OptioneeName_First_MI_Last, grecap_view.NFF_OptioneeName_Last_First_MI, grecap_view.Options_Granted, grecap_view.Option_Price, grecap_view.Grant_Date, grecap_view.Grant_Expiration_Date, grecap_view.NUF_Grant_Number, PRICE.MARKET_PRC, FAS123.FAS_NUM, FAS123.VOLATILITY, FAS123.RISK_FREE, FAS123.EXP_LIFE, FAS123.DIV_YIELD, FAS123.OPTION_VAL
FROM
MikeU_UTC.dbo.grecap_view grecap_view,
MikeU_UTC.dbo.price PRICE,
MikeU_UTC.dbo.fas123 FAS123
WHERE
grecap_view.Grant_Date *= PRICE.PRICE_DT AND grecap_view.Grant_Date *= FAS123.GRANT_DT AND round(grecap_view.Option_Price,6) *= round(FAS123.OPT_PRC,6) AND grecap_view.Grant_Expiration_Date *= FAS123.EXPIRE_DT AND grecap_view.Grant_Date >= &quot;Jan 1, 2001&quot; AND grecap_view.Grant_Date < &quot;Jan 1, 2002&quot;
ORDER BY
grecap_view.Grant_Date ASC, grecap_view.Option_Price ASC, grecap_view.Grant_Expiration_Date ASC, grecap_view.NUF_OptioneeName_First_MI_Last ASC, grecap_view.NUF_Grant_Number ASC, FAS123.FAS_NUM ASC

 

[synapsevampire]

It should be faster and just as reliabe on the database side, the best Crystal might do is pass it to the database in SQL anyway.

-k

 

[ulicki]

Thanks, synapsevampire,

I checked on SQL Server's, SQL Profiler and the it comes through.

 

[Siggy19]

ROUND should work fine.

You can also use &quot;BETWEEN (a - 0.001) AND (a + 0.001)&quot;, but this will probably be slower.

 

[synapsevampire]

I meant if you used the record selection criteria to do this, the best it might do is what you've already done.

-k