problem with data importing

[cyberdyne]

This is my NEW TABLE

SQL> DESC ORDERS;
Name Null? Type
----------------- -------- ----
ORDNO NOT NULL VARCHAR2(10) PRIMARY KEY
ADDRESS VARCHAR2(100)
GOODSDELAT VARCHAR2(75)
COMMENTS VARCHAR2(150)
DELDT VARCHAR2(10)
YEAR VARCHAR2(10)
QTNNO VARCHAR2(10) ----NEW FIELD ADDED (REFERENCE KEY OF TABLE QUOTATION)

This is my OLD TABLE

SQL> DESC ORDERS;
Name Null? Type
------------------------------- -------- ----
ORDNO VARCHAR2(10)
ADDRESS VARCHAR2(100)
GOODSDELAT VARCHAR2(75)
COMMENTS VARCHAR2(150)
DELDT VARCHAR2(10)
YEAR VARCHAR2(10)

My old table contains 800 recordsets.I want to import
data from this old table to new table.How can i fill QTNNO FIELD of new table.
My quotation table also contains 800 recordsets.

I am trying with following
DECLARE CURSOR C1 IS
SELECT QTNNO FROM QUOTATION_DETAILS;
QT QUOTATION_DETAILS.QTNNO%TYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO QT;
INSERT INTO DYNE.ORDERS
VALUES(ORDNO,ADDRESS,GOODSDELAT,COMMENTS,ADVANCE,MOP,CHNO,CHDT,BANK,TOTAL,BANKNAME,
BANK_ADDRESS,FINAUTHORITY,PMTSTATUS,INSTR,DELMODE,DELDT,YEAR,QTNNO)
SELECT ORDNO,ADDRESS,GOODSDELAT,COMMENTS,ADVANCE,MOP,CHNO,CHDT,BANK,TOTAL,BANKNAME,
BANK_ADDRESS,FINAUTHORITY,PMTSTATUS,INSTR,DELMODE,DELDT,YEAR,'QT' FROM CYBER.ORDERS
WHERE QTNNO ='QT';
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;
/

but it giving me error From:
Apoorva Gala
You can mail me at apoo1972@rediffmail.com

 

[lewisp]

You should exit from the loop before you try to do the insert, otherwise you will try to create two identical rows on the last fetch.

Pete

 

[lewisp]

I have studied the code a little closer now....

There are several problems with this procedure. One is that you are fetching from the cursor every time through the loop. There is no 'where' clause on the select, so what data are you retrieving?

Second, for each row in the loop you are inserting exactly the same data, so the uniqueness will be lost.

Thirdly, the EXIT statement is after the insert, so the loop will execute at least twice even if only one row is retrieved from the cursor, possibly causing uniqueness errors again.

Assuming you only want to return one row from the cursor, you need to re write the code like this:

DECLARE CURSOR C1 IS
SELECT QTNNO FROM QUOTATION_DETAILS;
QT QUOTATION_DETAILS.QTNNO%TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO QT;
CLOSE C1;
--
INSERT INTO DYNE.ORDERS
VALUES(ORDNO,ADDRESS,GOODSDELAT,COMMENTS,ADVANCE,MOP,CHNO,CHDT,BANK,TOTAL,BANKNAME,
BANK_ADDRESS,FINAUTHORITY,PMTSTATUS,INSTR,DELMODE,DELDT,YEAR,QTNNO)
SELECT ORDNO,ADDRESS,GOODSDELAT,COMMENTS,ADVANCE,MOP,CHNO,CHDT,BANK,TOTAL,BANKNAME,
BANK_ADDRESS,FINAUTHORITY,PMTSTATUS,INSTR,DELMODE,DELDT,YEAR,'QT' FROM CYBER.ORDERS
WHERE QTNNO ='QT';
END;

And even then, I cannot see where you are using the value selected from the cursor. Did you really mean to have 'QT' in quotes in the INSERT...SELECT statement?

Pete

 

[cyberdyne]

Thanks , but you have given me same reply which i hv made mistake. There is no QTNO field in cyber.orders table(Old Table) it is in new table (dyne.orders)



SELECT ORDNO,ADDRESS,GOODSDELAT,COMMENTS,ADVANCE,MOP,CHNO,CHDT,BANK,TOTAL,BANKNAME,
BANK_ADDRESS,FINAUTHORITY,PMTSTATUS,INSTR,DELMODE,DELDT,YEAR,'QT' FROM CYBER.ORDERS
WHERE QTNNO ='QT';

This is my <b>NEW TABLE</B> dyne.orders dyne is username

SQL> DESC ORDERS;
Name Null? Type
----------------- -------- ----
ORDNO NOT NULL VARCHAR2(10) PRIMARY KEY
ADDRESS VARCHAR2(100)
GOODSDELAT VARCHAR2(75)
COMMENTS VARCHAR2(150)
DELDT VARCHAR2(10)
YEAR VARCHAR2(10)
QTNNO VARCHAR2(10) ----NEW FIELD ADDED (REFERENCE KEY OF TABLE QUOTATION)

This is my <b>OLD TABLE</B> cyber.orders cyber is username.

SQL> DESC ORDERS;
Name Null? Type
------------------------------- -------- ----
ORDNO VARCHAR2(10)
ADDRESS VARCHAR2(100)
GOODSDELAT VARCHAR2(75)
COMMENTS VARCHAR2(150)
DELDT VARCHAR2(10)
YEAR VARCHAR2(10)
From:
Apoorva Gala
You can mail me at apoo1972@rediffmail.com

 

[sem]

Why do you quote QT? I mean, do you really need
WHERE QTNNO ='QT' instead of WHERE QTNNO =QT?
Your QT variable is never used.
If your QUOTATION_DETAILS table contains ordno field as a PK, you may execute smth like.

INSERT INTO DYNE.ORDERS
(ORDNO,ADDRESS,GOODSDELAT,COMMENTS,ADVANCE,MOP,CHNO,CHDT,BANK,TOTAL,BANKNAME,
BANK_ADDRESS,FINAUTHORITY,PMTSTATUS,INSTR,DELMODE,DELDT,YEAR,QTNNO)
SELECT ord.ORDNO,ord.ADDRESS,ord.GOODSDELAT,ord.COMMENTS, ord.ADVANCE,ord.MOP,ord.CHNO,ord.CHDT,ord.BANK,ord.TOTAL, ord.BANKNAME, ord.BANK_ADDRESS,ord.FINAUTHORITY,ord.PMTSTATUS,ord.INSTR, ord.DELMODE,ord.DELDT,ord.YEAR,det.QTNNO FROM CYBER.ORDERS ord,QUOTATION_DETAILS det
WHERE ord.ordno = det.ordno;

 

[lewisp]

Sorry, I really dont see the problem here. How can you say &quot;WHERE QTNNO = 'QT'&quot; when QTNNO doesn't exist in the table you are selecting from. No wonder you get an error!