Error while inserting into temp table

pbangalore · #1 (**permalink**) 11-23-09, 14:35

I have a temp table ca_retainco_entities and i am trying to insert into it by using the below sql

Insert into ca_retainco_entities(ent_id)
select First 100000 e.entity_id
from entity e
where substr(e.hier_name,5,2) in ('CA')
and e.hier_name not in ('BGRVCAXFRLO','BGRVCAXFRS1','BLYTCABARL0','BLYTCA XF92K','BLYTCAXGRS1','BLYTCAXHRL1','CEVLCAXFRS6',
'HVSUCAXFRS1','PYVLCAAARL0','WDFRCAAARL0','WDFRCAX FRS1','PLVRCAXFRS1');

and i get the following error
944: Cannot use "first" in this context.

Help is really appreciated

ceinma · #2 (**permalink**) 11-26-09, 11:21

what version of IDS?

I not sure, but this is supported only after version 10 xC3...

in IDS 11.50 , this code works

Code:

create temp table a (coda int);
Temporary table created.


create temp table b (codb int);
Temporary table created.


insert into b values (1);
1 row(s) inserted.


insert into b values (2);
1 row(s) inserted.


insert into b values (3);
1 row(s) inserted.



insert into a (coda)
select first 1  * from b

1 row(s) inserted.


Database closed.

pbangalore · #3 (**permalink**) 11-26-09, 14:55

We are using both IDS ver 9 and 10, hence the problem i guess.

pbangalore · #4 (**permalink**) 11-26-09, 15:29

is there a work around for using the select first.., in the IDS ver 9,10

ceinma · #5 (**permalink**) 11-26-09, 17:29

so far I know, only if you use a stored procedure with the foreach and then control the loop to return only the first or N records...
than you use this procedure in a select

select * from table(procedure) (col1,col2,col3) .

- disvantage: your select can't be dynamic...

Internet Services

Web Development

Digital Marketing

Consumer Tech