SQL Query , Data Conversion Issue

  • 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.

  • 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

  • Thanks Tom, have forwarded my query to stackoverflow.

  • 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)

  • 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

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

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