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: 2086
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: 2086
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: 6968
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: 2086
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 More than 1 Include criteria for sing... Roy Ware SYNCSORT 6 Wed Sep 13, 2017 11:45 pm
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm
No new posts Include/omit with "ALL" pa... mgl DFSORT/ICETOOL 6 Wed Jun 14, 2017 2:31 pm

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