Hi All, I'm testing a UDTF and don't understand why the results are
empty when I call the UDTF with no parameters.
CREATE OR REPLACE FUNCTION mylib.Display_ChkJrn (
fromDate DATE DEFAULT '2023-08-27',
toDate DATE DEFAULT CURRENT_DATE,
usr CHAR(10) DEFAULT '' )
RETURNS TABLE (
joobj CHAR(10),
....
pgmtext CHAR(50))
LANGUAGE SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL
SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO
RETURN
SELECT *
FROM mylib.chkjrn
-- WHERE jodate BETWEEN fromDate AND toDate
-- AND jouser <> usr
ORDER BY jodate DESC, joseqn;
**Note the WHERE clause is commented out.
So if I call Dispay_Chkjrn with no parameters I expected all rows would be
returned. But actually no rows are returned.
SELECT * FROM TABLE(mylib.Display_ChkJrn()) x;
But what is really confusing me is if I populate one of the parameters then
all rows are returned as expected.
SELECT * FROM TABLE(mylib.Display_ChkJrn(fromDate=>'2023-11-01')) x;
Since the WHERE clause is commented out in the UDTF then I would have
expected the passing parameters or not would have no effect on the
resultset and either way all rows would be returned.
This mailing list archive is Copyright 1997-2026 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].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.