Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 12 total |
Custom function problem |
Wed, Feb 1 2006 5:37 AM | Permanent 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 AM | Permanent 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 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent 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 AM | Permanent 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 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |