| View previous topic :: View next topic |
| Author |
Message |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Mon Jun 19, 2006 6:17 pm Post subject: DSNTIAUL - How do you code a varible? |
 |
|
I have the following query in a batch job. How can a make the dates in this statement variables?
BETWEEN '4/1/2006' AND '4/30/2006'
| Code: |
//STEP0160 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2P)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIB81) -
PARMS('SQL') -
LIB('DB2P.DSN.RUNLIB.LOAD')
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
INSERT INTO HOURS.PART_DEPS_WORK
SELECT HOURS.PARTDEPS.SSNO,
HOURS.FTBELIG2.MEMTYPE,
HOURS.MEMPLAN.PLANNAME,
HOURS.MEMPLAN.MEMDESC,
SUM(HOURS.PARTDEPS.CHECK_AMT)
FROM (HOURS.PARTDEPS INNER JOIN HOURS.FTBELIG2
ON HOURS.PARTDEPS.SSNO =
HOURS.FTBELIG2.SSNO)
INNER JOIN HOURS.MEMPLAN
ON HOURS.FTBELIG2.MEMTYPE =
HOURS.MEMPLAN.MEMTYPE
WHERE (((HOURS.PARTDEPS.MEMSTAT)='ACTIVE')
AND ((HOURS.PARTDEPS.RECVD_DATE)
BETWEEN '4/1/2006' AND '4/30/2006')
AND ((HOURS.MEMPLAN.EFF_TO_DATE)='12/31/9999'))
GROUP BY HOURS.PARTDEPS.SSNO,
HOURS.FTBELIG2.MEMTYPE,
HOURS.MEMPLAN.PLANNAME,
HOURS.MEMPLAN.MEMDESC
; |
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Jun 19, 2006 8:13 pm Post subject: |
|
|
NASCAR9,
What exactly do you mean by variable? Do you want to change it everytime you run your job? If so you can split the sql into 3 parts
Everything before the BETWEEN statement as part1 and BETWEEN stmt as part2 and everything after the BETWEEN stmt as part3.
So your sysin would be as follows
| Code: |
//SYSIN DD DSN=80 BYTE FILE WITH SQL BEFORE BETWEEN STMT1,
// DISP=SHR
// DD DSN=YOUR DYNAMIC BETWEEN STMT,
// DISP=SHR
// DD DSN=80 BYTE FILE WITH SQL AFTER BETWEEN STMT2,
// DISP=SHR
|
Another option is to generate the DATES in the between statement dynamically. So if you let me the conditions for the dates then may be I can suggest a way to get the dates.
Hope this helps....
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 20, 2006 9:38 am Post subject: |
|
|
Kolusu, Thanks for your reply. My requirement is to pull all deposits in a given calendar month, along with some plan information. The job will run sometime the following month. The Between range needs to include the From and TO date from the previous month.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 20, 2006 9:56 am Post subject: |
|
|
| Quote: |
The Between range needs to include the From and TO date from the previous month.
|
That is quite simple. The following sql will give you the first and last day of the previous month.
| Code: |
SELECT DATE(LAST_DAY(CURRENT DATE - 2 MONTHS) + 1 DAY)
,DATE(LAST_DAY(CURRENT DATE - 1 MONTHS))
FROM SYSIBM.SYSDUMMY1
;
|
So now you change your insert statement to the following
| Code: |
INSERT INTO HOURS.PART_DEPS_WORK
SELECT HOURS.PARTDEPS.SSNO,
HOURS.FTBELIG2.MEMTYPE,
HOURS.MEMPLAN.PLANNAME,
HOURS.MEMPLAN.MEMDESC,
SUM(HOURS.PARTDEPS.CHECK_AMT)
FROM (HOURS.PARTDEPS INNER JOIN HOURS.FTBELIG2
ON HOURS.PARTDEPS.SSNO =
HOURS.FTBELIG2.SSNO)
INNER JOIN HOURS.MEMPLAN
ON HOURS.FTBELIG2.MEMTYPE =
HOURS.MEMPLAN.MEMTYPE
WHERE (((HOURS.PARTDEPS.MEMSTAT)='ACTIVE')
AND ((HOURS.PARTDEPS.RECVD_DATE)
BETWEEN DATE(LAST_DAY(CURRENT DATE - 2 MONTHS) + 1 DAY)
AND DATE(LAST_DAY(CURRENT DATE - 1 MONTHS))
AND ((HOURS.MEMPLAN.EFF_TO_DATE)='12/31/9999'))
GROUP BY HOURS.PARTDEPS.SSNO,
HOURS.FTBELIG2.MEMTYPE,
HOURS.MEMPLAN.PLANNAME,
HOURS.MEMPLAN.MEMDESC
;
|
The last_day scalar function is explained in detail here
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.40?SHELF=&DT=20010718164132&CASE=
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 20, 2006 10:00 am Post subject: |
|
|
Kolusu Thanks! I was just looking at this function and was not sure how to code it in my query. This would have been for the last day of the month. My query for the first day was much more complicated than it need to be. Your solution is awesome!
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
