Hello,

How you do select * into temporary table ABC from mytable
Then do select from new created temporary table
Select * from ABC where something = something
This is very simple in many databases, but I can’t find a way to do it with EDB

Thank you

Linda,

> How you do select * into temporary table ABC from mytable
> Then do select from new created temporary table
> Select * from ABC where something = something
> This is very simple in many databases, but I can’t find a way to do it with EDB

Here a sample script:

SCRIPT
BEGIN

  DECLARE Result CURSOR WITH RETURN FOR Stmt;

  Execute Immediate 'Create Table ABC as select * from MyTable with data';

  PREPARE Stmt FROM
    'SELECT * from ABC where something =  ?';
  OPEN Result using 'something';

END

Uli

Linda,

If you want to do it with SQL, try:

CREATE TABLE NewTable (LIKE OldTable);
Insert into NewTable select * from OldTable where State='NY';

This does not create indexes or constraints for the new table so you would have to add those manually.

Barry

Correction (I misse temporary...):

Execute Immediate 'Create TEMPORARY Table ABC as select * from MyTable
with data';

Thank you all,

My understanding temp table is to make you life easier not more complex and more coding
This database requires more coding than any database I know, why so hard to accomplish simple task

Also I have 65 tables, I see over 200 files created, and possible the code will be triple

==================================


Uli Becker wrote:

Correction (I misse temporary...):

Execute Immediate 'Create TEMPORARY Table ABC as select * from MyTable
with data';

Linda

>My understanding temp table is to make you life easier not more complex and more coding

Sometimes you need to do things in a different way in different systems. I did wonder about your original question. Is a temporary table what you really need or would views be more appropriate?

>This database requires more coding than any database I know, why so hard to accomplish simple task

Can you provide us with examples please? Which databases and what functionality. It may be you're trying to do things in the same way as your old database and there are better approaches.

>Also I have 65 tables, I see over 200 files created,

Because ElevateDB like DBISAM uses the Windows file system rather than crating its own. Every table consists of two files, data & index, with a possible third file for BLOBs and CLOBs. In addition to that there are a configuration file, a log file, a catalog file and a lock file.

>and possible the code will be triple

I'm very doubtful about that happening. I've so far moved a number of substantial apps to ElevateDB and see no signs of that level of code bloat. If there had been I would be shouting at Tim VERY loudly.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Linda

>My understanding temp table is to make you life easier not more complex and more coding

Sometimes you need to do things in a different way in different systems. I did wonder about your original question. Is a temporary table what you really need or would views be more appropriate?

>This database requires more coding than any database I know, why so hard to accomplish simple task

Can you provide us with examples please? Which databases and what functionality. It may be you're trying to do things in the same way as your old database and there are better approaches.

>Also I have 65 tables, I see over 200 files created,

Because ElevateDB like DBISAM uses the Windows file system rather than crating its own. Every table consists of two files, data & index, with a possible third file for BLOBs and CLOBs. In addition to that there are a configuration file, a log file, a catalog file and a lock file.

>and possible the code will be triple

I'm very doubtful about that happening. I've so far moved a number of substantial apps to ElevateDB and see no signs of that level of code bloat. If there had been I would be shouting at Tim VERY loudly.

Roy Lambert [Team Elevate]

======================================================

Thank you,
Here is my situation, we have 3 list (columns) from 3 different tables, user can select any number of columns from any list, so we don’t know what they are selecting (Dynamic query), in this case we don’t know new table structure , (3 tables have inner join ).
Then we do another query to get some result and then do simple update to some values on the temp table to show the report as a view
We can do all that with sql server  without using any stored procedure or views.
My question if this doable with EDB , then how ?
So far I’m having many difficulties to move forward with database.
Thank you again guys

Linda


>Here is my situation, we have 3 list (columns) from 3 different tables, user can select any number of columns from any list, so we don’t know what they are selecting (Dynamic query), in this case we don’t know new table structure , (3 tables have inner join ).
>Then we do another query to get some result and then do simple update to some values on the temp table to show the report as a view
>We can do all that with sql server without using any stored procedure or views.
>My question if this doable with EDB , then how ?

What I can interpret from the info above is:

1. Users can pick a number of columns from a number of tables
2. You then generate SQL which involves INNER JOINs
3. You then do another query
4. You then alter the data in this second query result set somehow

So the fact that you want to alter the data means that you will need a temporary table or memory table. I assume you don't want to update the base data (you probably couldn't anyway). The fact that you want to run a query on the result from the original query means a second temporary table.

For some reason you don't want to use a stored procedure or view. Does your dislike of stored procedures extend to scripts as well?

How do you generate the two queries and the data update involved?

Can you show the code that you use to produce this now? It may mean nothing to me but there are a lot of other people on the website who will try and help.

Roy Lambert [Team Elevate]

<< Then we do another query to get some result and then do simple update to
some values on the temp table to show the report as a view
We can do all that with sql server  without using any stored procedure or
views. >>

How are you doing a multi-statement process without using a script or stored
procedure ?

<< My question if this doable with EDB , then how ? >>

It's done in the same manner that you're probably doing it with SQL Server,
apart from the initial SELECT syntax:

1) CREATE TEMPORARY TABLE xxx AS SELECT ...... instead of SELECT...INTO
2) UPDATE xxx ....
3) SELECT .... FROM xxx

In EDB, this can be done with a script or stored procedure, or you can
simply use a single TEDBQuery to execute all three statements in succession.
My guess is that you have the exact same options with SQL Server.

<< So far I’m having many difficulties to move forward with database. >>

EDB is not SQL Server, nor is it a SQL Server clone.  You're going to need
to get used to the fact that EDB does things differently in many cases, and
often according to the ANSI standard, which SQL Server may *not* be using.
The above CREATE TABLE AS example is just such a case.  The ANSI SQL 2003
standard is to use CREATE TABLE AS, not SELECT..INTO.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>The ANSI SQL 2003
>standard is to use CREATE TABLE AS, not SELECT..INTO.

But SELECT ... INTO ... is much nicer <vbg>

Roy