On Fri, Aug 31, 2018 at 5:54 AM, Андрей В. <a.veselo...@gmail.com> wrote:
> Hello,
>
> I'm use SQLAlchemy for work with existing Oracle DB(Python 3.5.4, SQLAlchemy
> 1.2.11, cx_Oracle 6.1, Oacle Client 11g1).
> I reflecting tables and views(including materialized views) for quering
> data.
>
> Example:
> Tere is MV exists in DB
> create materialized view test_mv_table
>
> refresh force on demand
> as
> select
> nvl(exp(sum(ln(abs(case when 0.1 <> 0 then 0.1 end)))) *
> decode(mod(sum(decode(sign(0.1), 0, 0, 1,0,1)), 2), 0, 1,-1),0) *
> nvl(sum(case when sign(0.1) = 0 then 0 end), 1) as number_column_eval,
> 0.1 as number_column_constant
> from dual
>
>
>
> Column number_column_constant defined explicitly as 0.1 and
> number_column_eval is result of evaluation equal the same 0.1
can you try to add to your SQL a CAST that includes numeric precision?
e.g. CAST <expr> AS NUMERIC(10, 2). it looks like there is
floating point conversion going on. you'd just have to play with it
as Oracle is not very consistent with this kind of thing.
>
> sqlplus qury result is:
>
>> SQL> select * from test_mv_table;
>> NUMBER_COLUMN_EVAL NUMBER_COLUMN_CONSTANT
>> ------------------ ----------------------
>> .1 .1
>
>
> Both values representation is '0.1'.
>
> Now I query data in code:
>
> from sqlalchemy import create_engine, MetaData
> from sqlalchemy.orm import sessionmaker
>
> # Reflect table
> eng = create_engine('CONNECTION_STRING')
> md = MetaData()
> md.reflect(bind=eng, only=['test_mv_table', ])
> test_mv_table = md.tables['test_mv_table']
>
> # Set asdecimal=True
> test_mv_table.columns.number_column_constant.type.asdecimal=True
> test_mv_table.columns.number_column_eval.type.asdecimal=True
>
> # Get data
> session = sessionmaker(bind=eng)()
> mv_res = session.query(test_mv_table.c.number_column_constant,
> test_mv_table.c.number_column_eval).all()
> session.close()
>
> # Print result
> print(mv_res)
>
> So I get result:
>
>> [(Decimal('0.1'), Decimal('0.1000000000000000000000000000000000000003'))]
>
>
> but expect that values will be equal(as it is when i query data by sblplus).
>
> I will be grateful if somebody explain to me why the value of
> number_column_eval was not quered as 0.1 and how I can get it as Decimal
> represented as 0.1.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
