RE: order by
> Aside from SQL brain-teasers, I cannot imagine a practical > use for ORDER BY > <>. If your real number is an integer, then that would mean order by the column in that position in the select list. Sorry If I missed this. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: order by
Thanks Justin - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 02, 2004 00:39 > At 10:44 AM 1/1/2004, you wrote: > >I was curious to find out why all the other sorts work the same but order by > >dbms_random.value ( which also returns a number like sqrt (3.14) ) behaves > >totally different. Any ideas ? > > When you order by dbms_random, Oracle will generate a random number for > each row and sort on those random numbers. When you order by a constant > real value, Oracle has no way to order the results, so they will be > returned in whatever order they are retrieved from the database. > > >Also could you please tell me what does it mean when someone says order by > > . An integer constant would refer to the > >positional parameter but what about a real value which is not an integer ? > > Aside from SQL brain-teasers, I cannot imagine a practical use for ORDER BY > <>. > > > Justin Cave > Distributed Database Consulting > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Justin Cave > 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: <[EMAIL PROTECTED] 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: order by
At 10:44 AM 1/1/2004, you wrote: I was curious to find out why all the other sorts work the same but order by dbms_random.value ( which also returns a number like sqrt (3.14) ) behaves totally different. Any ideas ? When you order by dbms_random, Oracle will generate a random number for each row and sort on those random numbers. When you order by a constant real value, Oracle has no way to order the results, so they will be returned in whatever order they are retrieved from the database. Also could you please tell me what does it mean when someone says order by . An integer constant would refer to the positional parameter but what about a real value which is not an integer ? Aside from SQL brain-teasers, I cannot imagine a practical use for ORDER BY <>. Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Justin Cave 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: order by
I was curious to find out why all the other sorts work the same but order by dbms_random.value ( which also returns a number like sqrt (3.14) ) behaves totally different. Any ideas ? Also could you please tell me what does it mean when someone says order by . An integer constant would refer to the positional parameter but what about a real value which is not an integer ? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 01, 2004 21:34 > It is for the same reason that 'select empno from emp' without > and order by, also returns the same results. > > Take a look at $ORACLE_HOME/sqlplus/demo/demobld.sql > > Didn't you ask this same question earlier this week? > > Jared > > On Thu, 2004-01-01 at 01:14, [EMAIL PROTECTED] wrote: > > List can you please explain to me why > > > > select empno from emp > > order by empno ; > > > > is the same as > > > > select empno from emp > > order by sqrt(3.14); > > > > but not the same as > > > > select empno from emp > > order by dbms_random.value; > > > > What does sort by a random value do ? and why isn't dbms_random.value in > > the documentation for 9i Release 2 ? > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Monday, December 29, 2003 03:34 > > > > > > > [EMAIL PROTECTED] wrote: > > > > > > > > Hello list, > > > > Can someone please explain to me why the following order by clauses are > > > > valid and yield the same results : > > > > > > > > select empno, deptno from emp > > > > order by sqrt (1) ; > > > > > > > > and > > > > > > > > select empno, deptno from emp > > > > order by sqrt ( 3.14234 ) ; > > > > > > > > The docs say that in the order by clause you could specify only (a) > > column > > > > names or (b) positional parameters or (c) expressions involving the > > columns > > > > > > > > > > A constant falls under the c) category. It's an expression, which > > > involves anything you want. That said, I fail to see any practical use > > > . > > > > > > -- > > > 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). > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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: Jared Still > 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: <[EMAIL PROTECTED] 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: order by
It is for the same reason that 'select empno from emp' without and order by, also returns the same results. Take a look at $ORACLE_HOME/sqlplus/demo/demobld.sql Didn't you ask this same question earlier this week? Jared On Thu, 2004-01-01 at 01:14, [EMAIL PROTECTED] wrote: > List can you please explain to me why > > select empno from emp > order by empno ; > > is the same as > > select empno from emp > order by sqrt(3.14); > > but not the same as > > select empno from emp > order by dbms_random.value; > > What does sort by a random value do ? and why isn't dbms_random.value in > the documentation for 9i Release 2 ? > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, December 29, 2003 03:34 > > > > [EMAIL PROTECTED] wrote: > > > > > > Hello list, > > > Can someone please explain to me why the following order by clauses are > > > valid and yield the same results : > > > > > > select empno, deptno from emp > > > order by sqrt (1) ; > > > > > > and > > > > > > select empno, deptno from emp > > > order by sqrt ( 3.14234 ) ; > > > > > > The docs say that in the order by clause you could specify only (a) > column > > > names or (b) positional parameters or (c) expressions involving the > columns > > > > > > > A constant falls under the c) category. It's an expression, which > > involves anything you want. That said, I fail to see any practical use > > . > > > > -- > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Jared Still 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: order by
List can you please explain to me why select empno from emp order by empno ; is the same as select empno from emp order by sqrt(3.14); but not the same as select empno from emp order by dbms_random.value; What does sort by a random value do ? and why isn't dbms_random.value in the documentation for 9i Release 2 ? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 03:34 > [EMAIL PROTECTED] wrote: > > > > Hello list, > > Can someone please explain to me why the following order by clauses are > > valid and yield the same results : > > > > select empno, deptno from emp > > order by sqrt (1) ; > > > > and > > > > select empno, deptno from emp > > order by sqrt ( 3.14234 ) ; > > > > The docs say that in the order by clause you could specify only (a) column > > names or (b) positional parameters or (c) expressions involving the columns > > > > A constant falls under the c) category. It's an expression, which > involves anything you want. That said, I fail to see any practical use > . > > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: order by
[EMAIL PROTECTED] wrote: > > Hello list, > Can someone please explain to me why the following order by clauses are > valid and yield the same results : > > select empno, deptno from emp > order by sqrt (1) ; > > and > > select empno, deptno from emp > order by sqrt ( 3.14234 ) ; > > The docs say that in the order by clause you could specify only (a) column > names or (b) positional parameters or (c) expressions involving the columns > A constant falls under the c) category. It's an expression, which involves anything you want. That said, I fail to see any practical use . -- 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: order by
Saturday, December 27, 2003, 11:14:25 AM, [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote:
bnini> Hello list, Can someone please explain to me why the
bnini> following order by clauses are valid and yield the
bnini> same results :
bnini> select empno, deptno from emp
bnini> order by sqrt (1) ;
The clauses are constants. I'm not entirely convinced that
you'd always get the "same results". Think of adding sqrt(1)
to each row returned by the query, and then sorting the rows
on that column. Within that sort, you could end up with many
different suborderings. For example, both the following
result sets meet the sort criteria:
empno deptno [sqrt(1)]
1 21
2 31
empno deptno [sqrt(1)]
2 31
1 21
bnini> The docs say that in the order by clause you could specify only (a) column
bnini> names or (b) positional parameters or (c) expressions involving the columns
This is just a minor doc flaw. Probably the writer didn't
think about expressions not involving columns. Once you get
into expressions, it'd be pretty difficult to enforce a rule
that an expression needed to involve a column in any
meaningful way. For example:
select empno, deptno from emp
order by substr('1' || to_char(empno),1,1);
This particular ORDER BY expression manages to include a
column in a completely useless manner.
Probably the doc-writer should have used different words,
but the thought that someone would sort by a constant was
likely far from the writer's mind.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to [EMAIL PROTECTED] and
include the word "subscribe" in either the subject or body.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
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: order by
Because both expressions evaluate to 1 and cause the output to be sorted by the first column? Oracle seems to ignore the values after the decimal point. SQL> select a, c from x 2 where rownum < 10 3 order by 1.7; A C-- - 4861 Y 4862 N 4863 Y 4864 N 4865 Y 4866 N 4867 Y 4868 N 4869 Y 9 rows selected. SQL> select a, c from x 2 where rownum < 10 3 order by 2.7; A C-- - 4862 N 4864 N 4868 N 4866 N 4861 Y 4869 Y 4867 Y 4865 Y 4863 Y 9 rows selected. Gudmundur - Original Message - From: <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, December 27, 2003 4:14 PM Subject: order by > Hello list,> Can someone please explain to me why the following order by clauses are> valid and yield the same results :> > select empno, deptno from emp> order by sqrt (1) ;> > and> > select empno, deptno from emp> order by sqrt ( 3.14234 ) ;> > The docs say that in the order by clause you could specify only (a) column> names or (b) positional parameters or (c) expressions involving the columns> > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net> -- > Author: <[EMAIL PROTECTED]> 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).>
order by
Hello list, Can someone please explain to me why the following order by clauses are valid and yield the same results : select empno, deptno from emp order by sqrt (1) ; and select empno, deptno from emp order by sqrt ( 3.14234 ) ; The docs say that in the order by clause you could specify only (a) column names or (b) positional parameters or (c) expressions involving the columns -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: Cannot use Order By with XMLAGG()
I found the issue. I was only running 9.2.0.1 and it successfully worked on 9.2.0.3.
I know this will help someone in the future. Good luck!
On Mon, Oct 06, 2003 at 08:49:30AM -0800, Jake Johnson wrote:
> No, the correct field is atrb_srt_ord. I also tried order by 1 and that didn't work
> either. The field names are in the describe below.
>
> Thanks
> Jake
>
>
> On Fri, Oct 03, 2003 at 01:29:33PM -0800, Dave Hau wrote:
> > Is it because you misspelled "atrb_srt_ord" as "atrb_str_ord" in the
> > ORDER BY clause?
> >
> > HTH,
> >
> > Dave
> >
> >
> >
> >
> >
> > [EMAIL PROTECTED] wrote:
> > >Hello,
> > >I cannot use order by in with my xmlagg clause. Any ideas???
> > >
> > >Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
> > >Production
> > >With the OLAP and Oracle Data Mining options
> > >JServer Release 9.2.0.1.0 - Production
> > >
> > >
> > >SELECT XMLELEMENT("Department",
> > > XMLAGG(
> > >XMLELEMENT("Employee", e.atrb_cd||' '||e.vw_cd)
> > >ORDER BY atrb_str_ord --this works if I -- it out
> > >))
> > > as "Dept_list"
> > >FROM dim_vw_atrb e;
> > >
> > >QL> desc dim_vw_atrb;
> > > Name Null?Type
> > > -
> > >
> > > CLNT_CD NOT NULL NUMBER(2)
> > > DIM_CDNOT NULL NUMBER(2)
> > > VW_CD NOT NULL NUMBER(2)
> > > ATRB_CD NOT NULL NUMBER(3)
> > > ATRB_SRT_ORD NOT NULL NUMBER(2)
> > > CRE_DTNOT NULL DATE
> > > CRE_IDSID NOT NULL VARCHAR2(8)
> > > LAST_MOD_DT NOT NULL DATE
> > > LAST_MOD_IDSIDNOT NULL VARCHAR2(8)
> > >
> > >Thanks,
> > >Jake
> > >
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Dave Hau
> > 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: Jake Johnson
> 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: Jake Johnson
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: Cannot use Order By with XMLAGG()
No, the correct field is atrb_srt_ord. I also tried order by 1 and that didn't work
either. The field names are in the describe below.
Thanks
Jake
On Fri, Oct 03, 2003 at 01:29:33PM -0800, Dave Hau wrote:
> Is it because you misspelled "atrb_srt_ord" as "atrb_str_ord" in the
> ORDER BY clause?
>
> HTH,
>
> Dave
>
>
>
>
>
> [EMAIL PROTECTED] wrote:
> >Hello,
> >I cannot use order by in with my xmlagg clause. Any ideas???
> >
> >Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
> >Production
> >With the OLAP and Oracle Data Mining options
> >JServer Release 9.2.0.1.0 - Production
> >
> >
> >SELECT XMLELEMENT("Department",
> > XMLAGG(
> >XMLELEMENT("Employee", e.atrb_cd||' '||e.vw_cd)
> >ORDER BY atrb_str_ord --this works if I -- it out
> >))
> > as "Dept_list"
> >FROM dim_vw_atrb e;
> >
> >QL> desc dim_vw_atrb;
> > Name Null?Type
> > -
> >
> > CLNT_CD NOT NULL NUMBER(2)
> > DIM_CDNOT NULL NUMBER(2)
> > VW_CD NOT NULL NUMBER(2)
> > ATRB_CD NOT NULL NUMBER(3)
> > ATRB_SRT_ORD NOT NULL NUMBER(2)
> > CRE_DTNOT NULL DATE
> > CRE_IDSID NOT NULL VARCHAR2(8)
> > LAST_MOD_DT NOT NULL DATE
> > LAST_MOD_IDSIDNOT NULL VARCHAR2(8)
> >
> >Thanks,
> >Jake
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Dave Hau
> 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: Jake Johnson
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: Cannot use Order By with XMLAGG()
Is it because you misspelled "atrb_srt_ord" as "atrb_str_ord" in the
ORDER BY clause?
HTH,
Dave
[EMAIL PROTECTED] wrote:
Hello,
I cannot use order by in with my xmlagg clause. Any ideas???
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SELECT XMLELEMENT("Department",
XMLAGG(
XMLELEMENT("Employee", e.atrb_cd||' '||e.vw_cd)
ORDER BY atrb_str_ord --this works if I -- it out
))
as "Dept_list"
FROM dim_vw_atrb e;
QL> desc dim_vw_atrb;
Name Null?Type
-
CLNT_CD NOT NULL NUMBER(2)
DIM_CDNOT NULL NUMBER(2)
VW_CD NOT NULL NUMBER(2)
ATRB_CD NOT NULL NUMBER(3)
ATRB_SRT_ORD NOT NULL NUMBER(2)
CRE_DTNOT NULL DATE
CRE_IDSID NOT NULL VARCHAR2(8)
LAST_MOD_DT NOT NULL DATE
LAST_MOD_IDSIDNOT NULL VARCHAR2(8)
Thanks,
Jake
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dave Hau
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).
Cannot use Order By with XMLAGG()
Hello,
I cannot use order by in with my xmlagg clause. Any ideas???
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SELECT XMLELEMENT("Department",
XMLAGG(
XMLELEMENT("Employee", e.atrb_cd||' '||e.vw_cd)
ORDER BY atrb_str_ord --this works if I -- it out
))
as "Dept_list"
FROM dim_vw_atrb e;
QL> desc dim_vw_atrb;
Name Null?Type
-
CLNT_CD NOT NULL NUMBER(2)
DIM_CDNOT NULL NUMBER(2)
VW_CD NOT NULL NUMBER(2)
ATRB_CD NOT NULL NUMBER(3)
ATRB_SRT_ORD NOT NULL NUMBER(2)
CRE_DTNOT NULL DATE
CRE_IDSID NOT NULL VARCHAR2(8)
LAST_MOD_DT NOT NULL DATE
LAST_MOD_IDSIDNOT NULL VARCHAR2(8)
Thanks,
Jake
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jake Johnson
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).
Sequence and order by
Is this documented anywhere? SQL> drop sequence test_seq; Sequence dropped. SQL> SQL> create sequence test_seq; Sequence created. SQL> SQL> select test_seq.nextval 2 from dual; NEXTVAL -- 1 SQL> SQL> select test_seq.nextval 2 from dual 3 order by dummy; select test_seq.nextval * ERROR at line 1: ORA-02287: sequence number not allowed here Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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).
Bug in 9.2.0.4 when order by constant desc
Hi all, Got the info' below from www.dba-village.com and thought of sharing it with u guys. If you try "select 'Hello' from dual order by 1 desc;" you may run into the bug. It creates a dump in udump and could disconnect your session. Oracle is creating a one-off patch to fix. The bug is for any order by x, where x is a number, desc and the column is a constant. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Sub-query in order by clause
Title: RE: Sub-query in order by clause Yes, I see that. (hangs head in shame) > -Original Message- > From: Nicoll, Iain [mailto:[EMAIL PROTECTED]] > > As I believe has been pointed out already it may possibly be > more efficient > if dept is very small and emp is very large (especially if > there are filters > and a join would be done before a filter was applied). > Alternatively it may > be that Oracle believe it is more intuitive to people with no > preconceived > ideas. > > -Original Message- > Sent: 04 April 2003 20:24 > > Why would you do that instead of > SELECT emp.deptno, empno, ename > FROM emp a, dept b > WHERE dept.deptno = emp.deptno > order by dept.deptname ; > > > -Original Message- > > From: Nicoll, Iain [ mailto:[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> ] > > > > Don't really know but couldn't it be useful if you had > > > > ORDER BY ( SELECT deptname FROM dept > > WHERE dept.deptno = emp.deptno)
RE: Sub-query in order by clause
Title: RE: Sub-query in order by clause I see your point. The answer to my question "why would you do that instead of ... " is, of course, "because the other way might be faster." Read Jonathan Lewis' earlier post, he had (as always) some good insight. > -Original Message- > From: Lyndon Tiu [mailto:[EMAIL PROTECTED]] > > Which way is faster? The subselect(nested select) or the join? > > Quoting Jacques Kilchoer <[EMAIL PROTECTED]>: > > > Why would you do that instead of > > SELECT emp.deptno, empno, ename > > FROM emp a, dept b > > WHERE dept.deptno = emp.deptno > > order by dept.deptname ; > > > > > -Original Message- > > > From: Nicoll, Iain [mailto:[EMAIL PROTECTED]] > > > > > > Don't really know but couldn't it be useful if you had > > > > > > ORDER BY ( SELECT deptname FROM dept > > > WHERE dept.deptno = emp.deptno)
RE: Sub-query in order by clause
Which way is faster? The subselect(nested select) or the join? Thanks. -- Lyndon Tiu Quoting Jacques Kilchoer <[EMAIL PROTECTED]>: > Why would you do that instead of > SELECT emp.deptno, empno, ename > FROM emp a, dept b > WHERE dept.deptno = emp.deptno > order by dept.deptname ; > > > -Original Message- > > From: Nicoll, Iain [mailto:[EMAIL PROTECTED] > > > > Don't really know but couldn't it be useful if you had > > > > ORDER BY ( SELECT deptname FROM dept > > WHERE dept.deptno = emp.deptno) > > > > > > -Original Message----- > > From: Ashish [mailto:[EMAIL PROTECTED] > > > > In Oracle 9.2.0.1, you can now order by using a single-row > subquery > > Here is an example: > > > > SELECT emp.deptno, empno, ename > > FROM emp > > ORDER BY ( SELECT deptno FROM dept > > WHERE dept.deptno = emp.deptno ); > > > > The question I have is what is the usefulness of this? Under > which > > circumstances > > this can be used? > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lyndon Tiu 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: Sub-query in order by clause
Jacques, As I believe has been pointed out already it may possibly be more efficient if dept is very small and emp is very large (especially if there are filters and a join would be done before a filter was applied). Alternatively it may be that Oracle believe it is more intuitive to people with no preconceived ideas. Iain Nicoll -Original Message- Sent: 04 April 2003 20:24 To: Multiple recipients of list ORACLE-L Why would you do that instead of SELECT emp.deptno, empno, ename FROM emp a, dept b WHERE dept.deptno = emp.deptno order by dept.deptname ; > -Original Message- > From: Nicoll, Iain [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] > > Don't really know but couldn't it be useful if you had > > ORDER BY ( SELECT deptname FROM dept > WHERE dept.deptno = emp.deptno) > > > -Original Message- > From: Ashish [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] > > In Oracle 9.2.0.1, you can now order by using a single-row subquery > Here is an example: > > SELECT emp.deptno, empno, ename > FROM emp > ORDER BY ( SELECT deptno FROM dept > WHERE dept.deptno = emp.deptno ); > > The question I have is what is the usefulness of this? Under which > circumstances > this can be used? -- 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: Sub-query in order by clause
Title: RE: Sub-query in order by clause Why would you do that instead of SELECT emp.deptno, empno, ename FROM emp a, dept b WHERE dept.deptno = emp.deptno order by dept.deptname ; > -Original Message- > From: Nicoll, Iain [mailto:[EMAIL PROTECTED]] > > Don't really know but couldn't it be useful if you had > > ORDER BY ( SELECT deptname FROM dept > WHERE dept.deptno = emp.deptno) > > > -Original Message- > From: Ashish [mailto:[EMAIL PROTECTED]] > > In Oracle 9.2.0.1, you can now order by using a single-row subquery > Here is an example: > > SELECT emp.deptno, empno, ename > FROM emp > ORDER BY ( SELECT deptno FROM dept > WHERE dept.deptno = emp.deptno ); > > The question I have is what is the usefulness of this? Under which > circumstances > this can be used?
RE: Sub-query in order by clause
Don't really know but couldn't it be useful if you had ORDER BY ( SELECT deptname FROM dept WHERE dept.deptno = emp.deptno) -Original Message- Sent: 04 April 2003 17:34 To: Multiple recipients of list ORACLE-L Hello list, In Oracle 9.2.0.1, you can now order by using a single-row subquery Here is an example: SELECT emp.deptno, empno, ename FROM emp ORDER BY ( SELECT deptno FROM dept WHERE dept.deptno = emp.deptno ); The question I have is what is the usefulness of this? Under which circumstances this can be used? Any ideas? -Ashish OCP DBA -- 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: Sub-query in order by clause
Wow ! I didn't realise that was possible - I'll have to see what Oracle can do with the execution plan. Your example could, of course, be rearranged to join emp to dept in order to order by some column of dept - and that might give us a clue why this could be useful. Pure hand-waving here - not arguing for or against - but what if you have a heavy-duty multitable query that returned only a few rows. It could be the case that joining in 'dept' (i.e. just one more table needed to supply the ordering) would require that join to be exercised a very large number of times, either as a nested loop, or as an extra hash join. Whereas if you effectively joined in 'dept' after the query was complete as per your example, the additional cost would be small. (In some ways, this is like taking advantage of the 'push_subq' hint to rearrange the moment at which Oracle executes a non-mergeable subquery.) In many cases though (if not all) I suspect the same effect could be produced by using a no_merge inline view - which has the added benefit of being intuitively a little more readable. (Although, your site may have a "no hints" standard that could preclude the use of the inline view option). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html UK___April 8th UK___April 22nd Denmark__May 21-23rd USA_(FL)_May 2nd Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK_(Manchester)_May Estonia___June (provisional) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 04 April 2003 17:33 > Hello list, > > In Oracle 9.2.0.1, you can now order by using a single-row subquery > Here is an example: > > SELECT emp.deptno, empno, ename > FROM emp > ORDER BY ( SELECT deptno FROM dept > WHERE dept.deptno = emp.deptno ); > > The question I have is what is the usefulness of this? Under which > circumstances > this can be used? Any ideas? > > -Ashish > OCP DBA > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Sub-query in order by clause
Hello list, In Oracle 9.2.0.1, you can now order by using a single-row subquery Here is an example: SELECT emp.deptno, empno, enameFROM empORDER BY ( SELECT deptno FROM deptWHERE dept.deptno = emp.deptno ); The question I have is what is the usefulness of this? Under which circumstances this can be used? Any ideas? -Ashish OCP DBA
Re: sql question -- distinct, group by and order by
Guang Mei wrote: > > Hi: > > I have a basic sql question about sql. I have the follwing four sqls and I > am wondering why #3 "costs" less than #4 in explain plan. #1 and #2 cost > the same. How is "distinct"and "group by" treated internally by Oracle? Is > #3 a better "optimized" sql than #4? > > TIA. > > Guang Mei > > 1. select id,NAME from project group by id,name ; > 2. select distinct id,name from project ; > 3. select id,NAME from project group by id,name order by id; > 4. select distinct id,name from project order by id; > > MT@atlas-SQL> desc project; > Name Null?Type > - > > IDNOT NULL NUMBER > NAME NOT NULL VARCHAR2(128) > > MT@atlas-SQL> select distinct id,name from project ; > > Execution Plan > -- >0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216 > ) > >10 SORT (UNIQUE) (Cost=54 Card=12 Bytes=216) >21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 > 16) > > COMMONNAMENOT NULL VARCHAR2(16) > MNEMONIC NOT NULL CHAR(1) > USE NOT NULL CHAR(1) > > MT@atlas-SQL> select id,NAME from project group by id,name ; > > Execution Plan > -- >0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216 > ) > >10 SORT (GROUP BY) (Cost=54 Card=12 Bytes=216) >21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 > 16) > > MT@atlas-SQL> select id,NAME from project group by id,name order by id; > > Execution Plan > -- >0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216 > ) > >10 SORT (GROUP BY) (Cost=54 Card=12 Bytes=216) >21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 > 16) > > MT@atlas-SQL> select distinct id,name from project order by id; > > Execution Plan > -- >0 SELECT STATEMENT Optimizer=CHOOSE (Cost=107 Card=12 Bytes=21 > 6) > >10 SORT (UNIQUE) (Cost=54 Card=12 Bytes=216) >21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 > 16) > Mei, I would disregard 'cost'; this is just what a more or less educated guess in some algorithm coded by some developer. Elapsed time is real (if nobody is computing finite elements or gzip'ing a few terabytes on your machine while you are testing), and, other things being equal, go for what requires the fewer buffer gets. Otherwise the plan answers your question - noticed 'SORT' ? What is in between parentheses only indicates what triggered the very same processing. -- 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).
sql question -- distinct, group by and order by
Hi: I have a basic sql question about sql. I have the follwing four sqls and I am wondering why #3 "costs" less than #4 in explain plan. #1 and #2 cost the same. How is "distinct"and "group by" treated internally by Oracle? Is #3 a better "optimized" sql than #4? TIA. Guang Mei 1. select id,NAME from project group by id,name ; 2. select distinct id,name from project ; 3. select id,NAME from project group by id,name order by id; 4. select distinct id,name from project order by id; MT@atlas-SQL> desc project; Name Null?Type - IDNOT NULL NUMBER NAME NOT NULL VARCHAR2(128) MT@atlas-SQL> select distinct id,name from project ; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216 ) 10 SORT (UNIQUE) (Cost=54 Card=12 Bytes=216) 21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 16) COMMONNAMENOT NULL VARCHAR2(16) MNEMONIC NOT NULL CHAR(1) USE NOT NULL CHAR(1) MT@atlas-SQL> select id,NAME from project group by id,name ; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216 ) 10 SORT (GROUP BY) (Cost=54 Card=12 Bytes=216) 21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 16) MT@atlas-SQL> select id,NAME from project group by id,name order by id; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216 ) 10 SORT (GROUP BY) (Cost=54 Card=12 Bytes=216) 21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 16) MT@atlas-SQL> select distinct id,name from project order by id; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=107 Card=12 Bytes=21 6) 10 SORT (UNIQUE) (Cost=54 Card=12 Bytes=216) 21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2 16) _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei 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: order by in subquery workaround
For databases < 8i you need to run statsrep80.sql
Babu
Jack van Zanen <[EMAIL PROTECTED]>@fatcity.com on 07/12/2002 02:53:21 AM
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Hi
I have in the past seen a download for a backported statspack on I believe
OTN. This would work on Oracle 8.0.5
Maybe still there
Jack
"Baker, Barbara"
agency.com>cc: (bcc: Jack
van Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: order by in
subquery workaround
[EMAIL PROTECTED]
11-07-2002 19:38
Please respond to
ORACLE-L
> Solaris 2.6
> Oracle RDBMS v8.0.5.2.1
>
List:
I'm trying to run a retrofit of statspack on an 8.0.5 database. (ya, I
know. upgrading would actually be easier)
The statspack stuff came from an 8.1.7 install I have on the same box.
The spreport.sql report encounters errors in the "order by" clause. The
problem is that you can't do an "order by" on a subquery before version
8.1.something-or-other
I know there's some kind of work around for this. I had something to do
with a hint. Even after 3 cups of coffee, I can't remember what it is.
Can anyone help?
Thanks!
Barb
here are the errors:
Shared Pool StatisticsBegin End
-- --
Memory Usage %: 75.99 70.05
% SQL with executions>1: 21.95 48.60
% Memory for SQL w/exec>1: 11.28 21.84
order by time desc, waits desc
*
ERROR at line 24:
ORA-00907: missing right parenthesis
pmon timer 1,184 1,177 355,304 3001
0.2
-
order by (e.buffer_gets - nvl(b.buffer_gets,0)) desc, e.hash_value,
st.piece
*
ERROR at line 41:
ORA-00907: missing right parenthesis
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
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).
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request
RE: order by in subquery workaround
Rob and Jack: Found it. (165420.1) Thanks for your help. Barb > -- > From: Robert Pegram[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Friday, July 12, 2002 6:48 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: order by in subquery workaround > > I downloaded it from metalink a while back. It was > pretty straight forward to install. The report name > was something different like statsrep.sql or > statsrep80.sql. Also, there was a view that had to be > created. All the information/instructions were in the > tar file that I downloaded. > > If you can't find it on metalink, let me know, and I > will round up my old notes and the tar file and send > it to you. > > Rob Pegram > Oracle Certified DBA > > --- Jack van Zanen <[EMAIL PROTECTED]> wrote: > > Hi > > > > I have in the past seen a download for a backported > > statspack on I believe > > OTN. This would work on Oracle 8.0.5 > > Maybe still there > > > > Jack > > > > > > > > > > > > "Baker, Barbara" > > > > > >> To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > agency.com> > > cc: (bcc: Jack van > > Zanen/nlzanen1/External/MEY/NL) > > > > Sent by: > > Subject: order by in subquery workaround > > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > 11-07-2002 19:38 > > > > > > Please respond to > > > > > > ORACLE-L > > > > > > > > > > > > > > > > > > > > > > > > > Solaris 2.6 > > > Oracle RDBMS v8.0.5.2.1 > > > > > List: > > I'm trying to run a retrofit of statspack on an > > 8.0.5 database. (ya, I > > know. upgrading would actually be easier) > > The statspack stuff came from an 8.1.7 install I > > have on the same box. > > > > The spreport.sql report encounters errors in the > > "order by" clause. The > > problem is that you can't do an "order by" on a > > subquery before version > > 8.1.something-or-other > > > > I know there's some kind of work around for this. I > > had something to do > > with a hint. Even after 3 cups of coffee, I can't > > remember what it is. > > > > Can anyone help? > > Thanks! > > Barb > > > > here are the errors: > > > > Shared Pool StatisticsBegin End > >-- -- > > Memory Usage %: 75.99 70.05 > > % SQL with executions>1: 21.95 48.60 > > % Memory for SQL w/exec>1: 11.28 21.84 > >order by time desc, waits desc > >* > > ERROR at line 24: > > ORA-00907: missing right parenthesis > > > > > > > > > > pmon timer 1,184 1,177 > > 355,304 3001 > > 0.2 > > > > >
Re: order by in subquery workaround
I downloaded it from metalink a while back. It was pretty straight forward to install. The report name was something different like statsrep.sql or statsrep80.sql. Also, there was a view that had to be created. All the information/instructions were in the tar file that I downloaded. If you can't find it on metalink, let me know, and I will round up my old notes and the tar file and send it to you. Rob Pegram Oracle Certified DBA --- Jack van Zanen <[EMAIL PROTECTED]> wrote: > Hi > > I have in the past seen a download for a backported > statspack on I believe > OTN. This would work on Oracle 8.0.5 > Maybe still there > > Jack > > > > > > "Baker, Barbara" > > >To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > agency.com> > cc: (bcc: Jack van > Zanen/nlzanen1/External/MEY/NL) > > Sent by: > Subject: order by in subquery workaround > > [EMAIL PROTECTED] > > > > > > > > > 11-07-2002 19:38 > > > Please respond to > > > ORACLE-L > > > > > > > > > > > > > Solaris 2.6 > > Oracle RDBMS v8.0.5.2.1 > > > List: > I'm trying to run a retrofit of statspack on an > 8.0.5 database. (ya, I > know. upgrading would actually be easier) > The statspack stuff came from an 8.1.7 install I > have on the same box. > > The spreport.sql report encounters errors in the > "order by" clause. The > problem is that you can't do an "order by" on a > subquery before version > 8.1.something-or-other > > I know there's some kind of work around for this. I > had something to do > with a hint. Even after 3 cups of coffee, I can't > remember what it is. > > Can anyone help? > Thanks! > Barb > > here are the errors: > > Shared Pool StatisticsBegin End >-- -- > Memory Usage %: 75.99 70.05 > % SQL with executions>1: 21.95 48.60 > % Memory for SQL w/exec>1: 11.28 21.84 >order by time desc, waits desc >* > ERROR at line 24: > ORA-00907: missing right parenthesis > > > > > pmon timer 1,184 1,177 > 355,304 3001 > 0.2 > > - > order by (e.buffer_gets - > nvl(b.buffer_gets,0)) desc, e.hash_value, > st.piece > * > ERROR at line 41: > ORA-00907: missing right parenthesis > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Baker, Barbara > 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 >
Re: order by in subquery workaround
Hi I have in the past seen a download for a backported statspack on I believe OTN. This would work on Oracle 8.0.5 Maybe still there Jack "Baker, Barbara" agency.com>cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: order by in subquery workaround [EMAIL PROTECTED] 11-07-2002 19:38 Please respond to ORACLE-L > Solaris 2.6 > Oracle RDBMS v8.0.5.2.1 > List: I'm trying to run a retrofit of statspack on an 8.0.5 database. (ya, I know. upgrading would actually be easier) The statspack stuff came from an 8.1.7 install I have on the same box. The spreport.sql report encounters errors in the "order by" clause. The problem is that you can't do an "order by" on a subquery before version 8.1.something-or-other I know there's some kind of work around for this. I had something to do with a hint. Even after 3 cups of coffee, I can't remember what it is. Can anyone help? Thanks! Barb here are the errors: Shared Pool StatisticsBegin End -- -- Memory Usage %: 75.99 70.05 % SQL with executions>1: 21.95 48.60 % Memory for SQL w/exec>1: 11.28 21.84 order by time desc, waits desc * ERROR at line 24: ORA-00907: missing right parenthesis pmon timer 1,184 1,177 355,304 3001 0.2 ----- order by (e.buffer_gets - nvl(b.buffer_gets,0)) desc, e.hash_value, st.piece * ERROR at line 41: ORA-00907: missing right parenthesis -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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). === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aan
order by in subquery workaround
> Solaris 2.6 > Oracle RDBMS v8.0.5.2.1 > List: I'm trying to run a retrofit of statspack on an 8.0.5 database. (ya, I know. upgrading would actually be easier) The statspack stuff came from an 8.1.7 install I have on the same box. The spreport.sql report encounters errors in the "order by" clause. The problem is that you can't do an "order by" on a subquery before version 8.1.something-or-other I know there's some kind of work around for this. I had something to do with a hint. Even after 3 cups of coffee, I can't remember what it is. Can anyone help? Thanks! Barb here are the errors: Shared Pool StatisticsBegin End -- -- Memory Usage %: 75.99 70.05 % SQL with executions>1: 21.95 48.60 % Memory for SQL w/exec>1: 11.28 21.84 order by time desc, waits desc * ERROR at line 24: ORA-00907: missing right parenthesis pmon timer 1,184 1,177 355,304 3001 0.2 ----- order by (e.buffer_gets - nvl(b.buffer_gets,0)) desc, e.hash_value, st.piece * ERROR at line 41: ORA-00907: missing right parenthesis -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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: order by versus order by desc problem ..... probably a simple
Run sql trace on each and see the differences in the execution plan. If #2 is using an index, try a rebuild and see if it changes. I have seen indexes be built without data in them (though data is in the table). Checking for a bug is an excellent idea as well. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456 -Original Message- Sent: Thursday, June 20, 2002 12:17 PM To: Multiple recipients of list ORACLE-L pro select distinct col1 from table_name where col1 like 'ABC_' and col2 > 0 order by col1; returns 38 rows .. but select distinct col1 from table_name where col1 like 'ABC_____' and col2 > 0 order by col1 desc; returns 0 rows just by putting in the descending clause anybody seen this happen before new index has been added on the table, but nothing else is new. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael 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: Fink, Dan 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: order by versus order by desc problem ..... probably a simple
You are exactly correct ... it is a bug in an earlier version on Oracle we have on one of our servers See Note:1036394.6 on metalink ! thank you ! Mike -Original Message- Sent: Thursday, June 20, 2002 11:51 AM To: Multiple recipients of list ORACLE-L simple Looks like a bug! -Original Message- Sent: Thursday, June 20, 2002 2:17 PM To: Multiple recipients of list ORACLE-L pro select distinct col1 from table_name where col1 like 'ABC_' and col2 > 0 order by col1; returns 38 rows .. but select distinct col1 from table_name where col1 like 'ABC_____' and col2 > 0 order by col1 desc; returns 0 rows just by putting in the descending clause anybody seen this happen before new index has been added on the table, but nothing else is new. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael 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: Khedr, Waleed 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: Johnson, Michael 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: order by versus order by desc problem ..... probably a simple
Looks like a bug! -Original Message- Sent: Thursday, June 20, 2002 2:17 PM To: Multiple recipients of list ORACLE-L pro select distinct col1 from table_name where col1 like 'ABC_' and col2 > 0 order by col1; returns 38 rows .. but select distinct col1 from table_name where col1 like 'ABC_____' and col2 > 0 order by col1 desc; returns 0 rows just by putting in the descending clause anybody seen this happen before new index has been added on the table, but nothing else is new. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael 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: Khedr, Waleed 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).
order by versus order by desc problem ..... probably a simple pro
select distinct col1 from table_name where col1 like 'ABC_' and col2 > 0 order by col1; returns 38 rows .. but select distinct col1 from table_name where col1 like 'ABC_____' and col2 > 0 order by col1 desc; returns 0 rows just by putting in the descending clause anybody seen this happen before new index has been added on the table, but nothing else is new. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael 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: problem using ROWNUM and ORDER BY clause together
Harvinder, My experience has been that Oracle applies rownum first, then order by. So, first it retrieves records that pass the 'where' clause, then sorts them. The results of your examples may be explained by the use of an index - try running explain plan against them to see if an index was used (because of the order-by clause). The most important thing you need to realize is that you cannot depend on the order of data being retrieved in the same order it was inserted. Raj's example is the correct way to solve your query request. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, May 30, 2002 4:52 PM To: Multiple recipients of list ORACLE-L Scenario is we have a table having 100 rows and column id_po as unique column with distinct values betweem 1 and 100... we we try select id_po from table1 where rownum < 5 order by id_po; it gives result as: 1 2 3 4 and if we try select id_acc,id_po from table1 where rownum < 5 order by id_po desc; 100 99 98 97 That implies oracle is first getting the result set and then apply order by and then rownum.. But when we try select id_acc,id_po from table1 where rownum < 2 order by id_po desc; result is: 1 where it should be 100 if above statement is true Thanks --Harvinder -Original Message- Sent: Thursday, May 30, 2002 4:30 PM To: Multiple recipients of list ORACLE-L When you use rownum < 2 you are effectively selecting only one row and stopping after that. Also This is not the right way to do it, the right way is to use inline view with rownum condition and order by in outer query. In your scenario, oracle will retrieve 2 rows and then sort them. These two rows can be anything and are not affected by the order by clause YET. Ordering one row reminds of an assignment we had in our Graphics class, we were asked to implement object rotation, a colleague complained his program is right, but the object wasn't getting rotated. When we had a look on his screen, he was trying to rotate a circle ! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 30, 2002 4:08 PM To: Multiple recipients of list ORACLE-L problem is it works if we have rownum < 3(or any value >2) and only fails if we use rownum < 2... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Mercadante, Thomas F 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: problem using ROWNUM and ORDER BY clause together
Harvinder you probably didn't get my point. SQL doesn't guarantee the order of rows retrieval in absence of ORDER BY clause. To order retrieved rows is the very essence of this clause. In absence of this (ORDER BY) clause, the order of rows retrieval is implementation specific (i.e. each vendor may choose any method they like). Bottom line, the query you are running is giving you the right results, but to get the results that you WANT, you will have to rewrite the query. Period. No matter how much you try, it is not guaranteed. Oracle now says that don't depend on the implicit order by performed by group by clause (when order by is not present in the query). I forgot the note id ... I recently read it on Metalink. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 30, 2002 3:49 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Scenario is we have a table having 100 rows and column id_po as unique column with distinct values betweem 1 and 100... we we try select id_po from table1 where rownum < 5 order by id_po; it gives result as: 1 2 3 4 and if we try select id_acc,id_po from table1 where rownum < 5 order by id_po desc; 100 99 98 97 That implies oracle is first getting the result set and then apply order by and then rownum.. But when we try select id_acc,id_po from table1 where rownum < 2 order by id_po desc; result is: 1 where it should be 100 if above statement is true Thanks --Harvinder *2 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: problem using ROWNUM and ORDER BY clause together
Scenario is we have a table having 100 rows and column id_po as unique column with distinct values betweem 1 and 100... we we try select id_po from table1 where rownum < 5 order by id_po; it gives result as: 1 2 3 4 and if we try select id_acc,id_po from table1 where rownum < 5 order by id_po desc; 100 99 98 97 That implies oracle is first getting the result set and then apply order by and then rownum.. But when we try select id_acc,id_po from table1 where rownum < 2 order by id_po desc; result is: 1 where it should be 100 if above statement is true Thanks --Harvinder -Original Message- Sent: Thursday, May 30, 2002 4:30 PM To: Multiple recipients of list ORACLE-L When you use rownum < 2 you are effectively selecting only one row and stopping after that. Also This is not the right way to do it, the right way is to use inline view with rownum condition and order by in outer query. In your scenario, oracle will retrieve 2 rows and then sort them. These two rows can be anything and are not affected by the order by clause YET. Ordering one row reminds of an assignment we had in our Graphics class, we were asked to implement object rotation, a colleague complained his program is right, but the object wasn't getting rotated. When we had a look on his screen, he was trying to rotate a circle ! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 30, 2002 4:08 PM To: Multiple recipients of list ORACLE-L problem is it works if we have rownum < 3(or any value >2) and only fails if we use rownum < 2... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: probem using ROWNUM and ORDER BY clause together
What happens if you say - rownum = 1 ? Melanie -Original Message- Sent: Thursday, May 30, 2002 3:08 PM To: Multiple recipients of list ORACLE-L problem is it works if we have rownum < 3(or any value >2) and only fails if we use rownum < 2... -Original Message- Sent: Thursday, May 30, 2002 3:34 PM To: Multiple recipients of list ORACLE-L it is normal documented behavior. what you want to do is use an "in-line view" to resolve the issue... select * from (select ... from ... where ... order by ...) where rownum < 2; Hope this helps... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, May 30, 2002 12:44 PM Hi, Whenever we have to use rownum with order by clause, we used to use subquery with order by and the use rownum in outer query..in 9i it was told that we can use order by with rownum without subquery and it works if we are selecting more than 2 rows like rownum < 3 but it fails when we do rownum < 2.. did anyone also seen this behaviour following is test script from sqlplus: SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3 and id_acc < 124 order by num_generations desc; ID_DESCENDENT NUM_GENERATIONS - --- 123 1 1 0 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3 and id_acc < 124 order by num_generations asc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 123 1 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2 and id_acc < 124 order by num_generations asc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2 and id_acc < 124 order by num_generations desc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 THE RESULT SHOULD BE : ID_DESCENDENT NUM_GENERATIONS - --- 123 1 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Tim Gorman 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: Harvinder Singh 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: Root, Melanie 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 P
RE: problem using ROWNUM and ORDER BY clause together
When you use rownum < 2 you are effectively selecting only one row and stopping after that. Also This is not the right way to do it, the right way is to use inline view with rownum condition and order by in outer query. In your scenario, oracle will retrieve 2 rows and then sort them. These two rows can be anything and are not affected by the order by clause YET. Ordering one row reminds of an assignment we had in our Graphics class, we were asked to implement object rotation, a colleague complained his program is right, but the object wasn't getting rotated. When we had a look on his screen, he was trying to rotate a circle ! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 30, 2002 4:08 PM To: Multiple recipients of list ORACLE-L problem is it works if we have rownum < 3(or any value >2) and only fails if we use rownum < 2... *2 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: probem using ROWNUM and ORDER BY clause together
problem is it works if we have rownum < 3(or any value >2) and only fails if we use rownum < 2... -Original Message- Sent: Thursday, May 30, 2002 3:34 PM To: Multiple recipients of list ORACLE-L it is normal documented behavior. what you want to do is use an "in-line view" to resolve the issue... select * from (select ... from ... where ... order by ...) where rownum < 2; Hope this helps... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, May 30, 2002 12:44 PM Hi, Whenever we have to use rownum with order by clause, we used to use subquery with order by and the use rownum in outer query..in 9i it was told that we can use order by with rownum without subquery and it works if we are selecting more than 2 rows like rownum < 3 but it fails when we do rownum < 2.. did anyone also seen this behaviour following is test script from sqlplus: SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3 and id_acc < 124 order by num_generations desc; ID_DESCENDENT NUM_GENERATIONS - --- 123 1 1 0 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3 and id_acc < 124 order by num_generations asc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 123 1 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2 and id_acc < 124 order by num_generations asc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2 and id_acc < 124 order by num_generations desc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 THE RESULT SHOULD BE : ID_DESCENDENT NUM_GENERATIONS - --- 123 1 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Tim Gorman 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: Harvinder Singh 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: probem using ROWNUM and ORDER BY clause together
it is normal documented behavior.
what you want to do is use an "in-line view" to resolve the issue...
select * from
(select ...
from ...
where ...
order by ...)
where rownum < 2;
Hope this helps...
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 30, 2002 12:44 PM
Hi,
Whenever we have to use rownum with order by clause, we used to use subquery
with order by and the use rownum
in outer query..in 9i it was told that we can use order by with rownum
without subquery and it works if we are
selecting more than 2 rows like rownum < 3 but it fails when we do rownum <
2..
did anyone also seen this behaviour
following is test script from sqlplus:
SQL> select id_descendent,num_generations from t_account_ancestor inner join
t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3
and
id_acc < 124 order by num_generations desc;
ID_DESCENDENT NUM_GENERATIONS
- ---
123 1
1 0
SQL> select id_descendent,num_generations from t_account_ancestor inner join
t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3
and
id_acc < 124 order by num_generations asc;
ID_DESCENDENT NUM_GENERATIONS
- ---
1 0
123 1
SQL> select id_descendent,num_generations from t_account_ancestor inner join
t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2
and
id_acc < 124 order by num_generations asc;
ID_DESCENDENT NUM_GENERATIONS
- ---
1 0
SQL> select id_descendent,num_generations from t_account_ancestor inner join
t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2
and
id_acc < 124 order by num_generations desc;
ID_DESCENDENT NUM_GENERATIONS
- ---
1 0
THE RESULT SHOULD BE :
ID_DESCENDENT NUM_GENERATIONS
- ---
123 1
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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: Tim Gorman
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).
probem using ROWNUM and ORDER BY clause together
Hi, Whenever we have to use rownum with order by clause, we used to use subquery with order by and the use rownum in outer query..in 9i it was told that we can use order by with rownum without subquery and it works if we are selecting more than 2 rows like rownum < 3 but it fails when we do rownum < 2.. did anyone also seen this behaviour following is test script from sqlplus: SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3 and id_acc < 124 order by num_generations desc; ID_DESCENDENT NUM_GENERATIONS - --- 123 1 1 0 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 3 and id_acc < 124 order by num_generations asc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 123 1 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2 and id_acc < 124 order by num_generations asc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 SQL> select id_descendent,num_generations from t_account_ancestor inner join t_a ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum < 2 and id_acc < 124 order by num_generations desc; ID_DESCENDENT NUM_GENERATIONS - --- 1 0 THE RESULT SHOULD BE : ID_DESCENDENT NUM_GENERATIONS - --- 123 1 Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: sort aggregate vs sort order by
Hi Larry THX Jack "Larry Elkins" <[EMAIL PROTECTED]>@fatcity.com on 28-12-2001 14:25:18 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Jack, I assume you are asking this question in reference to the access path, or explain plan, for a query. Sort (Aggregate) -- Query returns a single row using a summary function but does not include a GROUP BY clause. Sort (Order By) -- Query includes an ORDER BY clause. Sort (Group By) -- Query includes a GROUP BY clause. In the 8i documentation, this info can be found in Table 5-4 in the "Designing and Tuning for Performance" manual. FWIW, though mentioned elsewhere in the same manual, Table 5-4 doesn't mention the SORT (GROUP BY NO SORT) -- ordered data is being fed into the GROUP BY step negating the need for the sort operation that is needed for a GROUP BY. Can occur under certain conditions -- sometimes when index access supporting the ordering is used, after a sort merge, etc. I attached some examples to illustrate. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] > Sent: Friday, December 28, 2001 5:00 AM > To: Multiple recipients of list ORACLE-L > Subject: sort aggregate vs sort order by > > > > > Hi All, > > > I have tried the Oracle Doc's but can't find the answer to what is the > difference between the two > > Anybody can explain?? > > > TIA > > > > Jack > > === > De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor > de geadresseerde. Gebruik van deze informatie door anderen dan de > geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding > en/of verstrekking van deze informatie aan derden is niet toegestaan. > Ernst & Young staat niet in voor de juiste en volledige > overbrenging van de > inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. > === > The information contained in this communication is confidential and may be > legally privileged. It is intended solely for the use of the individual or > entity to whom it is addressed and others authorised to receive it. If you > are not the intended recipient you are hereby notified that any > disclosure, > copying, distribution or taking any action in reliance on the contents of > this information is strictly prohibited and may be unlawful. Ernst & > Young is neither liable for the proper and complete transmission of the > information contained in this communication nor for any delay in its > receipt. > === > > > > > > -- > 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). > (See attached file: examples.txt) === De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. === The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. === examples.txt Description: Binary data
RE: sort aggregate vs sort order by
Jack, I assume you are asking this question in reference to the access path, or explain plan, for a query. Sort (Aggregate) -- Query returns a single row using a summary function but does not include a GROUP BY clause. Sort (Order By) -- Query includes an ORDER BY clause. Sort (Group By) -- Query includes a GROUP BY clause. In the 8i documentation, this info can be found in Table 5-4 in the "Designing and Tuning for Performance" manual. FWIW, though mentioned elsewhere in the same manual, Table 5-4 doesn't mention the SORT (GROUP BY NO SORT) -- ordered data is being fed into the GROUP BY step negating the need for the sort operation that is needed for a GROUP BY. Can occur under certain conditions -- sometimes when index access supporting the ordering is used, after a sort merge, etc. I attached some examples to illustrate. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] > Sent: Friday, December 28, 2001 5:00 AM > To: Multiple recipients of list ORACLE-L > Subject: sort aggregate vs sort order by > > > > > Hi All, > > > I have tried the Oracle Doc's but can't find the answer to what is the > difference between the two > > Anybody can explain?? > > > TIA > > > > Jack > > === > De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor > de geadresseerde. Gebruik van deze informatie door anderen dan de > geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding > en/of verstrekking van deze informatie aan derden is niet toegestaan. > Ernst & Young staat niet in voor de juiste en volledige > overbrenging van de > inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. > === > The information contained in this communication is confidential and may be > legally privileged. It is intended solely for the use of the individual or > entity to whom it is addressed and others authorised to receive it. If you > are not the intended recipient you are hereby notified that any > disclosure, > copying, distribution or taking any action in reliance on the contents of > this information is strictly prohibited and may be unlawful. Ernst & > Young is neither liable for the proper and complete transmission of the > information contained in this communication nor for any delay in its > receipt. > === > > > > > > -- > 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). > Examples: SQL> select min(sal) from emp; Execution Plan ---------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2) 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=16) SQL> select ename from emp order by ename; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=40) 10 SORT (ORDER BY) (Cost=3 Card=8 Bytes=40) 21 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=40) SQL> select min(sal) from emp group by deptno; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=8) 10 SORT (GROUP BY) (Cost=3 Card=2 Bytes=8) 21 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=32) 1 select foo_date, count(*) 2 from code_master 3 where foo_date < sysdate - 1000 4* group by foo_date -- foo_date is indexed SQL> / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5000 Bytes=35000) 10 SORT (GROUP BY NOSORT) (Cost=4 Card=5000 Bytes=35000) 21 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=4 Card=5000 Bytes=35000) 1 select foo_date 2 from code_master 3 where foo_date < sysdat
sort aggregate vs sort order by
Hi All, I have tried the Oracle Doc's but can't find the answer to what is the difference between the two Anybody can explain?? TIA Jack === De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan. === The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. === -- 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).
RE: Order by question - newbie
Title: Message If your grabing all rows, and your calling all columns (some in order, some display) why wouldn't it do a fts?There is no reason to use the index. Now if you overloaded the index, it may opt to use the index, but to what avail? It is is the same size of the table. In overloading, I refere to puting PersonalCode at the end of the index you created, this way all teh data will be available from the index, there will be no need to go to the table, altough the table will be the same size as the index, but the index will already be sorted. Perhaps you may want to look at IOT, if this isn't high write table. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Csillag Zsolt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 28, 2001 3:37 PMTo: Multiple recipients of list ORACLE-LSubject: Order by question - newbie Hi,I have a table with 3 colums : PersonalCode, Name and SpecialOrder.The PersonalCode is the primary index and I have also an index on SpecialOrder,Name columns.However the following select:Select PersonalCode From MyTable Order By SpecialOrder,Namedoesn't use the SpecialOrder,Name index at all. If I watch it in Explain Plan window I can see that :SELECT STATEMENT, GOAL = CHOOSE 1181 16378 2456700 SORT ORDER BY 118116378 2456700 TABLE ACCESS FULL ADMIN MyTable 69 16378 2456700How can I make the index work? Thank you in advance Zsolt CsillagHungary
Re: Order by question - newbie
Csillag Zsolt wrote: > > Hi, > > I have a table with 3 colums : PersonalCode, Name and SpecialOrder. > > The PersonalCode is the primary index and I have also an index on > SpecialOrder,Name columns. > > However the following select: > > Select PersonalCode From MyTable > Order By SpecialOrder,Name > > doesn't use the SpecialOrder,Name index at all. > If I watch it in Explain Plan window I can see that : > > SELECT STATEMENT, GOAL = > CHOOSE 118116378 2456700 > SORT ORDER BY 118116378 2456700 > TABLE ACCESS FULL ADMIN MyTable 69 16378 2456700 > > How can I make the index work? > > Thank you in advance > > Zsolt Csillag > Hungary Using the index may not be the best of ideas. All right, everything is ordered in your index (let's totally forget about 'NLS sort', sorting 'logically' for a language which uses accentuated letters as opposed to the numerical ordering of ASCII (or EBCDIC) codes). You need to fully scan the index (since you have no WHERE condition) and for each row get its address (rowid), then fetch the relevant block in the table since the PersonalCode info IS NOT in the index. It is not at all obvious that it will be any better than a full scan, and obviously the optimizer thinks that it is not. You can force the index to be used with hints (use SET AUTOTRACE TRACEONLY under SQL*Plus, run the query without any hint, then run it with the appropriate hint - the best choice is the one with the smallest db block gets + consistent gets). I don't know your data, but what could SpecialOrder + Name be considered as an alternative PK? If this could be made the 'official' PK, by creating a unique constraint on PersonalCode you could keep all your FKs on it, and you could consider an index-organized table. Really depends on what you want to do. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- 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).
Order by question - newbie
Hi, I have a table with 3 colums : PersonalCode, Name and SpecialOrder. The PersonalCode is the primary index and I have also an index on SpecialOrder,Name columns. However the following select: Select PersonalCode From MyTable Order By SpecialOrder,Name doesn't use the SpecialOrder,Name index at all. If I watch it in Explain Plan window I can see that : SELECT STATEMENT, GOAL = CHOOSE 118116378 2456700 SORT ORDER BY 118116378 2456700 TABLE ACCESS FULL ADMIN MyTable 69 16378 2456700 How can I make the index work? Thank you in advance Zsolt Csillag Hungary
RE: Order By Position and UNION
This works in O7.3.4.5 select * from ( select empno e from emp union all select empno e from emp) order by e; -- Chris J. Guidry P.Eng. ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] > -Original Message- > From: Larry Elkins [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, July 12, 2001 04:40 PM > To: Multiple recipients of list ORACLE-L > Subject: OT: Order By Position and UNION > > Listers, > > <<>> > > For compound queries (containing set operators UNION, INTERSECT, MINUS, or > UNION ALL), the ORDER BY clause must use positions, rather than explicit > expressions. > > <<>> > > Now, against an 8.1.7 DB on WIN2K: > > SQL> select deptno, loc from dept > 2 union > 3 select empno, ename from emp > 4 order by deptno --<<<<<< DEPTNO, not 1 > 5 / > > DEPTNO LOC > -- - > 10 NEW YORK > 20 DALLAS > 30 CHICAGO > 40 BOSTON > 7369 SMITH > 7499 ALLEN > > > I've always used positional notation in the ORDER BY on my queries using > set > operators, and, the snippet from the SQL manual still says you have to. > The > 9i manual at tahiti contains the same statement regarding positional > notation. Now I've got someone telling me they *think* they were using > expressions even back in 7.x. Now I'm going to play it safe and continue > to > use positional notation, but, I was wondering if anyone has any insight > into > this. I hadn't tried expressions in V7 and was curious if it was accepted > even back then. Is this simply a documentation bug? There have been lots > of > enhancements to Oracle's SQL over the last few years so I could see them > missing something. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Larry Elkins > 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: Guidry, Chris 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: OT: Order By Position and UNION
Thanks for the response. I'm wondering if it has always been like that or if it changed with a recent version -- don't know since I have always used positional notation going back to V5 when I started using Oracle (and yes, I know people go back further than that -- that's not the point I was trying to make). Taking your example further, if I alias each column (in your case alias deptno and locid as empno), it will still work if I refer to empno in the ORDER BY. Not that our queries make much sense ;-) Anyway, I think it's obvious why I used OT in the subject line -- we are talking about some trivial things here ;-) But, there is still the curiosity factor that made me want to know if it has always been this way or if this behavior was introduced in a particular version or point release. For example, in-line views were "there" in 7.1 but not official until 7.2 (I think those are the correct versions, it's been a while). OK, I did a deja search since this is bugging me and at one point, based on posts I pulled up and the examples they provided, you were *required* to use positional. And as we see PL/SQL sometimes lagging SQL, there also were mentions of where referring to a column name was supported in SQL but not in PL/SQL, and references to in earlier versions where positional was required in both. But, none of the posts happened to mention what version of the DB. So I now know that I'm not crazy, that positional was mandatory at some point. It's interesting what the manuals say and your examples showing the difference between unioning 2 or 3 selects, and then what happens with aliasing all to be the same. I'll stick with positional to be safe. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, July 12, 2001 6:51 PM To: Multiple recipients of list ORACLE-L Larry, You can use column name if you only have two statements with your set operators. However, any more than that you must user positional. See below. 1 select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6* order by empno SQL> / order by empno * ERROR at line 6: ORA-00904: invalid column name SQL> select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6 order by 1 7 / EMPNO ENAME -- -- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS 45 101 46 202 47 103 48 105 49 105 50 404 51 421 52 211 53 424 54 402 55 433 56 217 57 222 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 31 rows selected. SQL> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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: OT: Order By Position and UNION
Larry, You can use column name if you only have two statements with your set operators. However, any more than that you must user positional. See below. 1 select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6* order by empno SQL> / order by empno * ERROR at line 6: ORA-00904: invalid column name SQL> select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6 order by 1 7 / EMPNO ENAME -- -- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS 45 101 46 202 47 103 48 105 49 105 50 404 51 421 52 211 53 424 54 402 55 433 56 217 57 222 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 31 rows selected. SQL>
OT: Order By Position and UNION
Listers, <<>> For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. <<>> Now, against an 8.1.7 DB on WIN2K: SQL> select deptno, loc from dept 2 union 3 select empno, ename from emp 4 order by deptno --<<<<<< DEPTNO, not 1 5 / DEPTNO LOC -- - 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON 7369 SMITH 7499 ALLEN I've always used positional notation in the ORDER BY on my queries using set operators, and, the snippet from the SQL manual still says you have to. The 9i manual at tahiti contains the same statement regarding positional notation. Now I've got someone telling me they *think* they were using expressions even back in 7.x. Now I'm going to play it safe and continue to use positional notation, but, I was wondering if anyone has any insight into this. I hadn't tried expressions in V7 and was curious if it was accepted even back then. Is this simply a documentation bug? There have been lots of enhancements to Oracle's SQL over the last few years so I could see them missing something. Regards, Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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 : Order by for varchar ???
I'd guess there are characters in the data that weren't in your previously posted sample. As I said, the function as written will only work with widths in the format of [whole-]numerator[/denominator]["|'] If you have any other formats, you'll have to modify the function to take care of them. HTH, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] "Apps Sol" mail.com>cc: Sent by: Fax to: root@fatcity. Subject: Re: SQL : Order by for varchar ??? com 06/08/2001 12:01 PM Please respond to ORACLE-L Looks like I am missing something.. this is the error we get select width, fractionToDecimal(width) from test * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "PS09.FRACTIONTODECIMAL", line 12 ORA-06512: at line 1 - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 07, 2001 3:41 PM > > Ooh, a fun one. If you are sure of the format of the data (as you'll see > in the following function) you can create a function to make the data > numeric. I can't think of another way to do it, but maybe someone else > can?... > > create or replace function fractionToDecimal (str in varchar2) return > number is > fract varchar2(100); > dec number; > begin > -- This function takes numbers in the form below and returns a decimal > number > -- [whole-]numerator[/denominator]["|'] > -- For example: > -- 5/8" > -- 1-1/2" > -- 4' > -- > > -- Remove the symbol. > -- > fract := rtrim(str, '"'''); > > -- Replace occurences of '-' with '+' > -- > fract := replace(fract, '-', '+'); > > -- Evaluate the resulting expression > -- > execute immediate 'select ' || fract || ' from dual' into dec; > > return dec; > end fractionToDecimal; > / > > 1 select width, fractionToDecimal(width) from test > 2* order by 2 desc > SQL> / > > WIDTHFRACTIONTODECIMAL(WIDTH) > > 4" 4 > 2" 2 > 1-1/2"1.5 > 1" 1 > 3/4" .75 > 5/8" .625 > > 6 rows selected. > > Diana Duncan > TITAN Technology Partners > One Copley Parkway, Ste 540 > Morrisville, NC 27560 > VM: 919.466.7337 x 316 > F: 919.466.7427 > E: [EMAIL PROTECTED] > > > > "Apps Sol" > > mail.com>cc: > Sent by: Fax to: > root@fatcity.Subject: SQL : Order by for varchar ??? > com > &g
Re: SQL : Order by for varchar ???
Looks like I am missing something..
this is the error we get
select width, fractionToDecimal(width) from test
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
error
ORA-06512: at "PS09.FRACTIONTODECIMAL", line 12
ORA-06512: at line 1
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, June 07, 2001 3:41 PM
>
> Ooh, a fun one. If you are sure of the format of the data (as you'll see
> in the following function) you can create a function to make the data
> numeric. I can't think of another way to do it, but maybe someone else
> can?...
>
> create or replace function fractionToDecimal (str in varchar2) return
> number is
> fract varchar2(100);
> dec number;
> begin
> -- This function takes numbers in the form below and returns a
decimal
> number
> -- [whole-]numerator[/denominator]["|']
> -- For example:
> -- 5/8"
> -- 1-1/2"
> -- 4'
> --
>
> -- Remove the symbol.
> --
> fract := rtrim(str, '"''');
>
> -- Replace occurences of '-' with '+'
> --
> fract := replace(fract, '-', '+');
>
> -- Evaluate the resulting expression
> --
> execute immediate 'select ' || fract || ' from dual' into dec;
>
> return dec;
> end fractionToDecimal;
> /
>
> 1 select width, fractionToDecimal(width) from test
> 2* order by 2 desc
> SQL> /
>
> WIDTHFRACTIONTODECIMAL(WIDTH)
>
> 4" 4
> 2" 2
> 1-1/2"1.5
> 1" 1
> 3/4" .75
> 5/8" .625
>
> 6 rows selected.
>
> Diana Duncan
> TITAN Technology Partners
> One Copley Parkway, Ste 540
> Morrisville, NC 27560
> VM: 919.466.7337 x 316
> F: 919.466.7427
> E: [EMAIL PROTECTED]
>
>
>
> "Apps Sol"
>
> mail.com>cc:
> Sent by: Fax to:
> root@fatcity.Subject: SQL : Order by for
varchar ???
> com
>
>
> 06/07/2001
> 01:56 PM
> Please
> respond to
> ORACLE-L
>
>
>
>
>
>
> One of our developers was looking for solution for his problem ..
>
> Any idea folks .. ??
>
> He wants to sort his data asc or desc for a varchar column ..
>
>
> 1> create table test(width varchar2(20));
>
> 2>
> insert into test values('5/8"');
> insert into test values('4"');
> insert into test values('3/4"');
> insert into test values('2"');
> insert into test values('1-1/2"');
> insert into test values('1"');
>
> 3>
> select width from test order by width DESC;
>
>
> 5/8"
> 3/4"
> 1"
> 1-1/2"
> 2"
> 4"
>
>
> Cheers
> RK
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Apps Sol
> 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 ORA
Re: SQL : Order by for varchar ???
Apps Sol wrote:
>
> One of our developers was looking for solution for his problem ..
>
> Any idea folks .. ??
>
> He wants to sort his data asc or desc for a varchar column ..
>
> 1> create table test(width varchar2(20));
>
> 2>
> insert into test values('5/8"');
> insert into test values('4"');
> insert into test values('3/4"');
> insert into test values('2"');
> insert into test values('1-1/2"');
> insert into test values('1"');
>
> 3>
> select width from test order by width DESC;
>
>
> 5/8"
> 3/4"
> 1"
> 1-1/2"
> 2"
> 4"
>
> Cheers
> RK
Are your developers masochist? I think that you have two ways out :
1) Write a stored function of death which evaluates expressions and
returns a number, which could be used in the order by.
2) Store the values as numbers (float), and write a function which, for
display purpose, returns them in the format given above (although I
don't see how make 1-1/2" not appear as 1/2"). This is likely to be
easier to do (given a float value p, finding the smaller integer n such
as p * n is an integer, or in other words is equal to trunc(p * n), then
returning to_char(p * n) || '/' || ltrim(to_char(n)) || '"'; you can
then quietly order by your number.
Guess which one I prefer. The problem of course is how data is entered,
which means that in practice you may find difficult to avoid 1). Now, if
it's all p/n or p kinds of strings, it's relatively easy to do with a
pinch of instr(), one cup of substr() and two of to_number().
--
Regards,
Stephane Faroult
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax:+44 (0) 7050-696-449
Performance Tools & Free Scripts
--
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--
--
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 : Order by for varchar ???
Ooh, a fun one. If you are sure of the format of the data (as you'll see
in the following function) you can create a function to make the data
numeric. I can't think of another way to do it, but maybe someone else
can?...
create or replace function fractionToDecimal (str in varchar2) return
number is
fract varchar2(100);
dec number;
begin
-- This function takes numbers in the form below and returns a decimal
number
-- [whole-]numerator[/denominator]["|']
-- For example:
-- 5/8"
-- 1-1/2"
-- 4'
--
-- Remove the symbol.
--
fract := rtrim(str, '"''');
-- Replace occurences of '-' with '+'
--
fract := replace(fract, '-', '+');
-- Evaluate the resulting expression
--
execute immediate 'select ' || fract || ' from dual' into dec;
return dec;
end fractionToDecimal;
/
1 select width, fractionToDecimal(width) from test
2* order by 2 desc
SQL> /
WIDTHFRACTIONTODECIMAL(WIDTH)
4" 4
2" 2
1-1/2"1.5
1" 1
3/4" .75
5/8" .625
6 rows selected.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
"Apps Sol"
mail.com>cc:
Sent by: Fax to:
root@fatcity.Subject: SQL : Order by for varchar ???
com
06/07/2001
01:56 PM
Please
respond to
ORACLE-L
One of our developers was looking for solution for his problem ..
Any idea folks .. ??
He wants to sort his data asc or desc for a varchar column ..
1> create table test(width varchar2(20));
2>
insert into test values('5/8"');
insert into test values('4"');
insert into test values('3/4"');
insert into test values('2"');
insert into test values('1-1/2"');
insert into test values('1"');
3>
select width from test order by width DESC;
5/8"
3/4"
1"
1-1/2"
2"
4"
Cheers
RK
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Apps Sol
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
---
RE: SQL : Order by for varchar ???
How about keeping a hidden field out in the table that has a trigger that,
when an insert or update is done, it takes the varchar value, goes thru some
magic, and converts it to the decimal equivalent. I mean, what if you have
mixed 1' 3 1/4 ". You would have to convert this to 15.25 then store it.
THEN do your ordering on this field.
Or, if you do have a function that can look at the value and convert it on
the fly, just order by that function acting upon the data field.
Such as Select * from test order by width_converter(width);
-Original Message-
Sent: Thursday, June 07, 2001 12:56 PM
To: Multiple recipients of list ORACLE-L
One of our developers was looking for solution for his problem ..
Any idea folks .. ??
He wants to sort his data asc or desc for a varchar column ..
1> create table test(width varchar2(20));
2>
insert into test values('5/8"');
insert into test values('4"');
insert into test values('3/4"');
insert into test values('2"');
insert into test values('1-1/2"');
insert into test values('1"');
3>
select width from test order by width DESC;
5/8"
3/4"
1"
1-1/2"
2"
4"
Cheers
RK
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Apps Sol
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: Kevin Lange
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).
SQL : Order by for varchar ???
One of our developers was looking for solution for his problem ..
Any idea folks .. ??
He wants to sort his data asc or desc for a varchar column ..
1> create table test(width varchar2(20));
2>
insert into test values('5/8"');
insert into test values('4"');
insert into test values('3/4"');
insert into test values('2"');
insert into test values('1-1/2"');
insert into test values('1"');
3>
select width from test order by width DESC;
5/8"
3/4"
1"
1-1/2"
2"
4"
Cheers
RK
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Apps Sol
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: Order by for varchar ???
Add a second column and store the value in decimal; then sort by that
column.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, June 07, 2001 12:56 PM
> One of our developers was looking for solution for his problem ..
>
> Any idea folks .. ??
>
> He wants to sort his data asc or desc for a varchar column ..
>
>
> 1> create table test(width varchar2(20));
>
> 2>
> insert into test values('5/8"');
> insert into test values('4"');
> insert into test values('3/4"');
> insert into test values('2"');
> insert into test values('1-1/2"');
> insert into test values('1"');
>
> 3>
> select width from test order by width DESC;
>
>
> 5/8"
> 3/4"
> 1"
> 1-1/2"
> 2"
> 4"
>
>
> Cheers
> RK
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Apps Sol
> 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: Marc Perkowitz
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).
Difference in speed of sorting using index and non-index column in the order by
Dear list, Someone ask me when Oracle is doing a sort, will it make a difference if we use indexed column and non-indexes column in the order by clause, my initial thinking was index only speed up the retrieval of data from disk, Oracle probably using other algorithm in sorting records, so indexing is not relevant in that case. Can someone shed light on this question. KC
Re: order by non-unique column, repeatable?
You are correct. Even though 95% of the time they WILL be returned in the same order, it is definitely not guaranteed. At 09:50 AM 4/6/01 -0800, you wrote: >If a select statement orders by a column whose values are not unique, are >the records returned in an order that is guaranteed to be repeatable from >query to query? (Assuming no updates, deletes, or inserts between >queries.) > >I would think not, that guaranteed repeatable ordering requires ordering on >a unique column or combination of columns, but I've been unable to generate >a test case that proves it. > >Thanks >Bill >-- > >__ >http://www.datacraft.com/http://plnet.org/ > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Bill Pribyl > 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: Regina Harter 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).
order by non-unique column, repeatable?
If a select statement orders by a column whose values are not unique, are the records returned in an order that is guaranteed to be repeatable from query to query? (Assuming no updates, deletes, or inserts between queries.) I would think not, that guaranteed repeatable ordering requires ordering on a unique column or combination of columns, but I've been unable to generate a test case that proves it. Thanks Bill -- __ http://www.datacraft.com/http://plnet.org/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pribyl 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).
