SQL Query , Data Conversion Issue

Question

SQL Query , Data Conversion Issue

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
January 7, 2017 at 11:45 am

#339195

Hi All
This post is not related to SQL server(Though related to a SQL query) so sorry in advance for posting this problem here in 2016 forum but i see no where to seek some help.
This problem is related to VoltDB ( In Memory Database).https://docs.voltdb.com/?utm_source=LL-UX&utm_medium=resources-dropdown&utm_content=resources-dropdown-top&utm_campaign=docs
The problem that i m facing is in prod environment though same is working absolutely fine in Dev Environment .
I have two tables.
1. PD_MAP
2. RISK_MASTER
CREATE TABLE PD_MAP
(
rating varchar(5),
upperbound float,
lowerbound float
) ;
INSERT INTO PD_MAP ( rating, lowerbound , upperbound ) values ('NGR1', 0.00095, 0.00134)
## Providing only one value in PD_MAP just for reference.
CREATE TABLE RISK_MASTER
(
EADPD FLOAT,
EAD FLOAT
);
INSERT INTO RISK_MASTER SELECT 4905118.6635,3880437028.5424;
My Final Query:
select rating
from PD_MAP AS A,
(select EADPD/EAD AS valuerange from RISK_MASTER ) AS B
WHERE a.upperbound > b.valuerange and lowerbound < b.valuerange
Output for valuerange column :
select EADPD/EAD AS valuerange from RISK_MASTER
#Output: 0.001264063461775206
Output of valuerange i.e 0.001264063461775206 is being used in PD_MAP table in where condition for finding rating corresponding to upperbound and lowerbound range. This query works fine dev environment but giving no output in prod. I have already matched both the environment and they are same.
Also, If i m hard coding valuerange in query then it works fine.
select ratingclass
from PD_MAP AS A,
(select 0.001264063461775206 AS valuerange from RISK_MASTER ) AS B
WHERE a.upperbound > b.valuerange and lowerbound < b.valuerange
Please let me know if anything else is required from my side.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

You may struggle to get a proper answer from anyone here as SSC is a SQL Server Community, meaning many people may not have (much) knowledge of many RDBMSs other than Microsoft SQL Server. I, for example, have experience with SQL Server (T-SQL), and a little MySQL. I, however, until you'd mentioned it had never heard of VoltDB.

Many of us likely probably don't have a test environment for VoltDB, so even if we were to attempt to replicate your problem, we could likely experience a different result on our environments (such as SQL Server) to VoltDB. Saying that, hazarding a guess would be that Dev and Prod are not truly the same (even though you believe that are).

Normally I would ask that you provide sample data that behaves differently on Prod and Dev, however, I doubt that that's going to help as I can't test in a VoltDB environemnt. Unless someone else here uses VoltDB, I would actually suggest that you use a different community to ask your question.

I had a look, and stackoverflow do have a tag for VoltDB, so I would suggest asking there if no one here can shed any light on the matter.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

Thanks Tom, have forwarded my query to stackoverflow.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Just a quick thought on this, but I have to wonder how VoltDB handles floating-point math. As your fields are declared as "float" data types, one thing you have to be aware of is that "float" is an "inexact" data representation methodology. Varying results with float values wouldn't necessarily surprise me. I wonder if these values might be better off as higher precision data types, such as decimal(20, 18) or something similar. Mathematical precision can be a bear to deal with, but using float as a datatype almost inevitably leads to a lack of precision.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Mr. Kapsicum (1/7/2017)
Output for valuerange column :
select EADPD/EAD AS valuerange from RISK_MASTER
#Output: 0.001264063461775206

You need to keep in mind that what you see as the Output is not the actual output, but a decimal representation of the output float value.

Too often they are not the same.

Typically, last digits come from rounding, truncating, or any other operation performed by the UI tool you use to view the values.

Do you know what is the FLOAT precision in VoltDB?

_____________
Code for TallyGenerator