Roy
>
> >SELECT datefield+CAST(integervalue (or integerfield) AS INTERVAL MONTH) AS NewDateField FROM Table
>
> That works fine until you want to make it a parameter rather than a constant or field
>

Indeed.  As I understand it this is a restriction on parameters being used as result columns (or column modifiers), not
anything to do with Intervals as such.

John

John

>Indeed. As I understand it this is a restriction on parameters being used as result columns (or column modifiers), not
>anything to do with Intervals as such.

Dunno, I'd have to dig out Tim's post about it. Its enough for me to know that it doesn't work which is a shame because most of my use for INTERVALs is in sql where I would use parameters.

Roy

Roy

> Dunno, I'd have to dig out Tim's post about it. Its enough for me to know that it doesn't work which is a shame
because most of my use for INTERVALs is in sql where I would use parameters.
>

.... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine.

John

John

>... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine.

Nice one - after I figured out how to set the parameter type in EDBManager

Roy Lambert

John,

> ... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine.

SCRIPT
BEGIN
  Execute Immediate 'SELECT Datum + CAST(? AS INTERVAL MONTH) AS
NewDateField FROM Kalender' using 2;
END

Does *not* work:

ElevateDB Error #700 An error was found in the statement at line 3 and
column 19 (Invalid expression ? found, dynamic parameter references not
allowed)

Uli

Uli

>> ... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine.
>
>SCRIPT
>BEGIN
> Execute Immediate 'SELECT Datum + CAST(? AS INTERVAL MONTH) AS
>NewDateField FROM Kalender' using 2;
>END
>
>Does *not* work:

Try this instead

SCRIPT(IN FRED INTEGER)
BEGIN               
DECLARE STMT  VARCHAR;
SET STMT =  'SELECT _dateadded +  INTERVAL ''' + CAST(fred AS VARCHAR)+''' MONTH AS NewDateField FROM calls';
  Execute Immediate STMT;
END

You have to write the whole statement.

Roy Lambert [Team Elevate]

Roy,

> Try this instead
>
> SCRIPT(IN FRED INTEGER)
> BEGIN
> DECLARE STMT  VARCHAR;
> SET STMT =  'SELECT _dateadded +  INTERVAL ''' + CAST(fred AS VARCHAR)+''' MONTH AS NewDateField FROM calls';
>     Execute Immediate STMT;
> END
>
> You have to write the whole statement.



That's what I wrote to Sorin at the beginning of this thread.
I just wanted to point out that John's solution withe a "real" parameter
does not work.

Uli

"Uli Becker" <johnmuller54@googlemail.com> wrote in message
news:A6647C79-773B-412A-9BA0-EFBA7B5E2E2E@news.elevatesoft.com...
> John,
>
> > ... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine.
>
> SCRIPT
> BEGIN
>    Execute Immediate 'SELECT Datum + CAST(? AS INTERVAL MONTH) AS
> NewDateField FROM Kalender' using 2;
> END
>
> Does *not* work:

I agree It doesn't work - and is not what I suggested  The point is that it is nothing to do with intervals, but is
about using paramters in the select list.

I am guessing the following (which is what I suggested) will work

SCRIPT
BEGIN
  Execute Immediate 'SELECT * FROM Kalender WHERRE Datum + CAST(? AS INTERVAL MONTH) > CURRENT_DATE ' using 2;
END

John

John

Seems to when you change

WHERRE

to the US rather than Scottish spelling

Roy Lambert [Team Elevate]

Roy
>
> Seems to when you change
>
> WHERRE
>

LOL - just back from a 50th birthday weekend bash....

John