I would usually tend to base complex queries on stored procedures and would then use local temporary tables within in the procedure to "collect data". That works with the DECLARE LOCAL TEMPORARY TABLE statement - recommended with the NOT TRANSACTIONAL clause.

These temporary tables are connection-specific. When declared in stored procedures, they do only exist during the block/compound statement they're declared in. In such cases, these temporary table are not visible for users at all.

When you are looking for permanently accessibe temporary tables, use the CREATE GLOBAL TEMPORARY TABLE statement. Unless you use the SHARE BY ALL clause, each connection can only access its own data. You would create such a table once (just like a base table), and each user can make use of it. Note when using them, it's important to use the appropriate setting of the ON COMMIT clause - just make sure the data you report logic adds are still there when they are selected: By default, the rows of a temporary table are deleted on COMMIT, so an intermediate COMMIT might empty the temporary table for that connection. (Yep, been there, too.)

From the SA Docs

Tables created by preceding the table name in a CREATE TABLE statement with a pound sign (#) are declared temporary tables, which are available only in the current connection. See DECLARE LOCAL TEMPORARY TABLE statement.

It seams that you did something wrong with your tests. I don't know Powerbuilder so a quick test with 2 iSql Connections will prove the documentation.

In Connection 1

create table #Test( t int );
insert into #test values (1);
select * from #Test;

In Connection 2

create table #Test( t int );
insert into #test values (2);
select * from #Test;

The result is that each select only shows one row.

HTH

Volker said it, but he did not SHOUT it: CREATE GLOBAL TEMPORARY TABLE T ( columns ) NOT TRANSACTIONAL

http://dcx.sybase.com/index.html#1201/en/dbreference/create-table-statement.html

You CREATE it once, and the schema is global and permanent, and it behaves exactly like a regular permanent base table EXCEPT the data is not global or persistent. To each connection, the table is empty when the connection starts. The connection can only SELECT FROM T and UPDATE T and DELETE T what it INSERTs INTO t itself. Other connections cannot see that connection's data. When the connection ends, its data goes away.

It is a wonderful invention, exactly what you are asking for: "can be shared by multiple queries but each user would only see their own records".

If you come from the Transact SQL world, think of it as a CREATE TABLE with @@SPID as part of the primary key, and all the necessary code to purge rows matching on @@SPID when the connection starts and end, and an extra predicate in every WHERE clause to select rows matching on @@SPID... which is exactly what folks have to do when they use ASE ("those poor people!" - Galaxy Quest).

Another attempt to improve the performance of complex joins is to materialize the result set (or parts of that).

That's done with materialized views.