Pervasive

Hi,

why this is not working. It works without the order by

Kelloggs

INSERT INTO MY_TABLE

SELECT * FORM MY_SERVER.MY_DBA._MYTABLE

WHERE MY_FIELD < '2002-12-31' ORDER BY MY_FIELD1

What error are you you getting?  Are you using PSQL v10.30 (or later)? 

I just tried with the following statements in PSQL 10.30 and it worked.  Do these work for you? 

CREATE TABLE "Tuition"(
 "ID" IDENTITY DEFAULT '0',
 "Degree" VARCHAR(4) NOT NULL  CASE ,
 "Residency" BIT NOT NULL ,
 "Cost_Per_Credit" REAL,
 "Comments" CHAR(100));

CREATE INDEX "Degrees" ON "Tuition"("Degree");

insert into tuition1 select * from tuition where id >4 order by degree;

 This is my original sql scrip. The error is: Execution error. on the ORDER<<???

If you remove the "ORDER BY MTWO_WIP_WOPRE ASC" it works fine.

INSERT INTO WORKHORD
select
'N' AS DEL_STATUS,
MTWO_WIP_WOPRE,
MTWO_WIP_WOSUF,
MTWO_WIP_STATUS,
MTWO_WIP_CODE,
MTWO_WIP_PROJ,
MTWO_WIP_SONUM,
MTWO_WIP_SOLINE,
MTWO_CUSTCODE,
MTWO_CUSTNAME,
MTWO_WIP_DESC,
MTWO_WIP_COMQTY,
MTWO_WIP_CUSORD,
MTWO_WIP_ASTART,
MTWO_WIP_AFIN,
MTWO_WIP_ASETUP,
MTWO_WIP_AMAT,
MTWO_WIP_AOUTPR,
MTWO_WIP_ALABOR,
MTWO_WIP_ATOTAL,
MTWO_WIP_AVOVHD,
MTWO_WIP_AFOVHD,
MTWO_WIP_AOTH,
MTWO_WIP_AMISC,
MTWO_WIP_AEXTRA,
MTWO_WIP_INSTR_1,
MTWO_WIP_INSTR_2,
MTWO_WIP_INSTR_3,
MTWO_WIP_INSTR_4,
MTWO_WIP_INSTR_5,
MTWO_WIP_INSTR_6,
MTWO_WIP_INSTR_7,
MTWO_WIP_INSTR_8,
MTWO_WIP_INSTR_9,
MTWO_WIP_INSTR_10
from UMCORP.WORKHORD
where  MTWO_WIP_AFIN < '2003-01-01'
ORDER BY MTWO_WIP_WOPRE ASC

ORDER BY MTWO_WIP_WOPRE ASC

What data type is MTWO_WIP_WOPRE? 

Are you using PSQL v10.30 (or later)?  

In your post, you have two ORDER BY clauses, is one just a typo?  

Does it work if you remove the ASC? 

I changed my original SQL statements to: 

CREATE TABLE "Tuition"(
 "ID" IDENTITY DEFAULT '0',
 "Degree" VARCHAR(4) NOT NULL  CASE ,
 "Residency" BIT NOT NULL ,
 "Cost_Per_Credit" REAL,
 "Comments" CHAR(100));

CREATE INDEX "Degrees" ON "Tuition"("Degree");

insert into tuition select * from demodata.tuition where id >4 order by degree;

and created a new database called "demo".  I ran the statements above and they worked while logged into the DEMO database.  

 Do my statements work for you using DEMODATA (and another database)? 

 MTWO_WIP_WOPRE = Double

two ORDER BY  = Typo

 Does it work if you remove the ASC? = No

Are you using PSQL v10.30 (or later)?  = 10.00.151.000

 Do my statements work for you using DEMODATA (and another database)?  = No

If it doesn't work with 10.00, I would suggest downloading 10.30 and installing that.  When I tried it with 10.30, it worked.  Pervasive won't fix 10.00 (or 10.3x) at this point.  You might try applying patches (10.01, 10.10, 10.20, and 10.30) until you find one that works or you can just download the 10.30 version and use the last version of PSQL v10. 

 Thanks!!!