Once loaded, the SQL Query input file can be treated identically to a normal
database file. Each field in the input record is given a numeric name so as
they can be distinguished. These names have the form Unnn where nnn
is a three digit number. For example, to reference the first three columns in
the SQL Query you would do as in the example below.
%%RECORD %U001 %U002 %U003
The real column names from the SQL query can be used instead
by setting the NAMCOL environment variable by simply defining it. Then
instead of the form Unnn the real column names will be used, converted
to uppercase and truncated to 10 characters. Any non-supported field character
may cause problems. Any columns that do not cleanly map can be renamed using
the SQL select x as y syntax if neccessary (this can be often since the
column naming conventions for Ingres are quite flexible).
The example below sets NAMCOL
and prints fields from dog.sql as used in an earlier example.
%%DEFINE PHYSDB animals %%DEFINE NAMCOL %%DATABASE "dog.sql" select name, type, id# as id from dog order by name %%RECORD %NAME %TYPE %ID
Note that like PHYSDB the NAMCOL directive is globally applied to all queries in the GRG file and cannot be set on a per-query basis. Note that setting NAMCOL will also affect the behaviour of any delimited text file databases in the same GRG file.
To establish which field is which when not using NAMCOL and what the column names have been mapped to a simple GRG file can be created that uses the default header and record text bodies.
Go to the first, previous, next, last section, table of contents.