Difference between revisions of "SQLLOOKUP()"
From Lianjapedia
Barrymavin (Talk | contribs) (→Description) |
Yvonne.milne (Talk | contribs) |
||
| Line 6: | Line 6: | ||
==See Also== | ==See Also== | ||
| − | [[KEYLOOKUP()]], [[LOOKUP()]], [[RLOOKUP()]], [[SEEK()]], [[SET RELATION]] | + | [[KEYLOOKUP()]], [[LOOKUP()]], [[RLOOKUP()]], [[SEEK()]], [[SET RELATION]], [[SQLCHECKUNIQUE()]] |
==Description== | ==Description== | ||
Latest revision as of 13:12, 18 March 2020
Contents
Purpose
Function to perform a cross-table lookup
Syntax
SQLLOOKUP(<cTable>, <cKeyField>, <cKeyValue>, <cResultExpr> [, <cNotFoundExpr>])
See Also
KEYLOOKUP(), LOOKUP(), RLOOKUP(), SEEK(), SET RELATION, SQLCHECKUNIQUE()
Description
The SQLLOOKUP() function operates in a similar way to the KEYLOOKUP() function but uses SQL SELECT. The function will auto-detect if the specified table is a native Lianja table or a Virtual Table and act accordingly. It can be used to perform cross-table lookups for validation or data mapping. The current state of all active cursors is saved and restored automatically so that you can query tables that are already open.
You should always provide a fully qualified database!table if your app is to work in Web/Mobile e.g "southwind!employees".
| Keywords | Description |
|---|---|
| cTable | Character expression to specify the table to be queried (native or Virtual Table). |
| cKeyField | Character expression to specify the column in the table to be queried. Two special options for cKeyField which are particularly useful for Virtual Tables also exist. If cKeyField is a valid WHERE clause (starting with "where ") then that will be used as the WHERE condition for the SQL SELECT statement that is executed. If cKeyField is a valid SELECT statement (starting with "select ") then that will be used as the SQL SELECT statement that is executed. |
| cKeyValue | Character expression containing the value to be compared against cKeyField. If this is a character field then enclose it in quotes within quotes: " 'string' ". If it is a numeric then enclose it like this "1". |
| cResultExpr | Character expression to specify what to return from the query. This can include Lianja/VFP functions as it is evaluated against the record returned from the query. |
| cNotfoundExpr | Character expression that will be evaluated and returned if no record was found that matches the query. |
Example
open database southwind ? sqllookup("employees","employeeid","1","trim(firstname)+' '+ lastname","'Not found'") Nancy Davolio ? sqllookup("employees","employeeid","77","firstname+lastname","'Not found'") Not found ? sqllookup("employees","lastname","'Davolio'","employeeid","0") 1 ? sqllookup("employees","lastname","'MacDavolio'","employeeid","0") 0 ? sqllookup("employees","where lastname = 'Davolio' and firstname = 'Nancy'", "","employeeid","0") 1 ? sqllookup("vt_orders","where orderid = 10248", "","customerid","'Not found'") WILMK ? sqllookup("vt_orders","select * from orders where orderid = 10248", "","customerid","'Not found'") WILMK
