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: 2025
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: 2025
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: 6966
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: 2025
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
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
This topic is locked: you cannot edit posts or make replies. Updating value of key using rewrite s... ParminderKumar COBOL Programming 4 Thu Aug 04, 2016 12:46 am


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