SQL Query Problem(possilble duplicate send, Sorry!)
Hello all,I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %.Here is the query:select "COUNTRY", count ("MSS") "COUNT_MSS"FROM (SELECT DISTINCTms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",NVL(a.country,'USA') "COUNTRY"FROM mscript ms, reviewms m, address a, journal jWHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id and M.MSNUMBER_JCO!
DE
= ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))order by 1)GROUP BY ROLLUP(COUNTRY)And the output:COUNTRY COUNT_MSSAUSTRALIA 1 AUSTRIA 2
BELGIUM 4 CANADA 20 CHILE 1 CHINA 3
CZECH REPUBLIC 1 DENMARK 4 ENGLAND 10
46Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement?As always thanks so much!Viktor
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Viktor wrote:
Hello all,
I am working with a query that does some counts. I've hit a brick wall
and can't get passed trying to figure out how I can make the query show
percentages %.
Here is the query:
select COUNTRY,
count (MSS) COUNT_MSS
FROM (
SELECT DISTINCT
ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,
m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ
NAME_ID,
NVL(a.country,'USA') COUNTRY
FROM mscript ms, reviewms m, address a, journal j
WHERE (m.first_return between '01/01/2003' and '12/31/2003'
or m.second_return between '01/01/2003 and '12/31/2003'
or m.second_return between '01/01/2003 and '12/31/2003')
and ms.journal_id = j.journal_id
and M.MSNUMBER_JCO! DE = ms.journal_id
and M.MSNUMBER_YRISSUE = ms.yr_of_issue
and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no
and M.MSNUMBER_CKCHAR = ms.check_char
and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)
and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)
and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)
and a.addtype_addrstyp in ('m', 'p')
and a.addtype_typeno = 1
and (a.addr_end_date is null
or a.addr_end_date sysdate)
and (first_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u')
or second_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))
order by 1)
GROUP BY ROLLUP(COUNTRY)
And the output:
COUNTRYCOUNT_MSS
AUSTRALIA 1
AUSTRIA 2
BELGIUM 4
CANADA 20
CHILE 1
CHINA 3
CZECH REPUBLIC1
DENMARK 4
ENGLAND 10
46
Is there a way I can also display a percentage column, that is the
percentage of the total in the same SQL statement?
As always thanks so much!
Viktor
Will this work? (ran a simple test case that worked, although this may not):
select
country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%'
pct
from (
select COUNTRY,
count (MSS) over (partition by MSS) COUNT_MSS,
count(MSS) over () overall_total
FROM (
SELECT DISTINCT
ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,
m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ
NAME_ID,
NVL(a.country,'USA') COUNTRY
FROM mscript ms, reviewms m, address a, journal j
WHERE (m.first_return between '01/01/2003' and '12/31/2003'
or m.second_return between '01/01/2003 and '12/31/2003'
or m.second_return between '01/01/2003 and '12/31/2003')
and ms.journal_id = j.journal_id
and M.MSNUMBER_JCO! DE = ms.journal_id
and M.MSNUMBER_YRISSUE = ms.yr_of_issue
and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no
and M.MSNUMBER_CKCHAR = ms.check_char
and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)
and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)
and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)
and a.addtype_addrstyp in ('m', 'p')
and a.addtype_typeno = 1
and (a.addr_end_date is null
or a.addr_end_date sysdate)
and (first_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u')
or second_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))
order by 1)
GROUP BY ROLLUP(COUNTRY))
(not sure how it will react to a 'group by rollup..' though.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bricklen Anderson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Thanks for your reply! Will try it now. Will this work in 8i?
Viktor
Bricklen Anderson [EMAIL PROTECTED] wrote:
Viktor wrote: Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select "COUNTRY", count ("MSS") "COUNT_MSS" FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID", NVL(a.country,'USA') "COUNTRY" FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003' or m.second_return between '01/01/2003 and '12/31/2003') and ms.journal_id = j.journal_id !
and
M.MSNUMBER_JCO! DE = ms.journal_id and M.MSNUMBER_YRISSUE = ms.yr_of_issue and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no and M.MSNUMBER_CKCHAR = ms.check_char and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+) and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+) and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+) and a.addtype_addrstyp in ('m', 'p') and a.addtype_typeno = 1 and (a.addr_end_date is null or a.addr_end_date sysdate) and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u') or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1) GROUP BY ROLLUP(COUNTRY) And the output: COUNTRY COUNT_MSS AUSTRALIA 1 AUSTRIA 2 BELGIUM 4 CANADA 20 CHILE 1 CHINA 3 CZECH REPUBLIC 1 !
DENMARK
4 ENGLAND 10 46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor Will this work? (ran a simple test case that worked, although this may not):select country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' pctfrom (select "COUNTRY",count ("MSS") over (partition by "MSS") "COUNT_MSS",count("MSS") over () overall_totalFROM (SELECT DISTINCTms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",NVL(a.country,'USA') "COUNTRY"FROM mscript ms, reviewms m, address a, journal jWHERE (m.first_return between '01/01/2003' and '12/31/2003'or m.second_return between '01/01/2003 and '12/31/2003'or m.second_return!
between
'01/01/2003 and '12/31/2003')and ms.journal_id = j.journal_idand M.MSNUMBER_JCO! DE = ms.journal_idand M.MSNUMBER_YRISSUE = ms.yr_of_issueand M.MSNUMBER_MS_SEQNO = ms.ms_sequence_noand M.MSNUMBER_CKCHAR = ms.check_charand m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)and a.addtype_addrstyp in ('m', 'p')and a.addtype_typeno = 1and (a.addr_end_date is nullor a.addr_end_date sysdate)and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))order by 1)GROUP BY ROLLUP(COUNTRY))(not sure how it will react to a 'group by rollup..' though.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bricklen AndersonINET: [EMAIL PROTECTED]Fat C!
ity
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 messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: SQL Query Problem(possilble duplicate send, Sorry!)
Viktor wrote: Thanks for your reply! Will try it now. Will this work in 8i? Viktor If it works at all, then it should work in both 8i and 9i, although I don't have a version of 8i handy right now to try this on. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bricklen Anderson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query Problem(possilble duplicate send, Sorry!)
hi,
There is a function RATIO_BY_PERCENT or something very similar to this in
SQL
just find it out
this gives u individual contributions as compared to the whole thing.
Regds,
Rohan
From: Viktor [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: SQL Query Problem(possilble duplicate send, Sorry!)
Date: Tue, 13 Jan 2004 12:34:35 -0800
Hello all,
I am working with a query that does some counts. I've hit a brick wall and
can't get passed trying to figure out how I can make the query show
percentages %.
Here is the query:
select COUNTRY,
count (MSS) COUNT_MSS
FROM (
SELECT DISTINCT
ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,
m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ
NAME_ID,
NVL(a.country,'USA') COUNTRY
FROM mscript ms, reviewms m, address a, journal j
WHERE (m.first_return between '01/01/2003' and '12/31/2003'
or m.second_return between '01/01/2003 and '12/31/2003'
or m.second_return between '01/01/2003 and '12/31/2003')
and ms.journal_id = j.journal_id
and M.MSNUMBER_JCODE = ms.journal_id
and M.MSNUMBER_YRISSUE = ms.yr_of_issue
and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no
and M.MSNUMBER_CKCHAR = ms.check_char
and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)
and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)
and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)
and a.addtype_addrstyp in ('m', 'p')
and a.addtype_typeno = 1
and (a.addr_end_date is null
or a.addr_end_date sysdate)
and (first_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u')
or second_recommend not in
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))
order by 1)
GROUP BY ROLLUP(COUNTRY)
And the output:
COUNTRYCOUNT_MSS
AUSTRALIA 1
AUSTRIA 2
BELGIUM 4
CANADA 20
CHILE 1
CHINA 3
CZECH REPUBLIC1
DENMARK 4
ENGLAND 10
46
Is there a way I can also display a percentage column, that is the
percentage of the total in the same SQL statement?
As always thanks so much!
Viktor
-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
_
Contact brides grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag
Only on www.shaadi.com. Register now!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rohan Karanjawala
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
SQL Query
Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query
Bambi, I think that the query (2) will return the the same count as query (1) if you use the column user_bytes from the dba_data_files rather than the column bytes. If I sum the bytes from dba_extents for a tablespace_name xxx and sum the user_bytes from dba_data_files for tablespace_name xxx I get the same value. I my case the tablespace is a partitioned table with LMT . size 38M with 2 M uniform extents. I did not use 100 % of each extent there fore the different between bytes and user_bytes. Ron [EMAIL PROTECTED] 11/14/2003 12:44:26 PM Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Nice try. Same result. 1 select a. tablespace_name, sum(b.user_bytes)/(1024*1024) allocated, 2 sum(a.bytes)/(1024*1024) used, round(sum(a.bytes)/sum(b.user_bytes),4) pct 3 from dba_extents a, dba_data_files b 4 where a.tablespace_name=b.tablespace_name 5 and a.tablespace_name='NAUAT' 6* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAME ALLOCATED USEDPCT -- -- -- -- NAUAT 31772837.3 23018 .0007 Also, note that both the allocated *and* used values are wrong...they should be TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 -Original Message- Sent: Friday, November 14, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Bambi, I think that the query (2) will return the the same count as query (1) if you use the column user_bytes from the dba_data_files rather than the column bytes. If I sum the bytes from dba_extents for a tablespace_name xxx and sum the user_bytes from dba_data_files for tablespace_name xxx I get the same value. I my case the tablespace is a partitioned table with LMT . size 38M with 2 M uniform extents. I did not use 100 % of each extent there fore the different between bytes and user_bytes. Ron [EMAIL PROTECTED] 11/14/2003 12:44:26 PM Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query
Bambi, Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake. HTH, SF Bellow, Bambi wrote: Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Odder still, I get inconsistent results. megs_allocated is always wrong, but megs_used is right when run against one tablespace, wrong against another: TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- MEDIUM_DATA361713 3616 1 Wrong! select sum(bytes)/1048576 from dba_extents where tablespace_name = 'MEDIUM_DATA' SUM(BYTES)/1048576 -- 1808 TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- SMALL_DATA2783232169.875.01 Right! 1* select sum(bytes)/1048576 from dba_extents where tablespace_name = 'SMALL_DATA' SQL / SUM(BYTES)/1048576 -- 169.875 As for why the ALLOCATED value is wrong...I don't know, but at least it's wrong consistently! PB --- Bellow, Bambi [EMAIL PROTECTED] wrote: [snip] Also, note that both the allocated *and* used values are wrong...they should be TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Bambi, I tried your sql on my test server and the used space is the same. here are the results. The ALLOCATED and PCT are way out, I'm looking. 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='AWSR_DATA_01' inux inux/ ABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT - -- -- -- WSR_DATA_01 40 38 95 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='AWSR_DATA_01' 7* group by a.tablespace_name,b.tablespace_name linux/ TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- AWSR_DATA_01 760 38 5 linux Bambi, The problem is a sum of the bytes each tome the tablespace_name is looked up in the dba_extents table. With both tables used.. 1 select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated 2from dba_extents a, dba_data_files b 3 where a.tablespace_name=b.tablespace_name 4 and a.tablespace_name='AWSR_DATA_01' 5* group by b.tablespace_name linux/ TABLESPACE_NAMEMEGS_ALLOCATED -- -- AWSR_DATA_01 760 With one table used.. 1 select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated 2from dba_data_files b 3 where b.tablespace_name='AWSR_DATA_01' 4* group by b.tablespace_name linux/ TABLESPACE_NAMEMEGS_ALLOCATED -- -- AWSR_DATA_01 40 CORRECT ANSWER. select count(*) from dba_data_files where tablespace_name ='AWSR_DATA_01' COUNT(*) = 1 select count(*) from dba_extents where tablespace_name ='AWSR_DATA_01' COUNT(*) = 19 19 X 40 = 760 Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
But Stephane, I am aggregating by tablespace for both extents and for data_files. There is nothing here that is separating out anything by datafile. And, if I take away the GROUP BY, I lose the ability to aggregate at all, which is the point of this... -Original Message- Sent: Friday, November 14, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Bambi, Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake. HTH, SF Bellow, Bambi wrote: Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
I encountered the same issues. Also, just to make things interesting, if you replace DBA_EXTENTS with DBA_FREE_SPACE, the number of MEGS_ALLOCATED is different. Still wrong, mind you, but different. None of this makes any sense to me. My bet is that I got me a bug. Bambi. -Original Message- Sent: Friday, November 14, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Bambi, I tried your sql on my test server and the used space is the same. here are the results. The ALLOCATED and PCT are way out, I'm looking. 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='AWSR_DATA_01' inux inux/ ABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT - -- -- -- WSR_DATA_01 40 38 95 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='AWSR_DATA_01' 7* group by a.tablespace_name,b.tablespace_name linux/ TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- AWSR_DATA_01 760 38 5 linux Bambi, The problem is a sum of the bytes each tome the tablespace_name is looked up in the dba_extents table. With both tables used.. 1 select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated 2from dba_extents a, dba_data_files b 3 where a.tablespace_name=b.tablespace_name 4 and a.tablespace_name='AWSR_DATA_01' 5* group by b.tablespace_name linux/ TABLESPACE_NAMEMEGS_ALLOCATED -- -- AWSR_DATA_01 760 With one table used.. 1 select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated 2from dba_data_files b 3 where b.tablespace_name='AWSR_DATA_01' 4* group by b.tablespace_name linux/ TABLESPACE_NAMEMEGS_ALLOCATED -- -- AWSR_DATA_01 40 CORRECT ANSWER. select count(*) from dba_data_files where tablespace_name ='AWSR_DATA_01' COUNT(*) = 1 select count(*) from dba_extents where tablespace_name ='AWSR_DATA_01' COUNT(*) = 19 19 X 40 = 760 Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Below is a very nice tablespace script, followed by some sample output. Thought you might like it. I found in somewhere. REM name: freespace.sql REM This script is used to list database freespace, total database REM space, largest extent, fragments and percent freespace. REM REM Usage sqlplus system/passwd @freespace REM REM Date CreateDescription REM 30-Oct-96Fan Zhang Initial creation REM REM dba tool key: freespace.sql -- list database freespace, total space and percent free REM set pau off set pages 35 set lines 120 col tablespace heading 'Tablespace' col freeheading 'Free|(Mb)' format 9.9 col total heading 'Total|(Mb)'format 99.9 col usedheading 'Used|(Mb)' format 9.9 col pct_freeheading 'Pct|Free' format 9.9 col pct_nextheading 'Pct|Next' format 9.9 col largest heading 'Largest|(Mb)' format 9.9 col nextheading 'Next|Ext(Mb)' format 9.9 col fragmentheading 'Fragment' format 999 col extents heading 'Max.|Ext.' format 999 spool freespace.txt compute sum of total on report compute sum of free on report compute sum of used on report break on report select substr(a.tablespace_name,1,13) tablespace, round(sum(a.total1)/(1024*1024), 1) Total, round(sum(a.total1)/(1024*1024), 1)-round(sum(a.sum1)/(1024*1024), 1) used, round(sum(a.sum1)/(1024*1024), 1) free, round(sum(a.sum1)/(1024*1024), 1)*100/round(sum(a.total1)/(1024*1024), 1) pct_free, round(sum(a.maxb)/(1024*1024), 1) largest, round(sum(a.next1)/(1024*1024), 1) Next, round(sum(a.next1)/(1024*1024), 1)*100/round(sum(a.maxb)/(1024*1024), 1) pct_next, max(a.max_ext) extents, max(a.cnt) fragment from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB, count(bytes) cnt, 0 next1, 0 max_ext fromdba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0, 0, 0 fromdba_data_files group by tablespace_name union select tablespace_name, 0, 0, 0, 0, max(next_extent) next1, max(extents) max_ext from dba_segments group by tablespace_name) a group by a.tablespace_name order by pct_free / spool off; SQL*Plus: Release 3.3.4.0.0 - Production on Fri Nov 14 14:11:53 2003 Copyright (c) Oracle Corporation 1979, 1996. All rights reserved. Connected to: Oracle7 Server Release 7.3.4.4.0 - Production With the distributed and parallel query options PL/SQL Release 2.3.4.4.0 - Production Total Used Free Pct Largest Next Pct Max. Tablespace (Mb) (Mb) (Mb) Free (Mb) Ext(Mb) Next Ext. Fragment - - PRODIDX 21500.0 20130.4 1369.6 6.4925.8 87.9 9.5 62 13 PROD27400.0 24014.1 3385.9 12.4243.3212.4 87.3 54 197 PRODALT 200.0149.3 50.7 25.4 50.7 19.1 37.741 SYSTEM200.0 92.0108.0 54.0 86.5 4.2 4.9 15 12 TOOLS 100.0 12.5 87.5 87.5 75.1 12.0 16.046 PATROL_DATA20.0 2.0 18.0 90.0 18.0 .3 1.711 RBS 2500.0156.1 2343.9 93.8443.9 8.0 1.82 24 USERS 100.0 3.2 96.8 96.8 93.4 1.0 1.125 BMC_SMGT_TS 1000.0 4.6995.4 99.5500.0 2.0 .41 280 PRODAUD 1000.0 3.4996.6 99.7996.6 .4 .0 201 PATROL_TEMP10.0 .0 10.0100.0 10.0 .0 .001 TEMP 2900.0 .0 2900.0100.0 24.0 .0 .00 165 - sum 56930.0 44567.6 12362.4 12 rows selected. -Original Message- Sent: Friday, November 14, 2003 1:54 PM To: Multiple recipients of list ORACLE-L But Stephane, I am aggregating by tablespace for both extents and for data_files. There is nothing here that is separating out anything by datafile. And, if I take away the GROUP BY, I lose the ability to aggregate at all, which is the point of this... -Original Message- Sent: Friday, November 14, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Bambi, Your second query is wrong because all
Re: SQL Query
The aggregate function operates last, on a result set. Why I suggested to suppress the GROUP BY is that then you would have seen that the number of bytes from each datafile is returnedtoo many times. If you have F1 and F2 associated to your database, with E1 and E2 in F1 and E3 in F2 (I hope the meaning of my symbols is obvious) by joining on the tablespace name you get F1 E1 F1 E2 F1 E3 F2 E1 F2 E2 F2 E3 Quite obviously, when you sum up the result is much too big. For files in that case its 3 times too big for each, and for extents two times too big for each. By contrast, the inline views in the query which works force the aggregates to be computed _before_ the final calculation. SF Bellow, Bambi wrote: But Stephane, I am aggregating by tablespace for both extents and for data_files. There is nothing here that is separating out anything by datafile. And, if I take away the GROUP BY, I lose the ability to aggregate at all, which is the point of this... -Original Message- Sent: Friday, November 14, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Bambi, Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake. HTH, SF Bellow, Bambi wrote: Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Be aware that this script does not include autoexted info Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] Sent by: To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: .com Subject: RE: SQL Query 11/14/2003 01:19 PM Please respond to ORACLE-L Below is a very nice tablespace script, followed by some sample output. Thought you might like it. I found in somewhere. REM name: freespace.sql REM This script is used to list database freespace, total database REM space, largest extent, fragments and percent freespace. REM REM Usage sqlplus system/passwd @freespace REM REM Date CreateDescription REM 30-Oct-96Fan Zhang Initial creation REM REM dba tool key: freespace.sql -- list database freespace, total space and percent free REM set pau off set pages 35 set lines 120 col tablespace heading 'Tablespace' col freeheading 'Free|(Mb)' format 9.9 col total heading 'Total|(Mb)'format 99.9 col usedheading 'Used|(Mb)' format 9.9 col pct_freeheading 'Pct|Free' format 9.9 col pct_nextheading 'Pct|Next' format 9.9 col largest heading 'Largest|(Mb)' format 9.9 col nextheading 'Next|Ext(Mb)' format 9.9 col fragmentheading 'Fragment' format 999 col extents heading 'Max.|Ext.' format 999 spool freespace.txt compute sum of total on report compute sum of free on report compute sum of used on report break on report select substr(a.tablespace_name,1,13) tablespace, round(sum(a.total1)/(1024*1024), 1) Total, round(sum(a.total1)/(1024*1024), 1)-round(sum(a.sum1)/(1024*1024), 1) used, round(sum(a.sum1)/(1024*1024), 1) free, round(sum(a.sum1)/(1024*1024), 1)*100/round(sum(a.total1)/(1024*1024), 1) pct_free, round(sum(a.maxb)/(1024*1024), 1) largest, round(sum(a.next1)/(1024*1024), 1) Next, round(sum(a.next1)/(1024*1024), 1)*100/round(sum(a.maxb)/(1024*1024), 1) pct_next, max(a.max_ext) extents, max(a.cnt) fragment from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB, count(bytes) cnt, 0 next1, 0 max_ext fromdba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0, 0, 0 fromdba_data_files group by tablespace_name union select tablespace_name, 0, 0, 0, 0, max(next_extent) next1, max(extents) max_ext from dba_segments group by tablespace_name) a group
Re: SQL Query
You can't join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes of the files for the tablespace. ie. select b.tablespace_name, b.bytes from dba_extents a, dba_data_files b where a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. The second query determines tablespace size based on the number of extents allocated to it. Drop all the objects in the tablespace, and your tablespace will no longer appear to have any space. HTH Jared Bellow, Bambi [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 09:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Query Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Ah, I see your point, now, and that's quite correct. Which means I can either aggregate on fileid, or aggregate by table and join. Nice catch, and mystery solved. No bug report. Take care and have a great weekend! Bambi. -Original Message- Sent: Friday, November 14, 2003 2:50 PM To: Multiple recipients of list ORACLE-L The aggregate function operates last, on a result set. Why I suggested to suppress the GROUP BY is that then you would have seen that the number of bytes from each datafile is returnedtoo many times. If you have F1 and F2 associated to your database, with E1 and E2 in F1 and E3 in F2 (I hope the meaning of my symbols is obvious) by joining on the tablespace name you get F1 E1 F1 E2 F1 E3 F2 E1 F2 E2 F2 E3 Quite obviously, when you sum up the result is much too big. For files in that case its 3 times too big for each, and for extents two times too big for each. By contrast, the inline views in the query which works force the aggregates to be computed _before_ the final calculation. SF Bellow, Bambi wrote: But Stephane, I am aggregating by tablespace for both extents and for data_files. There is nothing here that is separating out anything by datafile. And, if I take away the GROUP BY, I lose the ability to aggregate at all, which is the point of this... -Original Message- Sent: Friday, November 14, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Bambi, Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake. HTH, SF Bellow, Bambi wrote: Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Jared -- That is expected behavior for this query. What was weird is that I expected the aggregation in the group by to apply to multiple fileids making GROUP BY a.tablespace_name, b.tablespace_name to be able, then, to join a.tablespace_name to b.tablespace_name as a 1-1 join rather than amany-many. I could have sworn that has worked in the past, but, regardless, my other query returns what I need and the mystery is solved so I don't really need to pursue this with Oracle as a bug... and who really wants to start a bugreport on a Friday afternoon for something wimpy? Hope you have a great weekend! Bambi. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Friday, November 14, 2003 2:54 PMTo: Multiple recipients of list ORACLE-LSubject: Re: SQL QueryYou can't join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes of the files for the tablespace. ie. select b.tablespace_name, b.bytes from dba_extents a, dba_data_files b where a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. The second query determines tablespace size based on the number of extents allocated to it. Drop all the objects in the tablespace, and your tablespace will no longer appear to have any space. HTH Jared "Bellow, Bambi" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 09:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL QueryFriends --Why would these two queries return different results?This query works.SQL l1 selecta.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 pct2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used3 from dba_extents group by tablespace_name) a,4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated5 from dba_data_files group by tablespace_name) b6 where a.tablespace_name=b.tablespace_name7* and a.tablespace_name='NAUAT'SQL /TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT-- -- -- --NAUAT 22924.25 11509 50This query does not work1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,2 sum(a.bytes)/(1024*1024) megs_used,3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct4 from dba_extents a, dba_data_files b5 where a.tablespace_name=b.tablespace_name6 and a.tablespace_name='NAUAT'7* group by a.tablespace_name,b.tablespace_nameSQL /TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT-- -- -- --NAUAT 31773010.5 23018 .07Bambi.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bellow, BambiINET: [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 messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: SQL Query
Would have thought: 1. A developer would have known this - a SQL many-to-many join 2. A DBA would have known this - how else would you know what's happening with your tablespaces? (Clickety-pointy answers not allowed) - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Saturday, November 15, 2003 9:54 AM Subject: Re: SQL Query You can't join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes of the files for the tablespace. ie. select b.tablespace_name, b.bytes from dba_extents a, dba_data_files b where a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. The second query determines tablespace size based on the number of extents allocated to it. Drop all the objects in the tablespace, and your tablespace will no longer appear to have any space. HTH Jared "Bellow, Bambi" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 09:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL QueryFriends --Why would these two queries return different results?This query works.SQL l1 selecta.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 pct2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used3 from dba_extents group by tablespace_name) a,4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated5 from dba_data_files group by tablespace_name) b6 where a.tablespace_name=b.tablespace_name7* and a.tablespace_name='NAUAT'SQL /TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT-- -- -- --NAUAT 22924.25 11509 50This query does not work1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,2 sum(a.bytes)/(1024*1024) megs_used,3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct4 from dba_extents a, dba_data_files b5 where a.tablespace_name=b.tablespace_name6 and a.tablespace_name='NAUAT'7* group by a.tablespace_name,b.tablespace_nameSQL /TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT-- -- -- --NAUAT 31773010.5 23018 .07Bambi.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bellow, BambiINET: [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 messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: SQL Query
Sorry, no, group by has always worked that way. Thanks, I'll try to have a great weekend. :) Bellow, Bambi [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 01:54 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Query Jared -- That is expected behavior for this query. What was weird is that I expected the aggregation in the group by to apply to multiple fileids making GROUP BY a.tablespace_name, b.tablespace_name to be able, then, to join a.tablespace_name to b.tablespace_name as a 1-1 join rather than a many-many. I could have sworn that has worked in the past, but, regardless, my other query returns what I need and the mystery is solved so I don't really need to pursue this with Oracle as a bug... and who really wants to start a bug report on a Friday afternoon for something wimpy? Hope you have a great weekend! Bambi. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Subject: Re: SQL Query You can't join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes of the files for the tablespace. ie. select b.tablespace_name, b.bytes from dba_extents a, dba_data_files b where a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. The second query determines tablespace size based on the number of extents allocated to it. Drop all the objects in the tablespace, and your tablespace will no longer appear to have any space. HTH Jared Bellow, Bambi [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 09:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Query Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query
Sorry, don't understand the DBA part ( #2 ). Binley Lim [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 02:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: SQL Query Would have thought: 1. A developer would have known this - a SQL many-to-many join 2. A DBA would have known this - how else would you know what's happening with your tablespaces? (Clickety-pointy answers not allowed) - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Saturday, November 15, 2003 9:54 AM Subject: Re: SQL Query You can't join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes of the files for the tablespace. ie. select b.tablespace_name, b.bytes from dba_extents a, dba_data_files b where a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. The second query determines tablespace size based on the number of extents allocated to it. Drop all the objects in the tablespace, and your tablespace will no longer appear to have any space. HTH Jared Bellow, Bambi [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 09:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Query Friends -- Why would these two queries return different results? This query works. SQL l 1 select a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated, 2)*100 pct 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used 3 from dba_extents group by tablespace_name) a, 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 5 from dba_data_files group by tablespace_name) b 6 where a.tablespace_name=b.tablespace_name 7* and a.tablespace_name='NAUAT' SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT22924.25 11509 50 This query does not work 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 sum(a.bytes)/(1024*1024) megs_used, 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4 from dba_extents a, dba_data_files b 5 where a.tablespace_name=b.tablespace_name 6 and a.tablespace_name='NAUAT' 7* group by a.tablespace_name,b.tablespace_name SQL / TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT -- -- -- -- NAUAT 31773010.5 23018.07 Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query
Oh, right, #2 is refering to point-and-click GUI interfaces that some DBAs depend on that they no longer know how to write SQLs to navigate the data dictionary. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Saturday, November 15, 2003 1:19 PM Subject: Re: SQL Query Sorry, don't understand the DBA part ( #2 ). "Binley Lim" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 02:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: SQL QueryWould have thought: 1. A developer would have known this - a SQL many-to-many join 2. A DBA would have known this - how else would you know what's happening with your tablespaces? (Clickety-pointy answers not allowed) - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Saturday, November 15, 2003 9:54 AM Subject: Re: SQL Query You can't join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes of the files for the tablespace. ie. select b.tablespace_name, b.bytes from dba_extents a, dba_data_files b where a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. The second query determines tablespace size based on the number of extents allocated to it. Drop all the objects in the tablespace, and your tablespace will no longer appear to have any space. HTH Jared "Bellow, Bambi" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/14/2003 09:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL QueryFriends --Why would these two queries return different results?This query works.SQL l1 selecta.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 pct2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used3 from dba_extents group by tablespace_name) a,4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated5 from dba_data_files group by tablespace_name) b6 where a.tablespace_name=b.tablespace_name7* and a.tablespace_name='NAUAT'SQL /TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT-- -- -- --NAUAT 22924.25 11509 50This query does not work1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,2 sum(a.bytes)/(1024*1024) megs_used,3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct4 from dba_extents a, dba_data_files b5 where a.tablespace_name=b.tablespace_name6 and a.tablespace_name='NAUAT'7* group by a.tablespace_name,b.tablespace_nameSQL /TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT-- -- -- --NAUAT 31773010.5 23018 .07Bambi.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bellow, BambiINET: [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 messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Sql query : select max timestamp value from table
I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
Re: Sql query : select max timestamp value from table
Johan, First, you don't need the distinct. The proper query will return 1 row per ip. Second, take the max(timestamp) out of the group by. That is causing the problem. Daniel Johan Muller wrote: I have multiple timestamps values for single ip in a table, I need the max(timestamp) for each ip I select out. example: timestamp ip 2003-09-29 13:20:2368.209.182.4 2003-09-29 13:20:4468.209.182.4 2003-10-02 12:53:3868.209.182.4 2003-10-02 12:35:0668.75.94.158 2003-10-02 12:52:0368.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip? begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Sql query : select max timestamp value from table
select ip, max(timestamp) from table group by ip; -Original Message-From: Johan Muller [mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Sql query : select max timestamp value from table
Title: RE: Sql query : select max timestamp value from table select ip, max(timestamp) from table group by ip; Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Johan Muller [SMTP:[EMAIL PROTECTED] I have multiple timestamps values for single ip in a table, I need the max(timestamp) for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.4 2003-09-29 13:20:44 68.209.182.4 2003-10-02 12:53:38 68.209.182.4 2003-10-02 12:35:06 68.75.94.158 2003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Sql query : select max timestamp value from table
Johann, Take the timestamp out of your group by. Cheers, Melanie *** Melanie Caffrey Proximo Consulting Services, Inc. [EMAIL PROTECTED] (212) 686-6004 Ext. 32 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Johan Muller Sent: Thursday, October 02, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.4 2003-09-29 13:20:44 68.209.182.4 2003-10-02 12:53:38 68.209.182.4 2003-10-02 12:35:06 68.75.94.158 2003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Sql query : select max timestamp value from table
Title: Message select ip, max(timestamp) from table group by ip; -Original Message-From: Johan Muller [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
SQL Query
Hi, I have the following data , table Temp ID Text Order 1 B2 1 A1 1 C3 I want to write a query which says: wherever there is more than 1 occurrence of ID then concatenate the text in the order specified in the order column. So i would get: ID Text 1 A B C Any suggestions? Thanks Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query
Imran, Tom Kyte has a thread that might help: http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562, Essentially, you can do this: CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR ) RETURN VARCHAR2 IS ret VARCHAR2(32000); tmp VARCHAR2(4000); BEGIN loop fetch cur into tmp; exit when cur%NOTFOUND; ret := ret || ' ' || tmp; end loop; RETURN ret; END; / SQL select * from mike; ID TEXT ORDR -- -- -- 1 B 2 1 A 1 1 C 3 SELECT id, SUBSTR(CONCAT_LIST(CURSOR( SELECT text FROM (select id, text, ordr from mike order by ordr) ee WHERE e.id = ee.id)),1,40) list FROM ( select distinct id from mike) e; ID LIST -- 1 A B C Mike Imran Ashraf wrote: Hi, I have the following data , table Temp ID Text Order 1 B2 1 A1 1 C3 I want to write a query which says: wherever there is more than 1 occurrence of ID then concatenate the text in the order specified in the order column. So i would get: ID Text 1 A B C Any suggestions? Thanks Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query
Mike, Just wanted to supplement your example with another one from my environment (uses nested tables with CAST and MULTISET to achieve the same): SELECT c.customer_id ,customer_name ,utils.code_table_to_string(CAST( MULTISET( SELECT distinct dlr_dealer_code FROM dealer_customers dc ,dlr_dealer_master_t WHERE dc.customer_id = dc_in.customer_id AND dlr_dealer_id = dc.dealer_id ORDER BY dlr_dealer_code ) AS code_table_t )) spans_these_dealers FROM customers c ,( SELECT customer_id FROM dealer_customers GROUP BY customer_id HAVING COUNT(distinct dealer_id) 1 ) dc_in WHERE dc_in.customer_id = c.customer_id; Does eseentially the same thing. In this case it concatenates the dealer_codes per customer (identifies customers spanning multiple dealers) on the same row. Here is a sample of the output: 2969473096 Mountain Enterprises IncD100,D470,D480 2969473121 K C ConstructionB010,B150,B190 2969473195 GOODFELLOW BROS H140,H330 The TYPE code_table_t is defined as: TYPE CODE_TABLE_T IS table of varchar2(3000); And the conversion function is very similar in its functionality to dbms_utility.comma_to_table procedure: FUNCTION code_table_to_string (in_table code_table_t, in_rec_sep varchar2 DEFAULT config.c_rec_sep) RETURN delimited_list_t IS v_list delimited_list_t; BEGIN FOR i IN 1..in_table.COUNT LOOP IF i = 1 THEN v_list := in_table(1); ELSE v_list := v_list||in_rec_sep||in_table(i); END IF; END LOOP; RETURN v_list; END code_table_to_string; As opposed your suggestion of using the following: SELECT id, CONCAT_LIST(CURSOR( SELECT text FROM mike m_in ORDER BY ordr WHERE m_in.id = m.id)) list FROM ( select distinct id from mike) m; --- Mike Spalinger [EMAIL PROTECTED] wrote: Imran, Tom Kyte has a thread that might help: http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562, Essentially, you can do this: CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR ) RETURN VARCHAR2 IS ret VARCHAR2(32000); tmp VARCHAR2(4000); BEGIN loop fetch cur into tmp; exit when cur%NOTFOUND; ret := ret || ' ' || tmp; end loop; RETURN ret; END; / SQL select * from mike; ID TEXT ORDR -- -- -- 1 B 2 1 A 1 1 C 3 SELECT id, SUBSTR(CONCAT_LIST(CURSOR( SELECT text FROM (select id, text, ordr from mike order by ordr) ee WHERE e.id = ee.id)),1,40) list FROM ( select distinct id from mike) e; ID LIST -- 1 A B C Mike Imran Ashraf wrote: Hi, I have the following data , table Temp ID Text Order 1 B2 1 A1 1 C3 I want to write a query which says: wherever there is more than 1 occurrence of ID then concatenate the text in the order specified in the order column. So i would get: ID Text 1 A B C Any suggestions? Thanks Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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
RE: SQL Query
Err, not quite, actually. The solution presented below will simply generate the cartesian product of the sum of attributes from the table. The (grossly pedantic) solution is: select distinct a.col1, a.col2, a.col3||b.col3||c.col3 from fred a, fred b, fred c where a.col3 = 'A' and b.col3 = 'B' and c.col3='C' ... which really begs the question - 'what IS the question?' ! Although this solution works, it would be quite impractical to apply it to 'test' if there were 'n' values of col3. Imran - try framing your question in general terms. peter edinburgh -Original Message- Sent: Friday, September 19, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Assume your table is name fred: select a.col1 a.col2 a.col3||b.col3||c.col3 from fred a, fred b, fred c; Allan -Original Message- Sent: Friday, September 19, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Hi, I have the following data in a table: col1 col2 col3 1 2 A 1 2 B 1 2 C I want to display this as : col1 col2 col3 1 2 A B C How can i do this? Regards Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
SQL Query
Hi, I have the following data in a table: col1 col2 col3 1 2 A 1 2 B 1 2 C I want to display this as : col1 col2 col3 1 2 A B C How can i do this? Regards Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Assume your table is name fred: select a.col1 a.col2 a.col3||b.col3||c.col3 from fred a, fred b, fred c; Allan -Original Message- Sent: Friday, September 19, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Hi, I have the following data in a table: col1 col2 col3 1 2 A 1 2 B 1 2 C I want to display this as : col1 col2 col3 1 2 A B C How can i do this? Regards Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
SQL Query Help
I have a table with records like this CID S TO_CHAR(DATECREATED, MESSAGE PID - - -- 2 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 21:45:45 service change1 1 N 01-jan-1974 12:34:45 msisdn change 1* 1 N 01-jan-1974 19:45:45 service change1 2 N 01-jan-1974 19:45:45 service change1 1 N 01-nov-1974 17:45:45 service change1 1 N 01-nov-1974 19:45:45 service change1 I want to display only the records with the *(not a value stored in the database.just used as a marker here). i.e the records which meet the following. 1.earliest date 2.if there are multiple occurances of records with the same cid and pid combination i want only the record for the combination of cid-pid and with the most earliest record(oldest time stamp). i want to achieve this CID S TO_CHAR(DATECREATED, MESSAGE PID - - -- 2 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 19:45:45 service change1* 1 N 01-jan-1974 12:34:45 msisdn change 1* I need some help in getting the query that can get the results like that. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query Help
select columns from table A) where predicates and datecreated = (select min(datecreated) from table b where b.cid = a.cid and b.pid = a.pid) At 08:14 PM 6/13/2003 -0800, you wrote: I have a table with records like this CID S TO_CHAR(DATECREATED, MESSAGE PID - - -- 2 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 21:45:45 service change1 1 N 01-jan-1974 12:34:45 msisdn change 1* 1 N 01-jan-1974 19:45:45 service change1 2 N 01-jan-1974 19:45:45 service change1 1 N 01-nov-1974 17:45:45 service change1 1 N 01-nov-1974 19:45:45 service change1 I want to display only the records with the *(not a value stored in the database.just used as a marker here). i.e the records which meet the following. 1.earliest date 2.if there are multiple occurances of records with the same cid and pid combination i want only the record for the combination of cid-pid and with the most earliest record(oldest time stamp). i want to achieve this CID S TO_CHAR(DATECREATED, MESSAGE PID - - -- 2 N 01-feb-1974 19:45:45 service change1* 3 N 01-feb-1974 19:45:45 service change1* 1 N 01-jan-1974 12:34:45 msisdn change 1* I need some help in getting the query that can get the results like that. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: sql query optimization
Given the low elapsed time for each iteration of the query I wonder if the problem might be susceptible to either of the following approaches. 1. Calling the query less often. I'm guessing from the object names etc that this is some sort of scientific analysis program, and it may be that you are repeatedly calling the same logic with different binds when you could be doing some sort of batch processing. 2. Creating a Materialized view that will serve the query and could be stored if necessary in the keep pool. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gmei Sent: 10 June 2003 22:59 To: Multiple recipients of list ORACLE-L Subject: sql query optimization Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result is not good. All the columns involved in the where clause have been indexed. b1 and b2 are bind variables that are passed in. select distinct observationlist.geneid, pval, score, Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906), proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget from mt.dualblastresults, mt.seqtable querySeq, isi.observationlist, isi.termobs where subjID = :b1 and queryID = QuerySeq.AASeqID and querySeq.use='Y' and querySeq.geneID=observationlist.geneid and curationStatus='E' and evidenceCode in (3000900,3000902,3000906) and observationlist.id=obsID and target='GeneID' and termobs.termid in (select termid from isi.arc where arctype in (299,300) start with termid = :b2 connect by prior termid=parenttermid) order by mt.blast.pvaltonumber(pval) asc, score desc, geneid, decode(proteomerefid, null, 0, 1) desc; -- This query typically returns 10 or less rows. mt.dualblastresults is a view, all others are tables. BTW, I need distinct and order by in the query. Here is the explain plan and row counts in tables and their definition. Anyone has any suggestions to make it run faster? TIA. Guang Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124 ) 10 SORT (ORDER BY) (Cost=715 Card=1 Bytes=124) 21 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124) 32 NESTED LOOPS (Cost=609 Card=1 Bytes=124) 43 NESTED LOOPS (Cost=553 Card=1 Bytes=118) 54 NESTED LOOPS (Cost=550 Card=1 Bytes=106) 65 NESTED LOOPS (Cost=280 Card=30 Bytes=1830) 76 VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168 Bytes=8232) 87 UNION-ALL 98 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES ULTS' (Cost=102 Card=118 Bytes=2360) 109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118) 118 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES ULTS' (Cost=10 Card=50 Bytes=1000) 12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50) 136 TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C ost=1 Card=57344 Bytes=688128) 14 13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE) 155 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=9 Card=499 Bytes=22455) 16 15 INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID' (NON-UNIQUE) (Cost=2 Card=499) 174 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3 Card=2388115 Bytes=28657380) 18 17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ UE) (Cost=2 Card=2388115) 193 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42) 20 19 SORT (UNIQUE) (Cost=56 Card=7 Bytes=126) 21 20 FILTER 22 21 CONNECT BY 23 22 INDEX (RANGE SCAN) OF 'ARC_TERMID' (NON-UNIQ UE) (Cost=1 Card=2 Bytes=12) 24 22 TABLE ACCESS (BY USER ROWID) OF 'ARC' 25 22 INDEX (RANGE SCAN) OF 'ARC_TYPETERMPARENT' ( UNIQUE) (Cost=3 Card=8 Bytes=144) SQL select count(*) from mt.dualblastresults; COUNT(*) -- 22332188 SQL select count(*) from mt.seqtable ; COUNT(*) -- 373505 SQL select count(*) from isi.observationlist; COUNT(*) -- 2290858 SQL select count(*) from isi.termobs; COUNT(*) -- 2388115 SQL
Re: RE: sql query optimization
1. check your sort_area_size when you use distinct you need to sort. It might be too small and you may be sorting to the temp tablespace which is very slow. 2. how much does querySeq.use='Y' and otehr where clauses like it limit the number of rows you are looking for? I have found that if I craete a another table that just has the rows I need, I can often significantly improve performance. From: Niall Litchfield [EMAIL PROTECTED] Date: 2003/06/12 Thu PM 01:15:08 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: sql query optimization Given the low elapsed time for each iteration of the query I wonder if the problem might be susceptible to either of the following approaches. 1. Calling the query less often. I'm guessing from the object names etc that this is some sort of scientific analysis program, and it may be that you are repeatedly calling the same logic with different binds when you could be doing some sort of batch processing. 2. Creating a Materialized view that will serve the query and could be stored if necessary in the keep pool. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gmei Sent: 10 June 2003 22:59 To: Multiple recipients of list ORACLE-L Subject: sql query optimization Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result is not good. All the columns involved in the where clause have been indexed. b1 and b2 are bind variables that are passed in. select distinct observationlist.geneid, pval, score, Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906), proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget frommt.dualblastresults, mt.seqtable querySeq, isi.observationlist, isi.termobs where subjID = :b1 and queryID = QuerySeq.AASeqID and querySeq.use='Y' and querySeq.geneID=observationlist.geneid and curationStatus='E' and evidenceCode in (3000900,3000902,3000906) and observationlist.id=obsID and target='GeneID' and termobs.termid in (select termid from isi.arc where arctype in (299,300) start with termid = :b2 connect by prior termid=parenttermid) order by mt.blast.pvaltonumber(pval) asc, score desc, geneid, decode(proteomerefid, null, 0, 1) desc; -- This query typically returns 10 or less rows. mt.dualblastresults is a view, all others are tables. BTW, I need distinct and order by in the query. Here is the explain plan and row counts in tables and their definition. Anyone has any suggestions to make it run faster? TIA. Guang Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124 ) 10 SORT (ORDER BY) (Cost=715 Card=1 Bytes=124) 21 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124) 32 NESTED LOOPS (Cost=609 Card=1 Bytes=124) 43 NESTED LOOPS (Cost=553 Card=1 Bytes=118) 54 NESTED LOOPS (Cost=550 Card=1 Bytes=106) 65 NESTED LOOPS (Cost=280 Card=30 Bytes=1830) 76 VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168 Bytes=8232) 87 UNION-ALL 98 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES ULTS' (Cost=102 Card=118 Bytes=2360) 109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118) 118 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES ULTS' (Cost=10 Card=50 Bytes=1000) 12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50) 136 TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C ost=1 Card=57344 Bytes=688128) 14 13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE) 155 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=9 Card=499 Bytes=22455) 16 15 INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID' (NON-UNIQUE) (Cost=2 Card=499) 174 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3 Card=2388115 Bytes=28657380) 18 17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ UE) (Cost=2 Card=2388115) 193 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42) 20 19 SORT (UNIQUE) (Cost=56 Card=7 Bytes=126
sql query optimization
Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result is not good. All the columns involved in the where clause have been indexed. b1 and b2 are bind variables that are passed in. select distinct observationlist.geneid, pval, score, Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906), proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget frommt.dualblastresults, mt.seqtable querySeq, isi.observationlist, isi.termobs where subjID = :b1 and queryID = QuerySeq.AASeqID and querySeq.use='Y' and querySeq.geneID=observationlist.geneid and curationStatus='E' and evidenceCode in (3000900,3000902,3000906) and observationlist.id=obsID and target='GeneID' and termobs.termid in (select termid from isi.arc where arctype in (299,300) start with termid = :b2 connect by prior termid=parenttermid) order by mt.blast.pvaltonumber(pval) asc, score desc, geneid, decode(proteomerefid, null, 0, 1) desc; -- This query typically returns 10 or less rows. mt.dualblastresults is a view, all others are tables. BTW, I need distinct and order by in the query. Here is the explain plan and row counts in tables and their definition. Anyone has any suggestions to make it run faster? TIA. Guang Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124 ) 10 SORT (ORDER BY) (Cost=715 Card=1 Bytes=124) 21 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124) 32 NESTED LOOPS (Cost=609 Card=1 Bytes=124) 43 NESTED LOOPS (Cost=553 Card=1 Bytes=118) 54 NESTED LOOPS (Cost=550 Card=1 Bytes=106) 65 NESTED LOOPS (Cost=280 Card=30 Bytes=1830) 76 VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168 Bytes=8232) 87 UNION-ALL 98 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES ULTS' (Cost=102 Card=118 Bytes=2360) 109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118) 118 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES ULTS' (Cost=10 Card=50 Bytes=1000) 12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50) 136 TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C ost=1 Card=57344 Bytes=688128) 14 13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE) 155 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=9 Card=499 Bytes=22455) 16 15 INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID' (NON-UNIQUE) (Cost=2 Card=499) 174 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3 Card=2388115 Bytes=28657380) 18 17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ UE) (Cost=2 Card=2388115) 193 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42) 20 19 SORT (UNIQUE) (Cost=56 Card=7 Bytes=126) 21 20 FILTER 22 21 CONNECT BY 23 22 INDEX (RANGE SCAN) OF 'ARC_TERMID' (NON-UNIQ UE) (Cost=1 Card=2 Bytes=12) 24 22 TABLE ACCESS (BY USER ROWID) OF 'ARC' 25 22 INDEX (RANGE SCAN) OF 'ARC_TYPETERMPARENT' ( UNIQUE) (Cost=3 Card=8 Bytes=144) SQL select count(*) from mt.dualblastresults; COUNT(*) -- 22332188 SQL select count(*) from mt.seqtable ; COUNT(*) -- 373505 SQL select count(*) from isi.observationlist; COUNT(*) -- 2290858 SQL select count(*) from isi.termobs; COUNT(*) -- 2388115 SQL select count(*) from isi.arc; COUNT(*) -- 207375 SQL desc mt.dualblastresults Name Null?Type - --- ID NUMBER QUERYIDNUMBER SUBJID NUMBER MATCHLEN NUMBER IDENTITY NUMBER POSITIVE NUMBER GAPNUMBER PVAL VARCHAR2(16) SCORE NUMBER QUERYSTART NUMBER QUERYEND
Re: sql query optimization
This is an interesting (and relatively complex) query with what I think are several opportunities to tune it. I'd probably spend some time looking at the following to see if they might help you out: 1) Look at the sub-select with the connect by clause... Try executing that query on it's own and get an idea of it's execution time and the number of rows returned for different bind variables. Depending on the number of distinct values of arctype there may be some scope to optimise this component. Possibly create a table containing on the arctype 299 and 300 records and then remove this clause from the query - this could avoid accessing the table at all. I have no idea if creating such a table is practical for your scenario though. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) 3) Are the distinct and order by clausing really needed. Often a distinct is included to hide a fault in the query (like a missing join or criteria) - distinct can be very expensive at times but since your query runs fairly fast you probably aren't removing many rows. How many rows does the query return with versus without the distinct clause? 4) In the order by clause is mt.blast.pvaltonumber(pval) This looks like a function call - if you have a way to avoid this function call you may see a performance increase. You could test this by creating a table which stores the calculated result already and modify the query (remember to index and analyze the same as the original table). Does this help? Is it practical to store the result? Again though, the benefit will be determined by the number of rows being ordered and the amount of query time spent doing this - for large data sets a function call is murder though. 5) Finally, I just realised at the last minute that DUALBLASTRESULTS appears to be a view. Try bypassing the view and going straight to the base tables with the most restrictive criteria you have. Sometimes Oracle doesn't handle views really well within queries. I've seen improvements where the entire logic of the view was moved within the query - it shouldn't have changed anything from a theoretical point of view but it did. Hopefully this gives you some options to look at. Regards, Mark. gmei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: sql query optimization .com 11/06/2003 07:59 Please respond to ORACLE-L Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result is not good. All the columns involved in the where clause have been indexed. b1 and b2 are bind variables that are passed in. select distinct observationlist.geneid, pval, score, Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906), proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget from mt.dualblastresults, mt.seqtable querySeq, isi.observationlist, isi.termobs wheresubjID = :b1 and queryID = QuerySeq.AASeqID and querySeq.use='Y' and querySeq.geneID=observationlist.geneid and curationStatus='E
Re: sql query optimization
All very good suggestions, but given the low elapsed time and cost figures, I suspect most of the time is taken up jumping around buffer cache locating and pinning blocks. What would help a lot is to eliminate the table access by index rowid by including all query columns in your indexes. There will be a hit on inserts/updates/deletes, but presumably an acceptable hit compared to your many many selects. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 11:54 AM This is an interesting (and relatively complex) query with what I think are several opportunities to tune it. I'd probably spend some time looking at the following to see if they might help you out: 1) Look at the sub-select with the connect by clause... Try executing that query on it's own and get an idea of it's execution time and the number of rows returned for different bind variables. Depending on the number of distinct values of arctype there may be some scope to optimise this component. Possibly create a table containing on the arctype 299 and 300 records and then remove this clause from the query - this could avoid accessing the table at all. I have no idea if creating such a table is practical for your scenario though. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) 3) Are the distinct and order by clausing really needed. Often a distinct is included to hide a fault in the query (like a missing join or criteria) - distinct can be very expensive at times but since your query runs fairly fast you probably aren't removing many rows. How many rows does the query return with versus without the distinct clause? 4) In the order by clause is mt.blast.pvaltonumber(pval) This looks like a function call - if you have a way to avoid this function call you may see a performance increase. You could test this by creating a table which stores the calculated result already and modify the query (remember to index and analyze the same as the original table). Does this help? Is it practical to store the result? Again though, the benefit will be determined by the number of rows being ordered and the amount of query time spent doing this - for large data sets a function call is murder though. 5) Finally, I just realised at the last minute that DUALBLASTRESULTS appears to be a view. Try bypassing the view and going straight to the base tables with the most restrictive criteria you have. Sometimes Oracle doesn't handle views really well within queries. I've seen improvements where the entire logic of the view was moved within the query - it shouldn't have changed anything from a theoretical point of view but it did. Hopefully this gives you some options to look at. Regards, Mark. gmei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: sql query optimization .com 11/06/2003 07:59 Please respond to ORACLE-L Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result is not good. All the columns involved in the where clause have been indexed. b1 and b2 are bind variables that are passed in. select distinct observationlist.geneid, pval, score, Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906), proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget from mt.dualblastresults, mt.seqtable querySeq, isi.observationlist, isi.termobs wheresubjID = :b1 and queryID = QuerySeq.AASeqID and querySeq.use='Y' and querySeq.geneID=observationlist.geneid and curationStatus='E' and evidenceCode in (3000900,3000902,3000906) and observationlist.id=obsID and target='GeneID' and termobs.termid in (select termid from isi.arc where arctype in (299,300) start with termid = :b2 connect by prior termid=parenttermid) order by mt.blast.pvaltonumber(pval) asc, score desc, geneid, decode(proteomerefid, null, 0, 1) desc; -- This query typically returns 10 or less rows. mt.dualblastresults is a view, all others are tables. BTW, I need distinct and order by in the query. Here is the explain plan and row counts in tables and their definition. Anyone has any suggestions to make it run faster? TIA
Re: sql query optimization
Hi: Thanks for your help and suggestions, Mark. I have done some of the things you suggested already. Please see my text below. On Tue, 10 Jun 2003, Mark Richard wrote: This is an interesting (and relatively complex) query with what I think are several opportunities to tune it. I'd probably spend some time looking at the following to see if they might help you out: 1) Look at the sub-select with the connect by clause... Try executing that query on it's own and get an idea of it's execution time and the number of rows returned for different bind variables. Depending on the number of distinct values of arctype there may be some scope to optimise this component. Possibly create a table containing on the arctype 299 and 300 records and then remove this clause from the query - this could avoid accessing the table at all. I have no idea if creating such a table is practical for your scenario though. I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? 3) Are the distinct and order by clausing really needed. Often a distinct is included to hide a fault in the query (like a missing join or criteria) - distinct can be very expensive at times but since your query runs fairly fast you probably aren't removing many rows. How many rows does the query return with versus without the distinct clause? distinct is needed because otherwise it would return duplicates. It's more like 5 rows with distinct and 12 without (typically). 4) In the order by clause is mt.blast.pvaltonumber(pval) This looks like a function call - if you have a way to avoid this function call you may see a performance increase. You could test this by creating a table which stores the calculated result already and modify the query (remember to index and analyze the same as the original table). Does this help? Is it practical to store the result? Again though, the benefit will be determined by the number of rows being ordered and the amount of query time spent doing this - for large data sets a function call is murder though. Yes, it is a function call (from a package), it basically take care of the situation that pval (for example) can be 2e-56 or e-37, I need a way of order them. I tried with decode but found it did not improve the performance. I even tried to remove order by and the performance is basically the same. This is probably because the number of rows returned is small. 5) Finally, I just realised at the last minute that DUALBLASTRESULTS appears to be a view. Try bypassing the view and going straight to the base tables with the most restrictive criteria you have. Sometimes Oracle doesn't handle views really well within queries. I've seen improvements where the entire logic of the view was moved within the query - it shouldn't have changed anything from a theoretical point of view but it did. I did try to replace the view with the base tables and the performance are the same. I have a bounch of other queries using this view and I always get the same performance when I replace it with base tables. So I think there probably isn't much I can do here. Again, thanks for your help. Guang Hopefully this gives you some options to look at. Regards, Mark. gmei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: sql query optimization .com 11/06/2003 07:59 Please respond to ORACLE-L Hi: I have been trying for two days to see if I could optimize this query without much success. One of the programs here calls this query many many times and I hope I could make it run faster. It typically take about 1 sec to get the result. I have tried using exists to replace in and the result is not good. All the columns involved in the where clause have been indexed. b1 and b2 are bind variables that are passed in. select distinct observationlist.geneid, pval, score, Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906), proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
Re: sql query optimization
Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the boost you need. The concatenated index relates to my uncertainty about how Oracle can use them for recursive SQL. I did a simple test - creating the following indexes: 1) Unique index on child 2) Non-unique index on parent 3) Unique index on parent, child 4) Unique index on child, parent The table only had a handful of rows but Oracle chose to use index 1 and index 3 for the query instead of index 2. On a table of significant volume (I used to work on very large recursive SQL statements at one point) I would suggest testing the indexing combinations to see what Oracle likes - then remove the rest. Also, the requirements are different if you are traversing the tree in both directions - you seem to only be going down the tree. Good luck. Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: sql query optimization .com 11/06/2003 12:34 Please respond to ORACLE-L I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? 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.com San 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).
SQL Query Help
i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab ID LastModDate -- --- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query Help
Add group by ID; as in: SELECT ID,MAX(LastModDate) FROM Tab group by ID; -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:15 PM To: Multiple recipients of list ORACLE-L Subject: SQL Query Help i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab IDLastModDate ----- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query Help
RB: Try : SELECT ID, LastModDate FROM Tab a WHERE TRUNC(LastModDate) = (Select MAX(TRUNC(LastModDate)) From Tab b) The explanation of the error message follows. RWB === |---+--- | | | |---+--- ---| |Error: ORA 937| | | | | |Text: not a single-group group function | | | | | |---| | | | | |Cause: A SELECT list cannot include both a group function, such as AVG, COUNT,| | | | | | MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column | | | | | |expression, unless the individual column expression is included in a | | | | | |GROUP BY clause. | | | | | |Action: Drop either the group function or the individual column expression | | | | | |from the SELECT list or add a GROUP BY clause that includes all| | | | | |individual column expressions listed. | | | | | |. | ---| Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] Mobile.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: SQL Query Help 05/28/2003 07:14 PM Please respond to ORACLE-L
RE: SQL Query Help
I had tried this as per Oracle Metalink Note.When I add the group by ID it will display both the records like 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM but I want to see the only the record with the latest time stamp like 1 4/23/2003 10:26:42 PM -Original Message- Sent: Wednesday, May 28, 2003 6:25 PM To: Multiple recipients of list ORACLE-L Add group by ID; as in: SELECT ID,MAX(LastModDate) FROM Tab group by ID; -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:15 PM To: Multiple recipients of list ORACLE-L Subject: SQL Query Help i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab IDLastModDate ----- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Murray, Margaret INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query Help
This will work.But the actual query that I have is a join b/w two tables and not as simple as it looks in the example I had given SELECT ID,LastModDate FROM Tab I bascially will have to include my complete main query in the sub query with TRUNC(MAX This might be costly on the performance. do we have any other approach. thanks -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:08 PM To: Multiple recipients of list ORACLE-L RB: Try : SELECT ID, LastModDate FROM Tab a WHERE TRUNC(LastModDate) = (Select MAX(TRUNC(LastModDate)) From Tab b) The explanation of the error message follows. RWB === |---+--- | | | |---+--- ---| |Error: ORA 937| | | | | |Text: not a single-group group function | | | | | |---| | | | | |Cause: A SELECT list cannot include both a group function, such as AVG, COUNT,| | | | | | MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column | | | | | |expression, unless the individual column expression is included in a | | | | | |GROUP BY clause. | | | | | |Action: Drop either the group function or the individual column expression | | | | | |from the SELECT list or add a GROUP BY clause that includes all| | | | | |individual column expressions listed. | | | | | |. | ---| Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] Mobile.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: SQL Query Help
Re: SQL Query Help
SELECT ID,MAX(LastModDate) FROM Tab where id = 1 group by ID; -- Daniel W. Fink http://www.optimaldba.com Basavaraja, Ravindra wrote: I had tried this as per Oracle Metalink Note.When I add the group by ID it will display both the records like 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM but I want to see the only the record with the latest time stamp like 1 4/23/2003 10:26:42 PM -Original Message- Sent: Wednesday, May 28, 2003 6:25 PM To: Multiple recipients of list ORACLE-L Add group by ID; as in: SELECT ID,MAX(LastModDate) FROM Tab group by ID; -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:15 PM To: Multiple recipients of list ORACLE-L Subject: SQL Query Help i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab ID LastModDate -- --- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query Help
This latest alternative will clearly only work when the latest id is known - I doubt this is reality. There was a mention that the actual query is more complex - without seeing that complexity it's hard to offer further advice. RWB has provided the correct approach normally taken. If the date is indexed then Oracle can determine the max date very easily - the query looks worse than reality. Another approach (which is normally worse though) is to order the query by date then wrap a select * from (inner_query} where rownum = 1 around it. It's clunky though and probably isn't what you are after. Daniel W. Fink [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Re: SQL Query Help [EMAIL PROTECTED] 29/05/2003 12:54 Please respond to ORACLE-L SELECT ID,MAX(LastModDate) FROM Tab where id = 1 group by ID; -- Daniel W. Fink http://www.optimaldba.com Basavaraja, Ravindra wrote: I had tried this as per Oracle Metalink Note.When I add the group by ID it will display both the records like 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM but I want to see the only the record with the latest time stamp like 1 4/23/2003 10:26:42 PM -Original Message- Sent: Wednesday, May 28, 2003 6:25 PM To: Multiple recipients of list ORACLE-L Add group by ID; as in: SELECT ID,MAX(LastModDate) FROM Tab group by ID; -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 8:15 PM To: Multiple recipients of list ORACLE-L Subject: SQL Query Help i have a query that returns 2 rows with one column being id and the other being date-time stamp. i want to select the row with the latest timestamp among those two records.they have difference id values SELECT ID,LastModDate FROM Tab ID LastModDate -- --- 2 1/20/2003 2:56:18 AM 1 4/23/2003 10:26:42 PM I want to modify the above query to return the row with id=1 which has the latest timestamp I tried this SELECT ID,MAX(LastModDate) FROM Tab getting this error OERR: ORA 937 not a single-group group function how do i get this work. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). 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
RE: sql query
Reddy There are a variety of techniques to accomplish this, and it is difficult to determine which will work best in your situation. Here is a link to a classic article on this topic by the great Jonathan Gennick who participates in this list from time to time. http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, March 31, 2003 12:09 AM To: Multiple recipients of list ORACLE-L hi, i have a table with indent number and quantity with 10 rows ex ind_no qty 1 10 1 12 1 30 1 15 1 30 2 12 2 30 2 15 2 30 2 25 2 I have to dispay it as rows like 1 10 12 30 15 30 2 12 30 15 30 25 please help, regds, Sudhakar ___ Odomos - the only mosquito protection outside 4 walls - Click here to know more! http://r.rediff.com/r?http://clients.rediff.com/odomos/Odomos.htmodomosw n -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sudhakar Reddy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
SQL Query -- List of managers
Using the EMP table as an example I want to create a query that will show a
list of employees and the mgrs above them. Like this:
ENAME MGRS
--- --
SMITH SMITH
SMITH FORD
SMITH JONES
SMITH KING
ALLEN ALLEN
ALLEN BLAKE
ALLEN KING
WARDWARD
WARDBLAKE
WARDKING
JONES JONES
JONES KING
MARTIN MARTIN
MARTIN BLAKE
MARTIN KING
BLAKE BLAKE
BLAKE KING
CLARK CLARK
CLARK KING
SCOTT SCOTT
SCOTT JONES
SCOTT KING
KINGKING
TURNER TURNER
TURNER BLAKE
TURNER KING
ADAMS ADAMS
ADAMS SCOTT
ADAMS JONES
ADAMS KING
JAMES JAMES
JAMES BLAKE
JAMES KING
FORDFORD
FORDJONES
FORDKING
MILLER MILLER
MILLER CLARK
MILLER KING
So far I've got it to this:
1 select lpad(' ',3*level-3)||ename org_char, leve
2 empno, mgr
3 from emp
4* connect by prior mgr = empno
ORG_CHAR LEVEL EMPNOMGR
--- -- -- --
SMITH1 7369 7902
FORD 2 7902 7566
JONES 3 7566 7839
KING4 7839
ALLEN1 7499 7698
BLAKE 2 7698 7839
KING 3 7839
WARD 1 7521 7698
BLAKE 2 7698 7839
KING 3 7839
JONES1 7566 7839
KING 2 7839
MARTIN 1 7654 7698
BLAKE 2 7698 7839
KING 3 7839
BLAKE1 7698 7839
KING 2 7839
CLARK1 7782 7839
KING 2 7839
SCOTT1 7788 7566
JONES 2 7566 7839
KING 3 7839
KING 1 7839
TURNER 1 7844 7698
BLAKE 2 7698 7839
KING 3 7839
ADAMS1 7876 7788
SCOTT 2 7788 7566
JONES 3 7566 7839
KING4 7839
JAMES1 7900 7698
BLAKE 2 7698 7839
KING 3 7839
FORD 1 7902 7566
JONES 2 7566 7839
KING 3 7839
MILLER 1 7934 7782
CLARK 2 7782 7839
KING 3 7839
39 rows selected.
Which brain cell am I missing today that will help me get what I want?
Thanks,
Jeff Eberhard
Database Administrator
Rolls-Royce Gear Systems
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Eberhard, Jeff
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Re: SQL Query -- List of managers
Hello 9i (ORA-01489! be aware): SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, 19)) ename , ename mgrs FROM emp CONNECT BY PRIOR mgr = empno / Eberhard, Jeff wrote: Using the EMP table as an example I want to create a query that will show a list of employees and the mgrs above them. Like this: ENAME MGRS --- -- SMITH SMITH SMITH FORD SMITH JONES SMITH KING .. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: SQL Query -- List of managers
Vladimir Begun wrote: SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, typo: ^RTRIM -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
sql query
hi, i have a table with indent number and quantity with 10 rows ex ind_no qty 1 10 1 12 1 30 1 15 1 30 2 12 2 30 2 15 2 30 2 25 2 I have to dispay it as rows like 1 10 12 30 15 30 2 12 30 15 30 25 please help, regds, Sudhakar ___ Odomos - the only mosquito protection outside 4 walls - Click here to know more! http://r.rediff.com/r?http://clients.rediff.com/odomos/Odomos.htmodomoswn -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sudhakar Reddy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: sql query: to_date() :ORA-01830: date format picture ends bef
Correction
I am using
select * from cust where
to_char(DATECREATED,'DD-MON-YY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
-Original Message-
From: Basavaraja, Ravindra
Sent: Tuesday, March 11, 2003 12:19 PM
To: 'Multiple recipients of list ORACLE-L'
Subject: sql query: to_date() :ORA-01830: date format picture ends before
converting entire input string
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
sql query: to_date() :ORA-01830: date format picture ends before
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
RE: sql query: to_date() :ORA-01830: date format picture ends bef
Title: RE: sql query: to_date() :ORA-01830: date format picture ends before
Why are you comparing a date to a char?
select *
from cust
where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
/
should work it appears that your NLS_DATE_FORMAT kicked in when your query tried to do an implicit char to date conversion (on the LHS part of the where clause)
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!
-Original Message-
From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 3:20 PM
To: Multiple recipients of list ORACLE-L
Subject: sql query: to_date() :ORA-01830: date format picture ends
before
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San 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).
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
RE: sql query: to_date() :ORA-01830: date format picture ends bef
What's the to_char for?
try
select * from cust where
DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
-Original Message-
Sent: Tuesday, March 11, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
before
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Ji
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
RE: sql query: to_date() :ORA-01830: date format picture ends bef
You are comparing CHAR to DATE, you would need one of the following
conditions
1- where DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
2- where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
to be able to complete the WHERE clause
Rick Weiss
Oracle DBA
-Original Message-
Sent: Tuesday, March 11, 2003 13:20
To: Multiple recipients of list ORACLE-L
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = to_date('01-FEB-03
07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Weiss, Rick
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Re: sql query: to_date() :ORA-01830: date format picture ends before
Basavaraja, Ravindra wrote:
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
You are comparing apples with pears.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Re: sql query: to_date() :ORA-01830: date format picture ends before
Why are trying to compare date to string: to_char(...) = to_date(...) ?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, March 11, 2003 3:20 PM
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Re: sql query: to_date() :ORA-01830: date format picture ends before
Hello Ravindra,
You should use to_char OR to_data not both.
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
Wednesday, March 12, 2003, 2:20:17 AM, you wrote:
BR Hi...
BR I am getting the following error with a query like this
BR select * from cust where
BR to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
BR to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
BR ORA-01830: date format picture ends before converting entire input string
BR What is the problem?
BR Thanks
BR --
BR Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Best regards,
Sergeymailto:[EMAIL PROTECTED]
ICQ 160079606
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sergey V Dolgov
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
RE: sql query: to_date() :ORA-01830: date format picture ends bef
Ravindra,
All the solutions given by others are correct.
The reason for the error is that: In your where clause the date that you are
converting to char, is re-converted to date for comparison with a date
value. This conversion uses Oracle's implicit date conversion, the implicit
date conversion uses current NLS setting which by default is of two digit
year and without time component.
So if you remove time and its format from your
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS'), it should work. Or if you
change your NLS settings to include time component the same command should
work.
HTH!
Aleem
-Original Message-
Sent: Wednesday, March 12, 2003 1:20 AM
To: Multiple recipients of list ORACLE-L
Subject:sql query: to_date() :ORA-01830: date format picture ends
before
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Abdul Aleem
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Sql query
I have this sql query. I am wondering why this query takes so long time, Do I need more conditions to make it run, or it it just that this query take so long time to run? Anything wrong with the query? Please help me with this. SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period, 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierarki.art_ugrp FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp WHERE PBK.underlag.underlagid=1100 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid AND PBK.VARUKORGEANREL_ulag.varutyp=0; Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: Sql query
Hi Roland, Is the query producing right results in the first place? If there are n tables in the FROM clause, there must be atleast n-1 joins in the where clause to avoid the Cartesian product (which definitely screws up performance and rarely produces correct results.) In the FROM clause of your query, there are 6 tables, whereas in the WHERE clause there is only 1 join condition. HTH. Regards, Charu -Original Message- [EMAIL PROTECTED] Sent: Wednesday, February 05, 2003 6:24 PM To: Multiple recipients of list ORACLE-L I have this sql query. I am wondering why this query takes so long time, Do I need more conditions to make it run, or it it just that this query take so long time to run? Anything wrong with the query? Please help me with this. SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period, 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar ki.art_ugrp FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp WHERE PBK.underlag.underlagid=1100 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid AND PBK.VARUKORGEANREL_ulag.varutyp=0; Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
Re: Sql query
There is no join condition between first two tables (PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG) and last four tables (rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 05, 2003 9:52 AM I have this sql query. I am wondering why this query takes so long time, Do I need more conditions to make it run, or it it just that this query take so long time to run? Anything wrong with the query? Please help me with this. SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period, 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar ki.art_ugrp FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp WHERE PBK.underlag.underlagid=1100 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid AND PBK.VARUKORGEANREL_ulag.varutyp=0; Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: Sql query
Roland, A quick look suggest you have cartesian joins unless rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp all have only one row. Iain Nicoll -Original Message- Sent: 05 February 2003 14:53 To: Multiple recipients of list ORACLE-L I have this sql query. I am wondering why this query takes so long time, Do I need more conditions to make it run, or it it just that this query take so long time to run? Anything wrong with the query? Please help me with this. SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period, 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar ki.art_ugrp FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp WHERE PBK.underlag.underlagid=1100 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid AND PBK.VARUKORGEANREL_ulag.varutyp=0; Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nicoll, Iain INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: Sql query
Hi Roland, I make a little modification to your query below, SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period, 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar ki.art_ugrp FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp WHERE PBK.underlag.underlagid=1100 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid ANDPBK.VARUKORGEANREL_ulag.varutyp=0 I think you miss to join another four tables, AND RIK2.VARE = RIK2.HIERARKI_TEKST AND RIK2.HIERARKI_TEKST = RIK2.ART_HIERARKI AND RIK2.ART_HIERARKI = PBK..SOTIMENT_VGRP; Hope this is what you mean. Rgrds, Sony -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 9:53 PM To: Multiple recipients of list ORACLE-L Subject: Sql query I have this sql query. I am wondering why this query takes so long time, Do I need more conditions to make it run, or it it just that this query take so long time to run? Anything wrong with the query? Please help me with this. SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period, 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hier arki.art_ugrp FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp WHERE PBK.underlag.underlagid=1100 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid AND PBK.VARUKORGEANREL_ulag.varutyp=0; Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
SQL query without UNION clause
Hi all,
How do I forumlate the below query without using the UNION clause?
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
RE: SQL query without UNION clause
You would be surprised to discover what you could do with OR and suitably placed
parentheses.
- Original Message -
From: Krishnaswamy, Ranganath
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Wed, 18 Dec 2002 23:53:44
Hi all,
How do I forumlate the below query without using
the UNION clause?
SELECT H1.OID
HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID =
HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID
HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND
H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an
Object-Relationship tool
called Cocobase which doesn't support the UNION
clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is
confidential and may be legally
privileged. It is intended solely for the
addressee. Access to this message
by anyone else is unauthorised. If you are not the
intended recipient, any
disclosure, copying, or distribution of the
message, or any action or
omission taken by you in reliance on it, is
prohibited and may be unlawful.
Please immediately contact the sender if you have
received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San 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).
---
--
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroul
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
RE: SQL query without UNION clause
Try this,
SELECT DISTINCT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND
((TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')) OR H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202'))
Regards
Naveen
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L
Hi all,
How do I forumlate the below query without using the UNION clause?
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
Re: SQL query without UNION clause
I think you can translate it as OR, but you have to use some DISTINCT on
output rows (because you use UNION and not UNION ALL).
Without DISTINCT it is like:
SELECT
H1.OID HISTORIEOID
,FAHRZEUG.AMTLICHESKENNZEICHEN
,FAHRZEUG.OID
,H1.PRODUKT
,H1.AUFTRAGSPOSITIONSNR
,H1.MYTECHOBJEKT
FROM FAHRZEUG, HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND ( (TO_DATE(H1.DATUMSTR,'-MM-DD') = (
select max(TO_DATE(H1.DATUMSTR,'-mm-dd')) from historie, FAHRZEUG
WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT
AND HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN ('HU', 'AU')
)
OR
H1.PRODUKT IN('DS', 'NFZ','BES', 'DekraSiegel', '70', '700202'))
;
And I think you can change
select max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
to
select TO_DATE(MAX(H1.DATUMSTR),'-MM-DD')
it can now use index on column H1.DATUMSTR (or create function based index)
JP
On Thursday 19 December 2002 08:53, you wrote:
Hi all,
How do I forumlate the below query without using the UNION clause?
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
--
Pruner Jan
[EMAIL PROTECTED]
http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San 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).
RE: Problem with SQL query
Ranganath, Apologies my reply directly to yourself got bounced for some reason. A couple of questions. How good a filter is the ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER = :TAGESABSCHLUSSNUMMER line. And is the subquery done simply to ensure no duplicates. Generally you want to drive from the table which has the best filter so if the above was a good filter and is indexed you'd probably want to start with that first. I've generally found that if you know the tables data and the order that you'd ideally like to join them then the use of the ordered hint along with a use_nl and the correct ordering of the from clause gives the optimal plan. Iain Nicoll -Original Message- [mailto:[EMAIL PROTECTED]] Sent: 17 December 2002 05:29 To: Multiple recipients of list ORACLE-L Hi all, I have the following query which is running slow and doing full table scan on DIENSTLEISTUNGSOBJEKT and FZGBRIEF tables. I have created all the neccessary indexes on these two tables apart from other tables. In this regard I request you to help me in tuning the below query so that the full table scan is avoided and the response time is less. select ZPAA.AUFTRAGSPOSITION.POSITIONSNUMMER, ZPAA.AUFTRAGSPOSITION.ZAHLUNGSART, ZPAA.AUFTRAGSPOSITION.ZAHLUNGSARTTYP, ZPAA.AUFTRAGSPOSITION.VORGANGSNUMMER, to_char( AUFTRAG.AUFTRAGERTEILTDATUM, 'DD-MM-'), ZPAA.AUFTRAG.AUFTRAGSNUMMER, ZPAA.FAHRZEUG.AMTLICHESKENNZEICHEN, ZPAA.FAHRZEUG.FZGHALTERNAME, ZPAA.FZGBRIEF.FAHRZEUGARTTEXT, ZPAA.RECHNUNG.PREISGRUPPE, NULL BETRAG, NULL QUITTUNGSNR, ZPAA.PLAKETTENERGEBNIS.PLAKETTENLAUFZEITSTR, ZPAA.PLAKETTENERGEBNIS.PLAKETTENZUTEILUNG, ZPAA.AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID, S.MATERIALNUMMER, S.ERZEUGNISNR, ZPAA.AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE, PARTNER_PRUEFORT.PARTNERNUMMER, PARTNER_MHS.PARTNERNUMMER, PARTNER_AG.PARTNERNUMMER, PARTNER_AG.NAME, PARTNER_SV.PARTNERNUMMER, S.SVDIENSTLEISTUNGOID, to_char(S.ERZEUGNISDATUM, 'DD-MM-'), S.ERGEBNIS, S.SPERGEBNIS, S.SPPLAKETTE, ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSGRUPPE, ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSBEZEICHNUNG, NULL BETRAGOFFEN, NULL PARTNERNUMMER, NULL ENDEZEIT, NULL TAGESABSCHLUSSNUMMER from ZPAA.FAHRZEUG, ZPAA.AUFTRAG, ZPAA.AUFTRAGSPOSITION, ZPAA.FZGBRIEF, ZPAA.PLAKETTENERGEBNIS, ZPAA.AUFTRAGSPOSPOSITIONSSTATUS, ZPAA.DIENSTLEISTUNGSOBJEKT D, ZPAA.RECHNUNG, ZPAA.SVDIENSTLEISTUNG S, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_PRUEFORT, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_MHS, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_AG, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_SV where ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER = :TAGESABSCHLUSSNUMMER AND AUFTRAG.VORGANGSNUMMER = AUFTRAGSPOSITION.VORGANGSNUMMER AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID = S.AUFTRAGSPOSITIONOID AND S.SVDIENSTLEISTUNGOID = D.SVDIENSTLEISTUNGOID AND D.DIENSTLEISTUNGSOBJEKTOID FAHRZEUG.DIENSTLEISTUNGSOBJEKTOID AND D.SEQNO = (SELECT E.SEQNO FROM ZPAA.DIENSTLEISTUNGSOBJEKT E ,ZPAA.SVDIENSTLEISTUNG S1 WHERE S1.SVDIENSTLEISTUNGOID = E.SVDIENSTLEISTUNGOID AND S1.SVDIENSTLEISTUNGOID = S.SVDIENSTLEISTUNGOID AND ROWNUM 2) AND FAHRZEUG.FAHRZEUGOID = FZGBRIEF.FAHRZEUGOID AND S.ERGEBNIS = PLAKETTENERGEBNIS.PLAKETTENERGEBNISOID(+) AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID RECHNUNG.AUFTRAGSPOSITIONOID AND AUFTRAGSPOSITION.AKTUELLERSTATUS AUFTRAGSPOSPOSITIONSSTATUS.AUFTRAGSPOSITIONSSTATUSOID AND (AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE = '6' OR AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE = '8') AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_PRUEFORT.AUFTRAGSPOSITIONOID(+) AND PARTNER_PRUEFORT.ROLLENTYP(+) = 'ZP' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_MHS.AUFTRAGSPOSITIONOID(+)
Problem with SQL query
Hi all, I have the following query which is running slow and doing full table scan on DIENSTLEISTUNGSOBJEKT and FZGBRIEF tables. I have created all the neccessary indexes on these two tables apart from other tables. In this regard I request you to help me in tuning the below query so that the full table scan is avoided and the response time is less. select ZPAA.AUFTRAGSPOSITION.POSITIONSNUMMER, ZPAA.AUFTRAGSPOSITION.ZAHLUNGSART, ZPAA.AUFTRAGSPOSITION.ZAHLUNGSARTTYP, ZPAA.AUFTRAGSPOSITION.VORGANGSNUMMER, to_char( AUFTRAG.AUFTRAGERTEILTDATUM, 'DD-MM-'), ZPAA.AUFTRAG.AUFTRAGSNUMMER, ZPAA.FAHRZEUG.AMTLICHESKENNZEICHEN, ZPAA.FAHRZEUG.FZGHALTERNAME, ZPAA.FZGBRIEF.FAHRZEUGARTTEXT, ZPAA.RECHNUNG.PREISGRUPPE, NULL BETRAG, NULL QUITTUNGSNR, ZPAA.PLAKETTENERGEBNIS.PLAKETTENLAUFZEITSTR, ZPAA.PLAKETTENERGEBNIS.PLAKETTENZUTEILUNG, ZPAA.AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID, S.MATERIALNUMMER, S.ERZEUGNISNR, ZPAA.AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE, PARTNER_PRUEFORT.PARTNERNUMMER, PARTNER_MHS.PARTNERNUMMER, PARTNER_AG.PARTNERNUMMER, PARTNER_AG.NAME, PARTNER_SV.PARTNERNUMMER, S.SVDIENSTLEISTUNGOID, to_char(S.ERZEUGNISDATUM, 'DD-MM-'), S.ERGEBNIS, S.SPERGEBNIS, S.SPPLAKETTE, ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSGRUPPE, ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSBEZEICHNUNG, NULL BETRAGOFFEN, NULL PARTNERNUMMER, NULL ENDEZEIT, NULL TAGESABSCHLUSSNUMMER from ZPAA.FAHRZEUG, ZPAA.AUFTRAG, ZPAA.AUFTRAGSPOSITION, ZPAA.FZGBRIEF, ZPAA.PLAKETTENERGEBNIS, ZPAA.AUFTRAGSPOSPOSITIONSSTATUS, ZPAA.DIENSTLEISTUNGSOBJEKT D, ZPAA.RECHNUNG, ZPAA.SVDIENSTLEISTUNG S, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_PRUEFORT, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_MHS, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_AG, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_SV where ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER = :TAGESABSCHLUSSNUMMER AND AUFTRAG.VORGANGSNUMMER = AUFTRAGSPOSITION.VORGANGSNUMMER AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID = S.AUFTRAGSPOSITIONOID AND S.SVDIENSTLEISTUNGOID = D.SVDIENSTLEISTUNGOID AND D.DIENSTLEISTUNGSOBJEKTOID FAHRZEUG.DIENSTLEISTUNGSOBJEKTOID AND D.SEQNO = (SELECT E.SEQNO FROM ZPAA.DIENSTLEISTUNGSOBJEKT E ,ZPAA.SVDIENSTLEISTUNG S1 WHERE S1.SVDIENSTLEISTUNGOID = E.SVDIENSTLEISTUNGOID AND S1.SVDIENSTLEISTUNGOID = S.SVDIENSTLEISTUNGOID AND ROWNUM 2) AND FAHRZEUG.FAHRZEUGOID = FZGBRIEF.FAHRZEUGOID AND S.ERGEBNIS = PLAKETTENERGEBNIS.PLAKETTENERGEBNISOID(+) AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID RECHNUNG.AUFTRAGSPOSITIONOID AND AUFTRAGSPOSITION.AKTUELLERSTATUS AUFTRAGSPOSPOSITIONSSTATUS.AUFTRAGSPOSITIONSSTATUSOID AND (AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE = '6' OR AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE = '8') AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_PRUEFORT.AUFTRAGSPOSITIONOID(+) AND PARTNER_PRUEFORT.ROLLENTYP(+) = 'ZP' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_MHS.AUFTRAGSPOSITIONOID(+) AND PARTNER_MHS.ROLLENTYP(+) = 'MHS' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_AG.AUFTRAGSPOSITIONOID(+) AND PARTNER_AG.ROLLENTYP(+) = 'AG' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_SV.AUFTRAGSPOSITIONOID(+) AND PARTNER_SV.ROLLENTYP(+) = 'ZT' Operation Object Name RowsBytes CostTQ In/Out PStart PStop SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS OUTER NESTED LOOPS OUTER
RE: Problem with SQL query
In the explain plan's first line SELECT STATEMENT Hint=HINT: RULE But i don't see any rule hint in the SQL statement you have sent. Analyze the tables/indexes, use a choose hint and run the SQL plan again. What version you are on? what is the value of optimizer_mode, optimizer_goal Regards Naveen -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 10:59 AM To: Multiple recipients of list ORACLE-L Hi all, I have the following query which is running slow and doing full table scan on DIENSTLEISTUNGSOBJEKT and FZGBRIEF tables. I have created all the neccessary indexes on these two tables apart from other tables. In this regard I request you to help me in tuning the below query so that the full table scan is avoided and the response time is less. select ZPAA.AUFTRAGSPOSITION.POSITIONSNUMMER, ZPAA.AUFTRAGSPOSITION.ZAHLUNGSART, ZPAA.AUFTRAGSPOSITION.ZAHLUNGSARTTYP, ZPAA.AUFTRAGSPOSITION.VORGANGSNUMMER, to_char( AUFTRAG.AUFTRAGERTEILTDATUM, 'DD-MM-'), ZPAA.AUFTRAG.AUFTRAGSNUMMER, ZPAA.FAHRZEUG.AMTLICHESKENNZEICHEN, ZPAA.FAHRZEUG.FZGHALTERNAME, ZPAA.FZGBRIEF.FAHRZEUGARTTEXT, ZPAA.RECHNUNG.PREISGRUPPE, NULL BETRAG, NULL QUITTUNGSNR, ZPAA.PLAKETTENERGEBNIS.PLAKETTENLAUFZEITSTR, ZPAA.PLAKETTENERGEBNIS.PLAKETTENZUTEILUNG, ZPAA.AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID, S.MATERIALNUMMER, S.ERZEUGNISNR, ZPAA.AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE, PARTNER_PRUEFORT.PARTNERNUMMER, PARTNER_MHS.PARTNERNUMMER, PARTNER_AG.PARTNERNUMMER, PARTNER_AG.NAME, PARTNER_SV.PARTNERNUMMER, S.SVDIENSTLEISTUNGOID, to_char(S.ERZEUGNISDATUM, 'DD-MM-'), S.ERGEBNIS, S.SPERGEBNIS, S.SPPLAKETTE, ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSGRUPPE, ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSBEZEICHNUNG, NULL BETRAGOFFEN, NULL PARTNERNUMMER, NULL ENDEZEIT, NULL TAGESABSCHLUSSNUMMER from ZPAA.FAHRZEUG, ZPAA.AUFTRAG, ZPAA.AUFTRAGSPOSITION, ZPAA.FZGBRIEF, ZPAA.PLAKETTENERGEBNIS, ZPAA.AUFTRAGSPOSPOSITIONSSTATUS, ZPAA.DIENSTLEISTUNGSOBJEKT D, ZPAA.RECHNUNG, ZPAA.SVDIENSTLEISTUNG S, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_PRUEFORT, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_MHS, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_AG, ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_SV where ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER = :TAGESABSCHLUSSNUMMER AND AUFTRAG.VORGANGSNUMMER = AUFTRAGSPOSITION.VORGANGSNUMMER AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID = S.AUFTRAGSPOSITIONOID AND S.SVDIENSTLEISTUNGOID = D.SVDIENSTLEISTUNGOID AND D.DIENSTLEISTUNGSOBJEKTOID FAHRZEUG.DIENSTLEISTUNGSOBJEKTOID AND D.SEQNO = (SELECT E.SEQNO FROM ZPAA.DIENSTLEISTUNGSOBJEKT E ,ZPAA.SVDIENSTLEISTUNG S1 WHERE S1.SVDIENSTLEISTUNGOID = E.SVDIENSTLEISTUNGOID AND S1.SVDIENSTLEISTUNGOID = S.SVDIENSTLEISTUNGOID AND ROWNUM 2) AND FAHRZEUG.FAHRZEUGOID = FZGBRIEF.FAHRZEUGOID AND S.ERGEBNIS = PLAKETTENERGEBNIS.PLAKETTENERGEBNISOID(+) AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID RECHNUNG.AUFTRAGSPOSITIONOID AND AUFTRAGSPOSITION.AKTUELLERSTATUS AUFTRAGSPOSPOSITIONSSTATUS.AUFTRAGSPOSITIONSSTATUSOID AND (AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE = '6' OR AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE = '8') AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_PRUEFORT.AUFTRAGSPOSITIONOID(+) AND PARTNER_PRUEFORT.ROLLENTYP(+) = 'ZP' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_MHS.AUFTRAGSPOSITIONOID(+) AND PARTNER_MHS.ROLLENTYP(+) = 'MHS' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_AG.AUFTRAGSPOSITIONOID(+) AND PARTNER_AG.ROLLENTYP(+) = 'AG' AND AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID PARTNER_SV.AUFTRAGSPOSITIONOID(+) AND
Re: Sql query
sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Santosh Varma To: Multiple recipients of list ORACLE-L Sent: Friday, October 04, 2002 2:23 AM Subject: RE: Sql query Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen NahataSent: Thursday, October 03, 2002 9:03 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Sql query Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL edWrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_maxSQL / NAME--Naveen SQL I can run your query, then what's the problem?SQL Regards Naveen -Original Message-From: Santosh Varma [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 2:53 PMTo: Multiple recipients of list ORACLE-LSubject: Sql query cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from
RE: Sql query
HELP! -Original Message- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL / NAME -- Naveen SQL I can run your query, then what's the problem? SQL Regards Naveen -Original Message- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this
RE: Sql query
ARE YOU AN IDIOT -Original Message- Sent: 04 October 2002 16:13 To: Multiple recipients of list ORACLE-L HELP! -Original Message- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL / NAME -- Naveen SQL I can run your query, then what's the problem? SQL Regards Naveen -Original Message- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
RE: Sql query
Obligatory... ARE YOU AN IDIOT? -Original Message- Sent: Friday, October 04, 2002 11:13 AM To: Multiple recipients of list ORACLE-L HELP! -Original Message- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL / NAME -- Naveen SQL I can run your query, then what's the problem? SQL Regards Naveen -Original Message- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: Sql query
HELP! is this also sql server 7 command? :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 11:13 AM HELP! -Original Message- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL / NAME -- Naveen SQL I can run your query, then what's the problem? SQL Regards Naveen -Original Message- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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
Re: Sql query
Are you an idiot? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 04, 2002 11:13 AM HELP! -Original Message- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL / NAME -- Naveen SQL I can run your query, then what's the problem? SQL Regards Naveen -Original Message- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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
RE: Sql query
ARE YOU AN IDIOT! ;o) -Original Message- Sent: Friday, October 04, 2002 10:13 AM To: Multiple recipients of list ORACLE-L HELP! -Original Message- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL / NAME -- Naveen SQL I can run your query, then what's the problem? SQL Regards Naveen -Original Message- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
SQL Query
I have a table test((NAME VARCHAR2(10),AGE NUMBER(2)); data of the table is NAME AGE-- -ANAND 1BALU2CHANDU3DAVID4 I want a query which give me the result as NAME AGE-- -ANAND 4BALU3CHANDU2DAVID1 Can any body pl. help me. Anand KumarITW Signode India Ltd
Re: SQL Query
3 ïËÔÑÂÒØ 2002 12:03, ÷Ù ÎÁÐÉÓÁÌÉ:
I have a table test((NAME VARCHAR2(10),AGE NUMBER(2));
data of the table is
NAME AGE
-- -
ANAND 1
BALU 2
CHANDU3
DAVID4
I want a query which give me the result as
NAME AGE
---
ANAND 4
BALU 3
CHANDU 2
DAVID 1
select NAME, 5-AGE AGE from test;
Are you want that or general revers of AGE ?
Can any body pl. help me.
Anand Kumar
ITW Signode India Ltd
--
÷ÓÅÇÏ ÈÏÒÏÛÅÇÏ
íÉÈÁÉÌ é×ÁÎÏ×
åy« ±ç
ê ~'jS Ä,PÛiÿü0ÂÚ}ª¢`.¶+2)!j)H½©è¼DNh¯jz/µ×«j»
jТ·#^·
+'«¾' ³Î|ç9Óa¶Úÿ
+0}«\Ü¢d8'è®x1¨¥x%ËZÜ n,¶)à±êïǬND0åDÊ«±é_~º¶¬¨¥x%ËlzwZCY²Æ zÚ ËFº»j×·'(z-xEÀ
+ ;)zYb
.+-êîjwbØ^ë,j86Énuæ¥w¢{Zx§CRP Ä.í
éÚê ¨¥x%Ër¢ìÛhmêÞÞuúè.¬Ê,zwm
áÄ,÷(f§uú+¢Ø^®)ߢ¹¶*') ²æìr¸x
Sql query
cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
why do u want such a query? -Original Message-From: Anand Kumar N [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 1:33 PMTo: Multiple recipients of list ORACLE-LSubject: SQL Query I have a table test((NAME VARCHAR2(10),AGE NUMBER(2)); data of the table is NAME AGE-- -ANAND 1BALU2CHANDU3DAVID4 I want a query which give me the result as NAME AGE-- -ANAND 4BALU3CHANDU2DAVID1 Can any body pl. help me. Anand KumarITW Signode India Ltd
RE: SQL Query
TRY THIS : SELECT NAME, 4-AGE+1 FROM test; --- Naveen Nahata [EMAIL PROTECTED] wrote: why do u want such a query? -Original Message- Sent: Thursday, October 03, 2002 1:33 PM To: Multiple recipients of list ORACLE-L I have a table test((NAME VARCHAR2(10),AGE NUMBER(2)); data of the table is NAME AGE --- ANAND 1 BALU 2 CHANDU3 DAVID4 I want a query which give me the result as NAME AGE --- ANAND 4 BALU 3 CHANDU 2 DAVID 1 Can any body pl. help me. Anand Kumar ITW Signode India Ltd __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ASHRAF SALAYMEH INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
the below query adds/substracts the value in the age column... SELECT NAME, 4-AGE+1 FROM test; does not work.. -Original Message- SALAYMEH Sent: Thursday, October 03, 2002 5:28 PM To: Multiple recipients of list ORACLE-L TRY THIS : SELECT NAME, 4-AGE+1 FROM test; --- Naveen Nahata [EMAIL PROTECTED] wrote: why do u want such a query? -Original Message- Sent: Thursday, October 03, 2002 1:33 PM To: Multiple recipients of list ORACLE-L I have a table test((NAME VARCHAR2(10),AGE NUMBER(2)); data of the table is NAME AGE --- ANAND 1 BALU 2 CHANDU3 DAVID4 I want a query which give me the result as NAME AGE --- ANAND 4 BALU 3 CHANDU 2 DAVID 1 Can any body pl. help me. Anand Kumar ITW Signode India Ltd __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ASHRAF SALAYMEH INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Santosh, If you could tell why do you want the ages be reversed? Aleem -Original Message- Sent: Thursday, October 03, 2002 5:35 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Query the below query adds/substracts the value in the age column... SELECT NAME, 4-AGE+1 FROM test; does not work.. -Original Message- SALAYMEH Sent: Thursday, October 03, 2002 5:28 PM To: Multiple recipients of list ORACLE-L TRY THIS : SELECT NAME, 4-AGE+1 FROM test; --- Naveen Nahata [EMAIL PROTECTED] wrote: why do u want such a query? -Original Message- Sent: Thursday, October 03, 2002 1:33 PM To: Multiple recipients of list ORACLE-L I have a table test((NAME VARCHAR2(10),AGE NUMBER(2)); data of the table is NAME AGE --- ANAND 1 BALU 2 CHANDU3 DAVID4 I want a query which give me the result as NAME AGE --- ANAND 4 BALU 3 CHANDU 2 DAVID 1 Can any body pl. help me. Anand Kumar ITW Signode India Ltd __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ASHRAF SALAYMEH INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
huh? new value ANAND 1 4-1+1 = 4 BALU 2 4-2+1 = 3 CHANDU 3 4-3+1 = 2 DAVID 4 4-4+1 = 1 looks right to me. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 03, 2002 8:35 AM To: Multiple recipients of list ORACLE-L the below query adds/substracts the value in the age column... SELECT NAME, 4-AGE+1 FROM test; does not work.. -Original Message- SALAYMEH Sent: Thursday, October 03, 2002 5:28 PM To: Multiple recipients of list ORACLE-L TRY THIS : SELECT NAME, 4-AGE+1 FROM test; --- Naveen Nahata [EMAIL PROTECTED] wrote: why do u want such a query? -Original Message- Sent: Thursday, October 03, 2002 1:33 PM To: Multiple recipients of list ORACLE-L I have a table test((NAME VARCHAR2(10),AGE NUMBER(2)); data of the table is NAME AGE --- ANAND 1 BALU 2 CHANDU3 DAVID4 I want a query which give me the result as NAME AGE --- ANAND 4 BALU 3 CHANDU 2 DAVID 1 Can any body pl. help me. Anand Kumar ITW Signode India Ltd __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ASHRAF SALAYMEH INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query
Santosh, Till now you haven't exactly specified what you want and WHY you want such a thing. So I can only make a guess at what you want. If you want the age of the last record to be shown with the name of the first record and so on, then following is the query: SQL SELECT * FROM test; NAME AGE -- -- ANAND 1 BALU2 CHANDU 3 DAVID 4 SQL SELECT t1.name, t2.age 2 FROM (SELECT rownum r1, name FROM test) t1 3 , (SELECT rownum r2, age FROM test) t2 4 WHERE t1.r1 + t2.r2 - 1 = (SELECT 5 count(*) FROM test) 6 / NAME AGE -- -- DAVID 1 CHANDU 2 BALU3 ANAND 4 SQL I'm stupid! SQL SQL You are stupid!! unknown command beginning You are st... - rest of line ignored. Regards Naveen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San 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).
RE: Sql query
Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL edWrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_maxSQL / NAME--Naveen SQL I can run your query, then what's the problem?SQL Regards Naveen -Original Message-From: Santosh Varma [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 2:53 PMTo: Multiple recipients of list ORACLE-LSubject: Sql query cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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).
RE: Sql query
Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen NahataSent: Thursday, October 03, 2002 9:03 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Sql query Santosh, your query is working. See below SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL insert into client values(1, 'Naveen'); 1 row created. SQL insert into client values(2, 'Santosh'); 1 row created. SQL insert into project values(1, 'Oracle', 1); 1 row created. SQL insert into project values(2, 'Java', 1); 1 row created. SQL insert into project values(3, 'SQL', 2); 1 row created. SQL commit; Commit complete. SQL edWrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_maxSQL / NAME--Naveen SQL I can run your query, then what's the problem?SQL Regards Naveen -Original Message-From: Santosh Varma [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 2:53 PMTo: Multiple recipients of list ORACLE-LSubject: Sql query cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San 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).
RE: SQL Query tuning help
Thank you Stephane! Your final idea of FIRST_ROWS as a winner! Oracle slapped the data back in just a second. Thanks everyone for the ideas to try. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 3:42 PM To: Multiple recipients of list ORACLE-L DENNIS WILLIAMS wrote: I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Dennis, I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am. I would try things such as SELECT am.lid, am.name FROM am WHERE am.lid in (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') AND am.active = 1 ORDER BY am.name which may give the same plan as your first example; if this is the case, perhaps that SELECT am.lid, am.name FROM (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') x, am WHERE am.lid = x.lid AND am.active = 1 ORDER BY am.name will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS hint. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author
SQL Query tuning help
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Query tuning help
Dennis, If you use the ordered hint and have sa then so then am and also hint to use the index on sa(ret) then I think that would be about the best as you'd be starting with the best filter ie 1.3m/281 giving less than 5000 on average (assuming ret is indexed). I don't know if you'd have to through in an use_nl also. Iain Nicoll -Original Message- Sent: Tuesday, September 10, 2002 8:19 PM To: Multiple recipients of list ORACLE-L I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SQL Query tuning help
DENNIS WILLIAMS wrote: I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Dennis, I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am. I would try things such as SELECT am.lid, am.name FROM am WHERE am.lid in (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') AND am.active = 1 ORDER BY am.name which may give the same plan as your first example; if this is the case, perhaps that SELECT am.lid, am.name FROM (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') x, am WHERE am.lid = x.lid AND am.active = 1 ORDER BY am.name will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS hint. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SQL Query tuning help
Dennis, What is the distribution of sa.ret? I didn't see it included in an index. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 12:18 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Query tuning help I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
