| View previous topic :: View next topic |
| Author |
Message |
mf_user
Intermediate
Joined: 01 Jun 2003
Posts: 372
Topics: 105
|
 Posted: Thu Apr 19, 2012 6:16 am Post subject: Select max - 4 of year field |
 |
|
Hi, can you help me to write a SQL to pick (MAX YEAR - 4) from a DB2 table that has YEAR field.
I tried below but ended up with SQLCODE -120
| Code: |
SELECT COUNT(*) FROM DB2P.YEAR_TBL
WHERE YEAR_NBR = MAX(YEAR_NBR) - 4
WITH UR;
|
The file has year values from 2008 to 2013 and YEAR_NBR is a character field.
Thanks.
_________________
MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
== |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Apr 19, 2012 8:42 am Post subject: |
|
|
mf_user,
the description for an SQL Code of -120
implies,
you need a subquery...
| Code: | SELECT COUNT(*)
FROM DB2P.YEAR_TBL
WHERE YEAR_NBR = (
SELECT MAX(YEAR_NBR) - 4
FROM DB2P.YEAR_TBL
)
WITH UR; |
the max(year_nbr) - 4
will need some work since it is a char field.
year_nbr in max(year_nbr) needs to be cast as integer
then the max - 4 needs to be cast as char
so that the comparision can be made with year_nbr on the left side of the where clause.
provide us with the length attribute for year_nbr
and we can provide suggested code.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
mf_user
Intermediate
Joined: 01 Jun 2003
Posts: 372
Topics: 105
|
| Posted: Thu Apr 19, 2012 8:47 am Post subject: attributes |
|
|
Hi Dick, thanks a lot........
Here are the details..........
| Code: |
year_nbr Character (4)
|
Thanks.
_________________
MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
== |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 19, 2012 3:14 pm Post subject: |
|
|
use the following sql(untested)
| Code: |
SELECT COUNT(*)
FROM DB2P.YEAR_TBL
WHERE INT(YEAR_NBR) = (SELECT MAX(INT(YEAR_NBR)) - 4
FROM DB2P.YEAR_TBL)
; |
|
|
| Back to top |
|
|
mf_user
Intermediate
Joined: 01 Jun 2003
Posts: 372
Topics: 105
|
| Posted: Mon Apr 23, 2012 3:16 am Post subject: |
|
|
Kolusu, thanks.......it has worked and got the expected results !
_________________
MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
== |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
