Please try the following sql, i recreated the tables here and this sql works
without errors:



INSERT INTO TEMPORALPRUEBA1

(CLIENTID, DATE2, DATE1)

SELECT C_HISTORIA, DATA_INGRES, DATA_ALTA FROM TRACTAMENTS

WHERE

(C_HISTORIA >= 5000 And C_HISTORIA <= 5044) AND

(((C_PRESTACIO = 1004) AND

(C_MOTIU = '2'))OR

((C_PRESTACIO = 2014) AND

(C_MOTIU = '8'))

)ORDER BY C_HISTORIA





Van: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Namens Alejandro Garcia
Verzonden: woensdag 28 maart 2007 19:33
Aan: firebird-support@yahoogroups.com
Onderwerp: Re: [firebird-support] Create table from another table



I tryied to INSERT after CREATE, here's the query, but I get the error in
the ORDER line, I replaced the double quotes by simple ones as I was told
here but still does not work... I need to insert it ordered because I also
have to add a generator here (I still didn't arrive to add the generator
because I first tryied to solve this..)

> Here's the query, if I run the SELECT part without INSERT it
> works fine, but when I run this query I get the error:
> Invalid token.
>Dynamic SQL Error.
>SQL error code = -104.
>Token unknown - line 16, char -1.
>ORDER
>
> If I delete the ORDER BY part it also works fine and inserts it unordered
>
> INSERT INTO TEMPORALPRUEBA1
>(TEMPORALPRUEBA1. CLIENTID, TEMPORALPRUEBA1. DATE2, TEMPORALPRUEBA1. DATE1)
>SELECT
> TRACTAMENTS. C_HISTORIA,
> TRACTAMENTS. DATA_INGRES,
> TRACTAMENTS. DATA_ALTA
>FROM
> TRACTAMENTS
>WHERE
> (TRACTAMENTS. C_HISTORIA >= 5000 And TRACTAMENTS. C_HISTORIA <= 5044)
> AND
> ((TRACTAMENTS. C_PRESTACIO = 1004 AND
> TRACTAMENTS. C_MOTIU = "2" )OR
> (TRACTAMENTS. C_PRESTACIO = 2014 AND
> TRACTAMENTS. C_MOTIU = "8"))
>ORDER BY
> TRACTAMENTS. C_HISTORIA

The double quotes in the search clause are illegal syntax. Replace
them with single quotes.

Kurt Federspiel <federonline@... <mailto:federonline%40yahoo.com> >
escribió:
Create the table with a primary key or index like:
CREATE TABLE suppliers AS (
id int not null,
address varchar(255) not null,
cat_type int,
PRIMARY KEY (id),
CONSTRAINT FK_suppliers_companies FOEIGN KEY
REFERENCES companies (id)
);

Then, insert into the table with an ORDER BY clause
like:

INSERT INTO suppliers (id, address, cat_type)
(SELECT companies.id, companies.address,
categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000
ORDER BY companies.id);

Hope this helps.
Kurt.

--- Alejandro Garcia <aleplgr@... <mailto:aleplgr%40yahoo.es> > wrote:

> So? you create the table and then INSERT from a
> SELECT?
> If I do that I can't insert it ordered because the
> insert does not allow the ORDER BY in the INSERT
>
> Nick Upson <nick.upson@... <mailto:nick.upson%40gmail.com> >

escribió:

> No it's not possible
>
> On 28/03/07, Alejandro Garcia wrote:
> > Is this the forum for simple/newbie SQL questions?
> >
> > Is it possible in InterBase 7.5 create a table
> from another table? I'm reading the specification of
> the CREATE satatement but can't find how
> > Like this:
> >
> > CREATE TABLE suppliers
> > AS (SELECT companies.id, companies.address,
> categories.cat_type
> > FROM companies, categories
> > WHERE companies.id = categories.id
> > AND companies.id > 1000);
> >
> >
> >
> > CREATE TABLE table [EXTERNAL [FILE] ' filespec']
> > ( [, | …]);
> > = col {< datatype> | COMPUTED [BY] (< expr>) |
> domain}
> > [DEFAULT { literal | NULL | USER}]
> > [NOT NULL]
> > [ ]
> > [COLLATE collation]
>
>
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

>
> Visit http://www.firebirdsql.org and click the
> Resources item
> on the main (top) menu. Try Knowledgebase and FAQ
> links !
>
> Also search the knowledgebases at
> http://www.ibphoenix.com
>
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

>
> Yahoo! Groups Links
>
>
>
>
>
>
> ---------------------------------
>
> LLama Gratis a cualquier PC del Mundo.
> Llamadas a fijos y móviles desde 1 céntimo por
> minuto.
> http://es.voice.yahoo.com
>
> [Non-text portions of this message have been
> removed]
>
>

__________________________________________________________
Don't get soaked. Take a quick peek at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather


---------------------------------

LLama Gratis a cualquier PC del Mundo.
Llamadas a fijos y móviles desde 1 céntimo por minuto.
http://es.voice.yahoo.com

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]