|
View previous topic :: View next topic
|
| Author |
Message |
satyender
New User
Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA
|
|
|
|
Hi all,
Could anyone please help in this regard.
Thanks in Advance
I would like to know how to get the data of 5 yrs from a particular date.
I am using the followin query, but getting an error.
SELECT *
FROM
LEXUS.TLCS005
WHERE RID IN
(SELECT RID
FROM LEXUS.TLCS007
WHERE RDNDTE IN
(SELECT RDNDTE
FROM LEXUS.TLCS003
WHERE DATE(RDNDTE) >= DATE('2008/07/23') - 5 YEARS))
WITH UR;
ERROR:
| Code: |
RID OPSEQ OPCODE OPCOMB OPHOURS OPTECHID OPDESC
---------+---------+---------+---------+---------+---------+---------+------
DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE *N IS
INVALID
DSNT418I SQLSTATE = 22007 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRTIM SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -6805 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFE56B' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---- |
|
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
what is the data-type of RDNDTE?
does your site use backslash / , or hypen - as separator?
is yours a year/month/day system?
answer those three questions, and you will solve the problem, or at least know if it is RDNDTE or your literal. |
|
| Back to top |
|
|
satyender
New User
Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA
|
|
|
|
Thanks dbzTHEdinosauer
I have resoved by converting into days. Now its working fine.
Thanks for your quick response.
| Code: |
SELECT *
FROM
LEXUS.TLCS005
WHERE RID IN
(SELECT RID
FROM LEXUS.TLCS007
WHERE RDNDTE IN
(SELECT RDNDTE
FROM LEXUS.TLCS003
WHERE DATE(RDNDTE) >= CURRENT DATE - 1868 DAYS
AND DATE(RDNDTE) <= CURRENT DATE - 42 DAYS))
WITH UR; |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
now that you have converted the literal to the db2 current_date.
you probably could go back to the 5 years.
is any five year span always = 1868 days?
due to some recent experience I had, I would also suggest changing the left hand predicate
From: DATE(RDNDTE) TO: RDNDTE
and
CAST the date returned from
<CURRENT_DATE minus whatever>
to what ever data type attribute the column RDNDTE has- is.
you would then reduce the resource requirement by
not executing a function on every row
DATE(RDNDTE)
, and instead, only once:
to derive the right-hand predicates. |
|
| Back to top |
|
|
|
|
