Results 1 to 6 of 6

Thread: Sqlcode=-628

  1. #1
    Join Date
    Apr 2015
    Posts
    2

    Unanswered: Sqlcode=-628

    Hi,

    Unable to compile the below code.

    create or replace function abc()
    returns TABLE (V_POSN_DT1 INTEGER)
    lANGUAGE SQL
    MODIFIES SQL DATA
    SPECIFIC abc

    BEGIN
    *** DECLARE V_POSN_DT DATE;
    *** DECLARE V_POSN_DT_ID INTEGER DEFAULT 0;
    *** DECLARE V_SP_RETVAL INTEGER DEFAULT 0;**

    *
    RETURN (SELECT V_POSN_DT_ID FROM SYSIBM.SYSDUMMY1);
    ***
    *END

    its giving below error,can some body help me.

    Multiple or conflicting keywords involving the "RETURNS" clause are present.. SQLCODE=-628, SQLSTATE=42613, DRIVER=4.18.60

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Did you search on IBM docs for examples of table-functions?

    from IBM docs: Check that the statement conforms to the syntax and rules defined for the statement. Correct any invalid occurrences of duplicate or conflicting keywords.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2015
    Posts
    2
    Hi,

    Thanks for reply,Im new the DB2.

    can you please provide the solution for this.

    Thanks
    S

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    You need to use 'begin atomic' and study to learn why this is necessary. Don't expect others to do your job for you.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    "can you please provide the solution for this."
    this is a public forum where people try to help you for FREE, and we are not the oracle of db2
    if you are new : try to read the doc to understand DB2 and what you are doing - otherwise hire a consultant todo your job..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    If you use 10.1.2 and above, you can use pipelined table functions and they are compiled. It's the only exception and the official DB2 documentation is not correct when it completely forbids BEGIN ... END use in the table functions.
    Here is an example:
    Code:
    --#SET TERMINATOR @
    create or replace function test_tables(p_tabschema varchar(128))
    returns TABLE (tabname varchar(128), colcount int)
    BEGIN
      FOR C AS  
        SELECT TABSCHEMA, COLCOUNT
        FROM SYSCAT.TABLES
        WHERE TABSCHEMA=P_TABSCHEMA
      DO
        PIPE(C.TABSCHEMA, C.COLCOUNT);
      END FOR;
      RETURN;
    END@ 
    
    SELECT * FROM TABLE(TEST_TABLES('SYSCAT'))@
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •