Adam


I can't test it but I've posted a demo project to the binaries.

Roy Lambert

Hi Roy,

Thanks for your help with this - I'll check it out.

Interesting note about Memory tables vs Temporary tables. Is this a EDB
thing, or does it work in DBISam too? Should be changing all my queries
to use Temporary tables (after all, that's all they are)?

Also - are Temporary tables unique per user, or can one user see another
users temporary table. (Because that would cause issues with my current
setup. I'd have to figure out a way to dynamically name them instead).

I'll see what I can do about posting some demo data too - big problem at
this stage is that the existing tables have signatures already set. I
want to get away from them now, but it looks like a lot of work to do
this as one can't simply 'remove' the signature and restructure the table.

I don't believe the Order By's are needed until the very end. (They are
probably left in there as a result of 'debugging' which I forgot to take
out).

Cheers for your assistance mate! (And also your emails re TElasticForm

Adam.

Hi Tim,

Thanks for your reply...

> The main issue with this is the INTO clause, which is replaced in EDB by
> the CREATE TABLE AS SELECT.. convention.

Does this mean I can use TEDBQueries for Memory / Temporary tables
instead of scripts?

Ie - all I need to do to change this SQL is:

DBISAM:

Select Field1, Field2, Field3
Into Memory\MyTable
;
Select Field1, Field2, Field3
Into Memory\MyTable2
From Memory\MyTable



EDB:

Create Table As Memory.MyTable
Select Field1, Field2, Field3
;
Create Table As Memory.MyTable2
Select Field1, Field2, Field3
From Memory.MyTable


If that is true, changing over may not be as difficult as I first
thought as the structure will be pretty much the same for me to get my
head around. Probably still quite time consuming, but I may be able to
do it in a couple of weeks?

Cheers

Adam.

Adam


>Interesting note about Memory tables vs Temporary tables. Is this a EDB
>thing, or does it work in DBISam too? Should be changing all my queries
>to use Temporary tables (after all, that's all they are)?

They're an ElevateDB invention. I forget just when they were introduced.

>Also - are Temporary tables unique per user, or can one user see another
>users temporary table. (Because that would cause issues with my current
>setup. I'd have to figure out a way to dynamically name them instead).

I'd forgotten that. With the catalog based nature of ElevateDB you need unique names for memory tables since memory is treated just like a disk based database. A quick test reveals that isn't the case for temporary tables and they seem to be created /  accessed on a session by session basis so another reason to use them.

>I'll see what I can do about posting some demo data too - big problem at
>this stage is that the existing tables have signatures already set. I
>want to get away from them now, but it looks like a lot of work to do
>this as one can't simply 'remove' the signature and restructure the table.

Don't worry it just means I can't test the conversion.


Roy Lambert [Team Elevate]

Adam

>> The main issue with this is the INTO clause, which is replaced in EDB by
>> the CREATE TABLE AS SELECT.. convention.
>
>Does this mean I can use TEDBQueries for Memory / Temporary tables
>instead of scripts?

I do love wishful thinking

>Ie - all I need to do to change this SQL is:
>
>DBISAM:
>
>Select Field1, Field2, Field3
>Into Memory\MyTable
>;
>Select Field1, Field2, Field3
>Into Memory\MyTable2
> From Memory\MyTable
>
>
>
>EDB:
>
>Create Table As Memory.MyTable
>Select Field1, Field2, Field3
>;
>Create Table As Memory.MyTable2
>Select Field1, Field2, Field3
> From Memory.MyTable

No! Have a look at the output of the converter program I posted to the binaries.

First problem is DBISAM zapped the old memory table ElevateDB doesn't

Second creating the memory table has to be done from the context of the memory database which means that all the other tables have to be referenced by their database but the memory one doesn't

Third ElevateDB scripts are different

Roy Lambert [Team Elevate]

Adam

That's more like a novel than a script

While it's not much use for a conversion I think it is technically possible to do that script in 1 EDB statement.  I
wouldn't mind having a go if there was some data to test against.

John

Roy,

<< Looking at the sample Adam posted I can't see much problem with
converting. The only thing is I can't remember if you posted something
recently about ORDER BY now being used in CREATE TABLE AS. >>

ORDER BY still cannot be used in a CREATE TABLE statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam,

<< Does this mean I can use TEDBQueries for Memory / Temporary tables
instead of scripts? >>

No, you'll still need to use scripts for any time that you want to run a
series of SQL statements as a logical group.

--
Tim Young
Elevate Software
www.elevatesoft.com

John

>That's more like a novel than a script

Totally agree - and the plot stinks!

>While it's not much use for a conversion I think it is technically possible to do that script in 1 EDB statement. I
>wouldn't mind having a go if there was some data to test against.

You sir are a grade A lunatic

Roy Lambert

Tim

>ORDER BY still cannot be used in a CREATE TABLE statement.

Not that difficult to mask out but I wonder what I was remembering. One of the problems of search in the ngs is when all you can remember is "it was something about ORDER BY"

Roy Lambert