X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 73D0AD1B278 for ; Tue, 8 Jun 2004 19:05:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09164-09 for ; Tue, 8 Jun 2004 22:05:40 +0000 (GMT) Received: from hotmail.com (bay8-f34.bay8.hotmail.com [64.4.27.34]) by svr1.postgresql.org (Postfix) with ESMTP id 251FBD1B431 for ; Tue, 8 Jun 2004 19:05:37 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 8 Jun 2004 15:05:38 -0700 Received: from 69.65.137.210 by by8fd.bay8.hotmail.msn.com with HTTP; Tue, 08 Jun 2004 22:05:38 GMT X-Originating-IP: [69.65.137.210] X-Originating-Email: [el_vigia_ec@hotmail.com] X-Sender: el_vigia_ec@hotmail.com From: "Jaime Casanova" To: pgsql-sql@postgresql.org Subject: a query question Date: Tue, 08 Jun 2004 22:05:38 +0000 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 08 Jun 2004 22:05:38.0803 (UTC) FILETIME=[BB726C30:01C44DA4] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/62 X-Sequence-Number: 18007 hi all, i was trying to migrate some data into a new structure i created. so i execute this exact query: insert into rec_t_ordenpago("ent_codigo", "sec_codigo", "ani_codigo", "orp_numero", "orp_tipopersona", "orp_persona", "cic_codigo", "esp_codigo", "cur_codigo", "cur_paralelo", "rub_codigo", "orp_valor", "orp_fechaemision", "orp_fechavencimiento", "ban_codigo", "cta_numero", "est_codigo") select cua.ent_codigo, cua.sec_codigo, cua.ani_codigo, (select (nvl(max(orp_numero), 0) + 1) from rec_t_ordenpago), 'AL', alcu.alu_codigo, cua.cic_codigo, cua.esp_codigo, cua.cur_codigo, cua.cur_paralelo, cua.rub_codigo, cua.cpa_valor, current_date, cua.cpa_fechavencimiento, null, null, 'PE' from rec_m_cuadropagos cua, aca_t_alumnocurso alcu where cua.ent_codigo = 1 and alcu.ent_codigo = cua.ent_codigo and alcu.sec_codigo = cua.sec_codigo and alcu.ani_codigo = cua.ani_codigo and alcu.cic_codigo = cua.cic_codigo and alcu.esp_codigo = cua.esp_codigo and alcu.cur_codigo = cua.cur_codigo and alcu.cur_paralelo = cua.cur_paralelo but i ended up with duplicate key error then i replace the (select (nvl(max(orp_numero), 0) + 1) from rec_t_ordenpago) part with nextval('seq1') and that work. So my question is, is this a postgreSQL limitation or is the way it have to happen? why? thanx in advance, Jaime Casanova _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail