| View previous topic :: View next topic |
| Author |
Message |
Mint
Beginner
Joined: 19 Aug 2005
Posts: 3
Topics: 3
|
 Posted: Mon Sep 19, 2005 4:49 am Post subject: Passing data into a query |
 |
|
Hi
I have a jcl which has many control cards. Each control card has an sql query, in these queries i check for a condition where the date is equal to some date.This data value is hard coded now in the all the controlcards. The same condition is repeated in all the queries which are used. I want to know some way wherein i can automise the process of giving date value instead of hardcoding. The current format of that date field is 'yyyymm'. I need to pass this to all the queries.
If i do it using sysin , where in the user is entering the year and month there before running the job, that yyyymm should be passed to all the queries in the control cards. How do I pass this value into the condition of a query. I'am not using cobol, this is a BTEQ JCL.
Can anyone help me in doing this.
Thanks in Advance
Regards
Mint |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Sep 19, 2005 8:13 am Post subject: |
|
|
| Quote: |
I want to know some way wherein i can automise the process of giving date value instead of hardcoding. The current format of that date field is 'yyyymm'. I need to pass this to all the queries.
|
Mint,
What is the criteria for the date field? Is it like the current month ? if you want to automate the process you need to define the criteria to populate the date field.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
dhulipalla
Beginner
Joined: 24 Aug 2004
Posts: 21
Topics: 6
|
| Posted: Thu Nov 17, 2005 9:50 pm Post subject: |
|
|
Kolusu,
I do have some similar requirement where I need to pass the last month(mmyyyy) to the query..Is there a way to accomplish the task? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Nov 18, 2005 5:46 am Post subject: |
|
|
| Quote: |
I do have some similar requirement where I need to pass the last month(mmyyyy) to the query..Is there a way to accomplish the task?
|
dhulipalla,
can you post your query ?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
dhulipalla
Beginner
Joined: 24 Aug 2004
Posts: 21
Topics: 6
|
| Posted: Fri Nov 18, 2005 10:03 am Post subject: |
|
|
Kolusu,
I got the required query...here is the one..
| Code: |
SELECT FIELDS
FROM TABLENAME
WHERE A_DATE_YYYY||A_DATE_MM
= ADD_MONTHS( CAST(
CAST(EXTRACT (YEAR FROM DATE) AS FORMAT '9999')||
CAST(EXTRACT (MONTH FROM DATE) AS FORMAT '99')
AS DATE FORMAT 'YYYYMM'), - 1)
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Nov 18, 2005 10:21 am Post subject: |
|
|
Dhulipala,
If all you wanted is to get last month date in YYYYMM format then it is quite simple.
| Code: |
SELECT FIELDS
FROM TABLENAME
WHERE A_DATE_YYYY||A_DATE_MM = (SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,4) ||
SUBSTR(CHAR(CURRENT DATE - 1 MONTH),6,2))
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
