I'm struggling with creating my first SQL UDTF - I just don't know the SQL/PL beyond embedded SQLRPGLE (i.e. I don't know what I'm doing).
My input is USERNAME
Output is COM_NBR, COM_NAME, COM_TYPE (essentially a list of companies that a particular user is authorized to)
We have a table with one row per user name... the 40 character field can contain three asterisks, or other values.
If the field contains three asterisks, then I want to return all values from view V_CLIENTS. Otherwise, I want to return the values from V_USERSEC where field user_name = USERNAME input.
I've tried to use the Create Function interface in ACS... this is the SQL it produced in Run SQL Scripts
This produces an error " ; TOKEN NOT VALID"
I also tried this
This produces error: Message: [SQ20120] SQL table function must return a table result.
I'm hoping someone can point out my error(s).
Thx.
UPDATE: I've actually replaced this with a view. However, I'm still curious as to what I'm doing wrong.
My input is USERNAME
Output is COM_NBR, COM_NAME, COM_TYPE (essentially a list of companies that a particular user is authorized to)
We have a table with one row per user name... the 40 character field can contain three asterisks, or other values.
If the field contains three asterisks, then I want to return all values from view V_CLIENTS. Otherwise, I want to return the values from V_USERSEC where field user_name = USERNAME input.
I've tried to use the Create Function interface in ACS... this is the SQL it produced in Run SQL Scripts
Code:
CREATE FUNCTION MYLIB.VLF_CLIENTLIST(USERNAME CHARACTER(10)) RETURNS TABLE(COM_NBR CHARACTER(3), COM_NAME CHARACTER(30), COM_TYPE CHARACTER(4)) LANGUAGE SQL MODIFIES SQL DATA CONCURRENT ACCESS RESOLUTION DEFAULT FENCED NOT DETERMINISTIC CALLED ON NULL INPUT EXTERNAL ACTION NOT SECURED BEGIN DECLARE v_rfdata CHAR(40); BEGIN DECLARE v_rfdata CHAR(40); SELECT rfdta INTO v_rfdata FROM REFERRF WHERE rfcat = '0119' AND rfslc = UPPER(UserName); IF v_rfdata LIKE '%***%' THEN SELECT c.com_nbr, c.com_name, c.com_type INTO com_nbr, com_name, com_type FROM V_CLIENTS AS c; ELSE SELECT u.com_nbr, u.com_name, u.com_type INTO com_nbr, com_name, com_type FROM V_USERSEC AS u; END IF; END;;
I also tried this
Code:
CREATE FUNCTION MYLIB.VLF_CLIENTLIST(USERNAME CHARACTER(10)) RETURNS TABLE(COM_NBR CHARACTER(3), COM_NAME CHARACTER(30), COM_TYPE CHARACTER(4)) LANGUAGE SQL MODIFIES SQL DATA CONCURRENT ACCESS RESOLUTION DEFAULT FENCED NOT DETERMINISTIC CALLED ON NULL INPUT EXTERNAL ACTION NOT SECURED BEGIN DECLARE v_rfdata CHAR(40); BEGIN DECLARE v_rfdata CHAR(40); SELECT rfdta INTO v_rfdata FROM REFERRF WHERE rfcat = '0119' AND rfslc = UPPER(UserName); IF v_rfdata LIKE '%***%' THEN RETURN SELECT c.com_nbr, c.com_name, c.com_type FROM V_CLIENTS AS c; ELSE RETURN SELECT u.com_nbr, u.com_name, u.com_type FROM V_USERSEC AS u WHERE u.user_name = UPPER(USERNAME); END IF; END;;
I'm hoping someone can point out my error(s).
Thx.
UPDATE: I've actually replaced this with a view. However, I'm still curious as to what I'm doing wrong.
Comment