SQL Editor (View menu) - SQL Tab
ClicknDECiDE Builder permanently updates the SQL editor according to your query definition and taking care about the data source used. The SQL syntax is an internal ClicknDECiDE Builder syntax and the ClicknDECiDE Builder SQL engine will translate to the real Host SQL depending on the data source type used.
See also:
The ClicknDECiDE Builder SQL always begins with:
DEFINE SOURCE "ClicknDECiDE Sample Database" TYPE src;
that allows to specify the data source to be used.
Then the mandatory SQL statements are:
SELECT followed by the field names, separated by a comma. Each field name is preceded by the Alias corresponding to the Table name. Example:
SELECT CATEGORY."CATEGORY NAME", PRODUCT."PRODUCT"
FROM followed by the table names, preceded by the localization name if exist (e.g. a Library name for an AS/400). Each table name is separated with a comma and followed by an Alias name. Example:
FROM "CATEGORY" CATEGORY,"PRODUCT" PRODUCT
The optional SQL statements are:
JOIN INNER followed by the join condition between 2 field names of 2 tables. Each field name is preceded by the Alias corresponding to the table name. The join statement exists only if more than one table is selected. The JOIN statement can be JOIN LEFT or JOIN RIGHT depending on the join type defined between 2 tables. Example linking two tables:
JOIN INNER CATEGORY."CATEGORY CODE"=PRODUCT."CATEGORY CODE"
WHERE followed by the criteria conditions if exist. The criteria conditions can contain some parenthesis, after the join conditions if exist. Example:
WHERE PRODUCT."CATEGORY CODE" BETWEEN 1 AND 3 AND (PRODUCT."PRODREF" IN('0113','0114','0115','0116','0117','0118','0119','0120','0210','0211','0212','0213','0214') OR PRODUCT."PRODUCT" LIKE 'SPEED*')
ORDER BY followed by the field names or the position of the fields on which a sort condition has been defined. Each field name or field position is separated by a comma. Example:
ORDER BY 1,2 DESC
GROUP BY followed by all the field names not corresponding to an aggregate function. If the SELECT statement contain at least an aggregate field such as SUM(field), MIN(field), MAX(field), AVG(field), STDV(field), STDVP(field), VAR(field), VARP(field), COUNT(field) or COUNT(*), all the other fields must exist in the GROUP BY statement. It is a rule in SQL language. Example: adding a COUNT(*) function in the SELECT statement:
SELECT CATEGORY."CATEGORY NAME", PRODUCT."PRODUCT", COUNT(*)
The GROUP BY section is automatically added with all the fields not concerned by a grouping function:
GROUP BY CATEGORY."CATEGORY NAME",PRODUCT."PRODUCT"
HAVING followed by the criteria defined on an aggregate field. Example:
HAVING COUNT(*) > 2
Other SQL statements depending on the Data Source type:
DISTINCT: that clause can exist in the SELECT statement to retrieve only distinct values or group values.
Example:
SELECT DISTINCT PRODUCT."PRODUCT", "SALES DETAILS"."FAMILY" FROM "SALES DETAILS" "SALES DETAILS","PRODUCT" PRODUCT JOIN INNER "SALES DETAILS"."PRODREF" = PRODUCT."PRODREF"
The DISTINCT statement
can be modified using the icon
UNION: that clause allows getting the data from 2 queries one after the other, giving distinct records sorted by default on the first column. The 2 queries must have the same field type on the same position (numeric with numeric, character with character etc.). An order by clause can be apply on the query calling the other query, but not on the query called. Example: a query retrieving the salesman and customer name and area or country without duplicate and with a sort on the column #2. (AREA and COUNTRY):
SELECT SALESMAN."SALNAME", SALESMAN."AREA"
FROM "SALESMAN" SALESMAN
UNION
SELECT CUSTOMER."CUSTNAME", CUSTOMER."COUNTRY"
FROM "CUSTOMER" CUSTOMER
ORDER BY 2;
UNION ALL: that clause is working as the above UNION clause but produces detailed records instead of giving distinct records. The other rules are the same. Example with the same query retrieving the salesman and customer name and area or country with duplicate if exist and without sort:
SELECT SALESMAN."SALNAME", SALESMAN."AREA"
FROM "SALESMAN" SALESMAN
UNION ALL
SELECT CUSTOMER."CUSTNAME", CUSTOMER."COUNTRY"
FROM "CUSTOMER" CUSTOMER
Show native SQL: if you wish to see how ClicknDECiDE Builder send the translated SQL to the Host database, click on the command <Tools> <Options> then select the <Query> tab to activate the option "Show native SQL"
Going back to the Table tab: click on the <View> <Table> command or on the Table tab to modify the selected tables or join conditions.
Going back to the Query tab: click on the <View> <Query> command or on the Query tab to go back to the query worksheet.