Ask Your Question
0

How does the "SELECT INTO" work in Base [closed]

asked 2014-07-07 09:10:31 +0200

allanoptical gravatar image

On page 137 of the Base handbook there is reference to SELECT [INTO CACHED|TEMP|TEXT] "New+table"]

When I enter a line like SELECT * from "members" WHERE "memnum" >180 INTO TABLE "check" I get an error complaining about the INTO Token. What is the correct way to use this feature.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-28 22:01:00.247902

2 Answers

Sort by » oldest newest most voted
2

answered 2014-07-08 01:08:37 +0200

w_whalley gravatar image

I assume you are using the embedded HSQLDB engine. Review the HSQLDB 1.8 guide at http://www.hsqldb.org/doc/guide/ch09....

I did some experiments. These statements are inserted into the SQL tool (Tools/SQL). View/Refresh Tables to view the newly created tables.

SELECT * INTO TEXT OUTFILE FROM DTDIST WHERE DISTANCE = 1.0;

Produces a file OUTFILE.csv with the selected data, and a link to that file in base

SELECT * INTO CACHED OUTPUT FROM DTDIST WHERE DISTANCE = 1.0;

Produces a new table in base called OUTPUT with the data.

SELECT * INTO TEMP TFILE FROM DTDIST WHERE DISTANCE = 1.0;

The statment executes but no TFILE is found even after tables refresh. Repeating the SELECT command gives table already exists error. I can execute SELECT * FROM TFILE; but no output is shown in the sql editor. In Queries the SELECT statement gives a table with headings but no data; Same result even after the base file is closed and reopened. Examining the script in the unzipped base file shows that there is a table creation statement CREATE GLOBAL TEMPORARY MEMORY TABLE TFILE("ID" INTEGER,"DISTANCE" NUMERIC(100,4),"TIME" TIME), but no associated data.

So for you the statement

SELECT * INTO CACHED "check" FROM "members" WHERE "memnum" > 180;

should work.

edit flag offensive delete link more
0

answered 2014-07-08 10:00:12 +0200

allanoptical gravatar image

Many thanks, this does indeed work, exactly as I wanted it. I still find the syntax of SQL awkward and in this case, as in so many in my experience of coding, once I can am told the answer it is so obvious.

edit flag offensive delete link more

Comments

PLease use comments and upvoting/marking the answer as correct to show thanks instead of adding an answer to this question. https://wiki.documentfoundation.org/A...

bencomp gravatar imagebencomp ( 2014-07-10 21:22:38 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-07-07 09:10:31 +0200

Seen: 2,401 times

Last updated: Jul 08 '14