Re: Trying to use a SELECT ...INTO Command with InterBase, Sybase, and Informix

Peter DeSimone wrote: > > I am trying to run the following command: > > SELECT TSP_COMPONENTS.ID LEVEL0ID, TSP_COMPONENTS.NAME LEVEL0NAME, > TSP_COMPONENTS.ABBREVIATION LEVEL0Abbreviation > FROM TSP_COMPONENTS > WHERE (((TSP_COMPONENTS.COMPONENT_LEVEL)=0)) > INTO Level_0 > > I want to take the data from the SELECT'FROM'WHERE SQL Statement and put it > into a new table called LEVEL_0. The Interactive SQL tool doesn't like the > INTO command. Everything else in that SQL statement works fine. > > This statement works fine in Access and SQL: What is this product "SQL" you refer to? I understood it was a public standard. The SQL-92 standard does not have a statement which will do what you want. Most real-life products, including Informix do. Their syntax varies. RTFM. See below. > > SELECT TSP_COMPONENTS.ID AS LEVEL9ID, TSP_COMPONENTS.NAME AS LEVEL9NAME,> TSP_COMPONENTS.ABBREVIATION AS LEVEL9Abbreviation INTO Level_9 > FROM TSP_COMPONENTS > WHERE (((TSP_COMPONENTS.COMPONENT_LEVEL)=9)) > > Any help would be appreciated. > <snip> Axel's reply for Informix is correct: SELECT ... FROM ... WHERE ... INTO TEMP mytable; If you are developing across databases I suggest you use SQL as close to the ANSI standard as possible to avoid the kind grief you are experiencing with this. You will find Informix is fairly close with a few significant omissions. The last time I looked at Sybase/SQL-Server they were idiosyncratic to put it kindly. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---