Hi Guys,

Everytime I think of how to do this I keep hitting a brick wall. I
haven't been following EDB of late, have gone back to maintaining
applications with DBISam for the time being until I get this figured
out, so maybe there's a new feature or option that can be recommended.

The problem I have is that I have an application that has been
continually written over the last 7 years or so (and continues to be
developed and grows as an ongoing process).

The application now contains around 500 units and a few thousand
queries. I don't have the luxary of putting development 'on hold' for a
few months (or more likely a year) to rewrite a lot of the code.

The main brick wall I've been hitting is the queries I have. There are
many very complex and involved queries that use memory tables, where I
create a memory table via a query, and then do a second query on the
result of the first, and so on.

(ie:
 select into Memory\M1.....;

  select into Memory\M2.....;

  select into Memory\M3... FROM Memory\M1 Inner join Memory\M2......;

)

I believe that EDB requires scripts instead of queries for memory
tables, and these are rather different to DBISam. At present I don't
believe there is any simple way to bulk convert the SQL into script
format (unless something's changed recently?)

(Some of my queries end up returning a result, while others of these
complex queries end up altering existing data).

I'm just wondering if anyone can think of any options I may have to
convert my application across to EDB, or whether it's pretty much going
to be impossible to head that way?

Cheers

Adam.

Adam


Simply converting DBISAM scripts to ElevateDB scripts isn't difficult. Tim has built the facility in, its basically just wrapping the statement in an EXECUTE IMMEDIATE. Getting them to work can be much more difficult though. There are some significant differences between DBISAM's SQL and ElevateDB's and memory tables is one of the biggies.

DBISAM's scripts are essentially just a lot of sql statements executed one after the other. All of the logic is contained within an individual statement so it might be possible for you to come up with a AdamScriptRunner procedure. There would be some very small time penalties as you parse the original script but since that was being run as a series of queries its unlikely to be particularly high.

It depends on how complex your scripts become wether or not you could write a parser. Most lines would simply be stuffed into a TEDBQuery and run, others would need alteration.

I've added the code supplied by Tim that I have in a TEDBScript component on a datamodule. I just pass it sql and a table name and it creates the table in memory.

So for the example you quoted you'd get something like


script1.close;
if not script1.prepared then script1.prepare;
script1.parambyname('TableName').AsString := 'M1';
script1.parambyname('SQLStatement').AsString := sql1;
script1.execscript;
script1.close;
script1.parambyname('TableName').AsString := 'M2';
script1.parambyname('SQLStatement').AsString := sql2;
script1.execscript;
script1.close;
script1.parambyname('TableName').AsString := 'M3';
script1.parambyname('SQLStatement').AsString := sql3;
script1.execscript;

What you do with M3 after that is your affair.

Its not as good as doing a full conversion but it could allow you to move to ElevateDB

Roy Lambert [Team Elevate]


SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END

Hi Roy,

Thanks for your reply. I think you're right - I'm definitely going to
need to look at some sort of parser because rewriting the SQL isn't
going to be possible in this instance.

(Some took days to tweak to get 'just right'), so I'd need to make sure
that I didn't rewrite and alter the script and end up processing
incorrect figures.

> script1.close;
> if not script1.prepared then script1.prepare;
> script1.parambyname('TableName').AsString := 'M1';
> script1.parambyname('SQLStatement').AsString := sql1;
> script1.execscript;
> script1.close;
> script1.parambyname('TableName').AsString := 'M2';
> script1.parambyname('SQLStatement').AsString := sql2;
> script1.execscript;
> script1.close;
> script1.parambyname('TableName').AsString := 'M3';
> script1.parambyname('SQLStatement').AsString := sql3;
> script1.execscript;

In this script example, you execute the script 3 times (for 3 different
memory tables I assume). Is it not possible in EDB to have queries
create multiple memory tables, and then have a resulting query from
those memory tables in one SQL statement?

One of the things I loved with DBISAM (and maybe where I've gotten
myself stuck too), is the ability to have everything I want created
solely by SQL.

In the past (prior to DBISAM) I would do a query, then outside of the
result scroll through to grab data, manipulate, etc to get the result I
needed. In DBISam, I've created all my reports to run off a single
TDBISamQuery component / sql statement. (Well, multiple sql statements -
some of them may have more than 15 separate results and then combined in
the end to get the result set I'm after, but they're all contained in
the one TDBISamQuery.SQL property).

I not only found this neater to do for me, but it made it much easier
for me to debug in DBSYS (as I could execute each one individually for
debugging, etc) and it also meant that all the processing was done on
the server side too.

Have I taken the wrong approach? Should I not have been using memory
tables for this at all in the first place?

Best Regards

Adam.

Adam

>Thanks for your reply. I think you're right - I'm definitely going to
>need to look at some sort of parser because rewriting the SQL isn't
>going to be possible in this instance.

In theory it shouldn't be to difficult. Reality on the other hand has a habit of being awkward

I'd suggest starting with trying to understand the differences and an analysis of your SQL for where those differences crop up. I'm not sure there is a genuinely comprehensive list anywhere but here are a few that I suspect will bite you

- memory tables are vastly different
- varchar and char columns are no longer trimmed to fit
- null handling is different
- case insensitive comparisons are handled in a different way (you can still use the old DBISAM way though)
- full text indexing has changed, TEXTSEARCH can no only be used on indexed columns
- custom function handling has really changed

Its hard to remember everything I struggled with when I switched and ElevateDB has developed and changed as well.

The one thing that makes it possible at all is that DBISAM scripts are THICK and STUPID As I said in the last post all they are is a series of discrete sql statements, there's no additional logic or control so you can deal with them on a statement by statement basis rather than as a whole.

>(Some took days to tweak to get 'just right'), so I'd need to make sure
>that I didn't rewrite and alter the script and end up processing
>incorrect figures.

I can well understand. One thing I loath about SQL is its an all or nothing affair. Debugging is a nightmare since all you can do is examine final output.

> > script1.close;
> > if not script1.prepared then script1.prepare;
> > script1.parambyname('TableName').AsString := 'M1';
> > script1.parambyname('SQLStatement').AsString := sql1;
> > script1.execscript;
> > script1.close;
> > script1.parambyname('TableName').AsString := 'M2';
> > script1.parambyname('SQLStatement').AsString := sql2;
> > script1.execscript;
> > script1.close;
> > script1.parambyname('TableName').AsString := 'M3';
> > script1.parambyname('SQLStatement').AsString := sql3;
> > script1.execscript;
>
>In this script example, you execute the script 3 times (for 3 different
>memory tables I assume). Is it not possible in EDB to have queries
>create multiple memory tables, and then have a resulting query from
>those memory tables in one SQL statement?

Not in one sql statement (you can't in DBISAM either but in one script - of course. However, what I was trying to illustrate was a way to process each line of your current script without having to create a script manually or get into some heavy programming to create a script.

>One of the things I loved with DBISAM (and maybe where I've gotten
>myself stuck too), is the ability to have everything I want created
>solely by SQL.

That's sort of what I was thinking of with my suggestions but with a new TAdamsSQL component which does the work internally but looks to the outside world like an old style TDBISAMQuery.

>In the past (prior to DBISAM) I would do a query, then outside of the
>result scroll through to grab data, manipulate, etc to get the result I
>needed. In DBISam, I've created all my reports to run off a single
>TDBISamQuery component / sql statement. (Well, multiple sql statements -
>some of them may have more than 15 separate results and then combined in
>the end to get the result set I'm after, but they're all contained in
>the one TDBISamQuery.SQL property).

See above

>I not only found this neater to do for me, but it made it much easier
>for me to debug in DBSYS (as I could execute each one individually for
>debugging, etc) and it also meant that all the processing was done on
>the server side too.

I'm not sure how my suggestion would work for debugging in EDBManager, I suspect you'd have to build your component into its source, but I would only view this as a short term solution anyway (ie no more than the next century whilst you convert over to "proper" EDB SQL on a bit by bit basis.

>Have I taken the wrong approach? Should I not have been using memory
>tables for this at all in the first place?

If you hadn't what would you have used - real tables or Delphi? Real tables would have the same problems as memory tables. So you should have used  Delphi only to save having to do a conversion <VBG>

Post a couple of samples of the scripts you're talking about. Pick nasty ones and I'll give my opinion about converting.

Roy Lambert [Team Elevate]

Hi Roy,

Thanks for your reply and help again...

>> Thanks for your reply. I think you're right - I'm definitely going to
>> need to look at some sort of parser because rewriting the SQL isn't
>> going to be possible in this instance.
>
> In theory it shouldn't be to difficult. Reality on the other hand has a habit of being awkward

The simple number of SQL statments that need to be altered. I'd
literally have to check and change in the thousands of TDBISamQuery
components.

That's where as you suggest a TEDBAdamQuery would be a big help! (Even
if it's only short term to allow the application to function - it would
give me the time needed to slowly change to TEDBScripts or TEDBQueries
whilst being able to maintain and continue development of the
application). I really need something that I can do a 'search and
replace' on. (ie, TDBISamQuery to TEDBAdamQuery for instance).

Although I too am concerned about the 'in theory' vs 'reality' of how
difficult this may be to implement, and your statement about TEDBManager
is also another problem I didn't think about... debugging of existing
SQL scripts.

> I'd suggest starting with trying to understand the differences and an analysis of your SQL for where those differences crop up. I'm not sure there is a genuinely comprehensive list anywhere but here are a few that I suspect will bite you
>
> - memory tables are vastly different

Probably my biggest issue.

> - varchar and char columns are no longer trimmed to fit

I don't think this would affect me.

> - null handling is different

Whoops - this may or may not be an issue. I better do a search to see
what the differences are.

> - case insensitive comparisons are handled in a different way (you can still use the old DBISAM way though)
> - full text indexing has changed, TEXTSEARCH can no only be used on indexed columns
> - custom function handling has really changed

Neither of these should be an impact to me. (I think I have only 1
custom function which should be relatively easy to change).

> The one thing that makes it possible at all is that DBISAM scripts are THICK and STUPID

Don't say that! I found them very helpful and easy to understand / read!
(What does that say about me <vbg>)

> That's sort of what I was thinking of with my suggestions but with a new TAdamsSQL component which does the work internally but looks to the outside world like an old style TDBISAMQuery.

....if I can pull it off!

>> Have I taken the wrong approach? Should I not have been using memory
>> tables for this at all in the first place?
>
> If you hadn't what would you have used - real tables or Delphi?

Prior to DBISam when I was using BDE it was Delphi, and a LOT of work /
processing in Delphi. I prefer the SQL method as I actually find it
easier to debug. (I can view each result set at a time, and work off
that). I just don't know if doing everything in SQL is the 'right'
method, or not.


> Post a couple of samples of the scripts you're talking about. Pick nasty ones and I'll give my opinion about converting.

OK, here's one of my 'nastier' scripts. It may not be the most
efficient, but it helped me to debug / make sure I was getting the
correct results at each 'level':

______________________________________________________________________

select AQF.AQFLevel, AC.ID AppCourseID, AC.Type, A.Sex, AQF.Date StartDate
Into Memory\GTAInit1
from ApprenticeAQF AQF
inner join ApprenticeCourse AC on (AC.ID = AQF.AppCourseID)
inner join Apprentice A on (A.ID = AC.AppID)
;
select M.AQFLevel, M.AppCourseID, M.Type, M.Sex, M.StartDate, AQF.Date
EndDate
into Memory\GTAInit2
from Memory\GTAInit1 M
left outer Join ApprenticeAQF AQF on (AQF.AppCourseID = M.AppCourseID)
and (AQF.AQFLevel = M.AQFLevel + 1)
order by AQFLevel

;
select M.AQFLevel, M.AppCourseID, M.Type, M.Sex, M.StartDate, M.EndDate,
IF(AQF.ID is not Null, TRUE, Null) as Progression
into Memory\GTAInit3
from Memory\GTAInit2 M
left outer Join ApprenticeAQF AQF on (AQF.AppCourseID = M.AppCourseID)
and (AQF.AQFLevel = M.AQFLevel - 1)
order by progression
;
select M.AQFLevel, M.AppCourseID, M.Type, M.Sex, M.StartDate,
Coalesce(M.EndDate, AC.EndDate) Enddate, Coalesce(M.Progression,
AC.Progression, false) as Progression, A.Code, AC.ActiveState,
AC.CourseState, AC.CourseID, AC.CourseItem
into Memory\GTAInit
from Memory\GTAInit3 M
left outer Join ApprenticeCourse AC on (AC.ID = M.AppCourseID)
left outer join APprentice A on (A.ID = AC.AppID)
where (0=0)
/*1*/ and (AC.CourseState <> 'NSWthe2nd')
/*2*/ and (M.Type <> 'ABC')
order by progression
;


/* Get Begining Balances / Starters */
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex
into Memory\GTAInitStart
from Memory\GTAInit M
where (M.StartDate < '2006-01-01') and ((M.EndDate >= '2006-01-01') or
(M.EndDate is null))
;


/* Get Commencements */
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex
into Memory\GTAComms
from Memory\GTAInit M
where (M.StartDate >= '2006-01-01') and (M.StartDate <= '2006-03-31')
;


/* Get Completions */
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex
into Memory\GTAComps
from Memory\GTAInit M
where (M.EndDate >= '2006-01-01') and (M.EndDate <= '2006-03-31') and
(M.ActiveState = 'COMPLETED')
;

/* Get Cancellations */
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex
into Memory\GTACans
from Memory\GTAInit M
where (M.EndDate >= '2006-01-01') and (M.EndDate <= '2006-03-31') and
(M.ActiveState = 'CANCELLED')
;

/* Get Diversity Commencements*/
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 1 as ATSI, 0 as WNTT, 0 as DIS, 0 as NESB
into Memory\GTAMDCom1
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.StartDate >= '2006-01-01') and (M.StartDate <= '2006-03-31')
and (At.Attribute = 'ATSI')
union all
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 0 as ATSI, 1 as WNTT, 0 as DIS, 0 as NESB
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.StartDate >= '2006-01-01') and (M.StartDate <= '2006-03-31')
and (At.Attribute = 'WNTT')
Union all
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 0 as ATSI, 0 as WNTT, 1 as DIS, 0 as NESB
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.StartDate >= '2006-01-01') and (M.StartDate <= '2006-03-31')
and (At.Attribute = 'DIS')
Union all
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 0 as ATSI, 0 as WNTT, 0 as DIS, 1 as NESB
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.StartDate >= '2006-01-01') and (M.StartDate <= '2006-03-31')
and (At.Attribute = 'NESB')
;
Select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, Sum(ATSI) ATSI, Sum(WNTT) WNTT, Sum(DIS)
DIS, Sum(NESB) NESB
into Memory\GTAMDCom
from Memory\GTAMDCom1 M
group by M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex
;


