Can we create UDF using View instead of table

[lenygold via DBMonster.com]

Hi everybody!
I have a UDF. It is working fine.
But when i try apply the same UDF to a view it is not working.
CREATE VIEW SSN_SORTED AS
WITH TEMP(EMP#,SOCSE C#,JOB_FTN,DEPT ,SALARY,DATE_BN ,FST_NAME,LST_N AME) AS
(SELECT MAX(EMP#),MAX(S OCSEC#),MAX(JOB _FTN),MAX(DEPT) ,MAX(SALARY),MA X(DATE_BN)
,MAX(FST_NAME), MAX(LST_NAME)
FROM PERSONNEL
GROUP BY SOCSEC#
FETCH FIRST 20000 ROWS ONLY)
SELECT * FROM TEMP;

Here is UDF fow paging this veiw:
CREATE VIEW SSN_SORTED AS
WITH TEMP(EMP#,SOCSE C#,JOB_FTN,DEPT ,SALARY,DATE_BN ,FST_NAME,LST_N AME) AS
(SELECT MAX(EMP#),MAX(S OCSEC#),MAX(JOB _FTN),MAX(DEPT) ,MAX(SALARY),MA X(DATE_BN)
,MAX(FST_NAME), MAX(LST_NAME)
FROM PERSONNEL
GROUP BY SOCSEC#
FETCH FIRST 20000 ROWS ONLY)
SELECT * FROM TEMP;

UDF example of use:
SELECT * FROM TABLE(GET_PAGES _SSN('000-00-0000'))
FETCH FIRST 20 ROWS ONLY;

sqlcode: -440
No authorized routine named "GET_PAGES_ SSN" of type "FUNCTION
" having compatible arguments was found.
Here is same UDF working without any problem with Tables:

CREATE FUNCTION GET_PAGES(PAGE_ NO INTEGER,LINES INTEGER)
RETURNS TABLE ( EMP# INTEGER
,SOCSEC# CHAR(11)
,JOB_FTN CHAR(4)
,DEPT SMALLINT
,SALARY DECIMAL(7,2)
,DATE_BN DATE
,FST_NAME VARCHAR(20)
,LST_NAME VARCHAR(20)
)
RETURN
Select *
from
PERSONNEL
where
INTEGER(SUBSTR( (CHAR(EMP#)),1, 7)) >= LINES * (PAGE_NO - 1) + 1000000;

EXAMPLE OF USE
SELECT * FROM TABLE(GET_PAGES (3,12))
FETCH FIRST 12 ROWS ONLY;
EMP# SOCSEC# JOB_FTN DEPT SALARY DATE_BN FST_NAME
LST_NAME
----------- ----------- ------- ------ --------- ---------- -----------------
--- --------------------
2000000 182-27-6668 WKR 27 12712.08 02/04/1973 Fehdcd
Eamitaaa
2000001 360-75-6667 WKR 33 12739.97 02/04/1973 Tjiddd
Jeiitaya
2000002 223-88-6663 WKR 20 3143.58 01/01/1978 Xffabc
Feabaimm
2000003 599-41-6664 WKR 18 4255.15 01/02/1977 Jpebbb
Piybeabb
............... ............... ............... ............... ............... ...
............... ............... ............... ...........
Thank's in advance.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200809/1

[Serge Rielau]

>SELECT * FROM TABLE(GET_PAGES _SSN('000-00-0000'))

FETCH FIRST 20 ROWS ONLY;

sqlcode: -440
No authorized routine named "GET_PAGES_ SSN" of type "FUNCTION
" having compatible arguments was found.

You did not describe any function named get_pages_ssn(V ARCHAR()) in your
post.
Only get_pages(integ er, integer)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[lenygold via DBMonster.com]

Thank you Serge.
I fixed and it is working.

Serge Rielau wrote:

>>SELECT * FROM TABLE(GET_PAGES _SSN('000-00-0000'))
FETCH FIRST 20 ROWS ONLY;

sqlcode: -440
No authorized routine named "GET_PAGES_ SSN" of type "FUNCTION
" having compatible arguments was found.

You did not describe any function named get_pages_ssn(V ARCHAR()) in your
post.
Only get_pages(integ er, integer)

Cheers
Serge

--
Message posted via http://www.dbmonster.com