SQL Anywhere database - referring to the value of a variable

Author

referring to the value of a variable

Rony Santoso

2005-05-11, 1:23 pm

dear,

is it possible to replace table name with indirect variable reference (the
value of the variable) such as in the following:

insert into del_orders
select * from orders;

will be changed into:
---------------------------
declare ls_table char(30);

set ls_table = 'del_orders';

insert into ls_table
select * from orders;

Glenn Paulley

2005-05-11, 1:23 pm

"Rony Santoso" <rony_xg@yahoo.de> wrote in news:4282393d@forums
-1-dub:

> dear,
>
> is it possible to replace table name with indirect variable reference
> (the value of the variable) such as in the following:
>
> insert into del_orders
> select * from orders;
>
> will be changed into:
> ---------------------------
> declare ls_table char(30);
>
> set ls_table = 'del_orders';
>
> insert into ls_table
> select * from orders;
>
>

You need to construct the entire request as a string, and then execute it
using EXECUTE IMMEDIATE.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Rony Santoso

2005-05-11, 8:23 pm

emm, I mean in triggering operation. not in PB environment

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns9653935AE23E
7paulleyianywherecom
@10.22.241.106...
> "Rony Santoso" <rony_xg@yahoo.de> wrote in news:4282393d@forums
-1-dub:
>
>
> You need to construct the entire request as a string, and then execute it
> using EXECUTE IMMEDIATE.
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Greg Fenton

2005-05-12, 3:23 am

Rony Santoso wrote:
> emm, I mean in triggering operation. not in PB environment
>

So does Glenn.

You haven't posted the version (and build number) of ASA that you are
using, but EXECUTE IMMEDIATE is documented in the SQLAnywhere online docs:

http://tinyurl.com/b27jy [shortcut to docs on ianywhere.com]

SET @source = 'orders'
SET @dest = 'del_orders';
SET @cmd = 'INSERT INTO ' || @dest || ' SELECT * FROM ' || @source;
EXECUTE IMMEDIATE @cmd;

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Rony Santoso

2005-05-14, 3:23 am

I'm using ASA 8.0.1 (2600)

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:4282bec2@forums
-2-dub...
> Rony Santoso wrote:
>
> So does Glenn.
>
> You haven't posted the version (and build number) of ASA that you are
> using, but EXECUTE IMMEDIATE is documented in the SQLAnywhere online docs:
>
> http://tinyurl.com/b27jy [shortcut to docs on ianywhere.com]
>
>
> SET @source = 'orders'
> SET @dest = 'del_orders';
> SET @cmd = 'INSERT INTO ' || @dest || ' SELECT * FROM ' || @source;
> EXECUTE IMMEDIATE @cmd;
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-05-14, 9:24 am

Rony Santoso wrote:
> I'm using ASA 8.0.1 (2600)
>

Build 2600 was the initial release of 8.0.1. I suggest you consider
upgrading to a newer EBF for 8.0.1 or better yet, upgrade to the newest
MR, 8.0.3.

I unfortunately can't access the 8.0.1 docs right now, but in the 8.0.2
online docs EXECUTE IMMEDIATE is at:

Adaptive Server Anywhere SQL Reference Manual
4. SQL Statements
- EXECUTE statement [SP]

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links