can anyone tell me how to get a login id as part of a table reference

[Darren Reely]

Brian,

First let me say this sounds vaguely familiar. In fact I'm sure I've 
done this using Powerhouse a long long time ago.

Second, substring searches (beyond the simple STRING@) using Powerhouse 
are terrible. They are so slow I consider the ability as crippled. But I 
may have a helping solution for this particular case.

Consider the CACHE option. It has some limits like only being able to 
reference up to 255 records. But perhaps this will work for you.

If I were you I'd consider using a common table. This has an advantage 
that the developers now and in the future will not have to remember your 
  special table per user hack and the rules for using it. A disadvantage 
is that the screen will be responsible for keeping the table clean.

The table would have a repeating key to identify which screen process 
owns each row. Something like userid, process id and timestamp which 
would allow for multiple connections by one user and multiple users. You 
would create this key for each new search. To remove stale data, you'd 
call a special routine previous to any new searchs and in the EXIT 
procedure before leaving the screen. Perhaps a nightly routine would be 
used to remove old data that was not removed for some reason, such as a 
user disconnecting from a PC window.

You can add data to the table via Powerhouse code or a callable Oracle 
procedure. Since your performing substring searches, I prefer the Oracle 
solution which I've found is much faster. You would however have to 
convert the usual PH search string into something Oracle can handle. 
Possibly a special cursor could help the Powerhouse search speed. Either 
way, you'd perform this action in the POSTPATH procedure there by having 
the data ready for the FIND procedures.

Once the temporary data has been set up in the POSTPATH procedure, your 
FIND procedures should work against that table as you'd expect for any 
table.

Managing your row id could have a kink. What if the original data has 
changed, removed or appended? I'd build an appropriate key to that data 
into the temporary table and link to the original for display and 
possible editing. I don't see a need for sequence.nextval stuff. Simply 
create a counter starting at 1 and increment it for each record written 
to the temporary table.

If the row id needs to be in a sort order by some other data value, 
simply fetching the data in that order should do the trick.

As for my previous suggestion. Heed Peter's warning. I seem to remember 
something about this during a past review of some of the PH documentation.

You have a nice challenge. Good luck.

Darren