-
1. Re: Adding autoincrement/hash column to view
rareddy Feb 18, 2014 6:23 PM (in response to gioppoluca)auto increment on view will not work, as there is no where to store in Teiid and return it consistently next time or time after that. Hashing might work, you can define hashing UDF based on some other key in the view that you know going to be consistent always, and wrap that resultant column in a PK.
Ramesh..
-
2. Re: Adding autoincrement/hash column to view
shawkins Feb 18, 2014 6:30 PM (in response to gioppoluca)XMLTABLE allows a FOR ORDINALITY column to assign each row a number, which could be added to TEXTTABLE. There is also the ROWNUMBER() analytical function if a number should be based upon an ordering (although performance may be an issue unless materialized). We don't have a built-in hash function for this purpose and care would need to be taken in a UDF to ensure the probability of collisions is low for it to function properly as a pk.
-
3. Re: Adding autoincrement/hash column to view
rareddy Feb 19, 2014 8:52 AM (in response to shawkins)Steve,
When ORDINALITY or ROWNUMBER is used would user able to issue a follow on query to select a particular row from the resultset?
Ramesh..
-
4. Re: Adding autoincrement/hash column to view
shawkins Feb 19, 2014 10:03 AM (in response to rareddy)Ramesh,
Yes each provides a unique identifier for a row. FOR ORDINALITY is better performing in that it is associated with the results prepositionally as it loads. ROWNUMBER requires the entire result to be sorted regardless. However neither is entirely stable - if the data set changes the ordinal number will likely change, and unless the rownumber is over something like a create timestamp as data changes so too will the numbering.
Steve