Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    14

    Unanswered: Error while inserting into temp table

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Nov 2009
    Posts
    14
    We are using both IDS ver 9 and 10, hence the problem i guess.

  4. #4
    Join Date
    Nov 2009
    Posts
    14
    is there a work around for using the select first.., in the IDS ver 9,10

  5. #5
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •