getting row number
robert crossley
Is there any way I can select row number from a table with a simple select
statement?
I want to populate a dialog list with a list of current items in a table,
and allow a user to select one item from the list and have the map zoom to
it and populate other dialog controls based on that record.
To do this I was going to:
1. select from the table with a record of RowID and ordered by a
description field in that table,
2. set up and populate a type variable that I can keep track of the
RowID with the description of the record, and use this to populate the
variable that is used in the dialog
3. When a user chooses an item from the dialog list, In a called
subroutine from the dialog control, use the chosen record to find the
original RowID and select that record from the original table by using
select * from table where RowID = x, and move the map to that location etc.
The problem is that I can’t seem to select RowID as a field.
If I use the select statement:
Select C_FARMCODE, RowID "ROWNUM" from T_FARM_CUR into SelCurFarms
But it only returns one column., the farmcode.
While I could almost do what I want to by using the farmcode as a reference,
but there will be times when this may be duplicated or have a number of
blanks.
I have an MI_PRINX in the table from SQL, but that will be zero until that
record is saved to the server and refreshed.
If I save a copy of the table and add and update a column, the rowid won’t
necessarily match the original table.
Any Ideas? I thought I used to be able to do this in earlier versions.
Rob.
-------------------------------------------
Robert Crossley
Managing Director
Agtrix P/L Australia
Far Southern Queensland Office:
Unit 6, 2 Bonanza Drive
Billinudgel NSW 2483
AUSTRALIA
Postal:
PO Box 63
New Brighton 2483
P: 61 (0) 2 6680 1309
M: 61 (0)419 718 642
E: rob...@agtrix.com
W: www.agtrix.com <http://www.agtrix.com/>
S: robertcrossley
28°30'14.81"S
153°31'41.79"E
Brisbane Office:
109 Milsom St
Cooparoo 4151
Queensland
P: 61 7 3843 3363
ssim...@baltometro.org
As much as we have all wanted to use RowID for an identity value over the
years, a number of factors ensure that it won't work.
For example, if you add a column, select from the table with an ORDER By
column, and update the column in the query with RowID, the values will match
the sorted table's order, not the physical order!
The SELECT behavior you see is by design. SQL and a strict physical row
order just aren't compatible. Select statements performed on most RDBMSes
result in a data firehose, and if you don't specify an order, they're
allowed to shoot you data in any (effectively random) order that suits them.
In the end, you need to populate and maintain your own identity column
yourself. Adding a column and updating with RowID is one way to initialize
an identity column, but after that happens, you need to make sure that
identity values are kept unique and initialized when new rows are added.
Since you have a custom dialog, presumably it has an "Add" button; if so,
the code behind that button could allocate a new identity value for the new
row. And remember that MapInfo doesn't have "UNIQUE" indexes, so you have
to do the checking every time someone uses your "add" button, and you have
to pray that no-one adds new rows to the table any other way.
The way to update an existing record in your table is:
1. Select from the table where the ID column contains the value you want.
2. Make sure the query has exactly one row.
3. Update the entire query (without reference to RowID). Since the query
has only one record, only one row is updated.
Fetch Rec n is presumably much faster (constant time) than a Select
statement (log n on an indexed column) but it only works with the base
table.
Another alternative is the C_FARMCODE column. You say that this column
sometimes contains duplicates and blanks, but this might just be "dirty"
data that should be cleaned up before anyone uses it for decision making.
That of course is your call.
Spencer
Hi all,
--
You received this message because you are subscribed to the Google Groups
"MapInfo-L" group.To post a message to this group, send email to
mapi...@googlegroups.com To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en
robert crossley
That will save me hours of frustration.
I think I will use the following tactic.
If the routine finds one selected record from that table, it will use that
record for its data and allow the user to edit the data for that record.
This will allow me to sue the dialog for the current selected record.
The list of farms that a user can use will only be those that have a valid
MI_PRINX, viz. only records that have been saved previously. I know this is
unique. If this control is used to trigger the configuration, I will select
that farm based on the MI_PRINX and do its stuff on the dialog for the
selected record.
For the records that do not have a valid MI_PRINX, you will have to have
selected one of these first if you want to edit its details. Perhaps I can
deal with up to one record that has a zero MI_PRINX.
I can look at tools that also select the last record and a couple of other
ways of selecting the data. This might be particularly relevant for records
that have no object (eg. when inserted by external systems).
R
a.j.pil...@talk21.com
From: robert crossley <rob...@wotzhere.com>
To: mapi...@googlegroups.com
Sent: Wednesday, 21 September, 2011 1:26:26
Subject: RE: [MI-L] getting row number
Thanks Spencer.
That will save me hours of frustration.
I think I will use the following tactic.
If the routine finds one selected record from that table, it will use that
record for its data and allow the user to edit the data for that record.
This will allow me to sue the dialog for the current selected record.
robert crossley
Sorry I am a member of DNA, National Dyslexic Association….
--
Lee Morris
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of robert crossley
Sent: 21 September 2011 08:59
To: mapi...@googlegroups.com
Bill Thoen
Sorry I am a member of DNA, National Dyslexic Association….
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of a.j.pil...@talk21.com
Sent: Wednesday, 21 September 2011 5:01 PM
To: mapi...@googlegroups.com
Subject: Re: [MI-L] getting row number
This will allow me to sue the dialog...
For how much?
Bill Thoen