Create Function Using Query Manager (STRQM) - IBM AS400

This is a discussion on Create Function Using Query Manager (STRQM) - IBM AS400 ; I am trying to create a function and then call that function from within Query Manager, however I cannot seem to find the proper delimiter between the function and the SQL statement. Please see this example: CREATE FUNCTION SPCATTN( SPC1 ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: Create Function Using Query Manager (STRQM)

  1. Create Function Using Query Manager (STRQM)

    I am trying to create a function and then call that function from
    within Query Manager, however I cannot seem to find the proper
    delimiter between the function and the SQL statement.

    Please see this example:


    CREATE FUNCTION SPCATTN( SPC1 CHAR(1), SPC2 CHAR(1), SPC3
    CHAR(1) )
    RETURNS
    CHAR(1)
    LANGUAGE
    SQL

    BEGIN

    RETURN(

    CASE
    WHEN ( (SPC1 = '5') OR (SPC2 = '5') OR (SPC3 = '5') ) THEN
    '5'
    WHEN ( (SPC1 = 'P') OR (SPC2 = 'P') OR (SPC3 = 'P') ) THEN
    'P'
    ELSE
    'NULL'

    END
    );
    END

    --then i try to invoke the function here
    SELECT DBACCT, SPATTN(DBVIPS, DBVIPS2, DBVIPS3), etc......


    Thanks for your help.



  2. Re: Create Function Using Query Manager (STRQM)

    A *QMQRY object encapsulates only a single SQL statement.
    Investigate use of RUNSQLSTM instead; or possibly QM PROC (STRQMPROC)
    might provide such capability. The second statement in a RUNSQLSTM
    source could be a SQL CALL statement to QSYS.QCMDEXC to perform a
    STRQMQRY to perform the desired SELECT to the named output device.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    greggiefen wrote:
    > I am trying to create a function and then call that function from
    > within Query Manager, however I cannot seem to find the proper
    > delimiter between the function and the SQL statement.
    >
    > Please see this example:
    >
    > CREATE FUNCTION SPCATTN(
    > SPC1 CHAR(1), SPC2 CHAR(1), SPC3 CHAR(1) )
    > RETURNS CHAR(1) LANGUAGE SQL
    > BEGIN
    > RETURN(
    > CASE
    > WHEN ( (SPC1 = '5') OR (SPC2 = '5') OR (SPC3 = '5') )
    > THEN '5'
    > WHEN ( (SPC1 = 'P') OR (SPC2 = 'P') OR (SPC3 = 'P') )
    > THEN 'P'
    > ELSE 'NULL'
    > END
    > );
    > END
    >
    > --then i try to invoke the function here
    > SELECT DBACCT, SPATTN(DBVIPS, DBVIPS2, DBVIPS3), etc......


+ Reply to Thread