Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 2104
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: 2104
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: 6970
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: 2104
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 How to include a particular field val... sakrat DFSORT/ICETOOL 8 Fri Apr 06, 2018 8:27 pm
No new posts Merge 2 records sancraig16 SYNCSORT 19 Tue Mar 27, 2018 8:17 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts Can we merge these two sort cards int... abdulrafi JCL & VSAM 2 Thu Mar 08, 2018 4:03 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us