Posted: Thu May 03, 2007 7:59 am Post subject: form an sql query using SORT
I have a requirement to form a SQL query in the way below which i will use in a JCL :
Code:
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE DATE OF JOINING = JOIN DATE (from list)
AND DEPT = DEPT NAME (from list)
Explanation somewhat like this:
Code:
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE DATE OF JOINING IN ('2007-01-01','2007-02-01','2007-03-01' AND COULD BE OTHERS)
AND DEPT NAME IN ('ABC','DEF','GHI','XXX','YYY' AND COULD BE OTHERS)
But i have job flow as below:
Code:
//STEP1 EXEC COBOL PROGRAM WHICH WILL CREATE AN OUTPUT FILE 80 BYTES ASSUMPTION
2007-01-012007-02-012007-03-01....... BASED ON AN INPUT FILE READ FROM OTHER SOURCE
ABCDEFGHIXXXYYY...................... BASED ON AN INPUT FILE READ FROM OTHER SOURCE
//STEP2 EXEC PGM=IKJEFT01
.
.
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE DATE OF JOINING = JOIN DATE (from list)
AND DEPT = DEPT NAME (from list)
STEP2 should make use of that output file and form the SQL Query .How can i do that , can it be possible through SORT / ICETOOL or any other ways ? Please help me in this regard.
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Thu May 03, 2007 8:26 am Post subject:
yadav2005,
yes it can be done, but a few questions.
1.Does your cobol program write out just 2 records or can it have more than that? Remember that a single sql statement can be a max of 32760 bytes. So if your list is huge then your sql will fail even though sort was able to generate the sql.
2. The first line from your cobol pgm is for dates and 2nd line is for dept?
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Thu May 03, 2007 8:28 am Post subject:
Quote:
I have a requirement to form a SQL query in the way below which i will use in a JCL
JCL cannot execute SQL queries - it tells the m/f what resources the program needs. Please get your terminology right
Once SORT, or whatever, has created the queries how do you propose to execute them? Generally queries are assembled and executed within programs (as opposed to utilities). _________________ Utility and Program control cards are NOT, repeat NOT, JCL.
1.Does your cobol program write out just 2 records or can it have more than that? Remember that a single sql statement can be a max of 32760 bytes. So if your list is huge then your sql will fail even though sort was able to generate the sql.
Ans: It will write only 2 records with 1 st record with all dates one after the other and 2 nd record will have deptartment names one after the other.Because the program will create the file and it wont's be that long enough ,how can we handle the maximum bytes concept of 32760.Can this part be taken care in the program / SORT ?
2. The first line from your cobol pgm is for dates and 2nd line is for dept?
Ans: The first line is for Dates and second line is for Dept.
How can the Cobol Program itself generate the SQL ? Can you help in both the solutions ?
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Thu May 03, 2007 9:28 am Post subject:
Nic Clouston wrote:
JCL cannot execute SQL queries.
You execute within a program, or SPUFI or QMF.
Within a program e.g.
Code:
EXEC SQL
sql statement
END SQL.
It is in the manual - one of the first things you learn.
Nic,
I guess the whole point of generating the sql is to run in batch thru utilities like DNSTIAUL or DSNTEP2. Sad but true many of the newbie programmers think any utility which can invoked in a JCL is considered "A super JCL package which can literally do anything". I tried to educate but gave up
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Thu May 03, 2007 10:36 am Post subject:
Kolusu,
I won't give up! Sloppiness or misunderstanding at this level could mean getting something seriously wrong later or reduce their job prospects. I would not hire someone who did not SEEM to understand what JCL is when it is an everyday part of their job. _________________ Utility and Program control cards are NOT, repeat NOT, JCL.
Is there any way to stop the code after the last Date of joining as well as last dept so that in the IN List we do not get the empty date of joining and dept and i am looking for the query as below:
Code:
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE JOINDATE IN ('2007-01-01'
,'2007-02-01'
,'2007-03-01')
AND DEPT IN ('ABC'
,'DEF'
,'GHI'
,'XXX'
,'YYY');
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
2007-01-012007-02-012007-03-01
ABCDEFGHIXXXYYY......................
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTREC FIELDS=(01,80,SEQNUM,1,ZD)
OUTFIL ENDREC=2,IFOUTLEN=80,
IFTHEN=(WHEN=(81,1,ZD,EQ,1),
BUILD=(C'SELECT EMPNO,EMPNAME,SALARY',/,
C' FROM EMPLOYEE',/,
C' WHERE JOINDATE IN (',
C'''',01,10,C'''',/,
19X,C',',C'''',11,10,C'''',/,
19X,C',',C'''',21,10,C'''',C')',80:X)),
IFTHEN=(WHEN=(81,1,ZD,EQ,2),
BUILD=(C' AND DEPT IN (',
C'''',01,03,C'''',/,
15X,C',',C'''',04,03,C'''',/,
15X,C',',C'''',07,03,C'''',/,
15X,C',',C'''',10,03,C'''',/,
15X,C',',C'''',13,03,C'''',C');',80:X))
/*
SORTOUT:
Code:
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE JOINDATE IN ('2007-01-01'
,'2007-02-01'
,'2007-03-01')
AND DEPT IN ('ABC'
,'DEF'
,'GHI'
,'XXX'
,'YYY');
Thanks for your reply.Your solution will work if u know the fixed number of Date of joining and Code and in this case u have three date of joinings and 5 codes and you have coded as per it.In my case i do not know how many Date of joinings i will have in that 1 st record but maximum lrecl = 80 bytes and how many codes i will have in the second record is also unkown.If you see kolusu post , i do not want to form the query with spaces.My requirement is to form a query somehow and i guess we should have some indication of end of the line.
Can this solution be done by executing any REXX Exec , i do not have any knowledge on REXX , can somebody please help me out in this requirement. I guess with some minor modifications to Kolusu's SORT code , the query can be generated.
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE JOINDATE IN (
'2007-01-01'
,'2007-02-01'
,'2007-03-01'
)
AND DEPT IN (
'ABC'
,'DEF'
,'GHI'
,'XXX'
,'YYY'
);
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
