Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Posts
    8

    Unanswered: Create function problem in db2 9.1 luw

    Hi,
    I have a table and data as follows;

    create table htab (id char(2), name varchar(20));

    insert into htab values('1','a');
    insert into htab values('1','b');
    insert into htab values('2','c');
    insert into htab values('3','d');
    insert into htab values('3','e');
    insert into htab values('3','f');
    insert into htab values('4','g');

    select * from htab

    id name
    1 a
    1 b
    2 c
    3 d
    3 e
    3 f
    4 g

    I want to show data as

    id names
    1 a,b
    2 c
    3 d,e,f
    4 g

    I have the Function in SQL Server which is working fine as
    ============
    create function dbo.fxn_get_names(@id char(2))
    returns varchar(100)
    as
    begin
    declare @names varchar(100)
    select @names=isnull(@names+',','') + name from htab where id = @id
    return @names
    end
    GO


    select distinct id, names=dbo.fxn_get_names(id) from htab
    GO
    ======================
    Actually i want to convert it to DB2 9.1 LUW and tried as follows
    xxxxxxxxxxxxxxxxx
    CREATE FUNCTION MYFNC(MID CHAR)
    RETURNS VARCHAR(100)
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE MNAMES VARCHAR(100);
    SET MNAMES='';
    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID;
    RETURN MNAMES
    END
    xxxxxxxxxxxxxxxxx
    Getting the following problem
    -------------
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token ")" was found following "E MNAMES VARCHAR(100".
    Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=5.
    SQLSTATE=42601

    SET MNAMES=''
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "MNAMES" was found following "SET ". Expected
    tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID
    SQL0206N "MID" is not valid in the context where it is used. SQLSTATE=42703

    RETURN MNAMES END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "MNAMES" was found following "RETURN ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "MNAMES" was found following "RETURN ". Expected tokens may include: "JOIN <joined_table>
    -------------

    Anyone can give me the solution...


    -- Humayun

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Humayun View Post
    Hi,
    I have a table and data as follows;

    create table htab (id char(2), name varchar(20));

    insert into htab values('1','a');
    insert into htab values('1','b');
    insert into htab values('2','c');
    insert into htab values('3','d');
    insert into htab values('3','e');
    insert into htab values('3','f');
    insert into htab values('4','g');

    select * from htab

    id name
    1 a
    1 b
    2 c
    3 d
    3 e
    3 f
    4 g

    I want to show data as

    id names
    1 a,b
    2 c
    3 d,e,f
    4 g

    I have the Function in SQL Server which is working fine as
    ============
    create function dbo.fxn_get_names(@id char(2))
    returns varchar(100)
    as
    begin
    declare @names varchar(100)
    select @names=isnull(@names+',','') + name from htab where id = @id
    return @names
    end
    GO


    select distinct id, names=dbo.fxn_get_names(id) from htab
    GO
    ======================
    Actually i want to convert it to DB2 9.1 LUW and tried as follows
    xxxxxxxxxxxxxxxxx
    CREATE FUNCTION MYFNC(MID CHAR)
    RETURNS VARCHAR(100)
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE MNAMES VARCHAR(100);
    SET MNAMES='';
    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID;
    RETURN MNAMES
    END
    xxxxxxxxxxxxxxxxx
    Getting the following problem
    -------------
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token ")" was found following "E MNAMES VARCHAR(100".
    Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=5.
    SQLSTATE=42601

    SET MNAMES=''
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "MNAMES" was found following "SET ". Expected
    tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID
    SQL0206N "MID" is not valid in the context where it is used. SQLSTATE=42703

    RETURN MNAMES END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "MNAMES" was found following "RETURN ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "MNAMES" was found following "RETURN ". Expected tokens may include: "JOIN <joined_table>
    -------------

    Anyone can give me the solution...


    -- Humayun
    To get the function to compile you need to add a ; after the return smt.

    Code:
    CREATE FUNCTION MYFNC(MID CHAR)
    RETURNS VARCHAR(100)
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE MNAMES VARCHAR(100);
    SET MNAMES='';
    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID;
    RETURN MNAMES;
    END @
    But it won't give you the result that you are looking for. I don't know sql-server, but I assume that:

    Code:
    select @names=isnull(@names+',','') + name from htab where id = @id
    somehow iterates over all names associated with @id and concatenates them. In db2 you can either use a CTE, or xmlagg et. all to accomplish the same thing.
    --
    Lennart

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    There are many differences between T-SQL and DB2 SQL, so you should RTFM. For example, the variables names don't have to start with @, as in T-SQL, while for assignment you will use SET instead of SELECT. And so on.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Aug 2009
    Posts
    8

    Still problem

    Hi Lennart,
    Thanks for your reply.
    I have changed the sql as
    --------
    CREATE FUNCTION MYFNC(MID CHAR)
    RETURNS VARCHAR(100)
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE MNAMES VARCHAR(100);
    SET MNAMES='';
    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID;
    RETURN MNAMES;
    END @
    ----------
    Unfortunately getting the following error.

    ===========
    CREATE FUNCTION MYFNC(MID CHAR)
    RETURNS VARCHAR(100)
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE MNAMES VARCHAR(100)
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token ")" was found following "E MNAMES VARCHAR(100".
    Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=5.
    SQLSTATE=42601

    SET MNAMES=''
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "MNAMES" was found following "SET ". Expected
    tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID
    SQL0206N "MID" is not valid in the context where it is used. SQLSTATE=42703

    RETURN MNAMES
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "RETURN
    MNAMES". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    END @
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END @".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END @". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

    As an aid, a partial list of valid tokens is provided in the
    SQLERRM field of the SQLCA as "<token-list>". This list assumes
    the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601

    ========================

    Please be informed that i m using Command editor of Control Center.


    ---- Humayun

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    From this statement:
    SELECT (MNAMES || ',' || NAME) MNAMES FROM HTAB WHERE ID = MID;
    I understand that you expect several rows to be returned and you want to returned a comma delimited string. You should use a cursor, fetch each row into a temp variable and added to MNAMES.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Aug 2009
    Posts
    8

    Cursor will be costly

    Hi Florin,

    thnx for your reply.
    Actually cursor will be costly for my purpose.
    I have to call this function with other columns of a large table.

    TSQL is giving me expected result.

    -- Humayun

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the problem is not the syntax but the termination character
    when compiling the function try
    db2 -td@ -vf ......
    because ; is already used in the function
    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

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Humayun View Post
    Hi Lennart,
    [...]
    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    [...]
    ---- Humayun
    Are you using SYSPROC.ADMIN_CMD to create the function?
    --
    Lennart

Posting Permissions

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