|
View previous topic :: View next topic
|
| Author |
Message |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1209
Location: Bangalore,India
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
mistah kurtz
Active User
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; |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
mistah kurtz,
Did you try that with OUTREC instead of OUTFIL first? |
|
| Back to top |
|
|
mistah kurtz
Active User
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 |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1209
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';') |
But ur code ....cool |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
