Re: INSERT or MULTI INSERT

poratips shah via DBMonster.com wrote:
> Hi,
> I am using oracle 9 r2.
> I am trying to INSERT the records from another table and I ahve to
> increment the sequence_num column but it's not incrementing
> and giving me a Unique Constraint error because sequence_num is a part of
> the composite PK with order_id.
>
> I have following table structure
> =================================
> CREATE TABLE NR_ATG_ORDER (
> ATG_ORDER_ID VARCHAR2 (40) NOT NULL,
> DIVISION_ID VARCHAR2 (2) NOT NULL,
> NOPS_ORDER_ID VARCHAR2 (12),
> CUSTOMER_ID VARCHAR2 (11),
> ...
> JOB_REF_NUMBER VARCHAR2 (12),
> CONSTRAINT NR_ATG_ORDER_PK
> PRIMARY KEY ( ATG_ORDER_ID ) ) ;
>
> CREATE TABLE NR_ATG_ORDER_REL (
> ORDER_ID VARCHAR2 (40) NOT NULL,
> SEQUENCE_NUM NUMBER NOT NULL,
> ATG_ORDER_ID VARCHAR2 (40) NOT NULL,
> CONSTRAINT NR_ATG_ORDER_REL_PK
> PRIMARY KEY ( ORDER_ID, SEQUENCE_NUM ) ) ;
>
> ALTER TABLE NR_ATG_ORDER_REL ADD CONSTRAINT NR_ATG_ORDER_REL_FK1
> FOREIGN KEY (ATG_ORDER_ID)
> REFERENCES NR_ATG_ORDER (ATG_ORDER_ID) ;
>
> Now I have data in this table from I am loading into NR_ATG_ORDER table
>
> CREATE TABLE NR_ORDER_UPLOAD (
> NOPS_ORDER_ID VARCHAR2 (12) NOT NULL,
> LINE_NUMBER VARCHAR2 (4) NOT NULL,
> DIVISION_ID VARCHAR2 (2) NOT NULL,
> CUSTOMER_ID VARCHAR2 (11),
> ...
> JOB_REF_NUMBER VARCHAR2 (12),
> ORDER_FLAG VARCHAR2 (1),
> CONSTRAINT NR_ORDER_UPLOAD_PK
> PRIMARY KEY ( NOPS_ORDER_ID, LINE_NUMBER ) ) ;
>
> My First insert is
> ====================
> insert into nr_atg_order B
> (
> ATG_ORDER_ID,
> DIVISION_ID,
> NOPS_ORDER_ID,
> CUSTOMER_ID,
> STATUS_CODE,
> SHIPPING_METHOD,
> SHIPPING_DATE,
> ORDER_DATE,
> TRACKING_NUMBERS,
> PO_NUMBER,
> ORDER_TOTAL,
> SHIPPING_AMOUNT,
> TAX_AMOUNT,
> ORDER_TYPE ,
> ORDER_STATE,
> PAYMENT_STATE,
> JOB_REF_NUMBER
> )
> Select 'ATG_ORDER'||dynamo.ORDER_SEQ.nextval, -- generating ID
> DIVISION_ID,
> NOPS_ORDER_ID,
> CUSTOMER_ID,
> STATUS_CODE,
> CARRIER_NAME,
> SHIPPING_DATE,
> ORDER_DATE,
> TRACKING_NUMBERS,
> PO_NUMBER,
> ORDER_TOTAL,
> SHIPPING_AMOUNT,
> TAX_AMOUNT,
> null,
> 'inStock',
> 'processed',
> job_ref_number
> FROM NR_ORDER_UPLOAD A
> where A.NOPS_ORDER_ID not in
> (select B.nops_order_id from nr_atg_order B
> where b.nops_order_id = a.nops_order_id)
> AND job_ref_number is not null
> and job_ref_number in (select B.nops_order_id from nr_atg_order B
> where b.nops_order_id = a.job_ref_number)
> and order_flag = 1
> /
>
> My 2nd insert has problem
> ==========================
> insert into nr_atg_order_rel z
> (ORDER_ID,SEQUENCE_NUM,ATG_ORDER_ID)
> select a.order_id,
> (a.sequence_num)+1 ,
> c.atg_order_id
> from DYNAMO.NR_ATG_ORDER_REL a, nr_atg_order b, nr_atg_order c
> where a.atg_order_id = b.atg_order_id
> and b.nops_order_id = c.job_ref_number
> and c.atg_order_id not in (select a.atg_order_id from nr_atg_order_rel a
> where c.atg_order_id = a.atg_order_id)
> /
> Now I need to load into NR_ATG_ORDER_REL table but I am getting errors
>
> I have ORDER_ID, SEQUENCE_NUM and ATG_ORDER_ID columns in the table like
> ORDER_ID SEQUENCE_NUM ATG_ORDER_ID
> -------- ----------- ------------
> o27290008 0 order1001023
> o27400001 0 order1001071
> o27400002 0 order1001073
>
> When I Insert, for the same ORDER_ID it will increment by 1 means if the
> SEQUENCE_NUM is 0 then 1, if 1 then 2 and
> grab the ATG_ORDER_ID from the NR_ATG_ORDER which one I am generating
> during my 1st insert so records will now
>
> ORDER_ID SEQUENCE_NUM ATG_ORDER_ID
> -------- ----------- ------------
> o27290008 0 order1001023
> o27290008 1 ATG_ORDER52 -- new reocrds - sequence incrementing
>
> o27400001 0 order1001071
> o27400001 1 ATG_ORDER53 -- new reocrds - sequence incrementing
>
> o27400002 0 order1001073
> o27400002 1 ATG_ORDER54 -- new reocrds - sequence incrementing
>
> Assume that I have records inserted into ATG_ORDER table through 1st insert
> like
>
> ATG_ORDER_ID NOPS_ORDER_ID CUSTOMER_ID JOB_REF_NUMBER
> =========== ============= ============ ==============
> ATG_ORDER52 JA0072860000 RU332000000 RB0072760000
> ATG_ORDER53 CE0072850000 RU332000000 RB0072750000
> ATG_ORDER54 CE0072870000 RU332000000 RB0072760000
> ATG_ORDER55 JA0072830000 RU332000000 RB0072740000
> ATG_ORDER56 CE0072590000 RU332000000 RB0072530000
> ATG_ORDER57 HS0072840000 RU332000000 RB0072750000
>
> Thanks,
> Poratips

TO_CHAR(dynamo.ORDER_SEQ.nextval).

How do you expect to concatenate a string to a number.

For any further help post Oracle version and the error messages. We are unable to look over your shoulder or read your mind.

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)