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 Go to next message
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 Go to previous messageGo to next message
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 #364858 is a reply to message #364855] Wed, 10 December 2008 05:16 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Just a syntax error.

Why don't try to check the code at least on syntax errors?


Don't you see something strange here?

43 TGO.SYS_TYPE =TGL.SYS_TYPE,,



Bye Alessandro
Re: Regarding the merge error [message #364860 is a reply to message #364855] Wed, 10 December 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Tue, 09 December 2008 07:30
From your previous post:
Michel Cadot wrote on Fri, 05 December 2008 12:19
From time to time I'm tired and disheartened to repeat the same thing, but if you encourage me I can post it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel




Re: Regarding the merge error [message #364870 is a reply to message #364860] Wed, 10 December 2008 05:40 Go to previous messageGo to next message
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 #364871 is a reply to message #364870] Wed, 10 December 2008 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I followed the rules as mentioned there.If not, sorry and I will read it again and will follow the rules.

Read again the section I mentionned.
Do you think your posts are formatted?
Read other topics and you will the differences between unformatted and formatted queries.

Regards
Michel
Re: Regarding the merge error [message #364890 is a reply to message #364870] Wed, 10 December 2008 06:23 Go to previous message
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
Previous Topic: How to rewrite this stored procedure into a view ?
Next Topic: Oracle Connection Error
Goto Forum:
  


Current Time: Sat Dec 03 22:25:13 CST 2016

Total time taken to generate the page: 0.05490 seconds