Looks up a given key in a term list and returns a substitute string. This function enables you to perform string replacements within a query. This function is an InterSystems extension to MDX.
This function returns a string
evaluates to the string to look up in the term list.
, which is optional, evaluates to the value to return if lookup_value
is not found in the term list.
This argument is not case-sensitive.
This function examines the given term list, finds the term whose "key"
field equals the string given by lookup_value
and then returns the value contained in the field identified by alternative_field
The system notes the most recent modification time stamp of any term list and invalidates any query cache that uses an outdated term list.
For example, suppose that for HoleFoods, we have a term list named Teams that is defined as follows:
Here is a simple query that uses this term list:
SELECT Lookup("Teams",Outlet.Boston.Properties("NAME")) ON ROWS FROM HOLEFOODS
Lookup Red Sox
Here is a more complex query:
SELECT Lookup("Teams",Outlet.CURRENTMEMBER.Properties("NAME"),"No Team") ON 0,
outlet.city.MEMBERS ON 1 FROM HOLEFOODS
1 Amsterdam No Team
2 Antwerp No Team
3 Atlanta Braves
4 Bangalore No Team
5 Barcelona No Team
6 Beijing No Team
7 Berlin No Team
8 Boston Red Sox
9 Brasilia No Team