Re: Strange problem with max()?
- From: "--CELKO--" <jcelko212@xxxxxxxxxxxxx>
- Date: 14 Mar 2007 09:42:32 -0700
On Mar 14, 4:25 am, sergei.shei...@xxxxxx wrote:
Hello,
I've been at this problem all morning. Below you can see select sql
with included select in where statement. I have to use the max
function on the included select. The included select statement takes
only 10 seconds to execute and returns just under 4000 rows.
When I execute the entire statement, it GETS STUCK! If I remove max
and group by from the included select, everything executes well, but I
get wrong results, of course.
select * from TB_TRADE as TRD
where
CL_TRADE_ID in (select max(t.CL_TRADE_ID)
from TB_TRADE t inner join TB_TRADE_SET s
on t.CL_TRADE_SET_ID=s.CL_TRADE_SET_ID
and t.CL_VERSION = s.CL_VERSION
where s.CL_TRADE_DATE = convert(int, convert(char(8), dateadd(day,
-1, getdate()), 112))
and s.CL_STATUS in ('I','A')
and t.CL_BUY_SELL = 'S'
and t.CL_TRADE_BASIS = 'PR'
and t.CL_TRADE_CURRENCY = 'RUB'
group by s.CL_TRADE_SET_ID
)
Heeelp!
It's a Sybase database.
Sergei.
Try using "cl_trade_id = (SELECT MAX(Tcl_trade_id) ..AND TRD.?? = ??"
or whatever you need to do to make it a scalar correlated subquery.
Without DDL, it is hard to say more. Also you might want to look at
the research on upper and lower case formatting -- you are using one
of the worst conventions. I had to test this when I was with the US
Army and we were trying to make ADA work.
.
- Follow-Ups:
- Re: Strange problem with max()?
- From: sergei . sheinin
- Re: Strange problem with max()?
- References:
- Strange problem with max()?
- From: sergei . sheinin
- Strange problem with max()?
- Prev by Date: Re: Grid computing
- Next by Date: Re: How do I truncate or extend the log space for a database?
- Previous by thread: Strange problem with max()?
- Next by thread: Re: Strange problem with max()?
- Index(es):
Relevant Pages
|
