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

Can you post the entire SP? It will be easier to diagnose that way.

Andy

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.

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

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.

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

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?

I was having a blonde moment. I saw 'declare global temporary table' and thought, "That old chestnut!". I should read the post in future ;-)