Sign Up for Training |
insightsoftware Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

SQL= Reference


Expert

Related Articles...

This feature is for power users in special situations only and should not be used regularly

Overview

=NL (What,"SQL=...",Field,FilterField1,Filter1,FilterField2,Filter2,FilterField10,Filter10)

Purpose:  A special type of NL function that uses the power of SQL to allow complete flexibility in retrieving data from a database.

Argument Meaning
What "Rows", "Columns", "Sheets", N, -N, Blank, "SQL" (SQL shows the SQL statement created)
Table

To add Jet interpreted filters to the WHERE or HAVING clauses, add %Filter9% to your existing WHERE term. These parameters will be replaced by appropriate WHERE terms enclosed in ().

Please note that every %FilterX% must have at least one corresponding Jet filter (see the Filterfield information below for more details).

 

If your filter is *, the corresponding %FilterX% will be converted to field LIKE '%'.  If the preceding word in the SQL query is WHERE, and there are no following words like AND or OR, then WHERE will be removed.  If the preceding word is AND, OR, etc., then that word will be removed.  If the preceding word is WHERE and the following word is AND or OR, then the following word will get stripped.

For example:
  • If WHERE was blank in the following: "SQL=Where() AND %Filters1%", the "()" and the "AND" would be removed
  • In the following: "SQL=Where %Filters1%", the WHERE would get removed

To add user-determined sorting to your SQL statement, add %Sort% to the Order By clause.  Since Joins use a common sort (and Sub queries do not use sorting), you do not need to put a number on %Sort%.  Despite this, your NL filters for sorting must still be prefixed by a filter number (see the section on FilterField below)

Field

This is a field cache and returns a Record Key just as when an array of field names is passed in to this argument with a normal NL function.  ( for example:  {"Field1","Field2"} )

The field names actually returned depend on the SQL SELECT statement, itself

FilterField

This parameter contains the WHERE clause replacements in the format "1S=FieldName".  The first character (e.g., 1) corresponds to the %FilterX% that you want to replace.  The second characters determines the Field type and can be one of the following (please note that every %FilterX% in the SQL statement MUST have at least 1 corresponding Jet filter):

D = Date

N = Number

S = String

G = GUID

B = Boolean

The entire FilterField (including the 1S, 2D, etc.) can be prefixed with a "+" or "-" to sort.  So, for example, you can use "+1S=FieldName" or "-1S=FieldName".

Please note that despite the fact that a sort may not correspond to any particular %FilterX% replacement, you must still prefix your soft field with a filter number, data type, and =.  Simply using +FieldName will not work.

 

Filter Filter using standard Jet filters

 

Examples

The following function returns the contact names from the customers table where the contact name starts with A and sorted by the City in descending order.

=NL("Rows","SQL=SELECT ContactName FROM Customers WHERE %filter1% ORDER BY %sort%","ContactName","1S=ContactName","A*","-1s=City","*")

The following function returns all the contact names from the customers table sorted by the city.

=NL("Rows","SQL=SELECT ContactName FROM Customers WHERE %filter1% ORDER BY %sort%","ContactName","-1s=City","*")

Was this article helpful?
1 out of 1 found this helpful

Comments