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
 

 

using Parse on some other operator for CSV

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Dec 11, 2014 11:11 am    Post subject: using Parse on some other operator for CSV
Reply with quote

Hi All,

I have around more than 49k record in CSV file in below format
Code:

20140020172102,660,2_K408Z9I,2,2110,10
20140270082502,834,000001,1,2110,10   
20140270082502,834,000001,2,2110,10   


Which i want to convert

Code:
OPTION COPY                                                 
INREC PARSE=(%00=(ENDBEFR=C',',FIXLEN=14),                   
             %01=(ENDBEFR=C',',FIXLEN=03),                   
             %02=(ENDBEFR=C','),                             
             %03=(ENDBEFR=C',',FIXLEN=01),                   
             %04=(ENDBEFR=C',',FIXLEN=04),                   
             %05=(ENDBEFR=C',',FIXLEN=02))                   
OUTREC BUILD=(C'DELETE FROM owner.table',/, 
 C' WHERE ICN =''',%00,C'''',/,                             
 C' AND ACT_PLAN =''',%01,C'''',/,                           
 C' AND LINE_ITEM_CNTL_NO =''',%02,C'''',/,                 
 C' AND CAS_SEG_OCC_NUM = ',%03,/,                           
 C' AND CLP_PMT_INFO_LOOP_NUM = ',%04,/,                     
 C' AND NPS_LINE_ID = ',%05,C';')       


This i want to use delete SQl , However the issue is third field is declared as Varchar 50 so is there any way to get it in sort without specifying fixed length

I mean output should be like


Code:

Delete from owner.table
WHERE ICN = 'WHATEVER VALUE'
AND ACT_PLAN= 'WHATEVER VALUE'
AND LINE_ITEM_CNTL_NO=' WHATEVER VALUE'
AND CLP_PMT_INFO_LOOP_NUM= 'WHATEVER VALUE'
AND NPS_LINE_ID= ' WHATEVER VALUE';



I dont want to use Like operator in query as this need to be run in prod to clean the records.

Please advise

Code'd a bit and some typos
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Dec 11, 2014 12:44 pm    Post subject: Reply to: using Parse on some other operator for CSV
Reply with quote

SyncSORT topics in the shiny-new SYNCSORT part of the forum please.

Use the maximum length for the FIXLEN and then SQZ with TRAIL for your closing value.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Dec 11, 2014 1:46 pm    Post subject: Reply to: using Parse on some other operator for CSV
Reply with quote

Try this job:
Code:
//STEP01   EXEC PGM=SORT                                               
//SORTIN   DD *                                                       
20140020172102,660,2_K408Z9I,2,2110,10                                 
20140270082502,834,000001,1,2110,10                                   
20140270082502,834,000001,2,2110,10                                   
//SORTOUT  DD SYSOUT=*                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  INREC PARSE=(%00=(ENDBEFR=C',',FIXLEN=14),                           
               %01=(ENDBEFR=C',',FIXLEN=03),                           
               %02=(ENDBEFR=C',',FIXLEN=50),                           
               %03=(ENDBEFR=C',',FIXLEN=01),                           
               %04=(ENDBEFR=C',',FIXLEN=04),                           
               %05=(ENDBEFR=C',',FIXLEN=02)),                         
        BUILD=(%00,%01,%02,%03,%04,%05)                               
  OUTFIL BUILD=(C'DELETE FROM OWNER.TABLE',/,                         
   C' WHERE ICN =''',1,14,C'''',/,                                     
   C' AND ACT_PLAN =''',15,3,C'''',/,                                 
   C' AND LINE_ITEM_CNTL_NO =''',18,50,SQZ=(SHIFT=LEFT,TRAIL=C''''),/,
   C' AND CAS_SEG_OCC_NUM = ',68,1,/,                                 
   C' AND CLP_PMT_INFO_LOOP_NUM = ',69,4,/,                           
   C' AND NPS_LINE_ID = ',73,2,C';')                                   


Output is:
Code:
DELETE FROM OWNER.TABLE             
 WHERE ICN ='20140020172102'         
 AND ACT_PLAN ='660'                 
 AND LINE_ITEM_CNTL_NO ='2_K408Z9I' 
 AND CAS_SEG_OCC_NUM = 2             
 AND CLP_PMT_INFO_LOOP_NUM = 2110   
 AND NPS_LINE_ID = 10;               
DELETE FROM OWNER.TABLE             
 WHERE ICN ='20140270082502'         
 AND ACT_PLAN ='834'                 
 AND LINE_ITEM_CNTL_NO ='000001'     
 AND CAS_SEG_OCC_NUM = 1             
 AND CLP_PMT_INFO_LOOP_NUM = 2110   
 AND NPS_LINE_ID = 10;               
DELETE FROM OWNER.TABLE             
 WHERE ICN ='20140270082502'         
 AND ACT_PLAN ='834'                 
 AND LINE_ITEM_CNTL_NO ='000001'     
 AND CAS_SEG_OCC_NUM = 2             
 AND CLP_PMT_INFO_LOOP_NUM = 2110   
 AND NPS_LINE_ID = 10;               
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Dec 11, 2014 3:00 pm    Post subject: Reply to: using Parse on some other operator for CSV
Reply with quote

mistah kurtz,

Did you try that with OUTREC instead of OUTFIL first?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Dec 11, 2014 3:18 pm    Post subject:
Reply with quote

with OUTREC I was getting syntax error. It appears new line can not be used with OUTREC.

SYNCSORT FOR Z/OS 1.4.1.0R

Code:
SYSIN :                                                                       
  OPTION COPY                                                                 
  INREC PARSE=(%00=(ENDBEFR=C',',FIXLEN=14),                                   
               %01=(ENDBEFR=C',',FIXLEN=03),                                   
               %02=(ENDBEFR=C',',FIXLEN=50),                                   
               %03=(ENDBEFR=C',',FIXLEN=01),                                   
               %04=(ENDBEFR=C',',FIXLEN=04),                                   
               %05=(ENDBEFR=C',',FIXLEN=02)),                                 
        BUILD=(%00,%01,%02,%03,%04,%05)                                       
  OUTREC BUILD=(C'DELETE FROM OWNER.TABLE',/,                                 
                                           *                                   
   C' WHERE ICN =''',1,14,C'''',/,                                             
   C' AND ACT_PLAN =''',15,3,C'''',/,                                         
   C' AND LINE_ITEM_CNTL_NO =''',18,50,SQZ=(SHIFT=LEFT,TRAIL=C''''),/,         
   C' AND CAS_SEG_OCC_NUM = ',68,1,/,                                         
   C' AND CLP_PMT_INFO_LOOP_NUM = ',69,4,/,                                   
   C' AND NPS_LINE_ID = ',73,2,C';')                                           
WER268A  OUTREC STATEMENT  : SYNTAX ERROR                                     
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE   
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Dec 11, 2014 5:49 pm    Post subject: Reply to: using Parse on some other operator for CSV
Reply with quote

Thanks. I thought the attempted use on OUTREC indicated SyncSORT :-) Shifting it back.

Probably it is the confusion between OUTREC itself and OUTREC on OUTFIL which caused the outline code to be written that way.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Dec 11, 2014 5:55 pm    Post subject:
Reply with quote

Cool working like a charm
Earlier i Have used
Code:

OPTION COPY                                         
OUTFIL PARSE=(%00=(ENDBEFR=C',',FIXLEN=14),         
             %01=(ENDBEFR=C',',FIXLEN=03),           
             %02=(ENDBEFR=C',',FIXLEN=50),           
             %03=(ENDBEFR=C',',FIXLEN=01),           
             %04=(ENDBEFR=C',',FIXLEN=04),           
             %05=(ENDBEFR=C',',FIXLEN=02)),         
 BUILD=(C'DELETE FROM PNASCO.CLAIM_LINE_REMIT_CAS',/,
 C' WHERE ICN =''',%00,C'''',/,                     
 C' AND ACT_PLAN =''',%01,C'''',/,                   
 C' AND LINE_ITEM_CNTL_NO =''',%02,C'''',/,         
 C' AND CAS_SEG_OCC_NUM = ',%03,/,                   
 C' AND CLP_PMT_INFO_LOOP_NUM = ',%04,/,             
 C' AND NPS_LINE_ID = ',%05,C';')                   


But ur code ....cool
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 -> DFSORT/ICETOOL 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 get the right string from Vari... jackzhang75 CLIST & REXX 9 Mon Nov 16, 2015 10:04 pm
No new posts ICETOOL "SUBSET" operator q... David Sde DFSORT/ICETOOL 2 Tue Jul 28, 2015 7:53 pm
No new posts tws opc 5.4 how to change operator te... italo_pm IBM Tools 5 Wed Apr 29, 2015 11:22 am
No new posts Problem using PARSE Roach_MF DFSORT/ICETOOL 3 Tue Mar 17, 2015 11:44 pm
No new posts Rexx not equals operator harisukumaran CLIST & REXX 5 Sat Sep 20, 2014 2:57 am


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