Thanks for the responses Chuck and Joe.
I'll have to go with the = '1' for now, which is still better than not having access to the procedure in the first place.
-Kurt
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Saturday, February 04, 2012 12:16 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
