Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OLE DB Floating point conversion error

oxymorocycleAug 11 2017 — edited Aug 11 2017

I have an application in Delphi 10.1 using ADO to connect to an Oracle Database though OLE DB. I am querying a table with a column which has a floating point NUMBER data type. For some values in the table I get an error.

The returned values are VARIANT types with type VT_DECIMAL (https://msdn.microsoft.com/en-us/library/windows/desktop/ms221061(v=vs.85).aspx ) but the scale value in the decimal structure is set to 29 which is outside the 0 to 28 range defined for this type so, when Delphi calls the windows system function VariantChangeTypeEx to convert the value to a double type, it returns an E_INVALIDARG error.

I am not sure in which layer this invalid VARIANT structure is being generated but I assume that the conversion from the internal Oracle floating point data format to the VARIANT type being presented to my application is part of the OLE DB provider and therefore this behaviour is caused by a problem at that level.

I can work around this problem for some aspects of what I need to do but the conversion call is buried at a low level in the RTL and therefore I get the error whenever any of the higher layer functions tries to access one of these  values

Any help would be appreciated

Jon

Comments

Keith Jamieson
How do you detrmine which row to compare with.
Its clearly not the previous row judging by your example.

Here's a pointer. (Analytics )
with testdata as
(
select 'A' name, 1 num from dual union all
select 'B', 4 from dual union all
select 'C', 8 from dual union all
select 'D', 18  from dual union all
select 'h', 21 from dual union all
select 'E', 34  from dual union all
select 'f', 35 from dual union all
select 't', 36 from dual union all
select 'L', 51  from dual union all
select 'Z', 55 from dual 
)
select name,num
from
(
select name,num,lag(num,1,0) over (partition by 1 order by 1) prev_num from testdata
order by num
)
where num >= (prev_num+15)
450441
To phrase it differently, don't you just want the lowest number in each "bucket" of 15?
select num
from (select trunc(num/15), min(num)
      from test
      group by trunc(num/15)
     )
Centinul
How about this:
SQL> edit

Wrote file sqlplus_buffer.sql



  1  WITH test AS

  2  (

  3     SELECT 'A' AS name, 1 AS num FROM DUAL UNION ALL

  4     SELECT 'B' AS name, 4 AS num FROM DUAL UNION ALL

  5     SELECT 'C' AS name, 8 AS num FROM DUAL UNION ALL

  6     SELECT 'D' AS name, 18 AS num FROM DUAL UNION ALL

  7     SELECT 'h' AS name, 21 AS num FROM DUAL UNION ALL

  8     SELECT 'E' AS name, 34 AS num FROM DUAL UNION ALL

  9     SELECT 'f' AS name, 35 AS num FROM DUAL UNION ALL

 10     SELECT 't' AS name, 36 AS num FROM DUAL UNION ALL

 11     SELECT 'L' AS name, 51 AS num FROM DUAL UNION ALL

 12     SELECT 'Z' AS name, 55 AS num FROM DUAL

 13  )

 14  SELECT a.name,a.num,a.diff

 15  FROM

 16  (SELECT name,num,num-(LAG(num,1) OVER (order by num)) AS diff FROM test order by num) a

 17* WHERE a.diff >= 15 OR a.diff IS NULL

SQL> /



N        NUM       DIFF

- ---------- ----------

A          1

L         51         15



SQL>
I hope this helps. I had a hard time understanding your requirements.
Alfio
Thanks, but the solution is not correct.
Sorry, but the problem is very difficult to explain.

the range(15) start from the first record, so if the first number is 14, the second number will be >= 30.
Other example:

A, 14
B, 15
C, 19
D, 29
E, 30
F, 50
G, 51

I want select only 3 records: (A,14) , (E, 30) , (F, 50)

I hope that this example is more clearly.
450441
Answer
Why is the second number 30 and not 29?

According to the original post, it must be >= the previously selected number + 15.

14+15 = 29.

There's an entry for 29, so the second record should be that one.

Assuming I'm right, you just need to "adjust" your window by the 1st value.
with test as (
select 'A' nam,  14 num from dual union all
select 'B',  15 from dual union all
select 'C',  19 from dual union all
select 'D',  29 from dual union all
select 'E',  30 from dual union all
select 'F',  50 from dual union all
select 'G',  51 from dual)
select num
from (select trunc((num-minnum)/15), min(num) num
      from test, (select min(num) minnum from test) x
      group by trunc((num-minnum)/15)
     )

NU
--
14
29
50
Marked as Answer by Alfio · Sep 27 2020
Keith Jamieson
Ah I got you now

Always choose first number
Second number is first number that is 15 greater than first number
3rd number is first number that is 15 greater than second number.
etc

which gives
A, 14
E, 30
F, 50
Aketi Jyuuzou
I solved same question :-)
673100

and my site mentions same question B-)
http://www.geocities.jp/oraclesqlpuzzle/10-245.html
Alfio
thanks, very good, very well ...

I have an other question ....
I have the same scenario but more complicated, I have 3 columns: surname, name, datatime
and I have to use the same reasoning but for each surname:
example:
the range is 15 minuts

surname, name, datatime (dd/mm/yyyy hh24.mi)
x, a, 03/09/2008 10.00
x, b, 03/09/2008 10.08
x, c, 03/09/2008 10.16
y, a, 03/09/2008 10.10
y, b, 03/09/2008 10.30
y, c, 03/09/2008 10.50

i want select 5 record:
(x, a, 03/09/2008 10.00), (x, c, 03/09/2008 10.16) , (y, a, 03/09/2008 10.10) , (y, b, 03/09/2008 10.30) ,
(y, c, 03/09/2008 10.50)

thanks
JonWat
I don't think that is the correct answer. I think that will only work if you always manage to stay at the bottom boundary of your window (e.g. 14, 29, 44, 59 etc.)

It fails with the following data (gives you 40 and 45).

Aketi Jyuuzu's method works. Method is to work out what the next row would be if any given row is chosen (i.e. the first row that is at least 15 away), and then use a hierarchical query to choose the rows you would actually need starting with whatever is your first row.

(actually need to create a table so you can use rowids)

create table testxx as (
select 'A' nam, 14 num from dual union all
select 'B', 15 from dual union all
select 'C', 19 from dual union all
select 'D', 40 from dual union all
select 'E', 41 from dual union all
select 'F', 45 from dual union all
select 'G', 56 from dual);


with WorkView as (
Select nam,num,RowID as Row_ID,min(num) over() as startnum,
first_value(RowID) over(order by num
range between 15 following
and unbounded following) as childRowID
from Testxx)
select nam,num
from WorkView
Start With num = startnum
connect by prior childRowID = Row_ID;
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 8 2017
Added on Aug 11 2017
0 comments
583 views