SQL support / Static SQL statements |
Produces a result set from a query on database tables.
select-statement [ UNION [ALL] select-statement ] [...]
SELECT [subset-clause] [duplicates-option] { * | select-list } [ INTO variable [,...] ] FROM table-list [,...] [ WHERE condition ] [ GROUP BY column-list [ HAVING condition ]] [ ORDER BY column [{ASC|DESC}] [,...] ]
[ SKIP { integer | variable }] [ {FIRST|MIDDLE|LIMIT} { integer | variable ]
{ ALL | DISTINCT | UNIQUE }
{ [@]table-specification.* | [table-specification.]column | literal } [ [AS] column-alias ] [,...]
{ table-name | OUTER table-name | OUTER ( table-name [,...] ) } [,...]
table-specification [ [AS] table-alias]
[dbname[@dbserver]:][owner.]table
column-name [,...]
[table.]column
The dbname, dbserver and owner prefix of the table name should be avoided for maximum SQL portability.
MAIN DEFINE myrec RECORD key INTEGER, name CHAR(10), cdate DATE, comment VARCHAR(50) END RECORD DATABASE stock LET myrec.key = 123 SELECT name, cdate INTO myrec.name, myrec.cdate FROM items WHERE key=myrec.key END MAIN
If the SELECT statement returns more than one row of data, you must declare a database cursor to process the result set.
MAIN DEFINE myrec RECORD key INTEGER, name CHAR(10), cdate DATE, comment VARCHAR(50) END RECORD DATABASE stock LET myrec.key = 123 DECLARE c1 CURSOR FOR SELECT name, cdate FROM items WHERE key=myrec.key OPEN c1 FETCH c1 INTO myrec.name, myrec.cdate CLOSE c1 END MAIN
The SELECT statement can include the INTO clause, but it is strongly recommended that you use that clause in the FETCH instruction only.
The SELECT INTO TEMP statement creates temporary tables. Such statement does not return a result set.