Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
I need the output to be comma delimited as below
1
12
100
200
312
How is that comma delimited? There aren't any commas.
Quote:
Is this posible using SORT?
You need to do a better job of explaining the "rules" for getting from input to output.
Also, what is the RECFM and LRECL of the input file? What is the layout of the input record or records? What do you want for the output records (you say it's comma delimited but you don't show any commas), etc.
I'm sure it can do done with DFSORT, but I can't show you how until you tell me exactly what it is you want to do.
To clarify things again, here goes the answers to your questions.
Quote:
How is that comma delimited? There aren't any commas.
As you can see the input file is having commas between the dept-id's.
DEPT_I IN (1,12,100,200,312);
This is what I meant by comma delimited. I phrased as 'Output should be comma delimited' above which is wrong - My mistake.
To clarify, the output file should be having those departments - Each one in a new row. Exactly as how I showed it in my first post.
Quote:
Also, what is the RECFM and LRECL of the input file?
This was just a thought which came to my mind when I came across this scenario. I havent decided on the RECFM and LRECL but to be specific let it be FB and 80.
Quote:
What is the layout of the input record or records?
No predefined layout as such. This is part of a DB2 query and for the example I have pasted only 5 dept-id's. There can be 50 depts id's there and all seperated by commas.
Escapa, To answer your question, the departments may vary. its not a constant. Thanks!
There is no guarentee for the number of depts. On a given day there can be any number.
If I have no idea as to how many departments are there, is there any way where in we can do this using SORT? Can we bring in some logic into the SORT by which I could perform same logic until I encounter the end braces or the last comma or something like that?
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
No predefined layout as such. This is part of a DB2 query and for the example I have pasted only 5 dept-id's. There can be 50 depts id's there and all seperated by commas.
Your description of the input is too vague for a general solution. I don't know if the input will always be just one record, or can be spread across multiple records, e.g.
Code:
DEPT_I IN (1,12,100,200,312,
650,652,653,
850)
Obviously, if there are 50 dept-ids and each is 3 characters, that won't fit in one 80 byte line.
I don't know the maximum length of a dept-id.
Unless you can do a better job of describing the possible input formats, RECFM, LRECL, etc. I don't see how anyone can give you a general solution.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
This is part of a DB2 query and for the example I have pasted only 5 dept-id's. There can be 50 depts id's there and all seperated by commas.
Once "unstrung" how will these values be used?
How will changing the query be prevented (which would cause the "unstring" to fail)?
Might it be possible to store the values externally/individually and then dynamically use the values in the query (rather than trying to decompose the query)?
There can be 50 or 100 or 200 departments or even more.
The maximum length of DEPT is 4 (9999 is the max possible value).
Quote:
Unless you can do a better job of describing the possible input formats, RECFM, LRECL, etc
As explained in my posts before. I assume LRECL of 80 and RECFM of FB.
The input format would be like below,
Code:
DEPT_I IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,
24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,
45,46.4.48,49,50,51,52,53,53)
As you would have already known, I am a beginner when it comes to advanced concepts of DFSORT and for the same reason, I am not aware of the all the details that you might require for this solution.
I would be happy to help with more info, if required.
I am planning to prepare one driver file with all the departments listed each in a new row. The departments from this driver file will be used by many programs in my application.
The cursors defined in these programs will be retrieving data from the database based on these departments.
I can defenitely write a single cobol program to unstring and develop a file in my erquired format but I just want to understand as to how to do this using DFSORT.
Hope I have answered you.
Sorry but I dont understand the below question.
"How will changing the query be prevented (which would cause the "unstring" to fail)?"
The input format pasted above is wrong, the format got changed when I tried to edit it here.
Please see the correct one for below.
Code:
DEPT_I IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,
24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,
45,46.4.48,49,50,51,52,53,53)
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
"How will changing the query be prevented (which would cause the "unstring" to fail)?"
If a DFSORT solution is developed that depends on the "sameness"of the query, and the query is modified, it may fine for sql but will fail in the sort solution.
To repeat - why not store the values in this file you are trying to create file and then generate the IN from the external file? This way you do not need to decompose the query to get the list of depts - you use the list of depts to generate the query.
To repeat - why not store the values in this file you are trying to create file and then generate the IN from the external file? This way you do not need to decompose the query to get the list of depts - you use the list of depts to generate the query.
Yes. that is a good idea.. I can do that!
Regarding your first thought, whenever the query is modified it will be only pertaining to the departments and nothing else.
All the processing is based on the departments and hence I dont foresee any other changes to the query.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
All the processing is based on the departments and hence I dont foresee any other changes to the query.
It is the wide variety of ways that the sql could be written to specify the depts that could "break" the "unstring" process. This would get even more problematic if there were ever other value lists (for something other than dept) in the sql code.
.jpg)
