The selection of fields from an ODBC
data source or OLE DB provider is made through standard SQL SELECT statements. However, whether the SELECT statements
are accepted depends on the ODBC driver or OLE DB provider
used.
[ (Inner | Left | Right | Full) jointablenameonfieldref = fieldref ]
Furthermore, several SELECT
statements can sometimes be concatenated into one through the use of a
union operator:
selectstatement Union selectstatement
The SELECT statement is interpreted
by the ODBC driver or OLE DB provider, so deviations from the general SQL syntax might occur
depending on the capabilities of the ODBC drivers or OLE DB provider, for example:.
as is sometimes not allowed,
i.e. aliasname must follow immediately
after fieldname.
as is sometimes compulsory if
an aliasname is used.
distinct, as,
where, group by, order by, or union
is sometimes not supported.
The ODBC driver sometimes does not accept all the different quotation
marks listed above.
Information noteThis is not a complete description of the SQL SELECT
statement! E.g. SELECT statements
can be nested, several joins can be made in one SELECT
statement, the number of functions allowed in expressions is sometimes
very large, etc.
Arguments:
Select arguments
Argument
Description
distinct
distinct
is a predicate used if duplicate combinations of values in the selected
fields only should be loaded once.
distinctrow
distinctrow
is a predicate used if duplicate records in the source table only should
be loaded once.
fieldlist
fieldlist ::= (*| field ) {, field }
A list of the fields to be selected. Using * as field list indicates
all fields in the table.
fieldlist ::= field {,
field }
A list of one or more fields, separated by
commas.
field ::= ( fieldref | expression ) [as aliasname ]
The expression can e.g. be a numeric or string
function based on one or several other fields. Some of the operators and
functions usually accepted are: +, -, *, /, & (string concatenation),
sum(fieldname), count(fieldname), avg(fieldname)(average), month(fieldname),
etc. See the documentation of the ODBC driver for more information.
fieldref ::= [ tablename.
] fieldname
The tablename and the fieldname are text
strings identical to what they imply. They must be enclosed by straight
double quotation marks if they contain e.g. spaces.
The as clause is used for assigning
a new name to the field.
from
tablelist ::= table {,
table }
The list of tables that the fields are to
be selected from.
table ::= tablename [ [as
] aliasname ]
The tablename
may or may not be put within quotes.
where is a
clause used for stating whether a record should be included in the selection
or not.
criterion is a logical expression
that can sometimes be very complex. Some of the operators accepted are:
numeric operators and functions, =, <> or #(not
equal), >, >=, <, <=, and, or,
not, exists,
some, all,
in and also new SELECT
statements. See the documentation of the ODBC driver or OLE DB providerfor more information.
group by
group by is a clause used for aggregating (group) several records into one. Within
one group, for a certain field, all the records must either have the same
value, or the field can only be used from within an expression, e.g. as
a sum or an average. The expression based on one or several fields is
defined in the expression of the field symbol.
having
having is
a clause used for qualifying groups in a similar manner to how the where clause is used for qualifying
records.
order by
order by
is a clause used for stating the sort order of the resulting table of
the SELECT statement.
join
join is a qualifier
stating if several tables are to be joined together into one. Field names
and table names must be put within quotes if they contain blank spaces
or letters from the national character sets. When the script is automatically
generated by QlikView, the quotation mark used is the one preferred by
the ODBC driver or OLE DB provider specified in the data source definition of the data source
in the Connect
statement.
Example 1:
SELECT *
FROM `Categories`;
Example 2:
SELECT `Category
ID`, `Category Name` FROM `Categories`;
Example 3:
SELECT `Order
ID`, `Product ID`,
`Unit Price`
* Quantity * (1-Discount) as NetSales
FROM `Order
Details`;
Example 4:
SELECT `Order
Details`.`Order ID`,
Sum(`Order
Details`.`Unit Price` * `Order Details`.Quantity) as `Result`
FROM `Order
Details`, Orders
where Orders.`Order
ID` = `Order Details`.`Order ID`
Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com
