ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Help with first SQL UDTF

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with first SQL UDTF

    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

    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;;
    This produces an error " ; TOKEN NOT VALID"

    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;;
    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.
    Last edited by gwilburn; May 10, 2019, 09:52 AM.

  • #2
    I don't believe "External Action Not Secured" is valid.

    Comment


    • #3
      You can specify RETURN only once.
      If you want to access different tables you need to create temporary tables and populate them dynamically.
      Alternatively you may merge your SELECT statements with an Union Clause.

      You may try whether the following statement works:
      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);
      
                  SELECT rfdta
                      INTO v_rfdata
                      FROM REFERRF
                      WHERE rfcat = '0119' AND rfslc = UPPER(UserName);
      
                  Return SELECT c.com_nbr, c.com_name, c.com_type
                            FROM V_CLIENTS AS c
                            Where v_rfdata Like '%***%'
                          Union All
                          Select  u.com_nbr, u.com_name, u.com_type
                             FROM V_USERSEC AS u
                             WHERE u.user_name = UPPER(USERNAME)
                                   and v_rfdata not like '%***%';
       END;;
      BTW not sure why you tried to nest a compound statement in the outer compound statement and tried to definie vrtdata twice.
      ... in either way and END is missing in your code.

      Another option would be to declare 2 cursors , loop through the cursors/result sets depending on the V_rfdata returned and write the each fetched row with the PIPE statmement.

      Birgitta
      Last edited by B.Hauser; May 13, 2019, 01:02 AM.

      Comment


      • #4
        Birgitta,

        Thanks for the explanation. I actually placed the UNION (above) in my view and eliminated the stored procedure.

        I tried to use the ACS utility to create the stored procedure, however it is very confusing as to what to place in the statement section in ACS (I'm assuming the code between BEGIN and END). Probably just easier to write it by hand?

        Comment


        • #5
          To be honnest, I also writhe the Routine Body (Between Begin and End) by hand. I use ACS to select all the option and for defining the parameters and then I click on Show SQL and put my source code in the routine body in the generated script.

          Birgitta

          Comment


          • #6
            You are missing an END. The token it is referring to is the second ; You need to either add another end - or take out the second begin and declare statement. Then you need to add a return statement to return the data.

            Comment

            Working...
            X