Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL Merge Statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Thu Oct 01, 2009 8:51 pm    Post subject: SQL Merge Statement
Reply with quote

Hi
I am trying to use the MERGE to update a table.

Code:
MERGE INTO D1.TJAEREFERENCE B                             
USING D1.TJAECATALOGUE A                                 
ON                                                       
substr(A.TABLENAME,5,46) = substr(B.REFTBNAME,5,46)       
and A.REFINTGRTFLAG = 'Y'                                 
WHEN MATCHED THEN UPDATE SET                             
B.TLOTYPE = A.TLOTYPE                                     


But I am getting the error as below
Code:
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "D1". SOME SYMBOLS THAT MIGHT   
         BE LEGAL ARE: IS                                                       


Can some one please shed some light whats missing...
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Oct 02, 2009 12:19 pm    Post subject:
Reply with quote

Can you try removing all occurance of D1.
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Fri Oct 02, 2009 5:09 pm    Post subject: Reply to: SQL Merge Statement
Reply with quote

then system is looking for instance of table in my login id

same kinda result.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Oct 02, 2009 5:24 pm    Post subject:
Reply with quote

Can you post the changed code?
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Fri Oct 02, 2009 6:39 pm    Post subject: Reply to: SQL Merge Statement
Reply with quote

MERGE INTO TJAEREFERENCE B 00000003
USING TJAECATALOGUE A 00000004
ON 00000005
A.TABLENAME = B.REFTBNAME 00000006
and A.REFINTGRTFLAG = 'Y' 00000007
WHEN MATCHED THEN UPDATE SET 00000008
B.TLOTYPE = A.TLOTYPE;;;;;; 00000009
Code:
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "TJAEREFERENCE". SOME SYMBOLS   
         THAT MIGHT BE LEGAL ARE: IS                                           
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 502 0  0  -1  84  0 SQL DIAGNOSTIC INFORMATION           
DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000054'  X'00000000' SQL DIAGNOSTIC INFORMATION                   
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sat Oct 03, 2009 1:53 pm    Post subject:
Reply with quote

Please consult the grammar of MERGE statement from <<SQL reference>>.

You can notice that obviously there is a grammar error on the 2nd line in your statement.

Keyword "VALUE" must be followed by "USING"!

Code:
USING D1.TJAECATALOGUE A 
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us