Sorin


Are you trying to use it as a parameterised statement?

Roy Lambert [Team Elevate]

I'll try Uli suggestions later but no it's not parameterised
I want to use an integer field as the months number.

Thanks
Sorin

> Are you trying to use it as a parameterised statement?
>
> Roy Lambert [Team Elevate]
>

Thanks Uli

What is the right syntax for edbManager?

Sorin

> It's the same: just compose the statement like this:
>
> with MyQuery do
> begin
>   sql.add('select Datum + interval ''' + IntToStr(MyIntValue) + ''' month
> as NewField from Kalender');
>   open;
> end;
>
> Uli

Sorin


As far as I know you can't do that. It has to be a string constant.

Roy Lambert [Team Elevate]

Sorin


As far as I know the only way you can do that it to write your own function.

Roy Lambert [Team Elevate]

Sorin,

> I'll try Uli suggestions later but no it's not parameterised
> I want to use an integer field as the months number.

Just write a script or a procedure: read out the integer value and
compose the statement as I showed you.
You cannot use parameters in intervals.

Uli

Uli


From my reading I think Sorin wants to type the sql into ElevateDB and I don't think that can be done without writing your own function. I'll be happy to be proven wrong, but I can't get it to work whichever way I've tried.

As you show it you're creating the sql string before it gets submitted to the parser and an ElevateDB sql window doesn't do that.

Roy Lambert

Roy,

I agree, it's not possible with a "simple" query. He has to write a script.

If he wants to read the Integer-value from the same table (or another
table):

SCRIPT
BEGIN
   DECLARE MyDelta INTEGER;
   DECLARE Result Cursor WITH RETURN FOR Stmt;
   Execute Immediate 'Select Delta INTO ? from MyTable where MyTableID
= 1' using MyDelta;

   PREPARE Stmt FROM
      'select Datum + interval ''' + cast(MyDelta as varchar) + '''
month as
       NewDateField from MyTable where MyTableID = 1';
   OPEN Result;
END

If he want to pass the Integer-value as a parameter:

SCRIPT (In MyDelta INTEGER)
BEGIN
   DECLARE Result Cursor WITH RETURN FOR Stmt;

   PREPARE Stmt FROM
      'select Datum + interval ''' + cast(MyDelta as varchar) + '''
month as
       NewDateField from MyTable where MyTableID = 1';
   OPEN Result;
END

Unfortunately it's not clear, what he really wants and why a script is
not "allowed".

Uli

Sorin

> What is the right syntax for edbManager?

How about

SELECT datefield+CAST(integervalue (or integerfield) AS INTERVAL MONTH) AS NewDateField FROM Table

John

John


>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


Roy Lambert [Team Elevate]