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.
Syntax:
Select [all | distinct | distinctrow | top n [percent] ] fieldlist
From tablelist
[where criterion ]
[group by fieldlist [having criterion ] ]
[order by fieldlist [asc | desc] ]
[ (Inner | Left | Right | Full) join tablename on fieldref = 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.
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. ] fieldnameThe 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. See: From |
| where | 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 Qlik Sense, 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`
group by `Order Details`.`Order ID`;
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!