informix-community

Open full view…

External table usage

speres
Wed, 04 Dec 2019 15:09:16 GMT

Hi, I am trying to use external table with some query results, but I am getting syntax errors [201]. As I am newbie over SQL, can somebody give me some help please? My code: DROP TABLE IF EXISTS isconnected; CREATE EXTERNAL TABLE isconnected ( sesid INT, userdbname CHAR(40), machineid CHAR(40), bdename CHAR(15), dateconn DATETIME YEAR TO FRACTION ); USING (DATAFILES ("DISK:/tmp/ isconnected.ext") ); SELECT x0.sid, x0.username, x0.hostname, x1.sqs_dbname FROM sysmaster:"informix".syssessions x0, sysmaster:"informix".syssqlstat x1 WHERE ( x0.sid = x1.sqs_sessionid ) AND hostname is not null AND trim(hostname) <> '' AND trim(hostname) <> '-' AND sqs_dbname not like 'sys%' AND sqs_dbname <> '-' INTO EXTERNAL isconnected Thanks, SP

andreasl
Wed, 04 Dec 2019 15:24:49 GMT

One error might be that blank in "DISK:/tmp/ isconnected.ext” ... another one is the extra semi-colon before USING. Lastly that "INTO EXTERNAL" clause looks wrong too. Either you'd say "insert into isconnected SELECT xo.sid, ...", with the external table created first, or you'd create the external table from within the SELECT statement using "... INTO EXTERNAL isconnected USING (...)" - and not create the external table first.