×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
I have several SQL Views that resolve a "lookup-table driven" value in a
PF from the internal representation to the display value, using an SQL
UDF called "DISPLAYVAL."
In the fragment below, "tbl" is a field that identifies the relevant
range of records in our lookup table, i_key (a parameter to the
function) is the internal representation we are resolving, PTBLE, PCODE,
STBLE, and SCODE are keys to our lookup-table file, DEF is the display
value listed in the lookup-table file, and result is what we are returning.
select DEF INTO result from FOO/LOOKUP
where PTBLE = substring(tbl from 1 for 3)
and PCODE = substring(i_key from 1 for 5)
and STBLE = ' ' and SCODE = ' ';
. . .
> RETURN result;
It works great, for resolving the internal representation to the display
value: if, for a given column (we'll call it "USER"), a value of 00000
in the file is assigned to 'Doe, John Q.', the view shows a meaningful
'Doe, John Q.' instead of the less user-friendly value of 00000.
But I've just been informed that the users would like to be able to
search on at least some of these fields. And that doesn't work at all,
e.g., SELECT * FROM FOO/BAR WHERE USER = 'Doe, John Q.' returns
absolutely nothing.
Presently, for the fields that call DISPLAYVAL in the Views, I'm not
also including the unresolved values, but that could change.
Any suggestions on what to do here?
--
JHHL
midrange.com
