-1

I have a follow up question with regards to a similar issue I'm having, as the one posted on this thread: Why when converting SQL Real to Numeric does the scale slightly increase?.

I'm using Sybase IQ 16. I have a column called - StrikePrice with float datatype in a table called ProductTb. For a certain record, the value in the table for this column is StrikePrice=22411.39.

When I try to convert it in either decimal or numeric as follows : convert(decimal(31,5), StrikePrice) OR convert(numeric(31,5), StrikePrice), this gives a result as - 22411.39189.

How do I make sure that this shows the value as - 22411.39000 instead of 22411.39189? Is there any workaround that I can use to achieve this?

Thanks in advance for your help.

Example of what I'm looking for: When I have a value of let's say : 0.0090827, the desired output needed is : 0.00908 i.e. limited to 5 digits after the decimal. And when I have a value of : 22411.39, the desired output is : 22411.39000.

Adding more details

This(above explained issue) seems to work fine on Sybase ASE and the results are what I expect on ASE. The issue is only on Sybase IQ. See details below:

Sybase ASE (15.7):

select TradeNum, StrikePrice as "Original Strike Price in Database", convert(numeric(31, 5), StrikePrice) as "Converted Strike Price" from ProductTb where TradeNum in (8463676,72372333)

TradeNum Original Strike Price in Database Converted Strike Price

8463676 61.65 61.65000
72372333 85.6 85.60000
72372333 85.6 85.60000

Sybase IQ (16):

select TradeNum, StrikePrice as "Original Strike Price in Database", convert(numeric(31, 5), StrikePrice) as "Converted Strike Price" from ProductTb where TradeNum in (8463676,72372333)

TradeNum Original Strike Price in Database Converted Strike Price

72372333 85.6 85.59999
72372333 85.6 85.59999
8463676 61.65 61.64999

Raul
  • 1
  • 3

1 Answers1

0

Looks like you have a couple SQL function options:

round()

SELECT ROUND( 123.234, 1 ) FROM iq_dummy
=> 123.200

truncnum()

SELECT TRUNCNUM( 655, -2 ) FROM iq_dummy
=> 600

SELECT TRUNCNUM( 655.348, 2 ) FROM iq_dummy
=> 655.340

I don't have access to an IQ instance at the moment but I'm thinking something like the following may work:

convert(decimal(31,5),    round(StrikePrice,2))
convert(numeric(31,5), truncnum(StrikePrice,2))

UPDATE

The updated question appears to state there are 2 different rounding requirements based on the value in the StrikePrice column:

  • if < 1.0 then keep 5 decimal places of accuracy
  • if > 1.0 then keep 2 decimal places of accuracy

NOTE: I'm guessing at the threshold here (< 1.0 vs > 1.-) as this has not been explicitly stated in the question; if OP decides the threshold is something other than 1.0 this should be easy enough to address ... just change the 1.0 in the query (below) to the new threshold value

This logic can be implemented via a case statement.

I don't have access to an IQ instance but the following ASE example should be easy enough to convert to IQ:

create table testtab (a float)
go
insert testtab values (61.649999)   -- > 1.0 so round to 2 decimal places
insert testtab values (0.2234234)   -- < 1.0 so round to 5 decimal places
go
select convert(numeric(31,5),
                 case when a < 1.0
                      then round(a,5) 
                      else round(a,2)
                 end
              )
from testtab
go
 --------------
       61.65000      -- rounded to 2 decimal places
        0.22342      -- rounded to 5 decimal places
markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • Thanks for your response @markp-fuso. Actually, my intention is not to round the number, but limit the scale to 5 places after the decimal. For example, the StrikePrice field might currently have values as below: 0.465, 0.0090827, 0.0090909, 0.0095057, 0.0701, 0.152658, 0.6154. When the scale has more than 5 digits, I would like to get rid off them and limit the output to 5 places after decimal. Hence, when I have a number as : 22411.39, I need to get the output as : 22411.39000. Do you think this is possible? – Raul Jul 08 '21 at 15:38
  • have you tried the suggested code snippets? the problem you have is that the actual float value stored in the database has **more than 2 digits** of accuracy (after the decimal), while what you want is to bring along **only 2 digits** of accuracy, so how do you do this? you have to round (or truncate, your decision) the actual float data at anything beyond 2 digits, which can be done with either the `round()` or `truncnum()` functions, which can then be fed to your `convert()` call; `round(22411.39189,2)` should return `22411.39`, then your `convert()` should return `22411.39000` – markp-fuso Jul 08 '21 at 15:42
  • per your instructions, I'm assuming you want to convert `0.0090909` to `0.00000` (use `truncnum(StrikePrice,2)`) or `0.01000` (use `round(StrkePrice,2)`) – markp-fuso Jul 08 '21 at 15:45
  • Thanks for your suggestion. the requirement is a little more tricky, I should have been more clear. Apologies for that. When I have a value of let's say : 0.0090827, the desired output needed is : 0.00908 i.e. limited to 5 digits after the decimal. And when I have a value of : 22411.39, the desired output is : 22411.39000. Any suggestions, how to achieve this? – Raul Jul 08 '21 at 16:18
  • how do you determine when to truncate/round to 2x digits of accuracy vs when to keep the current 5x digits of accuracy? I'm **guessing** the rules change if the value is greater than 1.0 or less than 1.0 (replace `1.0` with some other threshold value) and if that's the case ... then add a `case` statement to the query to determine when to limit to `2` digits vs when to limit to `5` digits ... doable, but requires knowing your full set of requirements; I recommend you update the question with your more detailed requirements as well as several examples of what you're expecting – markp-fuso Jul 08 '21 at 16:22
  • Thanks @markp-fuso for your assistance. I will respond with more details in the following week. I did some more research and will send some more samples of data and answers to your question, and take this forward from there. Really appreciate your help in this! – Raul Jul 11 '21 at 18:09
  • Hi @markp-fuso. Hope you are doing well. Had been tied up with multiple projects and didn't get a chance to get back to this. I have added more details to my original question with details about how the results I expect work fine on Sybase ASE but not on Sybase IQ. Can you take a look and suggest if this seems to be just an issue with the way IQ is handling the conversion of float to numeric ? – Raul Jul 28 '21 at 21:16
  • what's the datatype of the `StrikePrice` column (ASE and IQ)? – markp-fuso Jul 28 '21 at 21:30
  • It's float in both databases currently. – Raul Jul 29 '21 at 16:22
  • It's float in both databases currently – Raul Jul 30 '21 at 17:19
  • your ASE vs IQ data shows us the 2x RDBMS products store the float values differently; in order to get the same exact values when converting to `numeric(31,5)` you'll need to have IQ round the data accordingly before converting to `numeric(31,5)`; I've updated the question with one idea – markp-fuso Jul 30 '21 at 17:46