rob@xxxxxxxxx wrote:
Instead of "USER" wouldn't you be searching on DEF? For example,

select LookupTheName(USER) from ...
where LookupTheName(USER)= 'Doe, John Q.'

Not exactly.

This DISPLAYVAL UDF is called in the views themselves, which are defined sort of like:

CREATE VIEW FOO/BAR_V1 (RECORD_ID, CATEGORY, TYPE, USER,
CREATED_USER, ACCOUNT_ID, ACTIVITY_ID, NOTE_FLAG, DOC_FLAG ) AS SELECT ACCRID, DISPLAYVAL(9001, DIGITS(ACCCAT)),
DISPLAYVAL(9011, ACCTYP), DISPLAYVAL(9012, DIGITS(ACCUSR)),
DISPLAYVAL(9012, DIGITS(ACCCUS)), ACCEID, ACCAID, ACCNOT, ACCDOC FROM FOO/BAR ; LABEL ON COLUMN FOO/BAR_V1 ( CATEGORY IS 'Category', TYPE is 'Type', USER IS 'User', CREATED_USER IS 'Created User'
)

(The actual views have about twice as many fields, some of them calculated from a DISPLAYDAT function that *does* provide a searchable result. And of course, some of the names have been changed to protect the innocent.)

The first parameter to DISPLAYVAL is the field identifier, from which DISPLAYVAL finds the correct range for the look-up; the second parameter is the key for the specific record to be looked up.

And the fields calculated with DISPLAYVAL aren't currently searchable. Is it because DISPLAYVAL is so complex? Because the "real" DISPLAYVAL contains IF statements (including one that selects between two variations of the fragment I quoted (and quote again below?
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;

Is it because I defined the UDF to return a VARCHAR (and then, inexplicably, didn't bother to TRIM the value of DEF before returning it?

I've been using SQL on and off for a few years now, mostly through the CLI, and I still feel like (and freely admit to being) a total NOOB with it.

Now, there would be no problem in my adding the raw internal values for the fields on which the VIEW calls DISPLAYVAL, but at present, they aren't in the VIEW.

--
JHHL

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].