Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

SQL= Reference


SQL= Reference

=NL (What,Table,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. Note that this feature is for power users in special situations only and should not be used regularly.

Argument Meaning
What Rows, Columns, Sheets, N, -N, Blank, SQL (SQL shows the SQL statement created)
Table To add Jet Essentials 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 Filterfield1 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 FilterField1 below)

Field Field cache (example: {"Field1","Field2"} = Returns a Record Key just like what happens when an array of field names is passed in to this argument with a normal NL function. The field names actually returned depend on the SQL Select Statement
FilterField

WHERE term replacements in the format "1S=FieldName".  The first character 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

 

Access Northwind 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?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments