The command Create Native SQL (Query Menu)

That command allows generating in a special SQL editor the native SQL corresponding to the ClicknDECiDE Builder current query.

That SQL editor allows thus to follow to work with the native SQL to make specific queries needing for example to insert another SQL in the current native SQL.

See also the command <Edit> <Insert SQL> that allows inserting a native SQL from another query at the mouse cursor location.

Example of use: you need to insert another query beginning with the SELECT statement just after the current FROM statement (the table list will be thus given by another query).

Remark about the SQL you will get: exporting to native SQL will only take care about the SQL that can be understood by the data source. So, avoid creating native SQL from within a ClicknDECiDE Builder having internal virtual fields or criteria on such fields because they cannot be used in native SQL, and the result could be different. If an internal formula contains a field name, that field can be selected in the native SQL but not the full formula.

Remark about the use of parameters in the native SQL editor:

It is possible to use some parameters in the native SQL editor.

The syntax to be used is the following:

{d7p <Parameter name>}

Example: SELECT * FROM {d7p PARAM_LIBRARY}/TABLE1 WHERE …

Here the parameter name is PARAM_LIBRARY that will allow feed the Library name as a variable.

Note: if the characters { or } must be keep in the criteria or are used in the parameter name, you need to enter it twice. In that case that character will be kept and no considered as a parameter declaration.

Warning: all the special expressions for parameter values cannot be used here. For example it will not be possible to use the IGNORE value or automatic ranges for a date, etc

New since version 4.20.04: Previously when using native SQL, it was not possible to use multiple values for parameters with the syntax {d7p parameter_name}. Now, ClicknDECiDE Builder accepts multiple values but only if you use the following SQL command « IN ({d7p parameter_name}) » that is the equivalent of the criteria “included in the list” in ClicknDECiDE Builder. Any other operand in this type of criteria will generate an error or return no data which should not be considered as an anomaly in the software but as a limitation.

Example : SELECT * FROM TABLE WHERE FIELD1 IN ( {d7p parameter_name} )

If the parameter called « parameter_name » is defined as string type and gets several values, the SQL will be sent as follow:

SELECT * FROM TABLE WHERE FIELD IN ( ‘VALUE1’,’VALUE2’,’VALUE3’)

But with any other criteria operator in place of the IN you will get an error or no data.

If the parameter called « parameter_name » is defined as numeric type and gets several values, the SQL will be sent as follow:

SELECT * FROM TABLE WHERE FIELD IN ( VALUE1, VALUE2 , VALUE3 )