Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 12 total
Thread Custom function problem
Wed, Feb 1 2006 5:37 AMPermanent Link

"Clive"
Hi,

I have a problem with custom functions and the order in which DBISAM is
passing in the rows.

If I have this statement for example

select MYFUNCTION(value),period from mytable order by period

and I have run the empty table command prior to the data being loaded into
the table then the rows are passed into the CustomFunction method from top
to bottom correctly according to the order by clause.

if I just execute "delete from mytable" then populate the rows, then run the
above SQL, rows are passed in in reverse order, ignoring the order by
clause. This is a problem as I am accumulating values and DBISAM is
reversing the order.

Least im pretty sure thats whats happening as I can replicate this behaviour
in my application.

Thanks
Clive.

Wed, Feb 1 2006 5:37 AMPermanent Link

"Clive"
On 4.21 B4

"Clive" <dd@dddd.com> wrote in message
news:E765629A-FEA4-40C2-A539-0C056F7F517F@news.elevatesoft.com...
> Hi,
>
> I have a problem with custom functions and the order in which DBISAM is
> passing in the rows.
>
> If I have this statement for example
>
> select MYFUNCTION(value),period from mytable order by period
>
> and I have run the empty table command prior to the data being loaded into
> the table then the rows are passed into the CustomFunction method from top
> to bottom correctly according to the order by clause.
>
> if I just execute "delete from mytable" then populate the rows, then run
> the above SQL, rows are passed in in reverse order, ignoring the order by
> clause. This is a problem as I am accumulating values and DBISAM is
> reversing the order.
>
> Least im pretty sure thats whats happening as I can replicate this
> behaviour in my application.
>
> Thanks
> Clive.
>
>

Wed, Feb 1 2006 5:38 AMPermanent Link

"Clive"
Sorry, Make that 4.22 B4

"Clive" <dd@dddd.com> wrote in message
news:6E51E055-96A6-4285-80D0-4B3AE19EBBF5@news.elevatesoft.com...
> On 4.21 B4
>
> "Clive" <dd@dddd.com> wrote in message
> news:E765629A-FEA4-40C2-A539-0C056F7F517F@news.elevatesoft.com...
>> Hi,
>>
>> I have a problem with custom functions and the order in which DBISAM is
>> passing in the rows.
>>
>> If I have this statement for example
>>
>> select MYFUNCTION(value),period from mytable order by period
>>
>> and I have run the empty table command prior to the data being loaded
>> into the table then the rows are passed into the CustomFunction method
>> from top to bottom correctly according to the order by clause.
>>
>> if I just execute "delete from mytable" then populate the rows, then run
>> the above SQL, rows are passed in in reverse order, ignoring the order by
>> clause. This is a problem as I am accumulating values and DBISAM is
>> reversing the order.
>>
>> Least im pretty sure thats whats happening as I can replicate this
>> behaviour in my application.
>>
>> Thanks
>> Clive.
>>
>>
>
>

Wed, Feb 1 2006 8:07 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Clive,

<< I have a problem with custom functions and the order in which DBISAM is
passing in the rows. >>

DBISAM processes queries in physical record order, hence you can never
assume any particular order in terms of the parameters being passed to the
custom function.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Feb 1 2006 2:17 PMPermanent Link

"Clive"
I was trying to do a runsum then resets itself every 12 records, which works
fine if you use empty table.

How do I set the physical record order?.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:736BF515-1E52-482A-B7D2-479CC9FBCC89@news.elevatesoft.com...
> Clive,
>
> << I have a problem with custom functions and the order in which DBISAM is
> passing in the rows. >>
>
> DBISAM processes queries in physical record order, hence you can never
> assume any particular order in terms of the parameters being passed to the
> custom function.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Thu, Feb 2 2006 8:31 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Clive,

<< I was trying to do a runsum then resets itself every 12 records, which
works fine if you use empty table.

How do I set the physical record order?. >>

The only way is to optimize the table on a specific index.  That will ensure
that the physical order matches the desired index order.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Feb 2 2006 5:55 PMPermanent Link

"Clive"
I had a group by in the SQL statement, which is not actually required so I
tried removing it and the ordering has sorted itself out and the rows are
passed in the correct order to my RUNSUM1 custom function.

However the physical order did match the order by clause so it should have
worked. and did even with the group by if you did an EMPTY table prior to
population instead of a DELETE

I have created a testapp to demonstrate the issue and emailed to the support
account

Thanks
Clive.


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:C9986871-B60E-4002-BE7B-326DC4116757@news.elevatesoft.com...
> Clive,
>
> << I was trying to do a runsum then resets itself every 12 records, which
> works fine if you use empty table.
>
> How do I set the physical record order?. >>
>
> The only way is to optimize the table on a specific index.  That will
> ensure that the physical order matches the desired index order.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Fri, Feb 3 2006 5:28 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Clive,

<< I had a group by in the SQL statement, which is not actually required so
I  tried removing it and the ordering has sorted itself out and the rows are
passed in the correct order to my RUNSUM1 custom function.

However the physical order did match the order by clause so it should have
worked. and did even with the group by if you did an EMPTY table prior to
population instead of a DELETE >>

DBISAM processes the records in various orders, depending upon what
available indexes are present, whether there is GROUP BY or ORDER BY, etc.
You really can't rely on them being in a given order unless you optimize the
table on a given index, and then run a straight SQL statement with no GROUP
BY, ORDER BY, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Feb 4 2006 2:02 AMPermanent Link

"Clive"
Why does it matter how I cleared the table down?. Does DELETE somehow make
the primary key invalid until EMTPY is called?.
It seems very strange behaviour to me, and causes a big problem for me due
to the inconsistency.

Did you get the sample app I sent. Doesnt that seem strange to you?


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:B630D2E6-CE20-44D1-92C0-283311829C94@news.elevatesoft.com...
> Clive,
>
> << I had a group by in the SQL statement, which is not actually required
> so I  tried removing it and the ordering has sorted itself out and the
> rows are passed in the correct order to my RUNSUM1 custom function.
>
> However the physical order did match the order by clause so it should have
> worked. and did even with the group by if you did an EMPTY table prior to
> population instead of a DELETE >>
>
> DBISAM processes the records in various orders, depending upon what
> available indexes are present, whether there is GROUP BY or ORDER BY, etc.
> You really can't rely on them being in a given order unless you optimize
> the table on a given index, and then run a straight SQL statement with no
> GROUP BY, ORDER BY, etc.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Sat, Feb 4 2006 2:03 AMPermanent Link

"Clive"
Why does it matter how I cleared the table down?. Does DELETE somehow make
the primary key invalid until EMTPY is called?.
Im trying not to harp on... but It seems very strange behaviour to me, and
creates a problem due
to the inconsistency.

Did you get the sample app I sent. Doesnt that seem strange to you?


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:B630D2E6-CE20-44D1-92C0-283311829C94@news.elevatesoft.com...
> Clive,
>
> << I had a group by in the SQL statement, which is not actually required
> so I  tried removing it and the ordering has sorted itself out and the
> rows are passed in the correct order to my RUNSUM1 custom function.
>
> However the physical order did match the order by clause so it should have
> worked. and did even with the group by if you did an EMPTY table prior to
> population instead of a DELETE >>
>
> DBISAM processes the records in various orders, depending upon what
> available indexes are present, whether there is GROUP BY or ORDER BY, etc.
> You really can't rely on them being in a given order unless you optimize
> the table on a given index, and then run a straight SQL statement with no
> GROUP BY, ORDER BY, etc.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>


Page 1 of 2Next Page »
Jump to Page:  1 2
Image