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
 

 

Multi row insert/fetch on a VARCHAR column in DB2

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

New User


Joined: 02 Aug 2011
Posts: 17
Location: India

PostPosted: Mon Nov 21, 2011 2:35 pm    Post subject: Multi row insert/fetch on a VARCHAR column in DB2
Reply with quote

Hi,

We are facing problems in inserting/fetching data into a VARCHAR column defined on a table using Multi row insert/fetch option.

Please let me know, will it be possible doing a multi row operation on VARCHAR column of DB2 table.

Thanks
Satish
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Mon Nov 21, 2011 2:46 pm    Post subject: Reply to: Multi row insert/fetch on a VARCHAR column in DB2
Reply with quote

instead of a generic whining...
it would have been better if You had told what kind of problem You faced
what also about looking at the manuals Yourself for the messages received icon_question.gif
Back to top
View user's profile Send private message
satishboddu

New User


Joined: 02 Aug 2011
Posts: 17
Location: India

PostPosted: Mon Nov 21, 2011 3:18 pm    Post subject:
Reply with quote

Code:
EXEC SQL                                       
     FETCH NEXT ROWSET FROM GTT_CSR FOR 100 ROWS
      INTO  :WR-FIRST-REC                       
END-EXEC                                       

05 WR-FIRST-REC.                                       
   10 WR-RULE-NO            PIC X(03) VALUE SPACES OCCURS 100 TIMES.
   10 WR-SEQ-NUM           PIC X(04) VALUE SPACES OCCURS 100 TIMES.
   10 WR-SELECT-VAR.                                   
      49 WR-SEL-LN            PIC S9(04) USAGE COMP
                                       OCCURS 100 TIMES.
      49 WR-SELECT           PIC X(470) VALUE SPACES 
                                       OCCURS 100 TIMES.
   10 WR-WHERE              PIC X(23) VALUE SPACES   
                                       OCCURS 100 TIMES.
   10 WR-FROM                 PIC X(50) VALUE SPACES   
                                       OCCURS 100 TIMES.


Please find above snippet, we tried to display data after fetch from this variables, we don't be able to see right data at VARCHAR column(data misplaced) while rest of all showing correct data as present in table.

Code:
WS-SEL-LEN     :094M                                                           
WS-SELECT      :                                                               
                                                                               
                                                            CASE WHEN QTB_50_SUB
QTB_20_TMR_FED_W2_MAG_PIN_C = ' ') THEN  CWA_RO_ZIP_5 ELSE CASE WHEN QTB_20_CO_L
 <> 'Y                                                                         
WS-WHERE       :       


As we see in the above Display statement , the WS-SELECT show text starting at some random position instead of starting at 1st position.

This is the problem we are facing.

Pls suggest.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Nov 21, 2011 3:35 pm    Post subject:
Reply with quote

suggest you go back to the manual.

your syntax is incorrect.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Nov 21, 2011 4:11 pm    Post subject:
Reply with quote

Hi Satish,

While using VARCHAR column in multi fetch you need to give SYNC clause for length defination of varchar column.

So your defination for Varchar column should be liek below

Code:
10 WR-SELECT-VAR OCCURS 100 TIMES.
    49 WR-SEL-LN PIC S9(04) USAGE COMP SYNC.
    49 WR-SELECT PIC X(470) VALUE SPACES .


Please check this out and let us know if it works for you

Regards,
Chandan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Nov 21, 2011 4:20 pm    Post subject:
Reply with quote

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.apsg/bjnqcobl2189515.htm#bjnqcobl2191273
more specifically figure 80.
Back to top
View user's profile Send private message
satishboddu

New User


Joined: 02 Aug 2011
Posts: 17
Location: India

PostPosted: Mon Nov 21, 2011 4:21 pm    Post subject:
Reply with quote

Thanks Chandan,
We are able to get correct results. Thank You so much.
icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif
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 Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am


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