Hi everyone, do you known how to use parameters in the INSERT INTO ..SELECT FROM ?
Like:
INSERT INTO tablename (Columnname...)
SELECT parameter1,  CASE WHEN xDayOfWeek BETWEEN parameter2 AND parameter3
                                          THEN 1 ELSE 0 END AS OccupiedDay
FROM tablename
WHERE xGUID=paramerter4

I have set values to these parameters before INSERT, but still there is errors, any good ideas about this?

RichardWu

>Hi everyone, do you known how to use parameters in the INSERT INTO ..SELECT FROM ?
>Like:
>INSERT INTO tablename (Columnname...)
>SELECT parameter1, CASE WHEN xDayOfWeek BETWEEN parameter2 AND parameter3
> THEN 1 ELSE 0 END AS OccupiedDay
>FROM tablename
>WHERE xGUID=paramerter4
>
>I have set values to these parameters before INSERT, but still there is errors, any good ideas about this?

ElevateDB does not permit the substitution of parameters for system objects (eg column names). If this is a script you will have to build the string manually and then use that either via EXECUTE IMMEDIATE or using a cursor.

Roy Lambert [Team Elevate]

So is there anyway to insert just the parameter values to a table(or temporary table) like:
INSERT INTO tablename (Columnname,Columname)
VALUES(parameter,parameter)

This is not the substitution of system objects, does it work in some way?





Roy Lambert wrote:

RichardWu

>Hi everyone, do you known how to use parameters in the INSERT INTO ..SELECT FROM ?
>Like:
>INSERT INTO tablename (Columnname...)
>SELECT parameter1, CASE WHEN xDayOfWeek BETWEEN parameter2 AND parameter3
> THEN 1 ELSE 0 END AS OccupiedDay
>FROM tablename
>WHERE xGUID=paramerter4
>
>I have set values to these parameters before INSERT, but still there is errors, any good ideas about this?

ElevateDB does not permit the substitution of parameters for system objects (eg column names). If this is a script you will have to build the string manually and then use that either via EXECUTE IMMEDIATE or using a cursor.

Roy Lambert [Team Elevate]

RichardWu,

> So is there anyway to insert just the parameter values to a table(or temporary table) like:
> INSERT INTO tablename (Columnname,Columname)
> VALUES(parameter,parameter)

This will work in a script e.g.:

Execute Immediate 'INSERT INTO MyTable (Col1,Col2) values (?,?)' using
Param1, Param2;

Uli

Richard,

<< Hi everyone, do you known how to use parameters in the INSERT INTO
...SELECT FROM ? >.

ElevateDB does not allow for the use of parameters in the SELECT columns
list.  I'm hoping to relax this a bit for things like CASE that don't affect
the type/size of the SELECT expression at some point, but for now this is
the rule.

Tim Young
Elevate Software
www.elevatesoft.com

Since you are setting a parameter value in code why dont you build the SQL
line in code using a format statement.  That will put the parameter value in
the SQL statement.

Ben

"Uli Becker" <johnmuller54@googlemail.com> wrote in message
news:EBCCC6CD-A088-4AC7-A05D-2177B6189B01@news.elevatesoft.com...
> RichardWu,
>
>> So is there anyway to insert just the parameter values to a table(or
>> temporary table) like:
>> INSERT INTO tablename (Columnname,Columname)
>> VALUES(parameter,parameter)
>
> This will work in a script e.g.:
>
> Execute Immediate 'INSERT INTO MyTable (Col1,Col2) values (?,?)' using
> Param1, Param2;
>
> Uli
>