| Navigation: Language Reference > 13 - Built-in Functions >====== SQL (use SQL code) ====== |    |
SQL(sql expression)
| 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.
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 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