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.
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
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;
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
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
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
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';')
