Example 1 - Create one spliced record for each match in two files z/OS DFSORT Application Programming Guide. The spliced COMBINE records are 31 bytes long and look like this: Z35 18693 DEV 10M $8,732,105 NY Y12 57301 MKT 50M $30,000 NJ Y12 89503 MKT 27M $9,185,354 CA. There are multiple ways to concatenate rows into string. Now we will see a couple of the easiest techniques here. Concatenate Multiple Rows Using FOR XML PATH. The simplest and straight forward way to concatenate rows into a string value is to use FOR XML PATH in a select query. This FOR XML PATH method can be used in SQL Server version 2005.
I have a daily report dataset, where details of several markets exist; one row per market. The record format looks like
Now, i need to pull all the above rows on to a single row of a monthly report file, record format could be:
How can we achieve it? Have an option for now to do it is via a job using DFSORT.Appreciate your interest.
Bill Woodger12.5k4 gold badges33 silver badges39 bronze badges
Raja ReddyRaja Reddy4728 gold badges18 silver badges37 bronze badges
1 Answer
ICEtool has an option Splice which could be used to merger multiple lines in to a single line .Please refer some icetool manual for exact syntax.
ramram
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Not the answer you're looking for? Browse other questions tagged mainframedfsort or ask your own question.
Answers were Sorted based on User's Feedback
Answer / pkb
It is possible to process more than one data set as a single data set by concatenating the DD statements.
//QSAM0080 DD DSN=SIMOTIME.DATA.FILE0001,DISP=SHR
// DD DSN=SIMOTIME.DATA.FILE0002,DISP=SHR
// DD DSN=SIMOTIME.DATA.FILE0003,DISP=SHR
The following is an example of a COBOL SELECT statement.
here the COBOL program will read QSAM0080 and the DD statements will map the three files (FILE0001, FILE0002 and FILE0003) as one file. The program's initial read will get the first record from FILE0001 and will not get an End-of-File condition until the last record of FILE0003 is processed.
//QSAM0080 DD DSN=SIMOTIME.DATA.FILE0001,DISP=SHR
// DD DSN=SIMOTIME.DATA.FILE0002,DISP=SHR
// DD DSN=SIMOTIME.DATA.FILE0003,DISP=SHR
The following is an example of a COBOL SELECT statement.
here the COBOL program will read QSAM0080 and the DD statements will map the three files (FILE0001, FILE0002 and FILE0003) as one file. The program's initial read will get the first record from FILE0001 and will not get an End-of-File condition until the last record of FILE0003 is processed.
![Concatenate multiple rows sql Concatenate multiple rows sql](/uploads/1/2/4/7/124706410/590024381.gif)
Answer / suputhru
concatenation is possible up to 16 pds or 255 ps
We can cancatinating diffent files by giving their DSN one after other, all the datasets must be same type.
rules: there should be logical record length must be same
and type recfm is also same
//jobcard
//step1 exec pgm=concat11
//ddname DD dsn=tst.punna.cobol,
// DD dsn=tst.punna.cobol1,
// DD dsn=tst.punna.cobol11,
// DD dsn=tst.punna.cobol211,
We can cancatinating diffent files by giving their DSN one after other, all the datasets must be same type.
rules: there should be logical record length must be same
and type recfm is also same
//jobcard
//step1 exec pgm=concat11
//ddname DD dsn=tst.punna.cobol,
// DD dsn=tst.punna.cobol1,
// DD dsn=tst.punna.cobol11,
// DD dsn=tst.punna.cobol211,
Answer / shankar
we can also concatenate through disp keyword parameter also..
use iebcopy utility and
sysut1 dd dsn=sample1.pds,disp=(old,pass)
sysut2 dd dsn=sample2.pds,disp=(shr)
sysin dd *
copy indd=sysut1 outdd=sysut2
/*
use iebcopy utility and
sysut1 dd dsn=sample1.pds,disp=(old,pass)
sysut2 dd dsn=sample2.pds,disp=(shr)
sysin dd *
copy indd=sysut1 outdd=sysut2
/*
Answer / chowdary
concatenation is possible up to 16 pds or 255 ps
rules: there should be logical record length must be same
and type recfm is also same
//jobcard
//joblib
//dd dsn=tst.punna.cobol,
dsn=tst.punna.cobol1,
like that we need to give up to our requirement
rules: there should be logical record length must be same
and type recfm is also same
//jobcard
//joblib
//dd dsn=tst.punna.cobol,
dsn=tst.punna.cobol1,
like that we need to give up to our requirement
Answer / krishnan
we have to use merge command