| View previous topic :: View next topic |
| Author |
Message |
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
 Posted: Wed Jun 30, 2010 2:31 pm Post subject: Modify SQL Query |
 |
|
Hi, I try below query but get SQLCODE -120 !!
| Code: |
SELECT ORDER_ID
FROM DB01.ORDER_DB
WHERE GROUP_CD = 'SCREWS'
AND GROUP_NBR = 'A01'
AND LAST_CHNG_TIME BETWEEN MAX(LAST_CHNG_TIME)
AND CURRENT TIMESTAMP
WITH UR;
|
Please help.
Thanks.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 30, 2010 2:39 pm Post subject: |
|
|
this post as well as all the others by me in this thread should be disregarded,
Kolusu and NASCAR9 have the proper solution.
consider the following just a rambling of someone who does not know what they are talking about.
dbz 1 jul, 0030 hours
| Code: |
LAST_CHNG_TIME BETWEEN MAX(LAST_CHNG_TIME)
AND CURRENT TIMESTAMP
|
just a guess, but where would the LAST_CHNG_TIME be > max(LAST_CHNG_TIME)?
_________________
Dick Brenholtz
American living in Varel, Germany
Last edited by dbzTHEdinosauer on Wed Jun 30, 2010 5:35 pm; edited 1 time in total |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jun 30, 2010 2:52 pm Post subject: |
|
|
I'm not 100% sure but, don't you need to Select the MAX LAST_CHNG_TIME for the compare?
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 30, 2010 2:55 pm Post subject: |
|
|
again, this post should also be ignored. dbz 1 jul, 0030 hrs.
| NASCAR9 wrote: | | I'm not 100% sure but, don't you need to Select the MAX LAST_CHNG_TIME for the compare? |
I wonder what sqlcode would be returned for that?
_________________
Dick Brenholtz
American living in Varel, Germany
Last edited by dbzTHEdinosauer on Wed Jun 30, 2010 5:38 pm; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 30, 2010 3:15 pm Post subject: |
|
|
ranga_subham,
Try this untested sql
| Code: |
SELECT ORDER_ID
FROM DB01.ORDER_DB
WHERE GROUP_CD = 'SCREWS'
AND GROUP_NBR = 'A01'
AND LAST_CHNG_TIME >= (SELECT MAX(LAST_CHNG_TIME)
FROM DB01.ORDER_DB
WHERE GROUP_CD = 'SCREWS'
AND GROUP_NBR = 'A01')
AND LAST_CHNG_TIME <= CURRENT TIMESTAMP
WITH UR;
|
Kolusu |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 30, 2010 3:50 pm Post subject: |
|
|
Again, ignore this post
========================
ok, my sarcasm aside.
this is all predicated on LAST_CHNG_TIME being a timestamp datatype:
how can the LAST_CHNG_TIME ever be > MAX(LAST_CHNG_TIME)?
I think db2 is just reacting to the futility of the BETWEEN statement.
the only LAST_CHNG_TIME that will be extracted is the one = MAX(LAST_CHNG_TIME).
by definition, the MAX(LAST_CHNG_TIME) is < or = CURRENT_TIMESTAMP
and LAST_CHNG_TIME can never be > MAX(LAST_CHNG_TIME).
If it turns out that LAST_CHNG_TIME is not a timestamp datatype,
then 'therein lies the rub' - but I would expect a different sqlcode.
I think that the solution would be
LAST_CHNG_TIME = MAX(LAST_CHNG_TIME)
instead of a BETWEEN clause that logically can never be satisfied.
It would also be interesting to know the version of DB2.
I realize that this is all predicated on the pre-compiler allowing this
construct and then the DB2 engine becoming lost.
_________________
Dick Brenholtz
American living in Varel, Germany
Last edited by dbzTHEdinosauer on Wed Jun 30, 2010 5:38 pm; edited 2 times in total |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jun 30, 2010 3:52 pm Post subject: |
|
|
| dbzTHEdinosauer wrote: | | NASCAR9 wrote: | | I'm not 100% sure but, don't you need to Select the MAX LAST_CHNG_TIME for the compare? |
I wonder what sqlcode would be returned for that? |
What exactly do you mean? Are you trying to be funny?
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 30, 2010 3:56 pm Post subject: |
|
|
| Quote: | | What exactly do you mean? Are you trying to be funny? |
see my post, before this one.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 30, 2010 3:57 pm Post subject: |
|
|
boy, I was prolific with my BS tonight.
It turns out that the max requires a select, I stand corrected.
_________________
Dick Brenholtz
American living in Varel, Germany
Last edited by dbzTHEdinosauer on Wed Jun 30, 2010 5:39 pm; edited 1 time in total |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jun 30, 2010 4:34 pm Post subject: |
|
|
V9 test
| Code: |
SELECT count(*) FROM FDBMD.RONL_SSN11 where ssno between 0 and max(ssno)
SQL0120N Invalid use of an aggregate function or OLAP function.
SQLSTATE=42903
SQL0120N Invalid use of an aggregate function or OLAP function.
Explanation:
An aggregate function or OLAP function can only be used in the select
list of a fullselect, the having clause, or, with restrictions, in a
WHERE clause or GROUP BY clause.
A WHERE clause can contain an aggregate function or OLAP function only
if that clause appears within a subquery of a HAVING clause and the
argument of the function is a correlated reference to a group.
A GROUP BY clause can contain an aggregate function or OLAP function
only if the argument of the function is a correlated reference to a
column in a different subselect than the one containing the GROUP BY
clause.
An OLAP function cannot be used within the argument list of an XMLQUERY
or XMLEXISTS expression.
The statement cannot be processed.
User response:
Change the statement so that the aggregate function or OLAP function is
not used or used only where it is supported.
sqlcode: -120
sqlstate: 42903
SQLSTATE: 42903DB2
|
This works
| Code: |
SELECT count(*)
FROM FDBMD.RONL_SSN11
where ssno between 0 and (select max(ssno)FROM FDBMD.RONL_SSN11)
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Wed Jun 30, 2010 4:38 pm Post subject: |
|
|
thx for the follow-up NASCAR9
obviously, my suggested solution would also require a select:
| Code: |
LAST_CHNG_TIME = (SELECT MAX(LAST_CHNG_TIME)
FROM DB01.ORDER_DB
WHERE GROUP_CD = 'SCREWS'
AND GROUP_NBR = 'A01')
|
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
| Posted: Thu Jul 01, 2010 11:33 am Post subject: |
|
|
Hi All,
Let me give it a try.
Thanks for your efforts.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
