Roy,

<< I presume that your perceptions of this at at least an order of magnitude
greater than mine since you wrote the stuff. However, I have an almost
entirely different viewpoint. To me the key fact is that the data is
inserted into the new temporary/memory table in the order I want it. There's
no funky internal name - in fact there's no index. OK you may have created
some sort of index for ElevateDB's use but as far as the outside world is
concerned there is no index. >>

Yes, but the fact that we're using an index is important.

<< I then pass ILbyProduct to my invoice printing program and it happily
chunters through the temporary table. It doesn't need an index. >>

Did you want your printing program to process this table in ProductName
order ?  If so, then it better know to set the active index order to
__OrderBy before printing.  *This* is what I'm talking about - because EDB
is using an index for the ordering, the default order of the table is not
what you think it is when you open the temporary table up directly.  If EDB
were simply sorting the actual rows and not using an index, then it would
make sense to allow for the ORDER BY.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I very obviously do not understand how the CREATE TABLE AS functions behind the scenes. My simplistic mind says the SELECT statement is used to generate a query and then ElevateDB uses the queries fielddefs to create a table and does a while not eof and moves the data over which is why I would think an ORDER BY on the SELECT statement would result in the data being inserted in the order I want.

I assume you're doing something cleverer and faster.

Roy Lambert

Roy,

<< I very obviously do not understand how the CREATE TABLE AS functions
behind the scenes. My simplistic mind says the SELECT statement is used to
generate a query and then ElevateDB uses the queries fielddefs to create a
table and does a while not eof and moves the data over which is why I would
think an ORDER BY on the SELECT statement would result in the data being
inserted in the order I want.

I assume you're doing something cleverer and faster. >>

Faster, yes.  Cleverer, not really.  The rows are simply being dumped out
into the result set in a raw fashion, and are indexed in the process for the
ORDER BY.  In such a case, the result set is the actual table being created.
Hence, the rows are not in any particular order, and require an index to be
ordered.  The default ordering (primary key) is the order in which the rows
were created.  Therefore, if one were to simply open up the table after
executing the CREATE TABLE AS, one would be seriously perplexed as to why
the rows were not ordered according to the ORDER BY.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Therefore, if one were to simply open up the table after
>executing the CREATE TABLE AS, one would be seriously perplexed as to why

Correct <vbg>

Roy Lambert

Sorry Tim, I'm missed your reply on this one with all my other News
Group rantings.

> << I guess the other option is to just stay with DBISam at present for
> my existing apps. I guess there's no real need for me to change to EDB.
> (That I can see). DBISam does everything I need for these applications,
> and maybe if it's not broke...? >>
>
> Besides the scripting and in-memory table issues, do you rely on any
> other DBISAM-specific features like the automatic right-trimming of
> string fields, or the empty string=NULL construct ? The SQL can be
> modified in a way that preserves the capabilities, but some of these
> other items require some more thought if you rely on them a lot in the
> existing application.

Good point. Yes - I do require emptystring=NULL in quite a bit of my
code (both SQL, and also with TFields), as well as the automatic
right-trimming of strin fields.

These two products I'm talking about are my main flag-ship products
(that keep my bread and butter on the table), and I kinda feel like I'm
being left behind by not updating to EDB.

On the other hand, it's looking like there's more and more work
required, and as these two applications are so large and involved
already - I'm beginning to doubt the benefit of changing over these two
apps when DBISam really does to what I want for them considering the
amount of (unpaid) work that would be involved in getting them changed.

I'll see how I go with this new app, and maybe revisit the thoughts on
converting in another 12 or 18 months when I have already built a larger
application with EDB and have a better understanding of it.

Cheers

Adam.

Hi Roy,

> The other one I found whilst doing the conversion is dates.
>
> The SQL Adam sent had bits like
>
> M.StartDate>  '2006-01-13'
>
> I'm waiting for him to let me know if these are supplied as parameters or not.

Likewise my aplogies for not getting back to you with the response on
this. (I missed this thread).

No, I don't use Parameters much with my queries at all. Back in the good
ol' days of Delphi 1 and 2, I used an RX component suite for my queries.
In that, they had a Macro ability where I could replace 'macro's in the
SQL.

I took this on board, and have been using a similar process ever since.
They work by putting a comment number at the start of a row/line as follows:


Select Field1, Field2, Field3
From MyTable
where
(Constraint 1 in here)
/*1*/

Macroreplace(MyQuery.sql, 1, 'and (Constraint 2 in here).

Gives me:

Select Field1, Field2, Field3
From MyTable
where
(Constraint 1 in here)
/*1*/ (Constraint 2 in here)

One of the main benefits of me doing this is the ability to dump the SQL
to a text file, and be able to see what my application is doing, and
load the SQL direct into DBSYS. (With parameters, I have no way of being
able to do this easily and seeing what the 'real' sql is behind the scenes).


On the plus side, if I'm doing dates I have a function already created
to convert a TDate to DBISam format. Thus I can simply create a new
function to convert a TDATE to EDB format and use it in exactily the
same way (with the Cast statement being thrown in automatically).

Cheers

Adam.

Adam


Right trimming string fields, posting NULL when a field (CHAR, VARCHAR, BLOB or CLOB) has only an empty string are both doable by modding table and query components or by some nasty mods to the sql.

The big problem with modding the sql is that the column type has to be known so modding the components is "easier"

Sorting out field = emptystring to be the same as field is null is also doable but getting more complex, and might well be impossible if the emptystring is contained in a variable.

Its looking more and more that you idea of leave well alone is the right one.

Roy Lambert [Team Elevate]