/* Get Diversity Completions*/
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 1 as ATSI, 0 as WNTT, 0 as DIS, 0 as NESB
into Memory\GTAMDComp1
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.EndDate >= '2006-01-01') and (M.EndDate <= '2006-03-31')
and (At.Attribute = 'ATSI')
and (M.Activestate = 'COMPLETED')
union all
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 0 as ATSI, 1 as WNTT, 0 as DIS, 0 as NESB
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.EndDate >= '2006-01-01') and (M.EndDate <= '2006-03-31')
and (At.Attribute = 'WNTT')
and (M.Activestate = 'COMPLETED')
Union all
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 0 as ATSI, 0 as WNTT, 1 as DIS, 0 as NESB
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.EndDate >= '2006-01-01') and (M.EndDate <= '2006-03-31')
and (At.Attribute = 'DIS')
and (M.Activestate = 'COMPLETED')
Union all
select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, 0 as ATSI, 0 as WNTT, 0 as DIS, 1 as NESB
from Memory\GTAInit M
inner join ApprenticeAttr AA on (AA.AppCourseID = M.AppCourseID)
inner join Attributes At on (At.ID = AA.AttributeID)
where (M.EndDate >= '2006-01-01') and (M.EndDate <= '2006-03-31')
and (At.Attribute = 'NESB')
and (M.Activestate = 'COMPLETED')
;
Select M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex, Sum(ATSI) ATSI, Sum(WNTT) WNTT, Sum(DIS)
DIS, Sum(NESB) NESB
into Memory\GTAMDComp
from Memory\GTAMDComp1 M
group by M.AQFLevel, M.AppCourseID, M.CourseID, M.Type, M.CourseState,
M.StartDate, M.EndDate, M.Sex
;


