Thanks a lot for the needful help. Your code works perfect .Really great.
But i have a small question i just want to add some text at the below of the query something like this:
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'
);
**** THIS IS AN SQL QUERY ****
Please let me know what needs to be done , do i have to change the Trailer1. Thank You.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Mon Nov 12, 2007 5:09 pm Post subject:
Just change the TRAILER1 operand to:
Code:
TRAILER1=(' );',/,
'**** THIS IS AN SQL QUERY ****')
_________________ 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
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Tue Nov 13, 2007 1:01 pm Post subject:
Here is a Rexx solution
Code:
/*- Rexx ------------------------------------------------------------*/
/* */
/* Rexx exec to: */
/* 1 - read joindates - 1 record multiple dates */
/* 2 - read department codes - 1 record multiple codes */
/* 3 - generate SQL statement using the above values in the WHERE */
/* condition */
/* (Includes extra comments on one or two things for a newbie to */
/* Rexx in batch) */
/*------------------------------------------------------------ Rexx -*/
cobol_data. = ''
/* Read file of dates and departments. Running in batch so allocation*/
/* is handled via JCL statements (//FILEIN DD DSN=.... */
'EXECIO * DISKR FILEIN(STEM cobol_data. FINIS'
If rc <> 0
Then Do
/* In batch SAY puts its output to SYSTSPRT */
Say 'Unexpected rc from EXECIO: Expected: 0 Got: 'rc
Return 12
End
/*-------------------------------------------------------------------*/
/* Check that we have: */
/* 2 records */
/* data on the date record - first record */
/* data on the dept record - second record */
/* */
/* Note: number of records read has been placed in cobol_data.0 */
/*-------------------------------------------------------------------*/
If cobol_data.0 <> 2
Then Do
Say 'Expected 2 records to be read. Read 'cobol_data.0
Return 12
End
If cobol_data.1 = ' '
Then Do
Say 'No dates on date record'
Return 12
End
If cobol_data.2 = ' '
Then Do
Say 'No depts on dept record'
Return 12
End
/*----------------------------------------------*/
/* OK so far..... */
/* Now extract dates to stem variable 'dates.'. */
/*----------------------------------------------*/
dates. = '' /* We will store the dates in this stem variable */
ix = 0
Do While cobol_data.1 <> ' '
ix = ix + 1
/* PARSE will take the first date (delimited by comma) and assign */
/* to dates.ix */
/* The remaining data will be rewritten to cobol_data.1 */
/* and the process repeated until cobol_data.1 is empty */
Parse Var cobol_data.1 1 dates.ix ',' cobol_data.1
End
dates.0 = ix /* Assign number of dates read into dates.0 */
/*----------------------------------------------*/
/* Now extract depts to stem variable 'depts.'. */
/* Same logic as for dates. */
/*----------------------------------------------*/
depts. = ''
ix = 0
Do While cobol_data.2 <> ' '
ix = ix + 1
Parse Var cobol_data.2 1 depts.ix ',' cobol_data.2
End
depts.0 = ix
/*---------------------------------*/
/* Now construct the SQL statement */
/*---------------------------------*/
sql_stmt.1 = " SELECT EMPNO,EMPNAME,SALARY"
sql_stmt.2 = " FROM EMPLOYEE "
sql_stmt.3 = " WHERE JOINDATE IN ("dates.1
ix1 = 3
Do ix = 2 to dates.0 /* Do the remaining dates */
ix1 = ix1 + 1
sql_stmt.ix1 = " ,"dates.ix
End
sql_stmt.ix1 = sql_stmt.ix1")"
ix1 = ix1 + 1
sql_stmt.ix1 = " AND DEPT IN ("depts.1
Do ix = 2 to depts.0 /* Do the remaining depts */
ix1 = ix1 + 1
sql_stmt.ix1 = " ,"depts.ix
End
sql_stmt.ix1 = sql_stmt.ix1");"
sql_stmt.0 = ix1
/*--------------------------------------------------------------*/
/* All done - now display the construction and write to FILEOUT */
/*--------------------------------------------------------------*/
Do i = 1 To sql_stmt.0
Say sql_stmt.i
End
"EXECIO * DISKW FILEOUT(STEM sql_stmt. FINIS)"
If rc <> 0
Then Do
Say 'Unexpected rc from EXECIO: Expected: 0 Got: 'rc
Return 12
ACF0C038 ACF2 LOGONID ATTRIBUTES HAV
READY
EXEC 'xyz.myuid.EXEC(FORUM)'
SELECT EMPNO,EMPNAME,SALARY
FROM EMPLOYEE
WHERE JOINDATE IN ('2007-01-31'
,'2007-02-28'
,'2007-03-31')
AND DEPT IN ('ABCD'
,'XYZ1'
,'34AD'
,'C1B2');
READY
END
******************************** BOT
_________________ Utility and Program control cards are NOT, repeat NOT, JCL.
All times are GMT - 5 Hours Goto page Previous1, 2
Page 2 of 2
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
