I have a SQL Anywhere 7.0 Database, have a table Product_detail which contains Column Name Datatype width Null Default batch_id char(10) No None prod_id Integer No None employee_id Integer No None quantity Integer No None price Numeric (14, 2) No None Its primary key is batch_id I have a Freeform Datawindow with this table I want to provide the first row, next row, prior row and last row How to get the first , next, prior and last row record from the above table? If the primary key datatype is integer, I know how to get first, next, prior and last row. But here Primary column datatype is char(10) Any idea?
![]() |
0 |
![]() |
ORDER BY is the only method offered in ANSI SQL to control the ordering of the resultset. In the absence of ORDER BY, the resultset can be returned in any order that the engine chooses. Changes in the optimizer in recent versions of SQL Anywhere make the resultset even less deterministic. If your are not using ORDER BY when your primary key is integer, it is only by happen chance that you are getting consistent resultset ordering. You should not rely on that resultset ordering will be maintained. ghkumartklm@yahoo.com wrote: > I have a SQL Anywhere 7.0 Database, have a table > Product_detail which contains > Column Name Datatype width Null > Default > batch_id char(10) No None > prod_id Integer No None > employee_id Integer No None > quantity Integer No None > price Numeric (14, 2) No None > > Its primary key is batch_id > I have a Freeform Datawindow with this table > I want to provide the first row, next row, prior row and > last row > How to get the first , next, prior and last row record from > the above tabl > If the primar key datatype is integer, I know how to get > first, next, prior and last row. But here > Primary column datatype is char(10) > Any idea? -- Chris Keating Sybase Adaptive Server Anywhere Professional Version 8 ***************************************************************************** Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition =and try out the market-leading database for mobile, embedded and small to medium sized business environments for free! http://www.ianywhere.com/promos/deved/index.html ***************************************************************************** iAnywhere Solutions http://www.iAnywhere.com ** Please only post to the newsgroup ** Whitepapers can be found at http://www.iAnywhere.com/developer ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm ** Use Case Express to report bugs http://case-express.sybase.com *****************************************************************************
![]() |
0 |
![]() |
If I use select * from prod_details order by batch_id ASC, shall i get the result ? > ORDER BY is the only method offered in ANSI SQL to control > the ordering of the resultset. In the absence of ORDER BY > , the resultset can be returned in any order that the > engine chooses. Changes in the optimizer in recent > versions of SQL Anywhere make the resultset even less > deterministic. > > If your are not using ORDER BY when your primary key is > integer, it is only by happen chance that you are getting > consistent resultset ordering. You should not rely on > that resultset ordering will be maintained. > > ghkumartklm@yahoo.com wrote: > > I have a SQL Anywhere 7.0 Database, have a table > > Product_detail which contains > > Column Name Datatype width Null > > Default > > batch_id char(10) No > > None prod_id Integer No > > None employee_id Integer No > > None quantity Integer No > > None price Numeric (14, 2) No > > None > > Its primary key is batch_id > > I have a Freeform Datawindow with this table > > I want to provide the first row, next row, prior row and > > last row > > How to get the first , next, prior and last row record > > from the above tabl > > If the primar key datatype is integer, I know how to get > > first, next, prior and last row. But here > > Primary column datatype is char(10) > > Any idea? > > -- > > Chris Keating > Sybase Adaptive Server Anywhere Professional Version 8 > > ********************************************************** > ******************* > > Sign up today for your copy of the SQL Anywhere Studio 9 > Developer Edition =and try out the market-leading > database for mobile, embedded and small to medium sized > business environments for free! > > http://www.ianywhere.com/promos/deved/index.html > > ********************************************************** > ******************* > > iAnywhere Solutions http://www.iAnywhere.com > > ** Please only post to the newsgroup > > ** Whitepapers can be found at > http://www.iAnywhere.com/developer ** EBFs can be found at > http://downloads.sybase.com/swx/sdmain.stm ** Use Case > Express to report bugs http://case-express.sybase.com > > ********************************************************** > *******************
![]() |
0 |
![]() |
You will get a resultset that is ordered by batch_id in ascending order. You can navigate that resultset in the same way that you navigate the resultset where the primary key is an integer. ghkumartklm@yahoo.com wrote: > If I use select * from prod_details order by batch_id ASC, > shall i get the result ? >> ORDER BY is the only method offered in ANSI SQL to control >> the ordering of the resultset. In the absence of ORDER BY >> , the resultset can be returned in any order that the >> engine chooses. Changes in the optimizer in recent >> versions of SQL Anywhere make the resultset even less >> deterministic. >> >> If your are not using ORDER BY when your primary key is >> integer, it is only by happen chance that you are getting >> consistent resultset ordering. You should not rely on >> that resultset ordering will be maintained. >> >> ghkumartklm@yahoo.com wrote: >>> I have a SQL Anywhere 7.0 Database, have a table >>> Product_detail which contains >>> Column Name Datatype width Null >>> Default >>> batch_id char(10) No >>> None prod_id Integer No >>> None employee_id Integer No >>> None quantity Integer No >>> None price Numeric (14, 2) No >>> None >>> Its primary key is batch_id >>> I have a Freeform Datawindow with this table >>> I want to provide the first row, next row, prior row and >>> last row >>> How to get the first , next, prior and last row record >>> from the above tabl >>> If the primar key datatype is integer, I know how to get >>> first, next, prior and last row. But here >>> Primary column datatype is char(10) >>> Any idea? >> -- >> >> Chris Keating >> Sybase Adaptive Server Anywhere Professional Version 8 >> >> ********************************************************** >> ******************* >> >> Sign up today for your copy of the SQL Anywhere Studio 9 >> Developer Edition =and try out the market-leading >> database for mobile, embedded and small to medium sized >> business environments for free! >> >> http://www.ianywhere.com/promos/deved/index.html >> >> ********************************************************** >> ******************* >> >> iAnywhere Solutions http://www.iAnywhere.com >> >> ** Please only post to the newsgroup >> >> ** Whitepapers can be found at >> http://www.iAnywhere.com/developer ** EBFs can be found at >> http://downloads.sybase.com/swx/sdmain.stm ** Use Case >> Express to report bugs http://case-express.sybase.com >> >> ********************************************************** >> ******************* -- Chris Keating Sybase Adaptive Server Anywhere Professional Version 8 ***************************************************************************** Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition =and try out the market-leading database for mobile, embedded and small to medium sized business environments for free! http://www.ianywhere.com/promos/deved/index.html ***************************************************************************** iAnywhere Solutions http://www.iAnywhere.com ** Please only post to the newsgroup ** Whitepapers can be found at http://www.iAnywhere.com/developer ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm ** Use Case Express to report bugs http://case-express.sybase.com *****************************************************************************
![]() |
0 |
![]() |
Hi Chris, The OP's Batch_id is char(10), so order by Batch_id can be tricky. Of the lenght of every value in this field is not equal, this could result to mistakes. eg.: Batch_id's "1","2","3","10","20" ordered would give this result: "1","10","2","20","3" The only soultion then would be filling up these values with "0", so the order become "01","02","03","10","20". Cheers, Pascal B. "Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> wrote in message news:45c1ea19$1@forums-1-dub... > You will get a resultset that is ordered by batch_id in ascending order. > You can navigate that resultset in the same way that you navigate the > resultset where the primary key is an integer. > > ghkumartklm@yahoo.com wrote: > > If I use select * from prod_details order by batch_id ASC, > > shall i get the result ? > >> ORDER BY is the only method offered in ANSI SQL to control > >> the ordering of the resultset. In the absence of ORDER BY > >> , the resultset can be returned in any order that the > >> engine chooses. Changes in the optimizer in recent > >> versions of SQL Anywhere make the resultset even less > >> deterministic. > >> > >> If your are not using ORDER BY when your primary key is > >> integer, it is only by happen chance that you are getting > >> consistent resultset ordering. You should not rely on > >> that resultset ordering will be maintained. > >> > >> ghkumartklm@yahoo.com wrote: > >>> I have a SQL Anywhere 7.0 Database, have a table > >>> Product_detail which contains > >>> Column Name Datatype width Null > >>> Default > >>> batch_id char(10) No > >>> None prod_id Integer No > >>> None employee_id Integer No > >>> None quantity Integer No > >>> None price Numeric (14, 2) No > >>> None > >>> Its primary key is batch_id > >>> I have a Freeform Datawindow with this table > >>> I want to provide the first row, next row, prior row and > >>> last row > >>> How to get the first , next, prior and last row record > >>> from the above tabl > >>> If the primar key datatype is integer, I know how to get > >>> first, next, prior and last row. But here > >>> Primary column datatype is char(10) > >>> Any idea? > >> -- > >> > >> Chris Keating > >> Sybase Adaptive Server Anywhere Professional Version 8 > >> > >> ********************************************************** > >> ******************* > >> > >> Sign up today for your copy of the SQL Anywhere Studio 9 > >> Developer Edition =and try out the market-leading > >> database for mobile, embedded and small to medium sized > >> business environments for free! > >> > >> http://www.ianywhere.com/promos/deved/index.html > >> > >> ********************************************************** > >> ******************* > >> > >> iAnywhere Solutions http://www.iAnywhere.com > >> > >> ** Please only post to the newsgroup > >> > >> ** Whitepapers can be found at > >> http://www.iAnywhere.com/developer ** EBFs can be found at > >> http://downloads.sybase.com/swx/sdmain.stm ** Use Case > >> Express to report bugs http://case-express.sybase.com > >> > >> ********************************************************** > >> ******************* > > -- > > Chris Keating > Sybase Adaptive Server Anywhere Professional Version 8 > > **************************************************************************** * > > Sign up today for your copy of the SQL Anywhere Studio 9 Developer > Edition =and try out the market-leading database for mobile, embedded > and small to medium sized business environments for free! > > http://www.ianywhere.com/promos/deved/index.html > > **************************************************************************** * > > iAnywhere Solutions http://www.iAnywhere.com > > ** Please only post to the newsgroup > > ** Whitepapers can be found at http://www.iAnywhere.com/developer > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm > ** Use Case Express to report bugs http://case-express.sybase.com > > **************************************************************************** *
![]() |
0 |
![]() |
Hi guys, Pardon me if I'm mistaken. But based on the initial question, I believe that what he wanted was to have the users traverse the records through a freeform DATAWINDOW (one record) and how he could do it. If this is just what he wanted, then dw.ScrollToRow(), dw.ScrollPriorRow() and dw.ScrollNextRow() is all that he needs to use. Sort order is a different topic that can easily be solved via ORDER BY in his SQL or by his Sort Order in the Datawindow. Arman > Hi Chris, > > The OP's Batch_id is char(10), so order by Batch_id can be > tricky. Of the lenght of every value in this field is not > equal, this could result to mistakes. > > eg.: Batch_id's "1","2","3","10","20" ordered would give > this result: "1","10","2","20","3" > > The only soultion then would be filling up these values > with "0", so the order become "01","02","03","10","20". > > Cheers, > Pascal B. > > > "Chris Keating(iAnywhere Solutions)" > <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> wrote > in message news:45c1ea19$1@forums-1-dub... > > You will get a resultset that is ordered by batch_id in > > ascending order. You can navigate that resultset in > > the same way that you navigate the resultset where the > primary key is an integer. > > > ghkumartklm@yahoo.com wrote: > > > If I use select * from prod_details order by batch_id > > > ASC, shall i get the result ? > > >> ORDER BY is the only method offered in ANSI SQL to > > control >> the ordering of the resultset. In the > > absence of ORDER BY >> , the resultset can be returned > > in any order that the >> engine chooses. Changes in the > > optimizer in recent >> versions of SQL Anywhere make > > the resultset even less >> deterministic. > > >> > > >> If your are not using ORDER BY when your primary key > > is >> integer, it is only by happen chance that you are > > getting >> consistent resultset ordering. You should > > not rely on >> that resultset ordering will be > > maintained. >> > > >> ghkumartklm@yahoo.com wrote: > > >>> I have a SQL Anywhere 7.0 Database, have a table > > >>> Product_detail which contains > > >>> Column Name Datatype width Null > > >>> Default > > >>> batch_id char(10) No > > >>> None prod_id Integer No > > >>> None employee_id Integer No > > >>> None quantity Integer > > No >>> None price Numeric (14, 2) > > No >>> None > > >>> Its primary key is batch_id > > >>> I have a Freeform Datawindow with this table > > >>> I want to provide the first row, next row, prior row > > and >>> last row > > >>> How to get the first , next, prior and last row > > record >>> from the above tabl > > >>> If the primar key datatype is integer, I know how to > > get >>> first, next, prior and last row. But here > > >>> Primary column datatype is char(10) > > >>> Any idea? > > >> -- > > >> > > >> Chris Keating > > >> Sybase Adaptive Server Anywhere Professional Version > > 8 >> > > >> > > > ********************************************************** > > >> ******************* >> > > >> Sign up today for your copy of the SQL Anywhere > > Studio 9 >> Developer Edition =and try out the > > market-leading >> database for mobile, embedded and > > small to medium sized >> business environments for free! > > >> > > >> http://www.ianywhere.com/promos/deved/index.html > > >> > > >> > > > ********************************************************** > > >> ******************* >> > > >> iAnywhere Solutions http://www.iAnywhere.com > > >> > > >> ** Please only post to the newsgroup > > >> > > >> ** Whitepapers can be found at > > >> http://www.iAnywhere.com/developer ** EBFs can be > > found at >> http://downloads.sybase.com/swx/sdmain.stm > > ** Use Case >> Express to report bugs > > http://case-express.sybase.com >> > > >> > > > ********************************************************** > >> ******************* > > > -- > > > > Chris Keating > > Sybase Adaptive Server Anywhere Professional Version 8 > > > > > ********************************************************** > ****************** * > > > > Sign up today for your copy of the SQL Anywhere Studio 9 > > Developer Edition =and try out the market-leading > > database for mobile, embedded and small to medium sized > business environments for free! > > > http://www.ianywhere.com/promos/deved/index.html > > > > > ********************************************************** > ****************** * > > > > iAnywhere Solutions http://www.iAnywhere.com > > > > ** Please only post to the newsgroup > > > > ** Whitepapers can be found at > > http://www.iAnywhere.com/developer ** EBFs can be found > > at http://downloads.sybase.com/swx/sdmain.stm ** Use > Case Express to report bugs http://case-express.sybase.com > > > > ********************************************************** > ****************** * > >
![]() |
0 |
![]() |
The initial question also indicated that he had this working already with integer. As Pascal correctly picked up, the issue is likely that the batch_id is storing the text equivalent of a number i.e., '1','2', '20', '30' and not 1,2,20,30. The sort rules for characters do not following the sort rules for numbers. As a result, storing numbers in a character field is fine provided sorting is not a requirement. If sorting is required, you must account for requirement either by padding values or other means (depending on the exact nature of the data). ArmanPorky wrote: > Hi guys, > > Pardon me if I'm mistaken. But based on the initial > question, I believe that what he wanted was to have the > users traverse the records through a freeform DATAWINDOW > (one record) and how he could do it. If this is just what he > wanted, then dw.ScrollToRow(), dw.ScrollPriorRow() and > dw.ScrollNextRow() is all that he needs to use. > > Sort order is a different topic that can easily be solved > via ORDER BY in his SQL or by his Sort Order in the > Datawindow. > > Arman > >> Hi Chris, >> >> The OP's Batch_id is char(10), so order by Batch_id can be >> tricky. Of the lenght of every value in this field is not >> equal, this could result to mistakes. >> >> eg.: Batch_id's "1","2","3","10","20" ordered would give >> this result: "1","10","2","20","3" >> >> The only soultion then would be filling up these values >> with "0", so the order become "01","02","03","10","20". >> >> Cheers, >> Pascal B. >> >> >> "Chris Keating(iAnywhere Solutions)" >> <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> wrote >> in message news:45c1ea19$1@forums-1-dub... >>> You will get a resultset that is ordered by batch_id in >>> ascending order. You can navigate that resultset in >>> the same way that you navigate the resultset where the >> primary key is an integer. > >>> ghkumartklm@yahoo.com wrote: >>>> If I use select * from prod_details order by batch_id >>>> ASC, shall i get the result ? >>>>> ORDER BY is the only method offered in ANSI SQL to >>> control >> the ordering of the resultset. In the >>> absence of ORDER BY >> , the resultset can be returned >>> in any order that the >> engine chooses. Changes in the >>> optimizer in recent >> versions of SQL Anywhere make >>> the resultset even less >> deterministic. >>>>> If your are not using ORDER BY when your primary key >>> is >> integer, it is only by happen chance that you are >>> getting >> consistent resultset ordering. You should >>> not rely on >> that resultset ordering will be >>> maintained. >> >>>>> ghkumartklm@yahoo.com wrote: >>>>>> I have a SQL Anywhere 7.0 Database, have a table >>>>>> Product_detail which contains >>>>>> Column Name Datatype width Null >>>>>> Default >>>>>> batch_id char(10) No >>>>>> None prod_id Integer No >>>>>> None employee_id Integer No >>>>>> None quantity Integer >>> No >>> None price Numeric (14, 2) >>> No >>> None >>>>>> Its primary key is batch_id >>>>>> I have a Freeform Datawindow with this table >>>>>> I want to provide the first row, next row, prior row >>> and >>> last row >>>>>> How to get the first , next, prior and last row >>> record >>> from the above tabl >>>>>> If the primar key datatype is integer, I know how to >>> get >>> first, next, prior and last row. But here >>>>>> Primary column datatype is char(10) >>>>>> Any idea? >>>>> -- >>>>> >>>>> Chris Keating >>>>> Sybase Adaptive Server Anywhere Professional Version >>> 8 >> >> ********************************************************** >>>>> ******************* >> >>>>> Sign up today for your copy of the SQL Anywhere >>> Studio 9 >> Developer Edition =and try out the >>> market-leading >> database for mobile, embedded and >>> small to medium sized >> business environments for free! >>>>> http://www.ianywhere.com/promos/deved/index.html >>>>> >>>>> >> ********************************************************** >>>>> ******************* >> >>>>> iAnywhere Solutions http://www.iAnywhere.com >>>>> >>>>> ** Please only post to the newsgroup >>>>> >>>>> ** Whitepapers can be found at >>>>> http://www.iAnywhere.com/developer ** EBFs can be >>> found at >> http://downloads.sybase.com/swx/sdmain.stm >>> ** Use Case >> Express to report bugs >>> http://case-express.sybase.com >> >> ********************************************************** >>>> ******************* > >>> -- >>> >>> Chris Keating >>> Sybase Adaptive Server Anywhere Professional Version 8 >>> >>> >> ********************************************************** >> ****************** * >>> Sign up today for your copy of the SQL Anywhere Studio 9 >>> Developer Edition =and try out the market-leading >>> database for mobile, embedded and small to medium sized >> business environments for free! > >>> http://www.ianywhere.com/promos/deved/index.html >>> >>> >> ********************************************************** >> ****************** * >>> iAnywhere Solutions http://www.iAnywhere.com >>> >>> ** Please only post to the newsgroup >>> >>> ** Whitepapers can be found at >>> http://www.iAnywhere.com/developer ** EBFs can be found >>> at http://downloads.sybase.com/swx/sdmain.stm ** Use >> Case Express to report bugs http://case-express.sybase.com >> ********************************************************** >> ****************** * >> >> -- Chris Keating Sybase Adaptive Server Anywhere Professional Version 8 ***************************************************************************** Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition =and try out the market-leading database for mobile, embedded and small to medium sized business environments for free! http://www.ianywhere.com/promos/deved/index.html ***************************************************************************** iAnywhere Solutions http://www.iAnywhere.com ** Please only post to the newsgroup ** Whitepapers can be found at http://www.iAnywhere.com/developer ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm ** Use Case Express to report bugs http://case-express.sybase.com *****************************************************************************
![]() |
0 |
![]() |
For the integer datatype , i used Count(*) function to find the highest value and decrease 1 to get prior record , but for batch_id datatype is char(10).its values are DD5060, SP100, 150 like this. It varies and not in a specific order. I want to display the first record, prior record, next record and last record using menue or buttons in a freeform datawindow . Order by clause is not applied because the freeform datawindow's retrieval argument is :batchid Any idea? > The initial question also indicated that he had this > working already with integer. > > As Pascal correctly picked up, the issue is likely that > the batch_id is storing the text equivalent of a number > i.e., '1','2', '20', '30' and not 1,2,20,30. The sort > rules for characters do not following the sort rules for > numbers. As a result, storing numbers in a character field > is fine provided sorting is not a requirement. If sorting > is required, you must account for requirement either by > padding values or other means (depending on the exact > nature of the data). > > ArmanPorky wrote: > > Hi guys, > > > > Pardon me if I'm mistaken. But based on the initial > > question, I believe that what he wanted was to have the > > users traverse the records through a freeform DATAWINDOW > > (one record) and how he could do it. If this is just > > what he wanted, then dw.ScrollToRow(), > > dw.ScrollPriorRow() and dw.ScrollNextRow() is all that > > he needs to use. > > Sort order is a different topic that can easily be > > solved via ORDER BY in his SQL or by his Sort Order in > > the Datawindow. > > > > Arman > > > >> Hi Chris, > >> > >> The OP's Batch_id is char(10), so order by Batch_id can > be >> tricky. Of the lenght of every value in this field > is not >> equal, this could result to mistakes. > >> > >> eg.: Batch_id's "1","2","3","10","20" ordered would > give >> this result: "1","10","2","20","3" > >> > >> The only soultion then would be filling up these values > >> with "0", so the order become "01","02","03","10","20". > >> > >> Cheers, > >> Pascal B. > >> > >> > >> "Chris Keating(iAnywhere Solutions)" > >> <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> > wrote >> in message news:45c1ea19$1@forums-1-dub... > >>> You will get a resultset that is ordered by batch_id > in >>> ascending order. You can navigate that resultset > in >>> the same way that you navigate the resultset where > the >> primary key is an integer. > > >>> ghkumartklm@yahoo.com wrote: > >>>> If I use select * from prod_details order by batch_id > >>>> ASC, shall i get the result ? > >>>>> ORDER BY is the only method offered in ANSI SQL to > >>> control >> the ordering of the resultset. In the > >>> absence of ORDER BY >> , the resultset can be > returned >>> in any order that the >> engine chooses. > Changes in the >>> optimizer in recent >> versions of SQL > Anywhere make >>> the resultset even less >> > deterministic. >>>>> If your are not using ORDER BY when > your primary key >>> is >> integer, it is only by happen > chance that you are >>> getting >> consistent resultset > ordering. You should >>> not rely on >> that resultset > ordering will be >>> maintained. >> > >>>>> ghkumartklm@yahoo.com wrote: > >>>>>> I have a SQL Anywhere 7.0 Database, have a table > >>>>>> Product_detail which contains > >>>>>> Column Name Datatype width Null > >>>>>> Default > >>>>>> batch_id char(10) No > >>>>>> None prod_id Integer No > >>>>>> None employee_id Integer > No >>>>>> None quantity Integer > >>> No >>> None price Numeric (14, 2) > >>> No >>> None > >>>>>> Its primary key is batch_id > >>>>>> I have a Freeform Datawindow with this table > >>>>>> I want to provide the first row, next row, prior > row >>> and >>> last row > >>>>>> How to get the first , next, prior and last row > >>> record >>> from the above tabl > >>>>>> If the primar key datatype is integer, I know how > to >>> get >>> first, next, prior and last row. But here > >>>>>> Primary column datatype is char(10) > >>>>>> Any idea? > >>>>> -- > >>>>> > >>>>> Chris Keating > >>>>> Sybase Adaptive Server Anywhere Professional Version > >>> 8 >> > >> > ********************************************************** > >>>>> ******************* >> >>>>> Sign up today for your > copy of the SQL Anywhere >>> Studio 9 >> Developer > Edition =and try out the >>> market-leading >> database > for mobile, embedded and >>> small to medium sized >> > business environments for free! >>>>> > http://www.ianywhere.com/promos/deved/index.html >>>>> > >>>>> > >> > ********************************************************** > >>>>> ******************* >> >>>>> iAnywhere Solutions > http://www.iAnywhere.com >>>>> > >>>>> ** Please only post to the newsgroup > >>>>> > >>>>> ** Whitepapers can be found at > >>>>> http://www.iAnywhere.com/developer ** EBFs can be > >>> found at >> http://downloads.sybase.com/swx/sdmain.stm > >>> ** Use Case >> Express to report bugs > >>> http://case-express.sybase.com >> > >> > ********************************************************** > >>>> ******************* > >>> -- > >>> > >>> Chris Keating > >>> Sybase Adaptive Server Anywhere Professional Version 8 > >>> > >>> > >> > ********************************************************** > >> ****************** * >>> Sign up today for your copy of > the SQL Anywhere Studio 9 >>> Developer Edition =and try > out the market-leading >>> database for mobile, embedded > and small to medium sized >> business environments for > free! > >>> > http://www.ianywhere.com/promos/deved/index.html >>> > >>> > >> > ********************************************************** > >> ****************** * >>> iAnywhere Solutions > http://www.iAnywhere.com >>> > >>> ** Please only post to the newsgroup > >>> > >>> ** Whitepapers can be found at > >>> http://www.iAnywhere.com/developer ** EBFs can be > found >>> at http://downloads.sybase.com/swx/sdmain.stm ** > Use >> Case Express to report bugs > http://case-express.sybase.com >> > ********************************************************** > >> ****************** * >> > >> > > -- > > Chris Keating > Sybase Adaptive Server Anywhere Professional Version 8 > > ********************************************************** > ******************* > > Sign up today for your copy of the SQL Anywhere Studio 9 > Developer Edition =and try out the market-leading > database for mobile, embedded and small to medium sized > business environments for free! > > http://www.ianywhere.com/promos/deved/index.html > > ********************************************************** > ******************* > > iAnywhere Solutions http://www.iAnywhere.com > > ** Please only post to the newsgroup > > ** Whitepapers can be found at > http://www.iAnywhere.com/developer ** EBFs can be found at > http://downloads.sybase.com/swx/sdmain.stm ** Use Case > Express to report bugs http://case-express.sybase.com > > ********************************************************** > *******************
![]() |
0 |
![]() |
Hi, This is such a tedious task, retrieving one row only and providing First, Next, Prior and Last Row Navigation. Commonly, this is used for multi-row retrieves, but on your case its not. Are you expecting a very large resultset? If you are and your intention is to retrieve only one row, you have different options I could think of: 1) Have a datastore that retrieves all of the batch ids only. This is then your basis for navigation and retrieving the actual batch id details (in the freeform). 2) You can do the following: - Initially, retrieve the first and last batch id, using Min (batch_id) and Max (batch_id) in your SELECT. Store this in an instance variable. This will be your basis for retrieving the First and Last Rows. - You should also have another instance variable that will hold the current batch_id. - For Next Row, base on the current batch_id, issue SELECT Min (batch_id) INTO :ls_next_id FROM <yourtable> WHERE batch_id > :is_batch_id - For Previous Row, base on the current batch_id, issue SELECT Max (batch_id) INTO :ls_prev_id FROM <yourtable> WHERE batch_id < :is_batch_id * is_batch_id is the instance variable that holds your current row, you must assign the batch id of the current displayed row to this variable. * #2 will do a table scan every time if batch_id is not contained on any index (primary key or regular index). > For the integer datatype , i used Count(*) function to > find the highest value and decrease 1 to get prior record > , but for batch_id datatype is char(10).its values are > DD5060, SP100, 150 like this. It varies and not in a > specific order. I want to display the first record, prior > record, next record and last record using menue or buttons > in a freeform datawindow . Order by clause is not applied > because the freeform datawindow's retrieval argument is > :batchid Any idea? > > > The initial question also indicated that he had this > > working already with integer. > > > > As Pascal correctly picked up, the issue is likely that > > the batch_id is storing the text equivalent of a number > > i.e., '1','2', '20', '30' and not 1,2,20,30. The sort > > rules for characters do not following the sort rules > > for numbers. As a result, storing numbers in a character > > field is fine provided sorting is not a requirement. If > > sorting is required, you must account for requirement > > either by padding values or other means (depending on > > the exact nature of the data). > > > > ArmanPorky wrote: > > > Hi guys, > > > > > > Pardon me if I'm mistaken. But based on the initial > > > question, I believe that what he wanted was to have > > > the users traverse the records through a freeform > > > DATAWINDOW (one record) and how he could do it. If > > > this is just what he wanted, then dw.ScrollToRow(), > > > dw.ScrollPriorRow() and dw.ScrollNextRow() is all that > > > he needs to use. > > > Sort order is a different topic that can easily be > > > solved via ORDER BY in his SQL or by his Sort Order in > > > the Datawindow. > > > > > > Arman > > > > > >> Hi Chris, > > >> > > >> The OP's Batch_id is char(10), so order by Batch_id > > can be >> tricky. Of the lenght of every value in this > > field is not >> equal, this could result to mistakes. > > >> > > >> eg.: Batch_id's "1","2","3","10","20" ordered would > > give >> this result: "1","10","2","20","3" > > >> > > >> The only soultion then would be filling up these > > values >> with "0", so the order become "01","02","03" > > ,"10","20". >> > > >> Cheers, > > >> Pascal B. > > >> > > >> > > >> "Chris Keating(iAnywhere Solutions)" > > >> <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> > > wrote >> in message news:45c1ea19$1@forums-1-dub... > > >>> You will get a resultset that is ordered by batch_id > > in >>> ascending order. You can navigate that > > resultset in >>> the same way that you navigate the > > resultset where the >> primary key is an integer. > > > >>> ghkumartklm@yahoo.com wrote: > > >>>> If I use select * from prod_details order by > > batch_id >>>> ASC, shall i get the result ? > > >>>>> ORDER BY is the only method offered in ANSI SQL to > > >>> control >> the ordering of the resultset. In the > > >>> absence of ORDER BY >> , the resultset can be > > returned >>> in any order that the >> engine chooses. > > Changes in the >>> optimizer in recent >> versions of > > SQL Anywhere make >>> the resultset even less >> > > deterministic. >>>>> If your are not using ORDER BY when > > your primary key >>> is >> integer, it is only by > > happen chance that you are >>> getting >> consistent > > resultset ordering. You should >>> not rely on >> that > > resultset ordering will be >>> maintained. >> > > >>>>> ghkumartklm@yahoo.com wrote: > > >>>>>> I have a SQL Anywhere 7.0 Database, have a > > table >>>>>> Product_detail which contains > > >>>>>> Column Name Datatype width Null > > >>>>>> Default > > >>>>>> batch_id char(10) No > > >>>>>> None prod_id Integer > > No >>>>>> None employee_id Integer > > No >>>>>> None quantity Integer > > >>> No >>> None price Numeric (14, 2) > > >>> No >>> None > > >>>>>> Its primary key is batch_id > > >>>>>> I have a Freeform Datawindow with this table > > >>>>>> I want to provide the first row, next row, prior > > row >>> and >>> last row > > >>>>>> How to get the first , next, prior and last row > > >>> record >>> from the above tabl > > >>>>>> If the primar key datatype is integer, I know how > > to >>> get >>> first, next, prior and last row. But here > > >>>>>> Primary column datatype is char(10) > > >>>>>> Any idea? > > >>>>> -- > > >>>>> > > >>>>> Chris Keating > > >>>>> Sybase Adaptive Server Anywhere Professional > > Version >>> 8 >> > > >> > > > > > ********************************************************** > > >>>>> ******************* >> >>>>> Sign up today for > > your copy of the SQL Anywhere >>> Studio 9 >> Developer > > Edition =and try out the >>> market-leading >> database > > for mobile, embedded and >>> small to medium sized >> > > business environments for free! >>>>> > > http://www.ianywhere.com/promos/deved/index.html >>>>> > > >>>>> >> > > > > > ********************************************************** > > >>>>> ******************* >> >>>>> iAnywhere Solutions > > http://www.iAnywhere.com >>>>> >>>>> ** Please only post > > to the newsgroup >>>>> > > >>>>> ** Whitepapers can be found at > > >>>>> http://www.iAnywhere.com/developer ** EBFs can be > > >>> found at >> > > http://downloads.sybase.com/swx/sdmain.stm >>> ** Use > > Case >> Express to report bugs >>> > > http://case-express.sybase.com >> >> > > > > > ********************************************************** > > >>>> ******************* > >>> -- >>> > > >>> Chris Keating > > >>> Sybase Adaptive Server Anywhere Professional Version > > 8 >>> > > >>> > > >> > > > > > ********************************************************** > > >> ****************** * >>> Sign up today for your copy > of the SQL Anywhere Studio 9 >>> Developer Edition =and > > try out the market-leading >>> database for mobile, > > embedded and small to medium sized >> business > > environments for free! > >>> > > http://www.ianywhere.com/promos/deved/index.html >>> > > >>> > > >> > > > > > ********************************************************** > > >> ****************** * >>> iAnywhere Solutions > > http://www.iAnywhere.com >>> >>> ** Please only post to > > the newsgroup >>> > > >>> ** Whitepapers can be found at > > >>> http://www.iAnywhere.com/developer ** EBFs can be > > found >>> at http://downloads.sybase.com/swx/sdmain.stm > > ** Use >> Case Express to report bugs > > http://case-express.sybase.com >> > > > > > ********************************************************** > > >> ****************** * >> >> > > > > -- > > > > Chris Keating > > Sybase Adaptive Server Anywhere Professional Version 8 > > > > > > > ********************************************************** > ******************* > > > Sign up today for your copy of the SQL Anywhere Studio 9 > > Developer Edition =and try out the market-leading > > database for mobile, embedded and small to medium sized > > business environments for free! > > > > http://www.ianywhere.com/promos/deved/index.html > > > > > > > ********************************************************** > ******************* > > > iAnywhere Solutions http://www.iAnywhere.com > > > > ** Please only post to the newsgroup > > > > ** Whitepapers can be found at > > http://www.iAnywhere.com/developer ** EBFs can be found > > at http://downloads.sybase.com/swx/sdmain.stm ** Use > > Case Express to report bugs > http://case-express.sybase.com > > > > > > ********************************************************** > *******************
![]() |
0 |
![]() |
What you want is more recent SQL Anywhere software that supports the following SELECT TOP n START AT x If you *must* access the rows this way, consider 1) using a CURSOR --or-- 2) retrieve into a datastore (with the dwo using Rows as Needed) the resultset and navigate the ds. --or-- 3)ASA7 also has NUMBER(*) that you can use for row numbered. I am reluctant to recommend its use as there are changes made in current SQL Anywhere versions that affect its use. You can use number(*) to impart a row ordering value in the resultset. ghkumartklm@yahoo.com wrote: > For the integer datatype , i used Count(*) function to find > the highest value and decrease 1 to get prior record , but > for batch_id datatype is char(10).its values are DD5060, > SP100, 150 like this. It varies and not in a specific order. > I want to display the first record, prior record, next > record and last record using menue or buttons in a freeform > datawindow . Order by clause is not applied because the > freeform datawindow's retrieval argument is :batchid > Any idea? > >> The initial question also indicated that he had this >> working already with integer. >> >> As Pascal correctly picked up, the issue is likely that >> the batch_id is storing the text equivalent of a number >> i.e., '1','2', '20', '30' and not 1,2,20,30. The sort >> rules for characters do not following the sort rules for >> numbers. As a result, storing numbers in a character field >> is fine provided sorting is not a requirement. If sorting >> is required, you must account for requirement either by >> padding values or other means (depending on the exact >> nature of the data). >> >> ArmanPorky wrote: >>> Hi guys, >>> >>> Pardon me if I'm mistaken. But based on the initial >>> question, I believe that what he wanted was to have the >>> users traverse the records through a freeform DATAWINDOW >>> (one record) and how he could do it. If this is just >>> what he wanted, then dw.ScrollToRow(), >>> dw.ScrollPriorRow() and dw.ScrollNextRow() is all that >>> he needs to use. >>> Sort order is a different topic that can easily be >>> solved via ORDER BY in his SQL or by his Sort Order in >>> the Datawindow. >>> >>> Arman >>> >>>> Hi Chris, >>>> >>>> The OP's Batch_id is char(10), so order by Batch_id can >> be >> tricky. Of the lenght of every value in this field >> is not >> equal, this could result to mistakes. >>>> eg.: Batch_id's "1","2","3","10","20" ordered would >> give >> this result: "1","10","2","20","3" >>>> The only soultion then would be filling up these values >>>> with "0", so the order become "01","02","03","10","20". >>>> >>>> Cheers, >>>> Pascal B. >>>> >>>> >>>> "Chris Keating(iAnywhere Solutions)" >>>> <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com> >> wrote >> in message news:45c1ea19$1@forums-1-dub... >>>>> You will get a resultset that is ordered by batch_id >> in >>> ascending order. You can navigate that resultset >> in >>> the same way that you navigate the resultset where >> the >> primary key is an integer. > >>>>> ghkumartklm@yahoo.com wrote: >>>>>> If I use select * from prod_details order by batch_id >>>>>> ASC, shall i get the result ? >>>>>>> ORDER BY is the only method offered in ANSI SQL to >>>>> control >> the ordering of the resultset. In the >>>>> absence of ORDER BY >> , the resultset can be >> returned >>> in any order that the >> engine chooses. >> Changes in the >>> optimizer in recent >> versions of SQL >> Anywhere make >>> the resultset even less >> >> deterministic. >>>>> If your are not using ORDER BY when >> your primary key >>> is >> integer, it is only by happen >> chance that you are >>> getting >> consistent resultset >> ordering. You should >>> not rely on >> that resultset >> ordering will be >>> maintained. >> >>>>>>> ghkumartklm@yahoo.com wrote: >>>>>>>> I have a SQL Anywhere 7.0 Database, have a table >>>>>>>> Product_detail which contains >>>>>>>> Column Name Datatype width Null >>>>>>>> Default >>>>>>>> batch_id char(10) No >>>>>>>> None prod_id Integer No >>>>>>>> None employee_id Integer >> No >>>>>> None quantity Integer >>>>> No >>> None price Numeric (14, 2) >>>>> No >>> None >>>>>>>> Its primary key is batch_id >>>>>>>> I have a Freeform Datawindow with this table >>>>>>>> I want to provide the first row, next row, prior >> row >>> and >>> last row >>>>>>>> How to get the first , next, prior and last row >>>>> record >>> from the above tabl >>>>>>>> If the primar key datatype is integer, I know how >> to >>> get >>> first, next, prior and last row. But here >>>>>>>> Primary column datatype is char(10) >>>>>>>> Any idea? >>>>>>> -- >>>>>>> >>>>>>> Chris Keating >>>>>>> Sybase Adaptive Server Anywhere Professional Version >>>>> 8 >> >> ********************************************************** >>>>>>> ******************* >> >>>>> Sign up today for your >> copy of the SQL Anywhere >>> Studio 9 >> Developer >> Edition =and try out the >>> market-leading >> database >> for mobile, embedded and >>> small to medium sized >> >> business environments for free! >>>>> >> http://www.ianywhere.com/promos/deved/index.html >>>>> >> ********************************************************** >>>>>>> ******************* >> >>>>> iAnywhere Solutions >> http://www.iAnywhere.com >>>>> >>>>>>> ** Please only post to the newsgroup >>>>>>> >>>>>>> ** Whitepapers can be found at >>>>>>> http://www.iAnywhere.com/developer ** EBFs can be >>>>> found at >> http://downloads.sybase.com/swx/sdmain.stm >>>>> ** Use Case >> Express to report bugs >>>>> http://case-express.sybase.com >> >> ********************************************************** >>>>>> ******************* > >>> -- >>>>> Chris Keating >>>>> Sybase Adaptive Server Anywhere Professional Version 8 >>>>> >>>>> >> ********************************************************** >>>> ****************** * >>> Sign up today for your copy of >> the SQL Anywhere Studio 9 >>> Developer Edition =and try >> out the market-leading >>> database for mobile, embedded >> and small to medium sized >> business environments for >> free! > >>> >> http://www.ianywhere.com/promos/deved/index.html >>> >> ********************************************************** >>>> ****************** * >>> iAnywhere Solutions >> http://www.iAnywhere.com >>> >>>>> ** Please only post to the newsgroup >>>>> >>>>> ** Whitepapers can be found at >>>>> http://www.iAnywhere.com/developer ** EBFs can be >> found >>> at http://downloads.sybase.com/swx/sdmain.stm ** >> Use >> Case Express to report bugs >> http://case-express.sybase.com >> >> ********************************************************** >>>> ****************** * >> >>>> >> -- >> >> Chris Keating >> Sybase Adaptive Server Anywhere Professional Version 8 >> >> ********************************************************** >> ******************* >> >> Sign up today for your copy of the SQL Anywhere Studio 9 >> Developer Edition =and try out the market-leading >> database for mobile, embedded and small to medium sized >> business environments for free! >> >> http://www.ianywhere.com/promos/deved/index.html >> >> ********************************************************** >> ******************* >> >> iAnywhere Solutions http://www.iAnywhere.com >> >> ** Please only post to the newsgroup >> >> ** Whitepapers can be found at >> http://www.iAnywhere.com/developer ** EBFs can be found at >> http://downloads.sybase.com/swx/sdmain.stm ** Use Case >> Express to report bugs http://case-express.sybase.com >> >> ********************************************************** >> ******************* -- Chris Keating Sybase Adaptive Server Anywhere Professional Version 8 ***************************************************************************** Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition =and try out the market-leading database for mobile, embedded and small to medium sized business environments for free! http://www.ianywhere.com/promos/deved/index.html ***************************************************************************** iAnywhere Solutions http://www.iAnywhere.com ** Please only post to the newsgroup ** Whitepapers can be found at http://www.iAnywhere.com/developer ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm ** Use Case Express to report bugs http://case-express.sybase.com *****************************************************************************
![]() |
0 |
![]() |