Results 1 to 11 of 11

Thread: DB2 Functions

  1. #1
    Join Date
    Feb 2003
    Posts
    49

    Unanswered: DB2 Functions

    HI,

    This time my problem is with functions

    CREATE FUNCTION WorkingHours( )
    RETURNS TABLE (hr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp)

    F1: BEGIN ATOMIC
    RETURN
    select NoOfHours,verno,Start_Date,null from xxx
    order by NoOfHours;

    END

    @

    I get two errors:
    1) the NoOfHours is not valid in this context. the error goes away when the remove the order by clause. Why can't I use it?
    2)null is not vaild in this context. Why??

    MY original code in sql server was

    CREATE FUNCTION WorkingHours ()
    RETURNS @Whours TABLE (hrs nvarchar(40),
    verno numeric(8,2),
    Startdte datetime,
    Enddte datetime)
    AS
    begin

    insert into @Whours select NoOfHours,Verno,Start_Date,null from xx order by NoOfHours
    update @Whours set Enddte=a.Startdte -1 from @Whours a where a.Whr=Whr and a.verno=(select min(verno) from @Whours z where z.verno>verno and z.Whr=Whr)

    RETURN
    end


    GO

    I want to do the same thing?? How to?
    Please help,
    Asha

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Unhappy

    Asha,
    The reason you cannot use the ORDER BY clause, is because you are returning a TABLE, and tables have no intrinsic order, only Result Sets are orderd. When declaring a function, you are only allowed to use a full-select and ORDER BY is not allowed there.

    The reason the NULL is not allowed in the column list is that the datatype of the column is unknown. If you always want to return a NULL value, use the NULLIF function with the appropriate datatypes.
    for example:
    select col1,nullif(' ',' ') as myStringCol ...
    select col1,nullif(1,1) as myIntCol ...

    see the reference manual on proper use of NULLIF.

    to do what you want:

    declare global temporary table
    insert into temp using select with nullif
    do update
    return select * from temptable

    HTH

    Andy

  3. #3
    Join Date
    Feb 2003
    Posts
    49
    Hi,

    Thanks Andy....I thought too

    CREATE FUNCTION WorkingHours( )
    RETURNS TABLE (hr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp)

    F1: BEGIN ATOMIC
    DECLARE GLOBAL TEMPORARY TABLE TT1(Whr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp);

    insert into TT1
    select NoOfHours,verno,Start_Date,null from xxx order by NoOfHours;

    update TT1 set Enddte=a.Startdte -1 from TT1 a where a.Whr=Whr
    and a.verno=(select min(verno) from TT1 z where z.verno>verno and z.Whr=Whr);

    RETURN select Whr,verno,Startdte,Enddte from TT1;
    END

    @

    But this gives and error

    SQL0104N An unexpected token "TABLE" was found following "ARE GLOBAL
    TEMPORARY". Expected tokens may include: "DEFAULT". LINE NUMBER=8.
    SQLSTATE=42601

    I think it must be something small but no getting it,

    Thanks ,
    Asha
    Originally posted by ARWinner
    Asha,
    The reason you cannot use the ORDER BY clause, is because you are returning a TABLE, and tables have no intrinsic order, only Result Sets are orderd. When declaring a function, you are only allowed to use a full-select and ORDER BY is not allowed there.

    The reason the NULL is not allowed in the column list is that the datatype of the column is unknown. If you always want to return a NULL value, use the NULLIF function with the appropriate datatypes.
    for example:
    select col1,nullif(' ',' ') as myStringCol ...
    select col1,nullif(1,1) as myIntCol ...

    see the reference manual on proper use of NULLIF.

    to do what you want:

    declare global temporary table
    insert into temp using select with nullif
    do update
    return select * from temptable

    HTH

    Andy

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Asha,
    Could you explain (in detail) to me what you are trying to do with the update statement. If I get a clear handle on it, I may be able to get the
    result you want without the temp table.

    Andy

    Originally posted by asha192
    Hi,

    Thanks Andy....I thought too

    CREATE FUNCTION WorkingHours( )
    RETURNS TABLE (hr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp)

    F1: BEGIN ATOMIC
    DECLARE GLOBAL TEMPORARY TABLE TT1(Whr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp);

    insert into TT1
    select NoOfHours,verno,Start_Date,null from xxx order by NoOfHours;

    update TT1 set Enddte=a.Startdte -1 from TT1 a where a.Whr=Whr
    and a.verno=(select min(verno) from TT1 z where z.verno>verno and z.Whr=Whr);

    RETURN select Whr,verno,Startdte,Enddte from TT1;
    END

    @

    But this gives and error

    SQL0104N An unexpected token "TABLE" was found following "ARE GLOBAL
    TEMPORARY". Expected tokens may include: "DEFAULT". LINE NUMBER=8.
    SQLSTATE=42601

    I think it must be something small but no getting it,

    Thanks ,
    Asha

  5. #5
    Join Date
    Feb 2003
    Posts
    49
    HI,

    There is table xxx that has the working hours , for n versions with different start dates. Typically like

    Workinghours Version no Startdate
    6 1 1-jan-2003
    8 2 15-july-2003
    4 3 20-Sept-2003

    That means that the end date of the version 1 was 14-July-2003 and that of version 2 is 19-sept-2003 and the version 3 is current on.

    Thanks,
    Asha

    Originally posted by ARWinner
    Asha,
    Could you explain (in detail) to me what you are trying to do with the update statement. If I get a clear handle on it, I may be able to get the
    result you want without the temp table.

    Andy

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Asha,

    select a.workinghours,a.version,a.startdate,
    (select b.startdate - 1 DAY from xxx as b where (a.workinghours = b.workinghours) and (b.version = (select min(c.version) from xxx as c
    where (c.workinghours = a.workinghours) and (c.version > a.version))))
    as enddate
    from xxx as a

    I do not know how well this will perform, but it should work.

    Andy

    Originally posted by asha192
    HI,

    There is table xxx that has the working hours , for n versions with different start dates. Typically like

    Workinghours Version no Startdate
    6 1 1-jan-2003
    8 2 15-july-2003
    4 3 20-Sept-2003

    That means that the end date of the version 1 was 14-July-2003 and that of version 2 is 19-sept-2003 and the version 3 is current on.

    Thanks,
    Asha

  7. #7
    Join Date
    Feb 2003
    Posts
    49
    Hi,

    Thanks Andy, I think that will work. But as I am new to DB2, I wanted the temporary global table to work.

    A sample code like this is also failing
    CREATE FUNCTION Test( )
    RETURNS integer

    F1: BEGIN ATOMIC
    DECLARE GLOBAL TEMPORARY TABLE TT1(
    Whr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp) ;
    RETURN 1;
    END

    @

    gives an error
    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 "TABLE" was found following "ARE GLOBAL
    TEMPORARY". Expected tokens may include: "DEFAULT". LINE NUMBER=5.
    SQLSTATE=42601

    I created a temporary table space, but from that time whenever I connect to the DB2, it gives an error

    SQL1478W The defined buffer pools could not be started. Instead, one small
    buffer pool for each page size supported by DB2 has been started.
    SQLSTATE=01626

    Any help would really make my db2 much better
    Thanks,
    Asha

    Originally posted by ARWinner
    Asha,

    select a.workinghours,a.version,a.startdate,
    (select b.startdate - 1 DAY from xxx as b where (a.workinghours = b.workinghours) and (b.version = (select min(c.version) from xxx as c
    where (c.workinghours = a.workinghours) and (c.version > a.version))))
    as enddate
    from xxx as a

    I do not know how well this will perform, but it should work.

    Andy

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Asha,
    The temp table should work. What version are you using?

    Andy

    Originally posted by asha192
    Hi,

    Thanks Andy, I think that will work. But as I am new to DB2, I wanted the temporary global table to work.

    A sample code like this is also failing
    CREATE FUNCTION Test( )
    RETURNS integer

    F1: BEGIN ATOMIC
    DECLARE GLOBAL TEMPORARY TABLE TT1(
    Whr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp) ;
    RETURN 1;
    END

    @

    gives an error
    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 "TABLE" was found following "ARE GLOBAL
    TEMPORARY". Expected tokens may include: "DEFAULT". LINE NUMBER=5.
    SQLSTATE=42601

    I created a temporary table space, but from that time whenever I connect to the DB2, it gives an error

    SQL1478W The defined buffer pools could not be started. Instead, one small
    buffer pool for each page size supported by DB2 has been started.
    SQLSTATE=01626

    Any help would really make my db2 much better
    Thanks,
    Asha

  9. #9
    Join Date
    Feb 2003
    Posts
    49
    Andy,

    I am using V8.

    Is it because of the error that the buffer pools cannot be started when I connect to DB2.

    SQL1478W The defined buffer pools could not be started. Instead, one small
    buffer pool for each page size supported by DB2 has been started.
    SQLSTATE=01626


    Thanks,
    Asha
    Originally posted by ARWinner
    Asha,
    The temp table should work. What version are you using?

    Andy

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Asha,
    No, that is not causing the 0104N error which is a syntax error. I checked the syntax for compouind statements in UDFs and declaring temp tables is not allowed.

    The 1478W is a warning message stating that your bufferpool definitions are either corrupt or too big for the memory available on the server.

    Andy

    Originally posted by asha192
    Andy,

    I am using V8.

    Is it because of the error that the buffer pools cannot be started when I connect to DB2.

    SQL1478W The defined buffer pools could not be started. Instead, one small
    buffer pool for each page size supported by DB2 has been started.
    SQLSTATE=01626


    Thanks,
    Asha

  11. #11
    Join Date
    Feb 2003
    Posts
    49
    Hi Andy,

    Thanks, that means I cannot write this

    CREATE FUNCTION Test( )
    RETURNS integer

    F1: BEGIN ATOMIC
    DECLARE GLOBAL TEMPORARY TABLE TT1(
    Whr decimal(5,2),
    verno decimal(8,2),
    Startdte timestamp,
    Enddte timestamp) ;
    RETURN 1;
    END

    @

    as temporary tables cannot be declared in functions.....

    Atleast my db2 is getting much better.

    Thanks,
    Asha
    Originally posted by ARWinner
    Asha,
    No, that is not causing the 0104N error which is a syntax error. I checked the syntax for compouind statements in UDFs and declaring temp tables is not allowed.

    The 1478W is a warning message stating that your bufferpool definitions are either corrupt or too big for the memory available on the server.

    Andy

Posting Permissions

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