Home » SQL & PL/SQL » SQL & PL/SQL » want to update a column of complex table (oracle 9i)
want to update a column of complex table [message #282039] Tue, 20 November 2007 09:23 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello sir,

i want to update a column's data of complex table but unable to.

my queries is

SELECT DISTINCT a.VC_GATE_ENT_NO, a.NU_PARTY_CODE, a.VC_RECEIPT_NO, a.VC_PARTY_TYPE,
 b.NU_QTY_AS_PER_CHALLAN,b.NU_QTY_RECEIVED, b.NU_BALANCE_QTY
 FROM HD_RECEIPT a, DT_RECEIPT b
 WHERE a.vc_comp_code='01'
 AND a.vc_comp_code = b.vc_comp_code
 AND a.VC_RECEIPT_NO = b.VC_RECEIPT_NO
 AND a.DT_RECEIPT_DATE = b.DT_RECEIPT_DATE
 AND a.VC_GATE_ENT_NO LIKE '01%'
 AND b.NU_QTY_AS_PER_CHALLAN = b.NU_QTY_RECEIVED
 AND a.NU_PARTY_CODE LIKE '359'


and my output is ------

VC_GATE_ENT_NO	NU_PARTY_CODE	VC_RECEIPT_NO	VC_PARTY_TYPE	NU_QTY_AS_PER_CHALLAN	NU_QTY_RECEIVED	NU_BALANCE_QTY
012982	359	012830	S	96.00	96.00	0.00
012983	359	012831	S	600.00	600.00	0.00
012985	359	012833	S	480.00	480.00	0.00
012986	359	012834	S	1,056.00	1,056.00	0.00
012987	359	012835	S	480.00	480.00	0.00
012988	359	012836	S	576.00	576.00	0.00
013005	359	012853	S	580.00	580.00	0.00
013006	359	012854	S	20.00	20.00	0.00
013011	359	012859	S	180.00	180.00	0.00
013015	359	012863	S	184.00	184.00	0.00



now i want to replace vc_gate_ent_no to 2982 in place of 012982.

regards
Re: want to update a column of complex table [message #282042 is a reply to message #282039] Tue, 20 November 2007 09:31 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I guess your vc_gate_ent_no is varchar2(15).
You can try
update hd_reciept set substr(vc_date_ent_no,1,6)=2982 where substr(vc_gate_ent_no,1,6)=012982;

But I havent tested it.
Re: want to update a column of complex table [message #282045 is a reply to message #282039] Tue, 20 November 2007 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is a complex table?

SELECT DISTINCT 
substr(a.VC_GATE_ENT_NO,3),
 a.NU_PARTY_CODE, a.VC_RECEIPT_NO, a.VC_PARTY_TYPE,
 b.NU_QTY_AS_PER_CHALLAN,b.NU_QTY_RECEIVED, b.NU_BALANCE_QTY
 FROM HD_RECEIPT a, DT_RECEIPT b
 WHERE a.vc_comp_code='01'
 AND a.vc_comp_code = b.vc_comp_code
 AND a.VC_RECEIPT_NO = b.VC_RECEIPT_NO
 AND a.DT_RECEIPT_DATE = b.DT_RECEIPT_DATE
 AND a.VC_GATE_ENT_NO LIKE '01%'
 AND b.NU_QTY_AS_PER_CHALLAN = b.NU_QTY_RECEIVED
 AND a.NU_PARTY_CODE LIKE '359'

Regards
Michel
Re: want to update a column of complex table [message #282128 is a reply to message #282045] Tue, 20 November 2007 23:43 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member

hello sir.
sorry for my mistake.
inplace of complex view i wrote complex table.

i made a trigger but unable to solve(recognise) the error.

SQL> CREATE OR REPLACE TRIGGER tr_complex_dt_receipt
  2  instead OF UPDATE
  3  ON v_complex_dt_receipt
  4  FOR EACH ROW
  5  BEGIN
  6  UPDATE HD_RECEIPT
  7  SET SUBSTR(vc_date_ent_no,1,6)=:NEW.vc_date_ent_no 
  8  WHERE SUBSTR(vc_gate_ent_no,1,6)=:OLD.vc_date_ent_no;
  9  END;
 10  
 11  /

Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER TR_COMPLEX_DT_RECEIPT:

LINE/COL ERROR
-------- ------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
3/11     PL/SQL: ORA-00927: missing equal sign
3/32     PLS-00049: bad bind variable 'NEW.VC_DATE_ENT_NO'
4/34     PLS-00049: bad bind variable 'OLD.VC_DATE_ENT_NO'



i have created a complex view
CREATE OR REPLACE VIEW v_complex_dt_receipt
AS SELECT DISTINCT a.VC_GATE_ENT_NO, a.NU_PARTY_CODE, a.VC_RECEIPT_NO, a.VC_PARTY_TYPE,
 b.NU_QTY_AS_PER_CHALLAN,b.NU_QTY_RECEIVED, b.NU_BALANCE_QTY
 FROM HD_RECEIPT a, DT_RECEIPT b
 WHERE a.vc_comp_code='01'
 AND a.vc_comp_code = b.vc_comp_code
 AND a.VC_RECEIPT_NO = b.VC_RECEIPT_NO
 AND a.DT_RECEIPT_DATE = b.DT_RECEIPT_DATE
 AND a.VC_GATE_ENT_NO LIKE '01%'
 AND b.NU_QTY_AS_PER_CHALLAN = b.NU_QTY_RECEIVED
 AND a.NU_PARTY_CODE LIKE '359'


help me how could i update the column on hd_receipt.

regards
Re: want to update a column of complex table [message #282147 is a reply to message #282128] Wed, 21 November 2007 00:26 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You cannot update a substring of a column value. You have to update the whole value as shown below.

-- test data:
SCOTT@orcl_11g> CREATE TABLE hd_receipt (vc_date_ent_no  VARCHAR2 (60))
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO hd_receipt VALUES ('ABCDEFGHIJKL')
  2  /

1 row created.


-- reproduction of error:
SCOTT@orcl_11g> UPDATE hd_receipt
  2  SET    SUBSTR (vc_date_ent_no, 1, 6) = 'ZXYXVU'
  3  /
SET    SUBSTR (vc_date_ent_no, 1, 6) = 'ZXYXVU'
              *
ERROR at line 2:
ORA-00927: missing equal sign


-- correction:
SCOTT@orcl_11g> UPDATE hd_receipt
  2  SET    vc_date_ent_no = 'ZXYXVU' || SUBSTR (vc_date_ent_no, 7)
  3  /

1 row updated.

SCOTT@orcl_11g> SELECT * FROM hd_receipt
  2  /

VC_DATE_ENT_NO
------------------------------------------------------------
ZXYXVUGHIJKL

SCOTT@orcl_11g> 

Previous Topic: Need help on SQL statement ..
Next Topic: My Procedure: lock held by in-doubt distributed transaction 11.11.2720358
Goto Forum:
  


Current Time: Wed Dec 07 11:03:20 CST 2016

Total time taken to generate the page: 0.18545 seconds