Re: SQL UDF question

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