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#,SOCSEC#,JOB_FTN,DEPT,SALARY,DATE_BN,FST_ NAME,LST_NAME) AS
(SELECT MAX(EMP#),MAX(SOCSEC#),MAX(JOB_FTN),MAX(DEPT),MAX( SALARY),MAX(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#,SOCSEC#,JOB_FTN,DEPT,SALARY,DATE_BN,FST_ NAME,LST_NAME) AS
(SELECT MAX(EMP#),MAX(SOCSEC#),MAX(JOB_FTN),MAX(DEPT),MAX( SALARY),MAX(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(VARCHAR()) in your
post.
Only get_pages(integer, 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(VARCHAR()) in your
post.
Only get_pages(integer, integer)

Cheers
Serge

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