>>> [EMAIL PROTECTED] 06/10/03 09:24PM
>>>
There may be different ways to write this query -
otherwise you need to
look at tuning this query. Four minutes for a
single product in a 5
million row table doesn't sound really good but I
guess it depends on
hardware.
Have a look at the explain plan for
the query - you have a lot of selection
criteria on the ICTRANS
table. If one (or a group) of those criteria is
very restrictive then
index (and analyze) that column(s) to see the
performance gain.
I've
had a go at writing this as an analytical query. The syntax may
be
incorrect since I don't have anything convenient to test it
against. There
may even be syntax errors - it was simply typed in
without being executed.
Good luck!!!
select a.item, a.location,
a.trans_date, sum(b.quantity) from (
select item, location,
trans_date
rank() over (partition by
item, location order by quantity desc)
the_rank
from ictrans
where
company = 2000 and trans_date between (current_date-14)
and
current_date
and doc_type = 'IS' and reason_code = 'VTCL'
and
substr(item,2,2) = '57') a,
ictrans b
where a.the_rank = 1
and a.item
= b.item
and a.location = b.location
and b.company = 2000 and
b.trans_date between (current_date-14) and
current_date
and b.doc_type =
'IS' and b.reason_code = 'VTCL'
and substr(b.item,2,2) =
'57')
"Teresita
Castro"
<[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
martmx.com>
cc:
Sent
by:
Subject: RE: How to put a TOP 1 in a
select
[EMAIL PROTECTED]
om
11/06/2003
11:59
Please respond
to
ORACLE-L
This is an example of the information.
I forgot to
mention that in each company we have supermarkets called in the
system
Locations. So when I do this query I have to return per item the
total of
sales in the las two week, the day that we sale more
per
supermarket
Item
trans_date
Quantity
Location
========================================
0570004
5/29/2003 12:00:00 AM -1 TJU02
0570004
6/3/2003 12:00:00 AM -1 TJU24
0570004
6/9/2003 12:00:00 AM -1 TJU31
0570006
5/28/2003 12:00:00 AM -1 TJU24
0570010
6/3/2003 12:00:00 AM -1 TJU02
0570010
5/30/2003 12:00:00 AM -1 TJU24
0570017
6/3/2003 12:00:00 AM -1 TJU24
0570018
5/29/2003 12:00:00 AM -4 TJU02
0570018 5/31/2003 12:00:00
AM -3 TJU02
0570018 5/28/2003 12:00:00 AM -2
TJU02
0570018 5/30/2003 12:00:00 AM -2
TJU02
0570018 6/3/2003 12:00:00 AM -2
TJU02
0570018 6/2/2003 12:00:00 AM -2
TJU02
0570018 6/1/2003 12:00:00 AM -1
TJU02
0570018 5/30/2003 12:00:00 AM -6
TJU24
0570018 6/1/2003 12:00:00 AM -6
TJU24
0570018 6/2/2003 12:00:00 AM -5
TJU24
0570018 6/3/2003 12:00:00 AM -3
TJU24
0570018 5/31/2003 12:00:00 AM -2
TJU24
0570018 5/28/2003 12:00:00 AM -1
TJU24
0570018 6/9/2003 12:00:00 AM -4
TJU31
0570019 6/2/2003 12:00:00 AM -3
TJU24
0570019 5/28/2003 12:00:00 AM -1
TJU24
0570019 6/9/2003 12:00:00 AM -1
TJU31
0570020 6/3/2003 12:00:00 AM -2
TJU02
0570020 5/31/2003 12:00:00 AM -1
TJU02
0570020 6/2/2003 12:00:00 AM -1
TJU02
0570020 6/1/2003 12:00:00 AM -1 TJU24
And this
should be the result
Item
trans_date sum( Quantity
)
Location
========================================
0570004
5/29/2003 12:00:00 AM -1 TJU02
0570004
6/3/2003 12:00:00 AM -1 TJU24
0570004
6/9/2003 12:00:00 AM -1 TJU31
0570006
5/28/2003 12:00:00 AM -1 TJU24
0570010
6/3/2003 12:00:00 AM -1 TJU02
0570010
5/30/2003 12:00:00 AM -1 TJU24
0570017
6/3/2003 12:00:00 AM -1 TJU24
0570018
5/29/2003 12:00:00 AM -16 TJU02
0570018 5/30/2003
12:00:00 AM (or 6/1/2003 12:00:00 AM ) -23 TJU24
0570018
6/9/2003 12:00:00 AM -4 TJU31
0570019 6/2/2003 12:00:00
AM -4 TJU24
0570019 6/9/2003 12:00:00 AM -1
TJU31
0570020 6/3/2003 12:00:00 AM -4
TJU02
0570020 6/1/2003 12:00:00 AM -1 TJU24
I
have a question I run this query:
SELECT distinct a.ITEM,
a.TRANS_DATE,a.LOCATION,
b.tot_QUANTITY
FROM ICTRANS
a,
(
SELECT ITEM, LOCATION,SUM(QUANTITY)
tot_QUANTITY,
MAX(QUANTITY)
max_QUANTITY
FROM ICTRANS WHERE COMPANY =
2000
-- and
LOCATION='TJU02'
AND
TRANS_DATE BETWEEN (CURRENT_DATE-14) AND
CURRENT_DATE
AND
DOC_TYPE = 'IS' AND REASON_CODE =
'VTCL'
AND
SUBSTR(ITEM,2,2) = '57'
GROUP BY
ITEM ,LOCATION
) b
WHERE
a.ITEM='0570018' AND a.ITEM = b.ITEM
AND a.QUANTITY
= b.max_QUANTITY
AND a.LOCATION=b.LOCATION
AND
a.TRANS_DATE BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE;
Just for
1 item and afther 4:12 minutes I have the results, the table
ICTRANS have
4,628,226 rows, that is normal?
When I tried to run the instruccion with
out the item='0570018' it never
ends.
>>>
[EMAIL PROTECTED] 06/10/03 05:29PM >>>
Your query
returns the maximum quantity (and associated date) for a single
ICTRANS
entry. If there are multiple entries per day then the logic is a
lot
more complex - but certainly achievable. Since we don't know how
data
is stored in the table though the query below MAY be
valid.
"Chelur,
Jayadas
{PBSG}"
To: Multiple
recipients of list
ORACLE-L
<[EMAIL PROTECTED]>
<[EMAIL PROTECTED]
cc:
epsi.com>
Subject: RE: How to put a
TOP 1 in a
select
Sent
by:
[EMAIL PROTECTED]
.com
11/06/2003
04:25
Please respond
to
ORACLE-L
This query would give you the total
quantity sold in the
past two weeks and the date on which maximum number
was
sold, for each item ...
SELECT
a.item,
a.tras_date AS
max_sale_date,
b.tot_qty
AS tot_sale_qty
FROM ICTRANS
a,
(
SELECT item, SUM(qty) tot_qty,
MAX(qty) max_qty
FROM
ICTRANS
WHERE
company =
2000
AND trans_date
BETWEEN TRUNC(SYSDATE-14) AND SYSDATE
AND doc_type
= 'IS'
AND
reason_code =
'VTCL'
AND
SUBSTR(item,2,2) = '57'
GROUP BY
item
) b
WHERE a.item =
b.item
AND a.qty =
b.max_qty
AND trans_date BETWEEN TRUNC(SYSDATE-14) AND
SYSDATE;
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this
message.
If you are
not the addressee indicated in this
message
(or responsible for delivery
of the message to such
person),
you may not copy or deliver this message to anyone.
In such case, you
should destroy this message and kindly notify the
sender
by
reply e-mail or by telephone on (61 3) 9612-6999.
Please
advise immediately if you or your employer does not consent
to
Internet e-mail for messages of this
kind.
Opinions, conclusions and
other information in this
message
that do not relate to the official business
of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by
it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net--
Author: Mark
Richard
INET: [EMAIL PROTECTED]
Fat City Network
Services -- 858-538-5051
http://www.fatcity.comSan Diego,
California -- Mailing list and web
hosting
services
---------------------------------------------------------------------
To
REMOVE yourself from this mailing list, send an E-Mail message
to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message
BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing
list you want to be removed from). You may
also send the HELP command
for other information (like
subscribing).
