|
View previous topic :: View next topic
|
| Author |
Message |
senthamizh
New User
Joined: 27 Apr 2009
Posts: 23
Location: mumbai
|
|
|
|
Hi ,
I have a input file in the below format.
-------------------------------------------------------------------------------------
| Code: |
SQL> SET ECHO OFF
SQL> SET HEADING OFF
SQL> SET TERMOUT OFF
SQL> SET FEEDBACK OFF
SQL> SET TRIMSPOOL ON
SQL> SET PAGESIZE 0
SQL> SET COLSEP ','
SQL> SELECT TZR,PROP_CODE,TO_CHAR(INV_DATE,'YYYY'),'3001',
2 TO_CHAR(INV_DATE,'YYYY-MM-DD'),'0',USER_NOTES
3 FROM OYT_XP_DAILY_SPREAD
4 WHERE INV_DATE BETWEEN TO_DATE('02/01/2018','MM/DD/YYYY')
3,BADPT,2018,3001,2018-02-01,0
3,MADPT,2018,3001,2018-02-02,0
3,CADPT,2018,3001,2018-02-03,0 |
------------------------------------------------------------------------
Below is the input file formate,RECFM=VB,LRECL=1028.
I want out to be written as below,
| Code: |
BADPT,2018,3001,2018-02-01,0
MADPT,2018,3001,2018-02-02,0
CADPT,2018,3001,2018-02-03,0 |
the Format should be RECFM=VB,LRECL=350.
Now i tried the below sort card,
| Code: |
SORT FIELDS=COPY
INCLUDE COND=(5,2,CH,EQ,C'1,',OR,
5,2,CH,EQ,C'2,',OR,
5,2,CH,EQ,C'3,')
OUTREC FIELDS=(1:7,5),CONVERT |
But it is not working.
Could you please advise.
Thanks,
Sen
Code'd |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
It's not clear what you are expecting CONVERT to do. You have no data which starts with '1,' or '2,'. Is that a typo in your sample?
Get rid of the OUTREC, don't use FIELDS on OUTREC (or INREC or OUTFIL OUTREC).
This may be what you want.
| Code: |
| INREC BUILD=(1,4,7) |
|
|
| Back to top |
|
|
senthamizh
New User
Joined: 27 Apr 2009
Posts: 23
Location: mumbai
|
|
|
|
The records in INPUT IS IN THE BELOW FORMATE(RCFM=VB,LRECL=1028)
Code:
SQL> SET ECHO OFF
SQL> SET HEADING OFF
SQL> SET TERMOUT OFF
SQL> SET FEEDBACK OFF
SQL> SET TRIMSPOOL ON
SQL> SET PAGESIZE 0
SQL> SET COLSEP ','
SQL> SELECT TZR,PROP_CODE,TO_CHAR(INV_DATE,'YYYY'),'3001',
2 TO_CHAR(INV_DATE,'YYYY-MM-DD'),'0',USER_NOTES
3 FROM OYT_XP_DAILY_SPREAD
4 WHERE INV_DATE BETWEEN TO_DATE('02/01/2018','MM/DD/YYYY')
3,BADPT,2018,3001,2018-02-01,0
3,MADPT,2018,3001,2018-02-02,0
3,CADPT,2018,3001,2018-02-03,0
After Sort i want ot extract only the below records from Input file and write it into outputfile.
BADPT,2018,3001,2018-02-01,0
MADPT,2018,3001,2018-02-02,0
CADPT,2018,3001,2018-02-03,0
The output file RECFM should be RECFM=FB,LRECL=350. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You've been a member here for six years+. Please use the code-tags to preserve spacing.
| Code: |
| OUTFIL BUILD=(7,346),VTOF |
Your INCLUDE was basically correct, except for the three conditions, which only need be one. |
|
| Back to top |
|
|
senthamizh
New User
Joined: 27 Apr 2009
Posts: 23
Location: mumbai
|
|
|
|
I am getting below error
| Code: |
WER250A INCLUDE/OMIT FIELD BEYOND RECORD |
when i use the below sort card
| Code: |
SORT FIELDS=COPY
INCLUDE COND=(5,2,CH,EQ,C'3,')
OUTREC BUILD=(7,346),VTOF |
|
|
| Back to top |
|
|
senthamizh
New User
Joined: 27 Apr 2009
Posts: 23
Location: mumbai
|
|
|
|
i tried
| Code: |
SORT FIELDS=COPY
INCLUDE COND=(5,2,CH,EQ,C'3,')
OUTFIL BUILD=(7,346),VTOF |
still getting the error:
| Code: |
WER250A INCLUDE/OMIT FIELD BEYOND RECORD |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Code: |
SORT FIELDS=COPY
INCLUDE COND=(5,2,CH,EQ,C'3,')
OUTFIL IFTHEN=(WHEN=INIT,
OVERLAY=(351:X)),
IFTHEN=(WHEN=INIT,
BUILD=(7,342)),VTOF,VLTRIM=C' ' |
|
|
| Back to top |
|
|
senthamizh
New User
Joined: 27 Apr 2009
Posts: 23
Location: mumbai
|
|
|
|
| I am able to get the result by using PARM='VLTESTI=1' |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Next time explain what you mean by "not working", including any error messages in full. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
