Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Question Unanswered: Between Timestamp, Data retrieval

    Hi all,

    I am creating a sp with the following input parameters
    IN fromtimestamp TIMESTAMP,
    IN totimestamp TIMESTAMP)

    Then the following
    SELECT A,B,C FROM TABLETEST
    WHERE
    TIMESTAMP_VALUE BETWEEN fromtimestamp and totimestamp

    I get the following error SQL0206N "fromtimestamp" is not valid in the context it is used.

    Can someone pls help?
    thanks
    mac

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the entire SP? It will be easier to diagnose that way.

    Andy

  3. #3
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Question

    CREATE PROCEDURE TEST1 (IN FROMTIMESTAMP TIMESTAMP,
    IN TOTIMESTAMP TIMESTAMP,
    OUT V INTEGER)

    LANGUAGE SQL
    --

    BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST AS (
    SELECT A.ID, A.NM FROM ABC.TABLETOTEST A
    WHERE
    A.TOTESTTIMESTAMP BETWEEN FROMTIMESTAMP AND TOTIMESTAMP)
    DEFINITION ONLY ON COMMIT PRESERVE ROWS;

    SELECT COUNT(*) INTO V FROM SESSION.TEST;
    END
    ---

    This is one part of a 5 Temp table declaration within the SP but all need the FROMTIMESTAMP AND TOTIMESTAMP as a basis for the where clause.
    For some reason timestamp doesnt seem to be an acceptable comparison for the operator.
    Any ideas or work arounds, I tried to replace BETWEEN with the > and < ,also same problem.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think that DB2 actually runs the full-select to define the rows for a temporary table, so why not leave off the where clause on the Temp table creation and include it when you fill it in?

    BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST AS (
    SELECT A.ID, A.NM FROM ABC.TABLETOTEST A
    DEFINITION ONLY ON COMMIT PRESERVE ROWS;

    insert into session.test SELECT A.ID, A.NM FROM ABC.TABLETOTEST A
    WHERE
    A.TOTESTTIMESTAMP BETWEEN FROMTIMESTAMP AND TOTIMESTAMP);

    SELECT COUNT(*) INTO V FROM SESSION.TEST;
    END

    Andy

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Mr. Winner, it's not like you to miss a sitter like that!

    References to the GTT should be nested in a new BEGIN-END block.

    Code:
    BEGIN
      DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST AS (
      SELECT A.ID, A.NM FROM ABC.TABLETOTEST A
      DEFINITION ONLY ON COMMIT PRESERVE ROWS;
    
      BEGIN
        insert into session.test SELECT A.ID, A.NM FROM ABC.TABLETOTEST A
        WHERE A.TOTESTTIMESTAMP BETWEEN FROMTIMESTAMP AND TOTIMESTAMP);
    
        SELECT COUNT(*) INTO V FROM SESSION.TEST;
      END
    END

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Damian,
    Why? You would need to do that if you were using a cursor on the GTT, but it is perfectly valid if you are not.

    Andy

  7. #7
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Thanks Mr Winner, a true life saver it worked brilliantly.

    and BTW you dont need to nest a BEGIN/END separately for the GTT, Damien.
    I got the program to run fine without nesting BEGIN/END unless there are some advantages? Maybe you cant Declare cursors later on?

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Quote Originally Posted by macjoubert
    and BTW you dont need to nest a BEGIN/END separately for the GTT, Damien.
    I got the program to run fine without nesting BEGIN/END unless there are some advantages? Maybe you cant Declare cursors later on?
    I was having a blonde moment. I saw 'declare global temporary table' and thought, "That old chestnut!". I should read the post in future ;-)

Posting Permissions

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