Обсуждение: Can I getting a unique ID from a select
I am using hibernate, using a view like a read only table and I need a primary key each time a select is issued. So in Oracle terms this might work, though I am skeptical that Hibernate is going to return a cached result. create view myview as select rownum, t1.field, t2.field from tableOne t1, tableTwo t2 where t1.key = t2.fkey select * from myview But what I really need is select makemeauniquekey, t1.field, t2.field ... Maybe there is no way I think... incrementing a sequence per select is untenable.
In response to "Timasmith" <timasmith@hotmail.com>: > I am using hibernate, using a view like a read only table and I need a > primary key each time a select is issued. > > So in Oracle terms this might work, though I am skeptical that > Hibernate is going to return a cached result. > > create view myview as > select rownum, t1.field, t2.field > from tableOne t1, tableTwo t2 > where t1.key = t2.fkey > > select * from myview > > But what I really need is > > select makemeauniquekey, t1.field, t2.field > ... > > Maybe there is no way I think... incrementing a sequence per select > is untenable. Create a sequence and use nextval(). -- Bill Moran Collaborative Fusion Inc. wmoran@collaborativefusion.com Phone: 412-422-3463x4023 **************************************************************** IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ****************************************************************
On Thu, Mar 01, 2007 at 06:16:02 -0800, Timasmith <timasmith@hotmail.com> wrote: > I am using hibernate, using a view like a read only table and I need a > primary key each time a select is issued. > > create view myview as > select rownum, t1.field, t2.field > from tableOne t1, tableTwo t2 > where t1.key = t2.fkey > > select * from myview > > But what I really need is > > select makemeauniquekey, t1.field, t2.field > ... Is there some reason you can't use the join key?
On Sat, Mar 03, 2007 at 18:12:19 -0600, Bruno Wolff III <bruno@wolff.to> wrote: > On Thu, Mar 01, 2007 at 06:16:02 -0800, > Timasmith <timasmith@hotmail.com> wrote: > > I am using hibernate, using a view like a read only table and I need a > > primary key each time a select is issued. > > > > create view myview as > > select rownum, t1.field, t2.field > > from tableOne t1, tableTwo t2 > > where t1.key = t2.fkey > > > > select * from myview > > > > But what I really need is > > > > select makemeauniquekey, t1.field, t2.field > > ... > > Is there some reason you can't use the join key? To expand on this, if you are joining on fields that will return only one record for each value, you should still be able to make a primary key for the returned records using a combination of the primary keys of both records being joined. If hibernate only works with primary keys consisting of one column, than you can create a new field using a function of the primary keys of the records being joined.
On Mar 3, 7:12 pm, b...@wolff.to (Bruno Wolff III) wrote: > On Thu, Mar 01, 2007 at 06:16:02 -0800, > Timasmith<timasm...@hotmail.com> wrote: > > > I am using hibernate, using a view like a read only table and I need a > > primary key each time a select is issued. > > > create view myview as > > select rownum, t1.field, t2.field > > from tableOne t1, tableTwo t2 > > where t1.key = t2.fkey > > > select * from myview > > > But what I really need is > > > select makemeauniquekey, t1.field, t2.field > > ... > > Is there some reason you can't use the join key? > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ Never heard of a 'join key' but that sounds very promising. How do I select it?
On Sat, Mar 03, 2007 at 16:46:45 -0800, Timasmith <timasmith@hotmail.com> wrote: > On Mar 3, 7:12 pm, b...@wolff.to (Bruno Wolff III) wrote: > > On Thu, Mar 01, 2007 at 06:16:02 -0800, > > Timasmith<timasm...@hotmail.com> wrote: > > > > > create view myview as > > > select rownum, t1.field, t2.field > > > from tableOne t1, tableTwo t2 > > > where t1.key = t2.fkey > > > Never heard of a 'join key' but that sounds very promising. How do I > select it? > The join key would be t1.key or t2.fkey from your example. However there may be multiple rows returned with the same value depending on what you are joining. If that is the case you, should be able to use the primary keys of the underlying tables to make a new candidate key for the joined rows.
On Mar 5, 3:35 am, b...@wolff.to (Bruno Wolff III) wrote: > On Sat, Mar 03, 2007 at 16:46:45 -0800, > Timasmith<timasm...@hotmail.com> wrote: > > > On Mar 3, 7:12 pm, b...@wolff.to (Bruno Wolff III) wrote: > > > On Thu, Mar 01, 2007 at 06:16:02 -0800, > > > Timasmith<timasm...@hotmail.com> wrote: > > > > > create view myview as > > > > select rownum, t1.field, t2.field > > > > from tableOne t1, tableTwo t2 > > > > where t1.key = t2.fkey > > > Never heard of a 'join key' but that sounds very promising. How do I > > select it? > > The join key would be t1.key or t2.fkey from your example. However there > may be multiple rows returned with the same value depending on what you > are joining. If that is the case you, should be able to use the primary > keys of the underlying tables to make a new candidate key for the joined > rows. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend Multiple rows with the same key renders Hibernate useless as it caches the 'row object' and then returns the first row every time for that object. I think the sequence will work though, in reflection I guess it would as fast as pulling another field, and with the numbers would be a very long time before getting duplicates - even if you had thousands of users, returning 100s of rows every few minutes (I think...).
On Mon, Mar 05, 2007 at 17:07:25 -0800, Timasmith <timasmith@hotmail.com> wrote: > > > > > > > create view myview as > > > > > select rownum, t1.field, t2.field > > > > > from tableOne t1, tableTwo t2 > > > > > where t1.key = t2.fkey > > Multiple rows with the same key renders Hibernate useless as it caches > the 'row object' and then returns the first row every time for that > object. > > I think the sequence will work though, in reflection I guess it would > as fast as pulling another field, and with the numbers would be a very > long time before getting duplicates - even if you had thousands of > users, returning 100s of rows every few minutes (I think...). Based on the naming (t1.key vs t2.fkey) it looks like you may have a one to many relationship. If so, can't you just bring in the primary key from t2, as under the above assumption there will be only one matching row from t1?