|
View previous topic :: View next topic
|
| Author |
Message |
Minimochi
New User
Joined: 25 Nov 2023
Posts: 3
Location: India
|
|
|
|
Hi All,
I have a jcl which unloads table data to a file and the unload card has simple query as - SELECT * from <table_name>
This table has a column name as DATE which is in format yyyy-mm-dd ex.2020-05-31
The ask from my boss was - Can we get data from 1st day of the month to the last day of the month. I gave them below where condition -
WHERE DATE BETWEEN (CURRENT_DATE - (DAY(CURRENT_DATE)-1)DAYS)
AND LAST_DAY(CURRENT_DATE)
This worked good. But this unload job is month end job and sometimes tend to run late which means the current date being 1st of month which will mess up my above WHERE condition
So, my simplest idea was to do replace CURRENT_DATE with (CURRENT_DATE -2 )
But my boss is asking if we can create new step and filter out the DATE field from the output file and create a new file with only date having a month's worth data.
I dont know how i can achieve filtering the date using sort. can you guys help  |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
Please, start doing something by yourself. Then you can ask, what and why goes wrong?
Where is any sample of the code you have tried so far?
So far is not clear: what is your final goal? Where is your DB2, where is your SORT, where is your JCL, and where is your VSAM?
P.S.
This in not "JCL & VSAM"
This is "DB2" and "DFSORT/SYNCSORT" |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
RTFM:
| Quote: |
FIRST_DAY Returns a date or timestamp that represents the first day of the month of the argument.
LAST_DAY Returns a datetime value that represents the last day of the month of the argument. |
|
|
| Back to top |
|
|
Minimochi
New User
Joined: 25 Nov 2023
Posts: 3
Location: India
|
|
|
|
Please, start doing something by yourself-> I got the DB2 solution with my where clause condition which I have given in my og post and since you asked to read the f*king manual. I have already used the last day function in my WHERE clause which you shared
My question is simply to understand - is there any DB2 BETWEEN and Last_date equivalent in jcl sort card
Also, THIS IS A JCL QUESTION not "DB2". I explained the DB2 part because I knew there would be someone who would ask if you tried anything?
Also, I did tried to search JCL sort and I found DATE1,DATE4 in jcl sort to deal with date conditions but I couldn't found anything where I can get first and last date of month in jcl sort utility
Also, please do not use condescending tone. You can simply ignore my post if you want to rather than being rude |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
| Minimochi wrote: |
My question is simply to understand - is there any DB2 BETWEEN and Last_date equivalent in jcl sort card |
Yes, there is such equivalent.
It's described in details in manuals, and in Google search from Internet, as well as in search from this forum.
P.S.
There is no such thing as, JCL SORT CARD!!!!!
You can hardly find your solution unless you understand the difference between JCL language, and SORT utility control statements. |
|
| Back to top |
|
|
Minimochi
New User
Joined: 25 Nov 2023
Posts: 3
Location: India
|
|
|
|
You can hardly find your solution unless you understand the difference between JCL language, and SORT utility control statements. -> I already have a DB2 solution I was looking if we have anything similar in jcl(to learn and increase my knowledge)
English is not my first language so I wrote jcl sort card. Also I don't need validation from a boomer whose only answer to every problem is "google it" or "RTFM" why not copy past the manual link on the home page and why even have a forum to discuss!
I regret asking question should've asked in stack overflow. Atleast would'nt have encountered a karen
Sayonara! |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
| Minimochi wrote: |
| You can hardly find your solution unless you understand the difference between JCL language, and SORT utility control statements. -> I already have a DB2 solution I was looking if we have anything similar in jcl(to learn and increase my knowledge) |
I repeat already 4 or 5 times:
There is no any solution of this kind in JCL language!!!!!!! Neither a similar one, nor a different one.
You can use SORT utility, which has absolutely nothing to do with JCL language.
| Quote: |
OUTREC
Function Field Subparameters
ADDDAYS
ADDMONS
ADDYEARS
SUBDAYS
SUBMONS
SUBYEARS
NEXTDday
PREVDday
LASTDAYW
LASTDAYM
LASTDAYQ
LASTDAYY |
Or, maybe, you unload data from DB2 by means of JCL language, too?   |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
Since we are talking in different languages, and there is absolutely no chance to understand each other, here is a sort of New Year gift.
| Code: |
//FILTER EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//*
//SYMNAMES DD *
INPUT_DATE,10,10,CH
BEFORE_YESTERDAY,21,8,CH
FIRST_OF_MONTH,30,10,CH
FIRST_DAY,38,2,CH
LAST_OF_MONTH,41,10,CH
//*
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7
//SORTIN DD * XXXXXXXX YYYY-YY-YY ZZZZ-ZZ-ZZ
2023-11-01
2023-11-05
2023-11-10
2023-11-15
2023-11-20
2023-11-25
2023-11-30
2023-12-01
2023-12-05
2023-12-10
2023-12-15
2023-12-20
2023-12-25
2023-12-30
2024-01-01
2024-01-05
2024-01-10
2024-01-15
2024-01-20
2024-01-25
2024-01-30
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7
//SELECTED DD SYSOUT=*
//REJECTED DD SYSOUT=*
//*
//SYSIN DD *
INREC OVERLAY=(BEFORE_YESTERDAY:&DATE1-2,
LAST_OF_MONTH:BEFORE_YESTERDAY,
Y4T,LASTDAYM,TOGREG=Y4T(-),
FIRST_OF_MONTH:LAST_OF_MONTH,
FIRST_DAY:C'01')
SORT FIELDS=COPY
OUTFIL FNAMES=(SELECTED),
REMOVECC,
INCLUDE=(INPUT_DATE,GE,FIRST_OF_MONTH,
AND,INPUT_DATE,LE,LAST_OF_MONTH),
HEADER1=('*** LIST OF ACCEPTED DATES BETWEEN ',
FIRST_OF_MONTH,' AND ',LAST_OF_MONTH,
' ***',/),
BUILD=(INPUT_DATE,80:X),
TRAILER1=(/,'*** TOTAL ACCEPTED DATES = ',
COUNT=(M0),' ***')
OUTFIL FNAMES=(REJECTED),
REMOVECC,
SAVE,
HEADER1=('*** LIST OF REJECTED DATES OUT OF ',
FIRST_OF_MONTH,' AND ',LAST_OF_MONTH,
' ***',/),
BUILD=(INPUT_DATE,80:X),
TRAILER1=(/,'*** TOTAL REJECTED DATES = ',
COUNT=(M0),' ***')
END
//*
|
| Code: |
*** LIST OF ACCEPTED DATES BETWEEN 2023-12-01 AND 2023-12-31 ***
2023-12-01
2023-12-05
2023-12-10
2023-12-15
2023-12-20
2023-12-25
2023-12-30
*** TOTAL ACCEPTED DATES = 7 *** |
| Code: |
*** LIST OF REJECTED DATES OUT OF 2023-12-01 AND 2023-12-31 ***
2023-11-01
2023-11-05
2023-11-10
2023-11-15
2023-11-20
2023-11-25
2023-11-30
2024-01-01
2024-01-05
2024-01-10
2024-01-15
2024-01-20
2024-01-25
2024-01-30
*** TOTAL REJECTED DATES = 14 *** |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| In such situations , you can never rely on current dates .. so always use a business date maintained by your system .. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2264
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| In such situations , you can never rely on current dates .. so always use a business date maintained by your system .. |
Yes, but (depending on business requirements) it can be also assumed that the job running from 1st to Nth of each month should generate the previous month report, while running after Nth of the month generates the current month report.
P.S.
None of the issues discussed in this thread is related to "JCL & VSAM", despite TS sticktoitiveness. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
