IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

using Parse on some other operator for CSV


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
guptae

Moderator


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

PostPosted: Thu Dec 11, 2014 11:11 am
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Thu Dec 11, 2014 12:44 pm
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: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Dec 11, 2014 1:46 pm
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Thu Dec 11, 2014 3:00 pm
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: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Thu Dec 11, 2014 3:18 pm
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

PostPosted: Thu Dec 11, 2014 5:49 pm
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: 1206
Location: Bangalore,India

PostPosted: Thu Dec 11, 2014 5:55 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts PARSE using REPEAT DFSORT/ICETOOL 1
No new posts Relational operator compare date rang... CLIST & REXX 14
No new posts Need to loop & extract from varia... CLIST & REXX 2
No new posts PARSE GDG name to separate generation... SYNCSORT 2
No new posts XML Parse for COBOL 5.2 COBOL Programming 1
Search our Forums:

Back to Top