_____

From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of Helen Borrie
Sent: Thursday, August 21, 2008 7:27 PM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Problem sorting on Numeric(9,2) column-orderinglinks,
ib_query




At 02:04 22/08/2008, Michael L. Horne wrote:

>Hello,
>
>I created a TIB_Query with the following SQL:
>
>select * from PURCHASE_AM_Order
>
>CREATE TABLE PURCHASE_AM_ORDER (
> PAMO_ID integer not NULL,
> PAMO_PART varchar(16),
> PAMO_QTY integer,
> PAMO_COST Numeric(9,2));
>
>In the Ordering tab I clicked on the "Create" button
>when I checked the Part, Qty, Cost, and clicked "OK",
>said yes to "Assign ordering items?" Where it created
>the following:
>
>PAMO_PART=PAMO_PART;PAMO_PART DESC
>PAMO_QTY=PAMO_QTY;PAMO_QTY DESC
>PAMO_COST=PAMO_COST;PAMO_COST DESC
>
>PAMO_PART=ITEM=1;POS=0
>PAMO_QTY=ITEM=2;POS=0
>PAMO_COST=ITEM=3;POS=0
>
>If I go to data tab and click on the column header
>to sort the "PAMO_COST" column I get an
>"Integer overflow" error.
>
>The way to solve this problem is to remove
>the ";POS=0" from the line "PAMO_COST=ITEM=3;POS=0".
>
>This seems to be a possible bug to me.

...or not. IBO doesn't create the ascending and descending indexes to
support these sorts: it's assumed that if you want to sort in these ways you
will have created the indexes ahead.

When you request the reordering, IBO sends the appropriate ORDER BY clause
without knowledge of whether those supporting indexes exist. Without a
suitable index, the Firebird engine has to walk the data and create a
temporary bitmap for the sort. The more obvious cause of your exception is
that the engine found a record containing data in this field that exceeds
the precision specified for it - numeric(9,2) is a 32-bit integer with a
scale of 2, so any number greater than 21,474,836.47 is going to throw an
overflow error as soon as it is touched.

To test this angle, I suggest your next move would be to attempt to create
the pairs of indexes that support the OrderingItems you defined, using bare
ISQL rather than any GUI tool. If you have oversized data there (or other
data misfits) then the CREATE INDEX request should throw it up.

Helen

.


<http://geo.yahoo.com/serv?s=97359714/grpId=402930/grpspId=1705007183/msgId=
43137/stime=1219361182/nc1=4507179/nc2=5202316/nc3=5379225>


Helen,

As much as I wish that you where right, you are not. The first thing I
checked was
the largest value which is 18110.56. Also, to confirm that it is not the
indexes causing
the problem I built them. This didn't solve the problem. Also, there is
only a small number
of records in the table.

HOWEVER, removing the ",POS=0" from the OrderingLinks for that sort item
allowed
sorting on that column. Therefore, I must conclude that the "POS" doesn't
work with
numeric(9,2) and that IBObjects shouldn't automatically add it to every line
in the
OrderingLinks when using the Create button.

I have used IBObjects for over 8 years, and it took me 2 hours to figure out
what
was causing the problem. I don't know if a new user would ever find the
problem.

Thanks
Michael Horne


[Non-text portions of this message have been removed]