Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Order by in script |
Sun, Sep 13 2009 6:42 AM | Permanent Link |
Peter | 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 |
Sun, Sep 13 2009 7:38 AM | Permanent Link |
Uli Becker | 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 |
Sun, Sep 13 2009 10:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Sep 13 2009 12:07 PM | Permanent Link |
Uli Becker | 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 |
Sun, Sep 13 2009 11:29 PM | Permanent Link |
Peter | 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 |
Mon, Sep 14 2009 3:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Sep 14 2009 5:52 AM | Permanent Link |
Uli Becker | 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 |
Mon, Sep 14 2009 4:36 PM | Permanent Link |
Richard Harding | > 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 |
Mon, Sep 14 2009 4:44 PM | Permanent Link |
Uli Becker | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |