Difference between revisions of "SQLLOOKUP()"
From Lianjapedia
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 50: | Line 50: | ||
[[Category:Functions]] | [[Category:Functions]] | ||
[[Category:Search Functions]] | [[Category:Search Functions]] | ||
+ | [[Category:SQL Functions]] |
Revision as of 05:22, 13 January 2017
Contents
Purpose
Function to perform a cross-table lookup
Syntax
SQLLOOKUP(<cTable>, <cKeyField>, <cKeyValue>, <cResultExpr> [, <cNotFoundExpr>])
See Also
KEYLOOKUP(), LOOKUP(), RLOOKUP(), SEEK(), SET RELATION
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.
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