SQL (use SQL code)

Top  Previous  Next

 

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