|
View previous topic :: View next topic
|
| Author |
Message |
CHANDAN KAPOOR
New User
Joined: 12 Apr 2008
Posts: 36
Location: NOIDA
|
|
|
|
Hi ,
I have unload file with records like :
10/25/20111072404363417
10/25/20112625004417717
10/25/20111072404361717
10/25/20111072404361917
10/25/20111072404362517
I want to append a unique rowid for each record with a hardcoded value
like 'FIL01' along with the record id(seq no of record),
So that the O/P file will look like.
FIL0100000000000000110/25/20111072404363417
FIL0100000000000000210/25/20112625004417717
FIL0100000000000000310/25/20111072404361717
FIL0100000000000000410/25/20111072404361917
FIL0100000000000000510/25/20111072404362517
Does anybody how to do this using DFSORT or ICETOOL. |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Yes, I'm sure someone does.
Have a look at the manual/search forum for BUILD, SEQNUM and you can probably get there quite quickly yourself. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
CHANDAN KAPOOR,
What is the bigger picture for this requirement, if this is unload, I am wondering why not do it in unload itself?
Regardless, see if below helps... I am assuming 80 byte FB input and output will be 100 byte FB.
| Code: |
//STEP0001 EXEC PGM=SORT
//SORTIN DD *
10/25/20111072404363417
10/25/20112625004417717
10/25/20111072404361717
10/25/20111072404361917
10/25/20111072404362517
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTREC BUILD=(C'FIL01',SEQNUM,15,ZD,1,80)
/*
//SYSOUT DD SYSOUT=*
//* |
OUTPUT
| Code: |
FIL0100000000000000110/25/20111072404363417
FIL0100000000000000210/25/20112625004417717
FIL0100000000000000310/25/20111072404361717
FIL0100000000000000410/25/20111072404361917
FIL0100000000000000510/25/20111072404362517 |
Thanks, |
|
| Back to top |
|
|
CHANDAN KAPOOR
New User
Joined: 12 Apr 2008
Posts: 36
Location: NOIDA
|
|
|
|
Hi ,
Can you suggest me that how to take care of this in uload itself.
Our unload file is VB , What things need to be changed in the
sortcard for the VB record. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| What is the bigger picture for this requirement, if this is unload, I am wondering why not do it in unload itself? |
I suspect the assumption is that by "unload" you were talking about unloading data from a database table. If this is true, you would change the database unload, not the sort. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
CHANDAN KAPOOR,
Below is for the VB input file.
| Code: |
//SYSIN DD *
SORT FIELDS=COPY
OUTREC BUILD=(1,4,C'FIL01',SEQNUM,15,ZD,5)
/* |
What is your DB2 version?
Dick,
Yes I was referring to DB2 unload.
Thanks, |
|
| Back to top |
|
|
CHANDAN KAPOOR
New User
Joined: 12 Apr 2008
Posts: 36
Location: NOIDA
|
|
|
|
Hi ,
The query used for Unload is
SELECT DISTINCT I.INQR_GEN_KEY,CURRENT_DATE
FROM HUM.TIQOINQ I
INNER JOIN HUM.TIQTEAM T
ON T.TEAM_ID=I.OWNER_TEAM_ID
INNER JOIN HUM.TIQIMTM M
ON M.TEAM_ID=I.OWNER_TEAM_ID AND
M.INVENTORY_TYPE_CD='UW'
WHERE I.INQR_GEN_KEY=I.INQA_GEN_KEY AND
I.STATUS_CD IN('OP','CL') AND
I.INQR_TYPE_CD = 'KM' AND
I.INQA_TYPE_CD = 'KM' AND
((T.TPOINT_IND = 'Y' AND
DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
= T.TPOINT_FREQ) OR
(DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
= M.TEAM_SLA) OR
(T.CLOSE_LOOP_IND = 'Y' AND
DATE(INQUIRY_CLOSE_TS) =
CURRENT_DATE - 5 DAYS ))
WITH UR;
The DB2 version is V9R1M0 , Is there any way to generate a
(hardcoded value +sequence no) in unload itself as we are doing in sort.
Thanks,
Chandan Kapoor |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
CHANDAN KAPOOR,
See if below untested helps...
Small world 
| Code: |
SELECT CAST ('FIL01' || DIGITS(CAST(ROW_CNT AS DECIMAL(15,0))) AS
CHAR(20)) AS FIL,T.INQR_GEN_KEY,CURRENT_DATE
FROM (
SELECT DISTINCT I.INQR_GEN_KEY,ROW_NUMBER () OVER () AS ROW_CNT
FROM HUM.TIQOINQ I
INNER JOIN HUM.TIQTEAM T
ON T.TEAM_ID=I.OWNER_TEAM_ID
INNER JOIN HUM.TIQIMTM M
ON M.TEAM_ID=I.OWNER_TEAM_ID AND
M.INVENTORY_TYPE_CD='UW'
WHERE I.INQR_GEN_KEY=I.INQA_GEN_KEY AND
I.STATUS_CD IN('OP','CL') AND
I.INQR_TYPE_CD = 'KM' AND
I.INQA_TYPE_CD = 'KM' AND
((T.TPOINT_IND = 'Y' AND
DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
= T.TPOINT_FREQ) OR
(DAYS(CURRENT_DATE)- DAYS(DATE(I.CREATED_TS))
= M.TEAM_SLA) OR
(T.CLOSE_LOOP_IND = 'Y' AND
DATE(INQUIRY_CLOSE_TS) =
CURRENT_DATE - 5 DAYS ))) T
ORDER BY FIL
WITH UR; |
Thanks, |
|
| Back to top |
|
|
|
|
