Hi,

I was wondering if someone could please help me with the following.

I'm trying to run a query / script in EDB that will put the result into
a temporary table, and then do a second query on that temporary table. I
still haven't been able to get my head around it.

Here's the SQL (that I obviously have wrong)...

-----------------------------
SCRIPT
BEGIN
execute immediate
'
Create Temporary Table MaxCSNo as
select max(ContractSort) Contractsort
From ContractSort
'
;
Execute Immediate
'
Select CS.ID, CS.Contract, CS.ContractSort
From Temporary table MaxCSNo M1
inner join ContSale CS on (CS.ContractSort = M1.ContractSort)
';
END
-----------------------------

There may be a better way of getting the same result (which I'm open too
as well but either way, I'd really like to nail this whole queries
on tempoarary queries thing in EDB.

Can someone please advise where I've gone wrong?

Cheers

Adam.

Adam


Not to sure just what you're asking here. Are you saying the script bombs or that you don't get a result set. If the former how and what?

Re-reading the script one thing I can see wrong is you haven't specified the WITH DATA clause at the end of the create temporary table statement.

I suspect the latter (if not now it will be soon)

If I'm correct what you're doing is expecting an ElevateDB script to operate as a DBISAM script and they just don't. Scripts in ElevateDB do not return a result set unless you specifically ask them to.

Have a look at the TnlhTemporary component I posted in the extensions ng.

As far as a better way I don't know if ElevateDB will do it but what about

Select CS.ID, CS.Contract, CS.ContractSort
Fromn ContSale
WHERE.ContractSort = (select max(ContractSort) Contractsort From ContractSort)

Roy Lambert [Team Elevate]

ps

A good approach with a script and EXECUTE IMMEDIATEs is to test each line separately in EDBManager and then glue together after they all work.

Hi Roy,

Thanks for your reply. Your alternative SQL did work - thanks for that.

As for scripts in EDB - I really wish I could get my head around the
native way to use them.

To make things really simple - let's say I have a table called table 1,
and I want to select records from that table into a temporary table, and
then do a select statement on the temporary table result - can one do
this simply (and natively) in EDB?

In dbisam it would be as simple as:

--------------
select *
into memory\MemTable1
from Realtable1
;
Select *
from memory\Memtable1
--------------

I'm aware that you can't use the same SQL statement in EDB - but what
I'm wanting to know is HOW I would get the same result as above in EDB?
(Ignore the fact that with that SQL Statement I could do it in one hit -
I'm using it as a simple example).

I will check out the TnlhTemporary component you've posted in the
Newsgroups - thanks, but does that mean that you can't do these natively
in EDB at all?

Cheers

Adam

Adam,

> --------------
> select *
> into memory\MemTable1
> from Realtable1
> ;
> Select *
> from memory\Memtable1
> --------------

first of all "Memory" is a different thing. In EDB you have to use an
"in Memory" database to use that.

But to "translate" your code just to get a temporary table, use this:

SCRIPT
BEGIN
  DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
  BEGIN
     Execute Immediate 'Drop Table "TempTable1"';
  EXCEPTION
  END;

  Execute Immediate 'Create Temporary Table "TempTable1" as select *
from "RealTable1" with data';

  Prepare Stmt from 'Select *  from "TempTable1"';
  Open CustCursor;
END

Hope that helps.

Uli

Hi Uli,

Thanks for your example and help. Yes - I was able to translate what you
put up to get a statement to work!

From what I can tell, each temporary table is unique to that session so
I shouldn't get conflicts from various users with the same temporary
table names too. (I used to use memory tables in DBISAM for what
temporary tables are used here for).

Looking at the script itself - I think I have a heck of a lot to learn
though.

Cursors, Stmt, with data - all are foreign to me - but at least I have a
working example now... thanks heaps!

Roy's component may be the way to go.. but I like to know how things
work natively first before moving beyond.

Thanks for your help!

Cheers

Adam.

Adam,

> Thanks for your example and help. Yes - I was able to translate what you
> put up to get a statement to work!

You are welcome!

>  From what I can tell, each temporary table is unique to that session so
> I shouldn't get conflicts from various users with the same temporary
> table names too.

Correct.

> Looking at the script itself - I think I have a heck of a lot to learn
> though.

Yes - it needs some time to learn, but beleive me: it's worth the time!

> Cursors, Stmt, with data - all are foreign to me - but at least I have a
> working example now... thanks heaps!

If you have any questions (after reading the manual : feel free to
ask here.

Regards Uli

Adam

>As for scripts in EDB - I really wish I could get my head around the
>native way to use them.

I'm still working on that

>To make things really simple - let's say I have a table called table 1,
>and I want to select records from that table into a temporary table, and
>then do a select statement on the temporary table result - can one do
>this simply (and natively) in EDB?

Uli's given you a good example. One thing I'd do differently is I'd query the configuration and only drop the table if it existed. Firstly I try and avoid exceptions where I can, secondly if the drop bombs you'll get a slightly misleading error message surfacing later on. I'm also slightly confused as to wether you would get a resultset as well as the temporary table.

Read on about why TnlhTemporary exists.

>I will check out the TnlhTemporary component you've posted in the
>Newsgroups - thanks, but does that mean that you can't do these natively
>in EDB at all?

No. The technology at the heart of TnlhTemporary is an ElevateDB script but I'm lazy.

The reason for TnlhTemporary is that with a query or script result set you can't use indices, and you may or may not be able to edit it, and you can't run queries against it. Also I'm lazy. Run a script to produce a temporary table and you still have to set up that table onto a table component to be able to use it. TnlhTemporary does that. It also will (if asked) auto generate a unique(ish) name for the table

Roy Lambert [Team Elevate]

Uli


Does that produce a result set as well as a temporary table? Asking to save me having to try it out.


Roy Lambert

Adam

To build a bit on what Uli said. The in-memory database changed RADICALLY between DBISAM and ElevateDB. I shall now display my bias:

In DBISAM in-memory was a nice programmer friendly feature. In ElevateDB its been elevated to the same status as a disk database and is as friendly as a cornered rattlesnake. The biggest problem is that memory is just treated as a temporary disk drive. You can have lots of memory based databases but there are all the restrictions that apply to moving data between disk based databases apply. Its still just as easy to address them simultaneously using tables but more difficult with SQL

From other posts by Tim in DBISAM he had to do all sorts of freaky stuff in the engine to make it work. In ElevateDB he doesn't. On the other hand in DBISAM we could use it easily in ElevateDB its more awkward.

The whole database concept is different in ElevateDB and this is at least partly behind the changes in the way it works.

Whilst I do have some in-memory stuff still in my code I'll be digging them out and replacing with temporary tables.

Roy Lambert [Team Elevate]

I am coming to this one a bit late.

I had a lot of scripts in DBISAM in the form Adam H. details at the start of this thread.

I have replaced virtually all of them with SQL Statements which call VIEWS. Views essentially allow the creation of a "permanent memory table"

CREATE VIEW "MyView" AS
SELECT
 SomeID,
 MAX(DeliveryDate) as MaxDate

FROM SomeTable

Run this once.

Then you can run the statement:

SELECT
 T1.SomeFields,
 MV.MaxDate

FROM Table1 T1
 LEFT JOIN MyView MV ON (MV.SomeID=T1.SomeID)

... this makes 2 improvements:

1. You can use a simple query statement, rather than the slightly more cumbersome script.

2. You can access MyView from _any_ statement. Before I was re-writing INTO MEMORY over and over and over, basically creating the same MEMORY tables, which were used by multiple users in multiple views. This also caused problems of LOCKING ... which EDB Views overcome.

Adam