| View previous topic :: View next topic |
| Author |
Message |
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
 Posted: Mon Nov 05, 2007 9:08 am Post subject: form a sql query using SORT / ICETOOL |
 |
|
Members,
I want to read from a file Julian date and Processing codes record by record and store in an internal array:
| Code: |
WS-CODE-ARRAY
WS-CODE-CNT PIC S9(04) COMP VALUE 0.
WS-CODE-MAX PIC S9(04) COMP VALUE 10.
WS-CODE-ELEMENT OCCURS 10 TIMES INDEXED BY WS-CODE-IDX.
WS-DATE PIC X(05).
WS-CODE PIC X(03).
|
For example if the input file is :
| Code: |
07101ABC
07101DEF
07101XYZ
07102ABC
07102ASM
|
I do not want to SORT the Input File because it is already sorted on some other field.
Also i am interested in building a query through SORT / ICETOOL which looks like after the program execution is completed and in another step:
| Code: |
SELECT * FROM TABLENAME
WHERE DATES IN ('LIST OF ALL JULIAN DATES THE PROGRAM HAS PROCESSED')
AND PROCESSING CODES IN ('LIST OF ALL PROCESSING CODES THE PROGRAM HAS PROCESSED');
|
Please help me how it can be done. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 05, 2007 10:46 am Post subject: |
|
|
yadav2005,
The query can be generated but there is a catch. The max length of a sql that can be processed is 32760. So if your input file has more than 410 rows then the sql will fail as (410 * 80 = 32800) as each code will be populated in 1 line of 80 bytes.
Looking at the data there is a lot of redundant data. so I would consider getting unique values of the julian date and codes.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Mon Nov 05, 2007 10:56 am Post subject: Re: form a sql query using SORT / ICETOOL |
|
|
| yadav2005 wrote: | Also i am interested in building a query through SORT / ICETOOL which looks like after the program execution is completed and in another step:
| Code: |
SELECT * FROM TABLENAME
WHERE DATES IN ('LIST OF ALL JULIAN DATES THE PROGRAM HAS PROCESSED')
AND PROCESSING CODES IN ('LIST OF ALL PROCESSING CODES THE PROGRAM HAS PROCESSED');
|
Please help me how it can be done. |
Why bother using sort to build a query when your program already has the information to build the query. Why not just extract the data in the program rather then another step. I don't thing you have the where correct in your proposed select I think you want the dates and the processing codes paired togeather. |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Mon Nov 05, 2007 12:55 pm Post subject: |
|
|
Basically we have the input records as:
| Code: |
111111 07111ABC
111112 07111ABC
111113 07112DEF
111114 07112DEF
111115 07112PQR
111116 07113PQR
|
We do not want the program to generate the query ,we are preferably looking for SORT / ICETOOLSo, I want the query which should be as below:
| Code: |
SELECT EMPNO,EMPNAME
FROM TABLENAME
WHERE DATES IN ('07111','07112','07113')
AND PROCESSING CODE IN ('ABC','DEF',PQR')
AND GENDER = 'M';
|
|
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Mon Nov 05, 2007 3:28 pm Post subject: |
|
|
| And then what will you do with the query? Run it in SPUFI? DSNTIAUL? I don't understand what you're doing. If you have everything you need in the COBOL program, and you have a requirement to generate the query, but not execute it, I don't understand why you wouldn't let the COBOL program generate the query. What's the advantage of generating the query in a separate step? |
|
| Back to top |
|
|
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
| Posted: Mon Nov 05, 2007 11:30 pm Post subject: |
|
|
yadav2005,
If u need the query to be generated in a separate file, then COBOL will do that for u. You can just generate the query in the program and write the query in a separate file rather than generate the query separately in a separate step.
But then... your requriement is still not clear. Please provide more details on what you are actually trying to accomplish so that we could help u out in this regard.
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay) |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Tue Nov 06, 2007 7:00 am Post subject: |
|
|
Yadav2005,
I am not clear as to what you are trying to do here. I understood that you want to run the SQL query by passing the values from your input file. If that is the case, then write a COBOL pgm to read your input file and pass the values to SQL and execute.
Why do you want to create SQL thru SORT? You can code your self the query instead of doing it thru utilities right. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
