I must be looking in all the wrong places whether IBM manuals, online Help or on the Net....

I create a scalar UDF such as:

create function one()
returns integer
return 1

Now, in MS SQL or Oracle, I can then enter a SQL statement such as:

SELECT one(); or SELECT one() from DUAL; and I get the number "1" returned as one row but I can't seem to get the equivalent in DB2.

If I try my UDF - or a system scalar function, for example:

select tan(1.5) from syscat.functions

I get the answer hundreds of times (it's probably not the right way then...)

Can anybody tell me the equivalent in DB2? The documentation always refers to some user defined table somewhere. Thanks...

When you execute

select tan(1.5) from syscat.functions

you, obviously, receive the value of tangent for each row in syscat.functions, which may well be in the hundreds.

There is an equivalent of Oracle's DUAL table in DB2; its name is "SYSIBM.SYSDUMMY1". It only contains one row; as a result,

SELECT TAN(1.5) FROM SYSIBM.SYSDUMMY1

will return only one row. Another way to get the same result would be

VALUES(TAN(1.5))

Your problem is that you need a table with only one row so that your result set has only one row. IBM provides just such a table: sysibm.sysdummy1

try:

select tan(1.5) from sysibm.sysdummy1

Andy