/* We should have all we need, in one form or another now....... let's
group together */

Select M.AppCourseID, M.AQFLevel, M.Type, Count(M.AppCourseID) Init_M, 0
Init_F, 0 Comm_M, 0 Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, 0 Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
into Memory\GTASemiComplete
from Memory\GTAInitStart M
where (M.Sex = 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all
Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, Count(M.AppCourseID)
Init_F, 0 Comm_M, 0 Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, 0 Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
from Memory\GTAInitStart M
where (M.Sex <> 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all

Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F,
Count(M.AppCourseID) Comm_M, 0 Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, 0
Can_F, 0 ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0
DIS_F, 0 NESB_F
from Memory\GTAComms M
where (M.Sex = 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all
Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
Count(M.AppCourseID) Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, 0 Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
from Memory\GTAComms M
where (M.Sex <> 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all

Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
0 Comm_F, Count(M.AppCourseID) Comp_M, 0 Comp_F, 0 Can_M, 0 Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
from Memory\GTAComps M
where (M.Sex = 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all
Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
0 Comm_F, 0 Comp_M, Count(M.AppCourseID) Comp_F, 0 Can_M, 0 Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
from Memory\GTAComps M
where (M.Sex <> 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all

Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
0 Comm_F, 0 Comp_M, 0 Comp_F, Count(M.AppCourseID) Can_M, 0 Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
from Memory\GTACans M
where (M.Sex = 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all
Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
0 Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, Count(M.AppCourseID) Can_F, 0
ATSI_S, 0 WNTT_S, 0 DIS_S, 0 NESB_S, 0 ATSI_F, 0 WNTT_F, 0 DIS_F, 0 NESB_F
from Memory\GTACans M
where (M.Sex <> 'M')
Group By M.AppCourseID, M.AQFLevel, M.Type
union all

Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
0 Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, 0 Can_F, Sum(ATSI) ATSI_S,
Sum(WNTT) WNTT_S, Sum(DIS) DIS_S, Sum(NESB) NESB_S, 0 ATSI_F, 0 WNTT_F,
0 DIS_F, 0 NESB_F
from Memory\GTAMDComm M
Group By M.AppCourseID, M.AQFLevel, M.Type
union all
Select M.AppCourseID, M.AQFLevel, M.Type, 0 Init_M, 0 Init_F, 0 Comm_M,
0 Comm_F, 0 Comp_M, 0 Comp_F, 0 Can_M, 0 Can_F, 0 ATSI_S, 0 WNTT_S, 0
DIS_S, 0 NESB_S, Sum(ATSI) ATSI_F, Sum(WNTT) WNTT_F, Sum(DIS) DIS_F,
Sum(NESB) NESB_F
from Memory\GTAMDComp M
Group By M.AppCourseID, M.AQFLevel, M.Type

;

/*make it look pretty for us now / Create the final Result Set*/

Select CD.Stream, C.Course, C."Course Description", M.AQFLevel, M.Type,
Sum(Init_M) Init_M, Sum(Init_F) Init_F, Sum(Comm_M) Comm_M, Sum(Comm_F)
Comm_F, Sum(Comp_M) Comp_M, Sum(Comp_F) Comp_F, Sum(Can_M) Can_M,
Sum(Can_F) Can_F, Sum(ATSI_S) ATSI_S, Sum(WNTT_S) WNTT_S, Sum(DIS_S)
DIS_S, Sum(NESB_S) NESB_S, Sum(ATSI_F) ATSI_F, Sum(WNTT_F) WNTT_F,
Sum(DIS_F) DIS_F, Sum(NESB_F) NESB_F
from Memory\GTASemiComplete M
left outer join ApprenticeCourse AC on (AC.ID = M.AppCourseID)
left outer join Course C on (C.ID = AC.CourseID)
left outer join CourseDet CD on (CD.CourseID = AC.CourseID) and (CD.Item
= AC.CourseItem)

group by CD.Stream, C.Course, C."Course Description", M.AQFLevel, M.Type
Order by CD.Stream, M. AQFLevel, M.Type
___________________________________________


Thanks again for your help Roy!

Cheers

Adam.

Adam


I don't know about nasty but there's certainly a lot of it It'll take me a few days but I'll have a look at rewriting (some at least of it) to ElevateDB so that we can see what the changes needed are.

Any chance of emailing me some demo tables?

Roy Lambert [Team Elevate]

Adam,

<< The main brick wall I've been hitting is the queries I have. There are
many very complex and involved queries that use memory tables, where I
create a memory table via a query, and then do a second query on the result
of the first, and so on. >>

The main issue with this is the INTO clause, which is replaced in EDB by the
CREATE TABLE AS SELECT.. convention.  The memory tables themselves will stay
exactly the same, with the only requirement being that you create an
in-memory database called "Memory", and that you use a period (.) instead of
a backslash (\) for the Memory database references.

<< I believe that EDB requires scripts instead of queries for memory tables,
and these are rather different to DBISam. At present I don't believe there
is any simple way to bulk convert the SQL into script format (unless
something's changed recently?) >>

You have been able to do so for quite some time:

http://www.elevatesoft.com/manual?action=viewmethod&id=edb2&product=d&version=7&comp=TEDBScript&method=ConvertSQL

However, as Roy indicated, this just converts the individual statements
into:

SCRIPT()
BEGIN
  EXECUTE IMMEDIATE 'First statement';
  EXECUTE IMMEDIATE 'Second statement';
  etc.
END

But doesn't interpret or change the existing SQL statements so that they
conform to the EDB syntax.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


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.

I'm also asking Adam if the order by clauses he has in there are needed.

Roy Lambert

Adam


Its long but I don't see any major problem. One question - are the ORDER BYs needed in the intermediate results?

Roy Lambert [Team Elevate]

Adam

I've finished the bit of my stuff I was working on so I'll start having a look at the conversion.

One thing I'm going to suggest is that temporary tables rather than memory tables are used since this removes the need to worry about the database context.

The biggest difference between memory tables and temporary tables is that temporary tables are self removing if the app terminates correctly but will hang around if there's a crash.

Roy Lambert