|
View previous topic :: View next topic
|
| Author |
Message |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| For someone who doesn't know how to define an integer host-variable in PL/1 you are very persistent in trying something that will NEVER work. |
|
| Back to top |
|
 |
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
| Can you please tel me how do i resolve the issue ? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
thnx for your help.
You mean TBL_DUR should be declared as FIXED BIN equivalent to INTEGER ? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| GuyC wrote: |
| You can not have a hv that contains '5 days' and expect it to work in static SQL. |
so forget about TBL_DUR
try the suggestion I gave you :
| GuyC wrote: |
| Code: |
...B.Date BETWEEN DATE(:old_DATE) - :Daydur days - :yeardur years
AND DATE(:old_DATE) - 1 DAY; |
|
and define daydur and yeardur as integers
----------------------------------------------------------------------------------
OR:
Oops, I followed my own advice and went back to the manual, following the link Akatsukami posted.
Apparently there is such a thing as duration decimal(8,0) which you can subtract or add to a date.
so :
| Code: |
DCL DURATION DEC FIXED(8,0);
..
DURATION = 00010000; <== 1 year
or
DURATION = 00000005; <== 5 days
...
B.Date BETWEEN DATE(:old_DATE) - :DURATION
AND DATE(:old_DATE) - 1 DAY;
.. |
|
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
As a SQLCODE of -180 indicates -- according to the manual -- that a date, time, or timestamp value is of incorrect format, don't you think that it would be a good idea to exhibit your code -- principally but not necessarily limited to your host variable declarations and embedded SQL -- and to add as many PUT EDIT statements immediately prior to execution as are needed to display those variable's values, and to also show that output?
I'm sorry if it is viewed as politically incorrect, but reading and understanding a few manual pages on the part of the querents would probably reduce the traffic on this site to a few percent of its present value. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Quote: |
I'm sorry if it is viewed as politically incorrect, but reading and understanding a few manual pages on the part of the querents would probably reduce the traffic on this site to a few percent of its present value.
|
Yes, but that would reduce the laughs that I have had - particularly the thread about trying to compile a mix of COBOL and ForTran!!!!!!!! |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Akatsukami wrote: |
I'm sorry if it is viewed as politically incorrect, but reading and understanding a few manual pages on the part of the querents would probably reduce the traffic on this site to a few percent of its present value. |
And what was left would be *much* more interesting.
I sometimes wonder if some of the topic starters on this board are being willfully obtuse in the hope that someone will get impatient and just give them the code. |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
Hi all, thnx for your inputs.
i found why it is giving -180
| Code: |
DCL DATE1 CHAR(12);
DATE1 = "'" || INPUT.DATE || "'";
DATE(DATE1) - :dur1 year - :dur2 day --> since DATE1 is of 12 chars. |
But when i run
| Code: |
| DATE(INPUT.DATE) - :dur1 year - :dur2 day |
am not getting any error but not the desired output.
| Quote: |
DUMMY RUN:
| Code: |
select date(2011-02-13) - 1 year, date('2011-02-13') - 1 year
from sysibm.sysdummy1 |
it gives
| Code: |
| 0005-06-19, 2010-02-13 |
|
I need the latter so i used 12 CHARS but getting -180 *N. |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
| plz help how to resolve.. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| wow, this is the first time i see someone counting the quotes as part of the length. |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
| if it is not counted, DATE1 field - '2011-02-13' is getting truncated - '2011-02-1 |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
this is the difference why Quotes are needed.
DUMMY RUN:
| Code: |
select [b]date(2011-02-13) - 1 year[/b], [u]date('2011-02-13') - 1 year[/u]
from sysibm.sysdummy1 |
it gives
| Code: |
| [b]0005-06-19[/b], [u]2010-02-13[/u] |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I'm glad you take the time to explain me why the quotes are needed and why it should be char(12) . But unfortenately you have no idea what you are talking about.
a literal '2011-02-13' is equivalent to a char variable containing 2011-02-13
a literal 2011-02-13 is actually not a literal but a computation 2011 - 02 - 13 which is equivalent to 1996
| don.leahy wrote: |
| I sometimes wonder if some of the topic starters on this board are being willfully obtuse in the hope that someone will get impatient and just give them the code. |
| Code: |
DCL OLD_DATE CHAR(10);
DCL OUTPUT_DATE CHAR(10);
DCL WORK_DAYS FIXED BIN(15);
OLD_DATE = '2011-02-13';
WORK_DAYS = 5;
EXEC SQL
SELECT DATE(:OLD_DATE) - :WORK_DAYS DAYS
INTO :OUTPUT_DATE FROM SYSIBM.SYSDUMMY1; |
|
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
you mean to say the above code is same as below. i dont think so.
considering INP_DATE from input file or SYSIN = 2011-02-13
| Code: |
DCL INP_DATE CHAR(10);
DCL OLD_DATE CHAR(10);
DCL OUTPUT_DATE CHAR(10);
DCL WORK_DAYS FIXED BIN(15);
OLD_DATE = INP_DATE;
WORK_DAYS = 5;
EXEC SQL
SELECT DATE(:OLD_DATE) - :WORK_DAYS DAYS
INTO :OUTPUT_DATE FROM SYSIBM.SYSDUMMY1; |
DUMMY RUN:
| Code: |
select [b]date(2011-02-13) - 1 year[/b], [u]date('2011-02-13') - 1 year[/u]
from sysibm.sysdummy1
it gives
[b]0005-06-19[/b], [u]2010-02-13[/u]
|
|
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
2011 minus 2 minus 13 is 1996.
Day 1996, counting from 01/01/01:
Year 1901 has 365 days
Year 1902 has 365 days
Year 1903 has 365 days
Year 1904 has 366 days
Year 1905 has 365 days
This totals 1826 days. 1996 minus 1826 is 170.
June 19 is Julian day 170. So 2011-02-13 in DB2 converts to 0005/06/19.
You need to stop that -- you don't do it very well. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
Hi..
| Code: |
select [b]date(2011-02-13) - 1 year[/b], [u]date('2011-02-13') - 1 year[/u]
from sysibm.sysdummy1
it gives
[b]0005-06-19[/b], [u]2010-02-13[/u]
|
I understand the arithmetic involved in date(2011-02-13) - 1 year. thnx for that.
but my requirement is date('2011-02-13') - 1 year and date being passed from JCL. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Can you repeat the question ? I still don't like the answers already given , I think they are wrong so I don't even want to try them. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
I believe that the duration needs to be declared as FIXED DEC (15,0), not as FIXED BIN (15) (the DB2 SQL reference appears to support this).
With this change, I have taken GuyC's code, successfully pre-compiled and compiled it, successfully link-edited it, successfully bound the package and the plan, and have written and successfully run JCL that executes that program via background TSO, taking the date as a parameter. I repeat "successfully" to emphasize that I had no problem in doing these things. |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
Hi..
select date(2011-02-13) - 1 year, date('2011-02-13') - 1 year
from sysibm.sysdummy1
it gives
0005-06-19, 2010-02-13
I understand the arithmetic involved in date(2011-02-13) - 1 year. thnx for that.
but my requirement is date('2011-02-13') - 1 year and date being passed from JCL.
Bolded is the problem now which gives the abend as the value is passed from the input file
Input file: 2011-02-13
I want to have the value in query as date('2011-02-13') |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Have you tried having '2011-02-13' in your input file instead of just 2011-02-13?? |
|
| Back to top |
|
|
venuhunev
New User
Joined: 26 May 2007
Posts: 70
Location: chennai
|
|
|
|
| Nope. cant change the input field as per the requirements. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
You don't know PL/I, do you?
You have been given the exact solution. You cannot understand it. You do not know how to test it. You do not know how to implement it. You can only repeat nonsense that reveals your ignorance, and have wasted three days doing so. |
|
| Back to top |
|
|
|
|
