Difference between revisions of "KEYLOOKUP()"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 3: | Line 3: | ||
==Syntax== | ==Syntax== | ||
− | KEYLOOKUP(<workarea> | <"alias"> | <"table">, <"indextagname">, <keyExpr>, <resultExpr> [, < | + | KEYLOOKUP(<workarea> | <"alias"> | <"table">, <"indextagname">, <keyExpr>, <resultExpr> [, <notfoundCExpr>]) |
==See Also== | ==See Also== | ||
Line 13: | Line 13: | ||
The KEYLOOKUP() function then evaluates the expression <valueExpr>, and returns the value of the <resultExpr> if the <key expression> is found. | The KEYLOOKUP() function then evaluates the expression <valueExpr>, and returns the value of the <resultExpr> if the <key expression> is found. | ||
− | The KEYLOOKUP() function returns a null string if the <key expression> is not found or the < | + | The KEYLOOKUP() function returns a null string if the <key expression> is not found or the character string expression <notfoundCExpr>, if specified. |
The current record position in the specified <workarea | alias | table> cursor is not moved so this function can also safely be used in SQL queries. | The current record position in the specified <workarea | alias | table> cursor is not moved so this function can also safely be used in SQL queries. |
Revision as of 11:39, 10 July 2019
Contents
Purpose
Function to perform a cross-table lookup
Syntax
KEYLOOKUP(<workarea> | <"alias"> | <"table">, <"indextagname">, <keyExpr>, <resultExpr> [, <notfoundCExpr>])
See Also
Data Mapping, LOOKUP(), RLOOKUP(), SEEK(), SET RELATION, SQLLOOKUP()
Description
The KEYLOOKUP() function looks up the specified index <keyExpr> in the index <indextagname> of the specified <workarea | alias | table>.
The KEYLOOKUP() function then evaluates the expression <valueExpr>, and returns the value of the <resultExpr> if the <key expression> is found.
The KEYLOOKUP() function returns a null string if the <key expression> is not found or the character string expression <notfoundCExpr>, if specified.
The current record position in the specified <workarea | alias | table> cursor is not moved so this function can also safely be used in SQL queries.
If the specified table is not already open then Lianja will open the table automatically.
This function can be used to dynamically lookup foreign key values in target tables.
It can also be used to lookup NoSQL key/value pairs.
See Data Mapping for more information on two way mapping using lookup tables.
Note: for Virtual Tables, see SQLLOOKUP().
Example
open database southwind use orders in 0 ? keylookup("employees","employeeid",orders.employeeid, lastname, "not found") lcTable = "employees" ? keylookup("&lcTable","employeeid",orders.employeeid, lastname, "not found") use employees alias emp in 2 ? keylookup("emp","employeeid",orders.employeeid, lastname, "not found") ? keylookup(2,"employeeid",orders.employeeid, lastname, "not found")