| **Navigation:**  [[introduction.htm|Language Reference]] > 13 - Built-in Functions >====== SQL (use SQL code) ====== | [[sort  sort queue entries .htm|{{btn_prev_n.gif|Previous page}}]][[introduction.htm|{{btn_home_n.gif|Return to chapter overview}}]][[sqlcallback  register or unregister a sqlcallbackinterface.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

**SQL**(//sql expression)//

{{blk2blue.jpg|blk2blue.jpg}}

| **SQL** | Specifies a proper SQL expression to be used to affect the records in a VIEW. |
| //sql expression// | A string expression containing valid SQL code. |

**SQL** specifies a valid SQL statement which is applied to a FILTER, ORDER, JOIN, PROP:FILTER, PROP:ORDER, or PROP:JOINEXPRESSION surrounding it. Anything specified in the //sql expression //is treated as pure SQL code.

{{tipbox.jpg|TipBox.jpg}}

With the SQL statement, you can now use native SQL code as filter criteria from within the IDE template interface. Example: In the Record Filter prompt, enter SQL('your SQL filter code').

When you are using the SQL statement with a view you need to be aware of how the SELECT statement is generated. By default, the view engine puts an SQL alias on each file in a VIEW. The SQL driver generates an SQL statement which uses an Alias of "A" for the first file in the View, "B" for the second etc. If you wish to use SQL, your filter has to be compatible with the previously generated SQL statement ' i.e., you need use A/B/etc as the file prefixes. If necessary, you can use [[prop alias.htm|PROP:Alias]] to change this behavior. Using the default configuration the following SQL() commands in a filter are illegal, and must be aliased prior to opening the VIEW:

**SQL(dbo.Employees.EmployeeID > 0)**

**! Illegal ' a period is not a valid character in an alias label**

**SQL(Employees.EmployeeID > 0)**

**! Use Employee{PROP:Alias} = '' or Employee{PROP:Alias} = 'Employees' prior to opening VIEW**

**SQL(Emp2:EmployeeID > 0) !This is not a valid SQL statement**

Using the defaults and assuming EmployeeID is a field of the first table in the view, then these statements are legal

**SQL(A.EmployeeID > 0)**

**SQL(EmployeeID > 0)**

**Examples:**

**OrdFile    FILE,DRIVER('ODBC'),PRE(Ord)**

**PrimaryKey  KEY(Ord:OrdID),PRIMARY**

**Record      RECORD**

**OrdID        LONG**

**Customer     LONG**

**PurchaseDate DATE**

**Info         CSTRING(61)**

**            END**

**           END**

**MyView  VIEW(AFile), FILTER('Ord:PurchaseDate = TODAY() AND ' & |**

**       '****SQL****(A.Info LIKE "%Must deliver today%")'), ORDER('****SQL****(A.Customer)')**

**END**

**MyView{PROP:Filter} = **

**  '****SQL****(A.Customer IN (SELECT CustID FROM Customers WHERE BadCustomer = 0))'**

**!ABC template generated code: SQL(ProductID > 5) entered on Record Filter line**

**BRW1.SetFilter('(****SQL****(ProductID > 5))')**

**!For the Clarion template chain, the generated code is:**

**BRW1::View:Browse{Prop:Filter} = '****SQL****(ProductID > 5)'**

**!It is also valid to mix SQL and Clarion references on the Record Filter line**

**! ****SQL****(SupplierID > 7) AND ****SQL****(ProductID > 5)**

**!or**

**! Pro:SupplierID > 7 AND ****SQL****(ProductID > 5) - or these can be flipped as**

**! ****SQL****(ProductID > 5) AND Pro:SupplierID > 7**