Re: UDF returning a Boolean

On 03-Feb-2012 14:04 , Kurt Anderson wrote:

Can a UDF return a Boolean value? I am taking my first stab at
making a UDF for SQL (after successfully writing my first stored
procedure). This function would wrap up a service program procedure
that returns an indicator. It looks like I might have to make the
return value CHAR and check for '1' to see if the UDF is returning a
'true' value. Since SQL can handle Boolean (e.g. where exists), I was
hoping the UDF could return such a value so I wouldn't need the
actual SQL to say = '1'.

For example:

Want to do:
Select * From callrecords
Where isTollFree( ANI )

Worried I might have to do:
Select * From callrecords
Where isTollFree( ANI ) = '1'

I didn't have any luck in my own search, so I was hoping someone
might be able to chime in. I'm at IBM i 7.1

No idea why having to code isTollFree(ANI)=1 would be "worrisome" ;-) No really, I do understand and I believe a boolean data type in SQL would be nice, especially for that scenario. Presumably the data type is still awaiting inclusion in standards.?

I checked the CREATE FUNCTION (scalar) and the RETURNS /data-type2/ does not include BOOLEAN as of v7r1; well, at least according to what is supposed to be the full list of each /built-in-type/ available, shown in the CREATE TABLE documentation.

So the best I can determine, there is still no BOOLEAN data type [regardless the token "BOOLEAN" is a reserved name for the SQL], so an alternative such as SMALLINT, CHAR(1), or any data type with the NULL indicator, with the caveat that a predicate define the logic\test. However there may be value considering that the "IS" from the NULL and DISTINCT predicates may be more desirable [visually] over an equivalence test or other comparison operators. Though not resolving the concern for testing any particular value, because IS NULL and IS NOT NULL represent a binary result even if the non-NULL value does not limit the outcome to binary, I still might prefer to use:
WHERE isTollFree(ANI) IS NOT NULL

If the alluded as /boolean/ for rows selected is desired, then there should be the possibility to reference the UDF as a table UDF [a UDTF] instead using the EXISTS predicate. But if the goal is merely to make the statement /pretty/ by eliminating the comparison operator, rather than limiting the values someone might misuse as the expected RETURNS to mean "yes", then the EXISTS predicate with fullselect is far from an improvement over the equal test:
WHERE EXISTS (SELECT 1 FROM TABLE(isTollFree(ANI))

Depending on how the isTollFree UDF is implemented, there may be various other ways to implement the logic. However if the data that answers the question is not already available in rows, then any other implementation that might best provide rows, might also probably best change the UDF to reference that same [effective row] data such that only the one copy is maintained. For example what may currently be an array of literal values in the isTollFree UDF could be exposed as rows from a UDTF or rows in a TABLE, but the isTollFree scalar would best change to reference the UDTF or TABLE for that data rather than using a separate array of literals.

Regards, Chuck