Home » SQL & PL/SQL » SQL & PL/SQL » Regarding the merge error (oracle 9i)
Regarding the merge error [message #364855] |
Wed, 10 December 2008 05:03  |
sowmyaa
Messages: 26 Registered: November 2008
|
Junior Member |
|
|
Hi,
Please find the below sql queries.There is an error in the second query but not able to find it out.Even thought the first and second are similar, but not able to find out the second error..could you pls guide me on the same?
I read the oracel faq and pasted the below from the sql screen only....
SQL> Merge Into T_S1 TGO
2 Using (Select Min(a.ASUP_ID) as MIN_ASUP, Max(a.ASUP_ID) as MAX_ASUP, b.DVC_SERIALNO, b.DVC_MODEL
3 FROM T_TET2 a JOIN T_T1 b ON (a.DVC_ID = b.DVC_ID)
4 Where DVC_TYPE = 'DISK'
5 GROUP BY b.DVC_SERIALNO, b.DVC_MODEL) TGL
6 ON (tgo.DVC_SERIALNO = tgl.DVC_SERIALNO And tgo.DVC_MODEL =tgl.DVC_MODEL)
7 When Matched Then
8 UPDATE SET TGO.MIN_ASUP = CASE WHEN TGO.MIN_ASUP > TGL.MIN_ASUP THEN TGL.MIN_ASUP ELSE TGO.MIN_ASUP END,
9 TGO.MAX_ASUP = CASE WHEN TGO.MAX_ASUP < TGL.MAX_ASUP THEN TGL.MAX_ASUP ELSE TGO.MAX_ASUP END
10 When Not Matched Then Insert VALUES (TGL.MIN_ASUP,TGL.MAX_ASUP,TGL.DVC_SERIALNO,TGL.DVC_MODEL);
4380320 rows merged.
SQL>
SQL> /
4380320 rows merged.
SQL>
SQL>
SQL> Merge Into TBL_LONG1 TGO
2 using (SELECT /*+ leading (a) use_nl(b)*/
3 b.ASUP_ID,
4 a.EVT_GMT_SEC,
5 tsi.SYS_SERIALNO,
6 tsi.SYS_MODEL,
7 tsi.SYS_VERSION,
8 tsi.SYS_TOP_DOMAIN,
9 tsi.SYS_TYPE,
10 td.DVC_SERIALNO,
11 td.DVC_MODEL,
12 tdi.DVC_SIZE_GB,
13 tdi.DVC_FW_REV,
14 asup_link(ta.asup_id) as asup_link,
15 a.S_ISENSEKEY,
16 a.S_IASC,
17 a.S_IASCQ,
18 a.S_IFRU
19 From
20 t_dvc_info tdi,
21 t_dvc td,
22 t_sys_info tsi,
23 t_asup ta,
24 t_evt_join b,
25 Z_SCSI_CMD_NOTREADYCONDITION a
26 Where a.sys_id = b.sys_id
27 AND a.log_file_id = b.log_file_id
28 AND a.evt_offset = b.evt_offset
29 AND b.asup_id = ta.asup_id
30 AND ta.sys_info_code = tsi.sys_info_code
31 AND b.dvc_id = td.dvc_id
32 AND b.dvc_info_code = tdi.dvc_info_code
33 AND b.ASUP_ID >= '20070101%') TGL
34 ON (
35 TGO.ASUP_ID = TGL.ASUP_ID And TGO.EVT_GMT_SEC = TGL.EVT_GMT_SEC
36 and TGO. SYS_SERIALNO =TGL. SYS_SERIALNO and TGO.SYS_MODEL=TGL.SYS_MODEL
37 and TGO.SYS_TYPE=TGL.SYS_TYPE and TGO.DVC_SERIALNO =TGL.DVC_SERIALNO
38 and TGO.DVC_MODEL=TGL.DVC_MODEL )
39 When Matched Then
40 UPDATE SET
41 (TGO.ASUP_ID =TGL.ASUP_ID,TGO.EVT_GMT_SEC =TGL.EVT_GMT_SEC,TGO.SYS_SERIALNO=TGL.SYS_SERIALNO,
42 TGO.SYS_MODEL=TGL.SYS_MODEL,TGO.SYS_VERSION=TGL.SYS_VERSION,TGO.SYS_TOP_DOMAIN =TGL.SYS_TOP_DOMAIN,
43 TGO.SYS_TYPE =TGL.SYS_TYPE,,
44 TGO.DVC_SERIALNO =TGL.DVC_SERIALNO,TGO.DVC_MODEL = TGL.DVC_SERIALNO ,TGO.DVC_SIZE_GB =TGL.DVC_SIZE_GB,
45 TGO.DVC_FW_REV =TGL.DVC_FW_REV,
46 TGO.ASUP_LINK = TGL.ASUP_LINK,TGO.S_ISENSEKEY = TGL.S_ISENSEKEY,TGO.S_IASC =TGL.S_IASC,
47 TGO.S_IASCQ =TGL.S_IASCQ,TGO.S_IFRU = TGL.S_IFRU
48 When Not Matched Then
49 Insert ( TGO.ASUP_ID,TGO.EVT_GMT_SEC,TGO.SYS_SERIALNO,
50 TGO.SYS_MODEL,TGO.SYS_VERSION,TGO.SYS_TOP_DOMAIN,TGO.SYS_TYPE,
51 TGO.DVC_SERIALNO,TGO.DVC_MODEL,TGO.DVC_SIZE_GB,TGO.DVC_FW_REV,
52 TGO.ASUP_LINK,TGO.S_ISENSEKEY,TGO.S_IASC,TGO.S_IASCQ,TGO.S_IFRU)
53 VALUES ( TGL.ASUP_ID,TGL.EVT_GMT_SEC,TGL.SYS_SERIALNO,
54 TGL.SYS_MODEL,TGL.SYS_VERSION,TGL.SYS_TOP_DOMAIN,TGL.SYS_TYPE,
55 TGL.DVC_SERIALNO,TGL.DVC_MODEL,TGL.DVC_SIZE_GB,TGL.DVC_FW_REV,
56 TGL.ASUP_LINK,TGL.S_ISENSEKEY,TGL.S_IASC,TGL.S_IASCQ,TGL.S_IFRU);
(TGO.ASUP_ID =TGL.ASUP_ID,TGO.EVT_GMT_SEC =TGL.EVT_GMT_SEC,TGO.SYS_SERIALNO=TGL.SYS_SERIALNO,
*
ERROR at line 41:
ORA-01747: invalid user.table.column, table.column, or column specification
SQL>
|
|
|
Re: Regarding the merge error [message #364857 is a reply to message #364855] |
Wed, 10 December 2008 05:13   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The first problem I spot is that your WHEM MATCHED THEN UPDATE clause is trying to set the same columns that you use to join in the ON clause - that's a no-no.
|
|
|
|
|
Re: Regarding the merge error [message #364870 is a reply to message #364860] |
Wed, 10 December 2008 05:40   |
sowmyaa
Messages: 26 Registered: November 2008
|
Junior Member |
|
|
I read the oracel faq forms as mentioned in my previous mail.
I think, I followed the rules as mentioned there.If not, sorry and I will read it again and will follow the rules.
But till now, I am not able to find out the error for the same.
As mentioned by you, I gave the same for the 'ON' condition and 'when matched update set'.
It's because, I checked the not null values in the table and gave the 'ON' condition.please let me know if I am wrong.
Thanks and REgards,
Sowmya
|
|
|
|
Re: Regarding the merge error [message #364890 is a reply to message #364870] |
Wed, 10 December 2008 06:23  |
 |
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Quote: | But till now, I am not able to find out the error for the same.
|
Processing ...
UPDATE TAB
SET X = 'A',,F='B'
UPDATE TAB
*
ORA-01747: invalid user.table.column, table.column, or column specification
Can you spot something similar between these code and your code?
Put more attention during your readings!!!!!!
You didn't spot the error in your code, the solution I posted before and how to format code on the forum guide when there's just a button to press ({..}).
Bye Alessandro
|
|
|
Goto Forum:
Current Time: Tue Feb 11 18:47:14 CST 2025
|