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 with INCLUDE clause

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

Senior Member


Joined: 01 Sep 2006
Posts: 2038
Location: Silicon Valley

PostPosted: Sat Jun 16, 2012 4:20 am    Post subject: SQL MERGE statement with INCLUDE clause
Reply with quote

Right now, I am not using the INCLUDE clause and it more or less works. But on some instances of the name, I get a -433:

Code:
 DSNT408I SQLCODE = -433, ERROR:  VALUE TBOC7001_BASE IS TOO LONG


For that reason, I think I need to have an INCLUDE clause which will let me specify a data type of VARCHAR(128).

But I am not having luck with the correct syntax. Does anyone have an example of a SQL MERGE statement with the INCLUDE clause?
Back to top
View user's profile Send private message

Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2038
Location: Silicon Valley

PostPosted: Sat Jun 16, 2012 4:30 am    Post subject: Reply to: SQL MERGE statement with INCLUDE clause
Reply with quote

I finally read the manual.
Quote:
INCLUDE can only be specified when the
MERGE statement is nested in the FROM clause of a SELECT statement.


But how do I resolve my -433 problem? The NAME column is VARCHAR(128)


Code:
MERGE INTO PEDRO.TABLE1 AS A                                   
                                                                     
USING (                                                             
VALUES ( 168   ,'TB','QUOC7001','TBOC7001_BASE',''      ,'' )) AS B
       (SCOPEID,TYPE,QUALIFIER ,NAME           , COLNAME,AUTHLEVEL)                     
ON    A.SCOPEID  = B.SCOPEID                                         
AND  A.TYPE      = B.TYPE                                           
AND  A.QUALIFIER = B.QUALIFIER                                       
AND  A.NAME      = B.NAME                                           
WHEN NOT MATCHED                                                     
THEN                                                                 
INSERT( SCOPEID,   TYPE,   QUALIFIER,   NAME,   COLNAME,   AUTHLEVEL)
VALUES                                                               
     (B.SCOPEID, B.TYPE, B.QUALIFIER, B.NAME, B.COLNAME, B.AUTHLEVEL)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Sat Jun 16, 2012 5:35 am    Post subject:
Reply with quote

Pedro,

can you INSERT a row into PEDRO.TABLE1 with
VALUES ( 168 ,'TB','QUOC7001','TBOC7001_BASE','' ,'' )
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2038
Location: Silicon Valley

PostPosted: Tue Jun 19, 2012 4:12 am    Post subject: Reply to: SQL MERGE statement with INCLUDE clause
Reply with quote

Found my problem... apparently the SQL parser wants a blank after a comma.

When all of the values are strung together, it causes some unpredictable results.
Code:
VALUES ( 168   ,'TB','QUOC7001','TBOC7001_BASE',''      ,'' )) AS B


Well, I could not predict it before. Maybe in the future.
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