Hello

I have a statement that operated will as an SQL expression...

SELECT * FROM Database1.Hilltop WHERE (HillType = 101) OR (HillType = 102) ORDER BY Suburb;

However, when I put that into a script as the IN SQLStatement variable...
PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

...the script fails, with the message "ElevateDB Error #700 An error was found in the statement at line 13 and column 25 (Expected WITH but instead found
ORDER)". If I remove the ORDER BY the script proceeds.

How should I handle this situation?

Regards & TIA

Peter

Peter,

> However, when I put that into a script as the IN SQLStatement variable...
> PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
> EXECUTE ResultStmt;
> How should I handle this situation?

You cannot use "order by" with "Create Table". Anyway it wouldn't make
sense to insert records into a table in a certain order. This will be
done by indexes or a query against this table.

Uli

Uli


>You cannot use "order by" with "Create Table".

Correct

>Anyway it wouldn't make
>sense to insert records into a table in a certain order.

Your,Tim's and possibly the standard's opinion. Not mine

>This will be
>done by indexes or a query against this table.

Which is a waste if its for a simple one off use and could have been accommodated by by the ORDER BY as it used to happen in DBISAM.

Roy Lambert

Roy,

> Which is a waste if its for a simple one off use and could have been accommodated by by the ORDER BY as it used to happen in DBISAM.

I understand what you mean, but: if you need such a sorted result only
once why not use the query itself?

Uli

Uli

I am with Roy on this one

Its Ok though, I can see how easy it is to add indexes and stuff on the table after the table has been created in the script.

Regards

Peter

Uli

>> Which is a waste if its for a simple one off use and could have been accommodated by by the ORDER BY as it used to happen in DBISAM.
>
>I understand what you mean, but: if you need such a sorted result only
>once why not use the query itself?

Because you can no longer edit fields in the query. I often have a boolean added into the query which is used for status and needs to be edited.

Roy Lambert

Roy,

> Because you can no longer edit fields in the query. I often have a boolean added into the query which is used for status and needs to be edited.

True!

Uli

> Because you can no longer edit fields in the query. I often have a boolean added into
the query which is used for status and needs to be edited.

You can edit on ordered query as long as there is an index on the ordered fields which can
included boolean fields.

Richard Harding

Richard,

> You can edit on ordered query as long as there is an index on the ordered fields which can
> included boolean fields.

Sure, but that's not what Roy wanted to say. He is talking about an
insensitive query. In DBISAM this canned dataset could be edited.

Uli