Creating ID field at runtime
So I have a grid which is grouping records based on whether certain fields have filled in or not. The problem though is that certain records can fall into multiple categories. So, what I'm doing is using a SQL union to re-query and tag one instance of the record with a label ("Bid") and the second instance with another label ("Sale") and base grouping off of that.
Anyways, up to this point everything works fine an dandy. However, now I have the problem of not having a 'unique' key field, since a single record might come up within two or three separate groupings. The best solution I can think of (though that may not be the case) is to just created a separate key field column at runtime which has a seed of 1 and auto increments from there, but, I'm not sure how to code it, or if is possibly a feature built into delphi which can do this for me.
Any help would be appreciated. :)
Anyways, up to this point everything works fine an dandy. However, now I have the problem of not having a 'unique' key field, since a single record might come up within two or three separate groupings. The best solution I can think of (though that may not be the case) is to just created a separate key field column at runtime which has a seed of 1 and auto increments from there, but, I'm not sure how to code it, or if is possibly a feature built into delphi which can do this for me.
Any help would be appreciated. :)
ASKER
Sorry, I guess I should have clarified myself a little more.
This field I want to create would be created at runtime (Formshow) and but NOT be saved to a table, simply used as a temporary key field since my actual ID field is no longer unique--basically a virtual field which is thrown away once the form is closed, since I can't think of any other way to set a key field...
This field I want to create would be created at runtime (Formshow) and but NOT be saved to a table, simply used as a temporary key field since my actual ID field is no longer unique--basically a virtual field which is thrown away once the form is closed, since I can't think of any other way to set a key field...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Of course, you may also be able to create a uniqe "key"" field by adding a DataSource column to each of the queries with the "Bid" datasource flagged as B and the "Sales data source flagged as S. Having done that, you can use the unique ID from each of the sources coupled with the DataSource field to provide the unique "Key". This would let you provide a means for uniquely identifying the rows and even, if desired, updating (or otherwise modifying) the selected row without much of a change in the queries or in the way you are handling the data.
As Geert said, though, a lot of the answer may depend on the target RDBMS. In some cases, knowing that bit of information ccould provide much cleaner answers. (For instance, if it is SQL server 2005/2008, the UNIONed data could pass through one last, outer SELECT that includes a RoW_NUMBER function that assigns a unique ROW_NUMBER ID to each row. ;-)
As Geert said, though, a lot of the answer may depend on the target RDBMS. In some cases, knowing that bit of information ccould provide much cleaner answers. (For instance, if it is SQL server 2005/2008, the UNIONed data could pass through one last, outer SELECT that includes a RoW_NUMBER function that assigns a unique ROW_NUMBER ID to each row. ;-)
for instance, with oracle there is analytical functions
for this you could use rank() over or number()
probably using lead() over (or lag() over) too
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407