SQLLOOKUP()

From Lianjapedia
Jump to: navigation, search

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