RE: A SQL Question
Select * from my_table order by col1; -Original Message- Sent: Thursday, March 13, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Igor (and all): Yes, our SPAM Cops and their filters are very strict with the wording in the e-mail footers. Unfortunately, FatCity.com uses the footer that gets caught by these filters. When replying to me directly, using list message, you need to remove the old footers from the e-mail. Sorry about this little problem. I will post my Corrected SQL Question again... Thanks. - Kirti -Original Message- Sent: Thursday, March 13, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Kirti, I tried to reply to your direct e-mail, but your mail-server is very strict and considered my message to be "Unsolicited Bulk Email". What I was trying to say is: Oracle-l list behaves very strangely (sometimes), I'm still waiting to see corrected version of your question. And actually I suspected, that the question isn't that simple -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 8:23 AM > Hi SQL Developers, > > I have a table as follows: > > Col1 Col2 > > AB > CD > EF > GH > BA > EF > CD > HG > > With a PK on (Col1, Col2). > > How do I write a SQL script to get following result? > > Col1Col2 > > AB > BA > CD > DC > EF > FE > G H > H G > > Thanks for your help. > > - Kirti > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: A SQL Question
Will 'ORDER BY col1' not do?? ;-) Atleast in this example it does. What are exact requirements? Regards Naveen -Original Message- Sent: Thursday, March 13, 2003 6:54 PM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Kirti, is this a trick question, or am I missing something? select col1, col2 from table order by col1 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Kirti, Would not and order by col1,col2 give the resulting set you want? Is the data shown correct? you have C,D twice. I think you ment C,D and D,C. Ron >>> [EMAIL PROTECTED] 03/13/03 08:23AM >>> Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Kirti, I tried to reply to your direct e-mail, but your mail-server is very strict and considered my message to be "Unsolicited Bulk Email". What I was trying to say is: Oracle-l list behaves very strangely (sometimes), I'm still waiting to see corrected version of your question. And actually I suspected, that the question isn't that simple -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 8:23 AM > Hi SQL Developers, > > I have a table as follows: > > Col1 Col2 > > AB > CD > EF > GH > BA > EF > CD > HG > > With a PK on (Col1, Col2). > > How do I write a SQL script to get following result? > > Col1Col2 > > AB > BA > CD > DC > EF > FE > G H > H G > > Thanks for your help. > > - Kirti > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Assuming dups can be deleted, here's my humble attempt: select col1, col2 from t order by col1, col2; Col1 Col2 -- AB BA CD EF GH HG 6 rows selected. select col1, col2 from t union select col2, col1 from t ; Col1 Col2 -- AB BA CD DC EF FE GH HG 8 rows selected. mohammed --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > Hi SQL Developers, > > I have a table as follows: > > Col1 Col2 > > AB > CD > EF > GH > BA > EF > CD > HG > > With a PK on (Col1, Col2). > > How do I write a SQL script to get following result? > > > Col1Col2 > > AB > BA > CD > DC > EF > FE > G H > H G > > Thanks for your help. > > - Kirti > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Deshpande, Kirti > 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). > __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb 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: A SQL Question
Hi Kirti, This isn't possible. The primary key won't allow for the duplicate values. There are 2 records of C,D and 2 records of E,F. Darrell >>> [EMAIL PROTECTED] 03/13/03 07:23AM >>> Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Darrell Landrum 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: A SQL Question
I messed up typing the data for the table. It has no dups. The second occurrence of C, D and E, F should actually be D, C and F, E. Sorry about that... Need more hot tea to wake me up !! - Kirti > -Original Message- > From: Deshpande, Kirti > Sent: Thursday, March 13, 2003 7:25 AM > To: oracle list (E-mail) > Subject: A SQL Question > > Hi SQL Developers, > > I have a table as follows: > > Col1 Col2 > > AB > CD > EF > GH > BA > EF > CD > HG > > With a PK on (Col1, Col2). > > How do I write a SQL script to get following result? > > Col1Col2 > > AB > BA > CD > DC > EF > FE > G H > H G > > Thanks for your help. > > - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: A SQL Question
Title: RE: A SQL Question Kirti, It's impossible to have a primary key as you have duplicate values. C-D and E-F both have dupes. If there should be D-C and F-E, a simple Order By Col1 would do the trick. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED] Hi SQL Developers, I have a table as follows: Col1 Col2 A B C D E F G H B A E F C D H G With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1 Col2 A B B A C D D C E F F E G H H G Thanks for your help. - Kirti
RE: A SQL Question
>Hi SQL Developers, > >I have a table as follows: > >Col1 Col2 > >AB >CD >EF >GH >BA >EF >CD >HG > >With a PK on (Col1, Col2). > >How do I write a SQL script to get following >result? > >Col1Col2 > >AB >BA >CD >DC >EF >FE >G H >H G > >Thanks for your help. > >- Kirti Kirti, On your example 'ORDER BY COL1' should be enough :-). I have a solution which is not excellent (I dislike the way I prevent the query from returning too many rows), but seems to be working even when there is no transitivity. May at least give you an idea on which to start work : select * from (select * from T connect by col1 = prior col2 and col1 > col2) x where rownum <= (select count(*) from T) / Regards, Stephane Faroult Oriole -- 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: A SQL Question
Hi Kirti, Just a clarification: PK on col1, col2 but you have duplicates C,D and E,F. If the dups are removed, is the porblem still valid? mohammed --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > Hi SQL Developers, > > I have a table as follows: > > Col1 Col2 > > AB > CD > EF > GH > BA > EF > CD > HG > > With a PK on (Col1, Col2). > > How do I write a SQL script to get following result? > > > Col1Col2 > > AB > BA > CD > DC > EF > FE > G H > H G > > Thanks for your help. > > - Kirti > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Deshpande, Kirti > 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). > __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb 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: A SQL Question
Title: RE: A SQL Question SELECT table.Col1, table.Col2 FROM table UNION SELECT table.Col2, table.Col1 FROM table ORDER BY table.Col1; Actually you might not even need the ORDER BY Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED] Hi SQL Developers, I have a table as follows: Col1 Col2 A B C D E F G H B A E F C D H G With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1 Col2 A B B A C D D C E F F E G H H G Thanks for your help. - Kirti
RE: A SQL Question
Kirti - I haven't had enough coffee this morning, so it seems to me the obvious solution is an order by clause. What am I missing here? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] SQL> select * from test; C C - - A B C D E F G H B A F E D C H G 8 rows selected. SQL> select * from test order by col1; C C - - A B B A C D D C E F F E G H H G 8 rows selected. -Original Message- Sent: Thursday, March 13, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Kirti,
I think, you have typo (duplicate rows), when describing data inserted into
table, considering PK on (col1, col2).
Shouldn't it be:
SQLWKS> create table test(
2> col1 varchar2(10),
3> col2 varchar2(10),
4> constraint PK_TEST primary key (col1, col2));
Statement processed.
SQLWKS>
SQLWKS> insert into test (col1, col2) values ('A', 'B');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('C', 'D');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('E', 'F');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('G', 'H');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('B', 'A');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('F', 'E');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('D', 'C');
1 row processed.
SQLWKS> insert into test (col1, col2) values ('H', 'G');
1 row processed.
SQLWKS> commit;
Statement processed.
SQLWKS>
SQLWKS> select * from test;
COL1 COL2
-- --
A B
C D
E F
G H
B A
F E
D C
H G
8 rows selected.
SQLWKS> select * from test order by col1;
COL1 COL2
-- --
A B
B A
C D
D C
E F
F E
G H
H G
8 rows selected.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, March 13, 2003 8:23 AM
> Hi SQL Developers,
>
> I have a table as follows:
>
> Col1 Col2
>
> AB
> CD
> EF
> GH
> BA
> EF
> CD
> HG
>
> With a PK on (Col1, Col2).
>
> How do I write a SQL script to get following result?
>
> Col1Col2
>
> AB
> BA
> CD
> DC
> EF
> FE
> G H
> H G
>
> Thanks for your help.
>
> - Kirti
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Deshpande, Kirti
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
A SQL Question
Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Tricky SQL Question -- Solved
Title: RE: Tricky SQL Question -- Solved Jonathan, Thanks for the tips ... let me see how I incorporate this ... things to do ... 1. write a *clever* routine to look at sys.mon_mods$ with dbms_stats.flush_database_monitoring_info to decide which tables to analyze in the next session. 2. Split the groups automatically between two instances ... if once instance is down, other will pick up all streams .. *this is easy to do in pl/sql). This allows me to have only two scripts to setup analysis on my 10 production and 24 other instances without a major maintenance. Now I am able to *predict* how much time it takes ... based on last analysis ... here is a sample input based on yesterday's data ... [EMAIL PROTECTED]> . oraenv ORACLE_SID = [CSI2] ? ABC [EMAIL PROTECTED]> sys SQL*Plus: Release 9.2.0.2.0 - Production on Fri Mar 7 08:36:56 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL> set serveroutput on SQL> exec system.dbpk_statistics.refresh_rank; Ranking based on analyze time is complete. Select from view "V_ANALYSIS_INFO" for an *estimate*. PL/SQL procedure successfully completed. SQL> set line 200 SQL> select * from system.v_analysis_info; Analysis Information - Group 01 includes 2160 tables, analysis should take approx 00134.10 seconds. Group 02 includes 413 tables, analysis should take approx 00134.03 seconds. Group 03 includes 33 tables, analysis should take approx 00130.91 seconds. Group 04 includes 11 tables, analysis should take approx 00128.63 seconds. Group 05 includes 4 tables, analysis should take approx 00113.87 seconds. Group 06 includes 4 tables, analysis should take approx 00152.77 seconds. Group 07 includes 2 tables, analysis should take approx 00098.63 seconds. Group 08 includes 2 tables, analysis should take approx 00180.71 seconds. 8 rows selected. I have configured it to make 8 parallel streams .. Here are some things that I am doing ... 1. The information is stored in a index organized table 2. As soon as the script loads, it loads (bulk collect) the lost of tables belonging to the group specified into an array. 3. Start executing dbms_stats on the tables in the array based on their parameters, capture elapsed times for analysis 4. If any analysis errors out, it also captures error message 5. Update the index organized table with a. last analyzed timestamp b. time it took to analyze the table c. error message if any d. uses dbms_stats to get latest rowcount 6. Exits The package has procedures to performs the set-up (tables/view/procedure/package creation). It also does two types of ranks, first time when analysis times are not available, it groups them by row count. After first analysis, it re-ranks them based on analysis time which is more accurate than row count. Thanks once again for all the ideas ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! *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.*1
RE: Tricky SQL Question
Title: RE: Tricky SQL Question Thanks Steven, I believe Tom touched on this in his discussion at recent Hotsos conference. My requirements were slightly different, but the logic is still good for me. Thanks Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 4:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: Tricky SQL Question Raj, I may not be offering information useful in solving your specific stats problem. If that's the case, Undskyld. However, this information is certainly useful. This link to the asktom website contains a method for dividing up large tables into ranges of rowids so that multiple sessions can efficiently process different pieces of the same object. Last month, I had the opportunity to see Mr. Kyte demonstrate this during one of his presentations. http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:7638743750722 -Steve -Original Message- Sent: Thursday, March 06, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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: 06 March 2003 16:49 > Hi all, > > I have a tricky situation ... I have a table > > columns are > owner varchar2(), > name varchar2(), > ana_tm number > > ana_tm represents how much time it took to perform statistics collection for > owner.name value. the number ranges from 0 to about 12000 right now, and is > subject to change. and say sum(ana_tm) over the table is say X. > > What I'd like to have is split this data into say N groups (Let's say 8), > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this > example). > > What I need is a way in SQL to splice the table list in eight groups so that > when I run a parallel 8 stream analyze, they all roughly take same amount of > time. I tried width_bucket() and it doesn't give me things that I need. It > assumes a linear distribution, which I do not have. > > Is this possible to do in SQL only? > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > Raj > - > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the
Re: sql question ???
A ZERO length varchar is treated as NULL so your second query should be select count(*) from cli_clients where trim(client_company) is null and cli_id in (257, 396, 727); At 12:09 PM 3/6/2003 -0800, you wrote: Hi, I got a SQL question (9i on Red Hat), commands shown below. The first sql returns 3 rows with value 1, so trim(client_company) = '', how come the 2nd sql doesn't return anything?? SQL> select decode(trim(client_company), '', 1, ' ', 2, null, 3, 4) from cli_clients where cli_id in (257, 396, 727); DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4) --- 1 1 1 3 rows selected. SQL> select count(*) from cli_clients where trim(client_company) = '' and cli_id in (257, 396, 727); COUNT(*) - 0 1 row selected. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tricky SQL Question
Jonathan Lewis wrote: > > Very cute - > > But it doesn't really cope well with > a few outlying values at the top end > of the range. Using double the count > to invert the high/low distribution is > neat - but only if the distribution is > fairly smooth to start with. > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > Absolutely right, and in fact Raj's solution (which I received after having posted mine) copes better with this. In fact I have already had the problem with parallel exports, and I think that the best solution would be to have one group for the 3 or 4 megatables you find in every schema, and then distribute the zillion remaining tables along the line I suggested. Something along the famous 95/5 Oracle distribution ... I guess that if you have n threads and one item represents more than 1/n minus a fudge factor it can safely be given a dedicated thread ... But it is too late for me now to do it in a single SQL statement :-). -- 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: Tricky SQL Question -- Solved
Very clever ! Can I make a couple of suggestions: You've got a very large number of tables in one group - and the startup time for the analyze might have a big impact on this group - so how about adding in (say) one second to the analyze type in order to cater for startup. Also - how about taking out any tables which individually take up more than the sum(all_times)/count(streams) before running the query on the rest. You might try randomising the ordering for the rest of the tables instead of ordering them by analyze time (since you have a large number and a lot use very small times) - I suspect this would help to flatten out the peaks in the timing, and make the number of tables per stream much more even - so reducing the effect of startup times. I have a very simple-minded (sub-optimal) procedural solution, but I'm trying to work out a way of expressing it non-procedurally. If I succeed I'll let you know. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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: 06 March 2003 19:38 > Okay, > > I cracked it ... if you are interested, read on ... it is not very optimal, > but close to what I want. To me 8 streams is standard, so you'd see 8 as > hardcoded. Also I found that > > select sum(obj_last_analyze_time)/8 from statistics_info > / > > was about 8425 (i.e. ~ 85 seconds). > > So I wrote this not-so-dynamic sql > > select group_id, sum(tm1), count(*) > from( > SELECT obj_owner, obj_name, tm1, >case when roll_sum <= 8400*1 then 1 else > case when roll_sum <= 8400*2 then 2 else >case when roll_sum <= 8400*3 then 3 else > case when roll_sum <= 8400*4 then 4 else >case when roll_sum <= 8400*5 then 5 else > case when roll_sum <= 8400*6 then 6 else >case when roll_sum <= 8400*7 then 7 else 8 >end > end >end > end >end > end >end group_id > FROM (SELECT rnum, obj_owner, obj_name, tm1, >SUM (tm1) OVER > (ORDER BY rnum RANGE UNBOUNDED PRECEDING) roll_sum > FROM (SELECT ROWNUM rnum, obj_owner, obj_name, tm1 > FROM (SELECT obj_owner ,obj_name,obj_last_analyze_time tm1 > FROM statistics_info > ORDER BY obj_last_analyze_time))) > ) group by group_id > / > > The output is as follows ... > > "GROUP_ID" "TOT_TIME" "TOT_TABLES" > -- > 1 8397 1755 > 2 8387 667 > 3 8204 135 > 4 7984 20 > 5 8954 7 > 6 6928 3 > 7 7113 2 > 8 11438 1 > > I'll probably make it dynamic enough ... inside my package ... > Cheers > Raj > - > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > -- 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).
RE: Tricky SQL Question
Raj, I may not be offering information useful in solving your specific stats problem. If that's the case, Undskyld. However, this information is certainly useful. This link to the asktom website contains a method for dividing up large tables into ranges of rowids so that multiple sessions can efficiently process different pieces of the same object. Last month, I had the opportunity to see Mr. Kyte demonstrate this during one of his presentations. http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:763874375 0722 -Steve -Original Message- Sent: Thursday, March 06, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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: 06 March 2003 16:49 > Hi all, > > I have a tricky situation ... I have a table > > columns are > owner varchar2(), > name varchar2(), > ana_tm number > > ana_tm represents how much time it took to perform statistics collection for > owner.name value. the number ranges from 0 to about 12000 right now, and is > subject to change. and say sum(ana_tm) over the table is say X. > > What I'd like to have is split this data into say N groups (Let's say 8), > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this > example). > > What I need is a way in SQL to splice the table list in eight groups so that > when I run a parallel 8 stream analyze, they all roughly take same amount of > time. I tried width_bucket() and it doesn't give me things that I need. It > assumes a linear distribution, which I do not have. > > Is this possible to do in SQL only? > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > Raj > - > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tricky SQL Question
Title: RE: Tricky SQL Question Stephane, Nice ... very nice script ... it is very close to what I came up with. Thanks everyone Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! *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.*1
Re: Tricky SQL Question
Very cute - But it doesn't really cope well with a few outlying values at the top end of the range. Using double the count to invert the high/low distribution is neat - but only if the distribution is fairly smooth to start with. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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: 06 March 2003 20:33 > > I have taken good note that 'elegant' is not one of your > requirements :-). > > select decode(sign(8 - mod(rownum - 1, 14)), > 1, mod(rownum - 1, 14), > 7 - mod(rownum - 1, 7)) "GROUP", >x.owner, >x.name > from (select owner, name > from your_table > order by ana_tm desc) x; > > > This should more or less work, even on 7.2. > -- 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).
Re: sql question ???
Andrea Oracle wrote: > > Hi, I got a SQL question (9i on Red Hat), commands > shown below. The first sql returns 3 rows with value > 1, so trim(client_company) = '', how come the 2nd sql > doesn't return anything?? > > SQL> select decode(trim(client_company), '', 1, ' ', > 2, null, 3, 4) from cli_clients where > cli_id in (257, 396, 727); > > DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4) > --- > 1 > 1 > 1 > > 3 rows selected. > > SQL> select count(*) from cli_clients where > trim(client_company) = '' and cli_id in (257, > 396, 727); > > COUNT(*) > - > 0 > > 1 row selected. > Because NULLs (and '' is a NULL string) can be DECODEdbut are never equal to anything. -- 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: Tricky SQL Question
> "Jamadagni, Rajendra" wrote: > > Thanks Jonathan, > > I'd like to assign the tables to a group, but need to do that > periodically. Also what I do is load all tables that belong to a group > in a pl/sql table (bulk updates/bulk collects). That's why I don't > want to do read-from-table ... do-action > > BTW this doesn't have to be optimal ... I am just trying to split the > load ... > > Raj > - > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > -Original Message- > From: Jonathan Lewis [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 06, 2003 12:44 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Tricky SQL Question > > Thinking back to university days, I think this > was called the knapsack problem, and at the > time there was no algorithm guaranteed to > give an optimal solution. > > If there is no simple non-procedural algorithm - > how about a strategy that simply allows each > slave to take the longest task that has not yet > been run until there are no jobs left to run ? > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon one-day tutorials: > Cost Based Optimisation > Trouble-shooting and Tuning > Indexing Strategies > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > UK___March 19th > UK___April 8th > UK___April 22nd > > USA_(FL)_May 2nd > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > 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: 06 March 2003 16:49 > > > Hi all, > > > > I have a tricky situation ... I have a table > > > > columns are > > owner varchar2(), > > name varchar2(), > > ana_tm number > > > > ana_tm represents how much time it took to perform statistics > collection for > > owner.name value. the number ranges from 0 to about 12000 right now, > > and is > > subject to change. and say sum(ana_tm) over the table is say X. > > > > What I'd like to have is split this data into say N groups (Let's > say 8), > > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in > this > > example). > > > > What I need is a way in SQL to splice the table list in eight groups > > so that > > when I run a parallel 8 stream analyze, they all roughly take same > amount of > > time. I tried width_bucket() and it doesn't give me things that I > need. It > > assumes a linear distribution, which I do not have. > > > > Is this possible to do in SQL only? > > > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > > Raj Raj, I have taken good note that 'elegant' is not one of your requirements :-). select decode(sign(8 - mod(rownum - 1, 14)), 1, mod(rownum - 1, 14), 7 - mod(rownum - 1, 7)) "GROUP", x.owner, x.name from (select owner, name from your_table order by ana_tm desc) x; This should more or less work, even on 7.2. -- 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 ???
Hi, I got a SQL question (9i on Red Hat), commands shown below. The first sql returns 3 rows with value 1, so trim(client_company) = '', how come the 2nd sql doesn't return anything?? SQL> select decode(trim(client_company), '', 1, ' ', 2, null, 3, 4) from cli_clients where cli_id in (257, 396, 727); DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4) --- 1 1 1 3 rows selected. SQL> select count(*) from cli_clients where trim(client_company) = '' and cli_id in (257, 396, 727); COUNT(*) - 0 1 row selected. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle 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: Tricky SQL Question -- Solved
Title: RE: Tricky SQL Question -- Solved Okay, I cracked it ... if you are interested, read on ... it is not very optimal, but close to what I want. To me 8 streams is standard, so you'd see 8 as hardcoded. Also I found that select sum(obj_last_analyze_time)/8 from statistics_info / was about 8425 (i.e. ~ 85 seconds). So I wrote this not-so-dynamic sql select group_id, sum(tm1), count(*) from( SELECT obj_owner, obj_name, tm1, case when roll_sum <= 8400*1 then 1 else case when roll_sum <= 8400*2 then 2 else case when roll_sum <= 8400*3 then 3 else case when roll_sum <= 8400*4 then 4 else case when roll_sum <= 8400*5 then 5 else case when roll_sum <= 8400*6 then 6 else case when roll_sum <= 8400*7 then 7 else 8 end end end end end end end group_id FROM (SELECT rnum, obj_owner, obj_name, tm1, SUM (tm1) OVER (ORDER BY rnum RANGE UNBOUNDED PRECEDING) roll_sum FROM (SELECT ROWNUM rnum, obj_owner, obj_name, tm1 FROM (SELECT obj_owner ,obj_name,obj_last_analyze_time tm1 FROM statistics_info ORDER BY obj_last_analyze_time))) ) group by group_id / The output is as follows ... "GROUP_ID" "TOT_TIME" "TOT_TABLES" -- 1 8397 1755 2 8387 667 3 8204 135 4 7984 20 5 8954 7 6 6928 3 7 7113 2 8 11438 1 I'll probably make it dynamic enough ... inside my package ... Cheers Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jamadagni, Rajendra Sent: Thursday, March 06, 2003 1:16 PM To: '[EMAIL PROTECTED]' Subject: RE: Tricky SQL Question - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 06 March 2003 16:49 > Hi all, > > I have a tricky situation ... I have a table > > columns are > owner varchar2(), > name varchar2(), > ana_tm number > > ana_tm represents how much time it took to perform statistics collection for > owner.name value. the number ranges from 0 to about 12000 right now, and is > subject to change. and say sum(ana_tm) over the table is say X. > > What I'd like to have is split this data into say N groups (Let's say 8), > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this > example). > > What I need is a way in SQL to splice the table list in eight groups so that > when I run a parallel 8 stream analyze, they all roughly take same amount of > time. I tried width_bucket() and it doesn't give me things that I need. It > assumes a linear distribution, which I do not have. > > Is this possible to do in SQL only? > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > Raj *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.*1
RE: Tricky SQL Question
Title: RE: Tricky SQL Question Thanks Jonathan, I'd like to assign the tables to a group, but need to do that periodically. Also what I do is load all tables that belong to a group in a pl/sql table (bulk updates/bulk collects). That's why I don't want to do read-from-table ... do-action BTW this doesn't have to be optimal ... I am just trying to split the load ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 06, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Tricky SQL Question Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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: 06 March 2003 16:49 > Hi all, > > I have a tricky situation ... I have a table > > columns are > owner varchar2(), > name varchar2(), > ana_tm number > > ana_tm represents how much time it took to perform statistics collection for > owner.name value. the number ranges from 0 to about 12000 right now, and is > subject to change. and say sum(ana_tm) over the table is say X. > > What I'd like to have is split this data into say N groups (Let's say 8), > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this > example). > > What I need is a way in SQL to splice the table list in eight groups so that > when I run a parallel 8 stream analyze, they all roughly take same amount of > time. I tried width_bucket() and it doesn't give me things that I need. It > assumes a linear distribution, which I do not have. > > Is this possible to do in SQL only? > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > Raj > - > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > -- 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). *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.*1
Re: Tricky SQL Question
Thinking back to university days, I think this was called the knapsack problem, and at the time there was no algorithm guaranteed to give an optimal solution. If there is no simple non-procedural algorithm - how about a strategy that simply allows each slave to take the longest task that has not yet been run until there are no jobs left to run ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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: 06 March 2003 16:49 > Hi all, > > I have a tricky situation ... I have a table > > columns are > owner varchar2(), > name varchar2(), > ana_tm number > > ana_tm represents how much time it took to perform statistics collection for > owner.name value. the number ranges from 0 to about 12000 right now, and is > subject to change. and say sum(ana_tm) over the table is say X. > > What I'd like to have is split this data into say N groups (Let's say 8), > so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this > example). > > What I need is a way in SQL to splice the table list in eight groups so that > when I run a parallel 8 stream analyze, they all roughly take same amount of > time. I tried width_bucket() and it doesn't give me things that I need. It > assumes a linear distribution, which I do not have. > > Is this possible to do in SQL only? > > Thanks in advance, yes, you can go crazy with syntax, it is 9202. > Raj > - > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > -- 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).
Tricky SQL Question
Title: Tricky SQL Question Hi all, I have a tricky situation ... I have a table columns are owner varchar2(), name varchar2(), ana_tm number ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right now, and is subject to change. and say sum(ana_tm) over the table is say X. What I'd like to have is split this data into say N groups (Let's say 8), so that each group ends up having a sum(ana_tm) ~ X/N (i.e. X/8 in this example). What I need is a way in SQL to splice the table list in eight groups so that when I run a parallel 8 stream analyze, they all roughly take same amount of time. I tried width_bucket() and it doesn't give me things that I need. It assumes a linear distribution, which I do not have. Is this possible to do in SQL only? Thanks in advance, yes, you can go crazy with syntax, it is 9202. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SQL question
Title: RE: SQL question Just trap the error and ignore it or add some other code for that particular situation, i.e. BEGIN INSERT INTO A VALUES (1); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- account already exists NULL; END; -Original Message- From: Rick Stephenson [mailto:[EMAIL PROTECTED]] Sent: Monday, February 24, 2003 3:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL question Sorry, I guess I could have been a little more clear. Another example: Table Employee: Emp_id number primary key -- generated with a sequence Emp_name varchar2(20) unique Table Employee_log: Emp_id number primary key Time_stamp date primary key Emp_stats varchar2(50) A process receives the employee name, and other information that needs to be stored in the table employee_log. The process needs to retrieve the emp_id from the employee table, so it does a lookup. If the employee exists, the emp_id is retrieved and the information is then inserted into the employee_log table. If the employee does not currently exist, a new employee is added to the table employee. We run into problems when we have many concurrent processes running and more than one process receives the same employee name. They both do a lookup and they both conclude the employee does not exist. Thus, they both try and do an insert into the employee table. One will succeed and the other will fail. Is there away to avoid this scenario? I hope I made this a little clearer. Thanks, Rick Stephenson -Original Message- Sent: Monday, February 24, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Rick - What about selecting the primary key for your table from a sequence? Oracle will ensure each session receives a unique number. What is your overall goal? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 1:50 PM To: Multiple recipients of list ORACLE-L OS: Solaris 2.8 Database: Oracle 9.2.0.2 Situation in chronological order Connection A: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection B: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection A: insert into table A(id) values = 1; Result: 1 row inserted Connection B: insert into table A(id) values = 1; Result: Unique constraint violated -- This is the problem. How do I avoid this happening? Question: How can I force connection B to wait for connection A to insert the new row before it does the select? If I were updating the row, I could use the "for update" clause to force the wait. Is there a clean way to do that for an insert? Thanks for your help, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of
Re: SQL question
Try to separate the employee-lookup-and-create into separate procedure. In the procedure, if the lookup does not find the employee, then call another procedure with an autonomous transaction to create the employee, that way the employee creation does not become part of the master transaction, is lightning quick, and greatly reduces the possibility of two processes creating the exact same employee record. Not guaranteed, but in 99.99% of the cases, this should work fine. Is this a packaged application, or can you make changes to the code that is invoked ? Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, February 25, 2003 7:54 AM > Sorry, I guess I could have been a little more clear. > > Another example: > > Table Employee: > Emp_id number primary key -- generated with a sequence > Emp_name varchar2(20) unique > > Table Employee_log: > Emp_id number primary key > Time_stamp date primary key > Emp_stats varchar2(50) > > A process receives the employee name, and other information that needs to be > stored in the table employee_log. The process needs to retrieve the emp_id > from the employee table, so it does a lookup. If the employee exists, the > emp_id is retrieved and the information is then inserted into the > employee_log table. If the employee does not currently exist, a new > employee is added to the table employee. > > We run into problems when we have many concurrent processes running and more > than one process receives the same employee name. They both do a lookup and > they both conclude the employee does not exist. Thus, they both try and do > an insert into the employee table. One will succeed and the other will > fail. > > Is there away to avoid this scenario? > > I hope I made this a little clearer. > > Thanks, > > Rick Stephenson > > -Original Message- > Sent: Monday, February 24, 2003 1:05 PM > To: Multiple recipients of list ORACLE-L > > Rick - What about selecting the primary key for your table from a sequence? > Oracle will ensure each session receives a unique number. > > What is your overall goal? > > > > Dennis Williams > DBA, 40%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Monday, February 24, 2003 1:50 PM > To: Multiple recipients of list ORACLE-L > > > > OS: Solaris 2.8 > > Database: Oracle 9.2.0.2 > > > > Situation in chronological order > > Connection A: select * from table A where id = 1; Result: no rows returned > -- This means I need to insert the row, as it does not exists yet. > > Connection B: select * from table A where id = 1; Result: no rows returned > -- This means I need to insert the row, as it does not exists yet. > > Connection A: insert into table A(id) values = 1; Result: 1 row inserted > > Connection B: insert into table A(id) values = 1; Result: Unique constraint > violated -- This is the problem. How do I avoid this happening? > > > > Question: How can I force connection B to wait for connection A to insert > the new row before it does the select? > > > > If I were updating the row, I could use the "for update" clause to force the > wait. Is there a clean way to do that for an insert? > > > > Thanks for your help, > > > > Rick Stephenson > > > > > > This email and any files transmitted with it are confidential and intended > solely for the use of the individual or entity to which they are addressed. > This message contains confidential information and is intended only for the > individual named. If you are not the named addressee you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately by e-mail if you have received this e-mail by mistake and delete > this e-mail from your system. If you are not the intended recipient you are > notified that disclosing, copying, forwarding or otherwise distributing or > taking any action in reliance on the contents of this information is > strictly prohibited. > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > This email and any files transmitted with it are confidential and intended > solely for the use of the individual or entity to which they are addressed. > This message contains confidential information and is intended only
Re: SQL question
Rick Stephenson wrote: > > Sorry, I guess I could have been a little more clear. > > Another example: > > Table Employee: > Emp_id number primary key -- generated with a sequence > Emp_name varchar2(20) unique > > Table Employee_log: > Emp_id number primary key > Time_stamp date primary key > Emp_stats varchar2(50) > > A process receives the employee name, and other information that needs to be > stored in the table employee_log. The process needs to retrieve the emp_id > from the employee table, so it does a lookup. If the employee exists, the > emp_id is retrieved and the information is then inserted into the > employee_log table. If the employee does not currently exist, a new > employee is added to the table employee. > > We run into problems when we have many concurrent processes running and more > than one process receives the same employee name. They both do a lookup and > they both conclude the employee does not exist. Thus, they both try and do > an insert into the employee table. One will succeed and the other will > fail. > > Is there away to avoid this scenario? > > I hope I made this a little clearer. > > Thanks, > > Rick Stephenson > > -Original Message- > Sent: Monday, February 24, 2003 1:05 PM > To: Multiple recipients of list ORACLE-L > > Rick - What about selecting the primary key for your table from a sequence? > Oracle will ensure each session receives a unique number. > > What is your overall goal? > > Dennis Williams > DBA, 40%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Monday, February 24, 2003 1:50 PM > To: Multiple recipients of list ORACLE-L > > OS: Solaris 2.8 > > Database: Oracle 9.2.0.2 > > > > Situation in chronological order > > Connection A: select * from table A where id = 1; Result: no rows returned > -- This means I need to insert the row, as it does not exists yet. > > Connection B: select * from table A where id = 1; Result: no rows returned > -- This means I need to insert the row, as it does not exists yet. > > Connection A: insert into table A(id) values = 1; Result: 1 row inserted > > Connection B: insert into table A(id) values = 1; Result: Unique constraint > violated -- This is the problem. How do I avoid this happening? > > > > Question: How can I force connection B to wait for connection A to insert > the new row before it does the select? > > > > If I were updating the row, I could use the "for update" clause to force the > wait. Is there a clean way to do that for an insert? > > > > Thanks for your help, > > > > Rick Stephenson Rick, Given what I currently know of the state of the economy, I guess that the insert will be a fairly rare occurrence ? I think that therefore locking the employee table in exclusive mode is acceptable ? I'd rather code something along the following lines : done := false; while not done loop insert into employee_log select emp_id, sysdate, your_data_here from employee where emp_name = input_name; if (sql%rowcount = 0) then begin lock table employee in exclusive mode nowait; insert into employee yadda yadda done :=true; exception when table_already_locked then null; end; else done := true; end if; end loop; commit; May be a bit hard on CPU; perhaps that adding a short pause when the 'table already locked by another session' exception is hit would be the thing to do. Depends on how intensive all this is. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
Sorry, I guess I could have been a little more clear. Another example: Table Employee: Emp_id number primary key -- generated with a sequence Emp_name varchar2(20) unique Table Employee_log: Emp_id number primary key Time_stamp date primary key Emp_stats varchar2(50) A process receives the employee name, and other information that needs to be stored in the table employee_log. The process needs to retrieve the emp_id from the employee table, so it does a lookup. If the employee exists, the emp_id is retrieved and the information is then inserted into the employee_log table. If the employee does not currently exist, a new employee is added to the table employee. We run into problems when we have many concurrent processes running and more than one process receives the same employee name. They both do a lookup and they both conclude the employee does not exist. Thus, they both try and do an insert into the employee table. One will succeed and the other will fail. Is there away to avoid this scenario? I hope I made this a little clearer. Thanks, Rick Stephenson -Original Message- Sent: Monday, February 24, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Rick - What about selecting the primary key for your table from a sequence? Oracle will ensure each session receives a unique number. What is your overall goal? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 1:50 PM To: Multiple recipients of list ORACLE-L OS: Solaris 2.8 Database: Oracle 9.2.0.2 Situation in chronological order Connection A: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection B: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection A: insert into table A(id) values = 1; Result: 1 row inserted Connection B: insert into table A(id) values = 1; Result: Unique constraint violated -- This is the problem. How do I avoid this happening? Question: How can I force connection B to wait for connection A to insert the new row before it does the select? If I were updating the row, I could use the "for update" clause to force the wait. Is there a clean way to do that for an insert? Thanks for your help, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rick Stephenson 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 containin
Re: SQL question
Why not use a sequence to populate ID, and let it fire of a before insert trigger. code example below: create sequence TAB1_PKSEQ ; create or replace trigger test_pkgenBEFORE INSERT OR UPDATE OF col_id on TABLE_AFOR EACH ROWBEGINIF INSERTING THEN SELECT TAB1_PKSEQ1.NextVal INTO :new.COL_ID FROM DUAL; /* or something like the above */END IF;END;/ I am assuming you have other columns to populate too, which is not the PK of the table. Cheers : Ferenc MantfeldDreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: Rick Stephenson To: Multiple recipients of list ORACLE-L Sent: Tuesday, February 25, 2003 6:49 AM Subject: SQL question OS: Solaris 2.8 Database: Oracle 9.2.0.2 Situation in chronological order Connection A: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection B: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection A: insert into table A(id) values = 1; Result: 1 row inserted Connection B: insert into table A(id) values = 1; Result: Unique constraint violated -- This is the problem. How do I avoid this happening? Question: How can I force connection B to wait for connection A to insert the new row before it does the select? If I were updating the row, I could use the "for update" clause to force the wait. Is there a clean way to do that for an insert? Thanks for your help, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: SQL question
Rick - What about selecting the primary key for your table from a sequence? Oracle will ensure each session receives a unique number. What is your overall goal? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, February 24, 2003 1:50 PM To: Multiple recipients of list ORACLE-L OS: Solaris 2.8 Database: Oracle 9.2.0.2 Situation in chronological order Connection A: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection B: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection A: insert into table A(id) values = 1; Result: 1 row inserted Connection B: insert into table A(id) values = 1; Result: Unique constraint violated -- This is the problem. How do I avoid this happening? Question: How can I force connection B to wait for connection A to insert the new row before it does the select? If I were updating the row, I could use the "for update" clause to force the wait. Is there a clean way to do that for an insert? Thanks for your help, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
Why not just have Connection B trap the Unique Constrait Error and branch to some different code? What would Connection B have done if it had found the record where id=1? -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 2/24/2003 2:49 PM, Rick Stephenson <[EMAIL PROTECTED]> wrote: > >OS: Solaris 2.8 >Database: Oracle 9.2.0.2 > >Situation in chronological order >Connection A: select * from table A where id = 1; Result: no rows >returned -- This means I need to insert the row, as it does not >exists yet. >Connection B: select * from table A where id = 1; Result: no rows >returned -- This means I need to insert the row, as it does not >exists yet. >Connection A: insert into table A(id) values = 1; Result: 1 row >inserted >Connection B: insert into table A(id) values = 1; Result: Unique >constraint violated -- This is the problem. How do I avoid this >happening? > >Question: How can I force connection B to wait for connection A >to insert the new row before it does the select? > >If I were updating the row, I could use the "for update" clause to >force the wait. Is there a clean way to do that for an insert? > >Thanks for your help, > >Rick Stephenson > > > >This email and any files transmitted with it are confidential and >intended solely for the use of the individual or entity to which >they are addressed. This message contains confidential information >and is intended only for the individual named. If you are not the >named addressee you should not disseminate, distribute or copy this >e-mail. Please notify the sender immediately by e-mail if you have >received this e-mail by mistake and delete this e-mail from your >system. If you are not the intended recipient you are notified that >disclosing, copying, forwarding or otherwise distributing or taking >any action in reliance on the contents of this information is strictly >prohibited. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Davey 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
OS: Solaris 2.8 Database: Oracle 9.2.0.2 Situation in chronological order Connection A: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection B: select * from table A where id = 1; Result: no rows returned -- This means I need to insert the row, as it does not exists yet. Connection A: insert into table A(id) values = 1; Result: 1 row inserted Connection B: insert into table A(id) values = 1; Result: Unique constraint violated -- This is the problem. How do I avoid this happening? Question: How can I force connection B to wait for connection A to insert the new row before it does the select? If I were updating the row, I could use the "for update" clause to force the wait. Is there a clean way to do that for an insert? Thanks for your help, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
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: SQL question
>>[EMAIL PROTECTED] wrote: >> . never trust Vladimir Begun, check everything what he's saying :) >> >> Trust? >> >> I don't know you well enough to not trust you. >May be 'trust' is not a right word here :) Sorry. Mine was supposed to have a :). Sorry. > . never use the sql that looks cool but does not work properly > . never tune a query that returns wrong result and compare its >performance with that one that works correctly but slowly. > > As you will see in another post, both queries > return identical results for me on 8.1.7. Check default NLS_DATE_FORMAT parameters of your session. Yup, that was it. Though I tend to always use mm/dd/ hh24:mi:ss unless there's some particular reason to do otherwise. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
Looks like you are a master of telepathy too... :) Khedr, Waleed wrote: What about: select count(count(*)) from emp group by ename, job Have fun :) We do... :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
Jared [EMAIL PROTECTED] wrote: . never trust Vladimir Begun, check everything what he's saying :) Trust? I don't know you well enough to not trust you. May be 'trust' is not a right word here :) Sorry. . never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its performance with that one that works correctly but slowly. As you will see in another post, both queries return identical results for me on 8.1.7. Check default NLS_DATE_FORMAT parameters of your session. To summarize, agregation can be done using: 1. (for this particular case) SELECT COUNT( DISTINCT( RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1)) || RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1)) || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*') ) ) AS l FROM emp / 2. SELECT COUNT(*) FROM ( SELECT DISTINCT ename , job , mydate FROM emp ) / 3. SELECT COUNT(COUNT(*)) FROM emp GROUP BY ename , job , mydate / 4. SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; unreliable solution (does not handle nulls and dates properly) SQL> SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM. HH24:MI:SS'; Session altered SQL> SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 6000 Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
. never trust Vladimir Begun, check everything what he's saying :)
Trust?
I don't know you well enough to not trust you.
. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
performance with that one that works correctly but slowly.
As you will see in another post, both queries
return identical results for me on 8.1.7.
Jared
Vladimir Begun <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/30/2003 12:16 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: SQL question
Jared
Windows 2k 9.2.0.1
534 hsecs
214 hsecs
Query I've used:
SELECT COUNT(
DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
|| RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1))
|| NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
)
) AS l
FROM emp
/
As you can see it's tightly bound to table definition one
has to handle nulls for varchars/chars.
L
-
7000
Check the resources -- I have doubts that this query is a
winner :)
So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :)
. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
performance with that one that works correctly but slowly.
Thanks!
Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Vladimir Begun wrote:
> Jared
>
> Jared Still wrote:
>
>> Though not a dramatic difference, the CONCAT was faster
>> and less resource intensive than the inline view with GROUP BY.
>>
>> :)
>
>
> Ok, let it be like that, but your test does not check some
> other things, like common sense, logic, and session memory.
> Performance can vary as I mentioned sometimes can be
> neglected, however let's consider the tricks you made before
> your test:
>
> 1. create index emp_idx on emp(ename, job, mydate); -- what for do
> you need it? It was not in the original problem definition. It's
> not used, however you created it, what's that for?
>
> 2. Both queries give different results, that's what I mentioned --
> you just proved my words :) One must be very careful with that.
> Even DISTINCT can lose sometimes like in your example, but it does
> not mean that the logic of the application works correctly in
> case of || = CONCAT is used. Think also about an artificial limit
> your create -- each and every varchar has to be padded to it's
> maximum length (become CHAR) -- that optional and case dependent,
> however; all date and numeric columns have to be formatted
> otherwise you can face the same case like your your example.
>
> SQL> SELECT COUNT(*) FROM emp;
>
>COUNT(*)
> --
> 64000
>
> SQL> select count(distinct(ename||job||mydate)) FROM emp;
>
> COUNT(DISTINCT(ENAME||JOB||MYDATE))
> ---
> 2000
>
> SQL> SELECT COUNT(*)
>2 FROM (
>3SELECT DISTINCT
>4 ename, job, mydate
>5FROM emp
>6 );
>
>COUNT(*)
> --
>7000
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: SQL question
> Ok, let it be like that, but your test does not check some > other things, like common sense, logic, and session memory. > Performance can vary as I mentioned sometimes can be > neglected, however let's consider the tricks you made before > your test: Well, I've never claimed to be common. And I didn't ignore session memory. If the PGA and UGA memory numbers are the same between runs, they don't appear in the run_stats.sql query. > 1. create index emp_idx on emp(ename, job, mydate); -- what for do > you need it? It was not in the original problem definition. It's > not used, however you created it, what's that for? An index seemed reasonable to me. But in the cause of fairness, I dropped the index and reran it a couple times to allow for re-caching the blocks. Similar results. 13:17:17 SQL>@th 389 hsecs 257 hsecs PL/SQL procedure successfully completed. 13:17:25 SQL>@run_stats NAME RUN1 RUN2 DIFF -- -- -- LATCH.active checkpoint queue latch 2 1 -1 LATCH.redo writing6 5 -1 STAT...calls to get snapshot scn: kcmgss 12 11 -1 STAT...redo entries 8 7 -1 STAT...messages sent 1 0 -1 STAT...deferred (CURRENT) block cleanout 3 2 -1 applications LATCH.library cache 74 71 -3 STAT...db block changes 17 14 -3 STAT...consistent gets 3346 3343 -3 LATCH.redo allocation 9 12 3 STAT...session logical reads 3458 3462 4 LATCH.messages 11 6 -5 STAT...db block gets112119 7 STAT...enqueue requests 41 50 9 LATCH.sort extent pool 90100 10 STAT...enqueue releases 40 50 10 LATCH.enqueue hash chains80100 20 LATCH.cache buffers lru chain63 23-40 LATCH.loader state object freelist 20 60 40 LATCH.enqueues 160200 40 LATCH.checkpoint queue latch 90 7-83 LATCH.cache buffers chains 7662 7783121 STAT...redo size 20888 20756 -132 STAT...recursive cpu usage 391258 -133 STAT...physical reads 552792240 STAT...physical reads direct550790240 STAT...physical writes 550790240 STAT...physical writes direct 550790240 STAT...physical writes non checkpoint 550790240 STAT...session pga memory 0 15848 15848 STAT...session pga memory max 0 15848 15848 31 rows selected. > 2. Both queries give different results, that's what I mentioned -- > you just proved my words :) One must be very careful with that. > Even DISTINCT can lose sometimes like in your example, but it does > not mean that the logic of the application works correctly in > case of || = CONCAT is used. Think also about an artificial limit > your create -- each and every varchar has to be padded to it's > maximum length (become CHAR) -- that optional and case dependent, > however; all date and numeric columns have to be formatted > otherwise you can face the same case like your your example. > > SQL> SELECT COUNT(*) FROM emp; > >COUNT(*) >-- > 64000 > >SQL> select count(distinct(ename||job||mydate)) FROM emp; > >COUNT(DISTINCT(ENAME||JOB||MYDATE)) >--- > 2000 >SQL> SELECT COUNT(*) >2 FROM ( >3SELECT DISTINCT >4 ename, job, mydate >5FROM emp >6 ); > >COUNT(*) >-- >7000 Interesting. My results correspond, I don't know why the difference. 13:11:04 SQL>set echo on 13:11:08 SQL>@q1 13:11:09 SQL>select count(distinct(ename||job||mydate)) 13:11:09 2 from emp 13:11:09 3 / COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 7000 1 row selected. 13:11:09 SQL>@q2 13:11:12 SQL> 13:11:12 SQL> 13:11:12 SQL>SELECT COUNT(*) 13:11:12 2 FROM ( 13:11:12 3 SELECT DISTINCT 13:11:12 4 ename, job, mydate 13:11:12 5 FROM emp 13:11:12 6 ) 13:11:12 7 / COUNT(*) --
RE: SQL question
Nah, I'm tired of it now.
It was an interesting diversion.
Jared
"Khedr, Waleed" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/30/2003 11:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: SQL question
What about:
select count(count(*))
from emp
group by ename, job
Have fun :)
Waleed
-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Jared
Jared Still wrote:
> Though not a dramatic difference, the CONCAT was faster
> and less resource intensive than the inline view with GROUP BY.
>
> :)
Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:
1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?
2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
--
64000
SQL> select count(distinct(ename||job||mydate)) FROM emp;
COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
2000
SQL> SELECT COUNT(*)
2 FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6 );
COUNT(*)
--
7000
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: SQL question
Jared Windows 2k 9.2.0.1 534 hsecs 214 hsecs Query I've used: SELECT COUNT( DISTINCT( RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1)) || RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1)) || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*') ) ) AS l FROM emp / As you can see it's tightly bound to table definition one has to handle nulls for varchars/chars. L - 7000 Check the resources -- I have doubts that this query is a winner :) So, the moral of this story: . never trust Vladimir Begun, check everything what he's saying :) . never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its performance with that one that works correctly but slowly. Thanks! Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Vladimir Begun wrote: Jared Jared Still wrote: Though not a dramatic difference, the CONCAT was faster and less resource intensive than the inline view with GROUP BY. :) Ok, let it be like that, but your test does not check some other things, like common sense, logic, and session memory. Performance can vary as I mentioned sometimes can be neglected, however let's consider the tricks you made before your test: 1. create index emp_idx on emp(ename, job, mydate); -- what for do you need it? It was not in the original problem definition. It's not used, however you created it, what's that for? 2. Both queries give different results, that's what I mentioned -- you just proved my words :) One must be very careful with that. Even DISTINCT can lose sometimes like in your example, but it does not mean that the logic of the application works correctly in case of || = CONCAT is used. Think also about an artificial limit your create -- each and every varchar has to be padded to it's maximum length (become CHAR) -- that optional and case dependent, however; all date and numeric columns have to be formatted otherwise you can face the same case like your your example. SQL> SELECT COUNT(*) FROM emp; COUNT(*) -- 64000 SQL> select count(distinct(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL> SELECT COUNT(*) 2 FROM ( 3SELECT DISTINCT 4 ename, job, mydate 5FROM emp 6 ); COUNT(*) -- 7000 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
What about: select count(count(*)) from emp group by ename, job Have fun :) Waleed -Original Message- Sent: Thursday, January 30, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Jared Jared Still wrote: > Though not a dramatic difference, the CONCAT was faster > and less resource intensive than the inline view with GROUP BY. > > :) Ok, let it be like that, but your test does not check some other things, like common sense, logic, and session memory. Performance can vary as I mentioned sometimes can be neglected, however let's consider the tricks you made before your test: 1. create index emp_idx on emp(ename, job, mydate); -- what for do you need it? It was not in the original problem definition. It's not used, however you created it, what's that for? 2. Both queries give different results, that's what I mentioned -- you just proved my words :) One must be very careful with that. Even DISTINCT can lose sometimes like in your example, but it does not mean that the logic of the application works correctly in case of || = CONCAT is used. Think also about an artificial limit your create -- each and every varchar has to be padded to it's maximum length (become CHAR) -- that optional and case dependent, however; all date and numeric columns have to be formatted otherwise you can face the same case like your your example. SQL> SELECT COUNT(*) FROM emp; COUNT(*) -- 64000 SQL> select count(distinct(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL> SELECT COUNT(*) 2 FROM ( 3SELECT DISTINCT 4 ename, job, mydate 5FROM emp 6 ); COUNT(*) -- 7000 -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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).
Re: SQL question
Jared Jared Still wrote: Though not a dramatic difference, the CONCAT was faster and less resource intensive than the inline view with GROUP BY. :) Ok, let it be like that, but your test does not check some other things, like common sense, logic, and session memory. Performance can vary as I mentioned sometimes can be neglected, however let's consider the tricks you made before your test: 1. create index emp_idx on emp(ename, job, mydate); -- what for do you need it? It was not in the original problem definition. It's not used, however you created it, what's that for? 2. Both queries give different results, that's what I mentioned -- you just proved my words :) One must be very careful with that. Even DISTINCT can lose sometimes like in your example, but it does not mean that the logic of the application works correctly in case of || = CONCAT is used. Think also about an artificial limit your create -- each and every varchar has to be padded to it's maximum length (become CHAR) -- that optional and case dependent, however; all date and numeric columns have to be formatted otherwise you can face the same case like your your example. SQL> SELECT COUNT(*) FROM emp; COUNT(*) -- 64000 SQL> select count(distinct(ename||job||mydate)) FROM emp; COUNT(DISTINCT(ENAME||JOB||MYDATE)) --- 2000 SQL> SELECT COUNT(*) 2 FROM ( 3SELECT DISTINCT 4 ename, job, mydate 5FROM emp 6 ); COUNT(*) -- 7000 -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
SELECT COUNT(*) FROM ( SELECT DISTINCT col1, col2. FROM ..) --- Charu Joshi <[EMAIL PROTECTED]> wrote: > Thanks all, > > My question was related more to the 'design' of SQL language. To my > mind the > expression COUNT(DISTINCT a,b) looked a natural extension of the > syntax > COUNT(DISTINCT a). Even COUNT(DISTINCT(a,b)) would look good enough > to me. > Probably it's too trivial a thing to bother about. Using the > subquery would > very well give the desired results. > > I have been thinking of reading CJ Date and other experts' articles > on the > design (and limitations) of SQL, but couldn't find any good > resources on the > net. If you know of any links, then can you please let me know? > > Thanks once again, > Charu. > > -Original Message- > Sent: Wednesday, January 29, 2003 10:04 PM > To: Multiple recipients of list ORACLE-L > > Charu, > The COUNT() function requires a single expression. "ename, job" is > not a valid expression. "ename||job" is a valid expression since it > will > return a single value. > Another alternative would be > select count(*) > from (select distinct ename, job from emp); > > Dan Fink > > -Original Message- > Sent: Wednesday, January 29, 2003 11:19 AM > To: Multiple recipients of list ORACLE-L > > Hello Listers, > > How to find out the COUNT of DISTINCT values of multiple columns? > > For eg. > > SQL> SELECT DISTINCT ename FROM emp; > -- This works. > > SQL> SELECT COUNT(DISTINCT ename) FROM emp; > -- So does this. > > SQL> SELECT DISTINCT ename, job FROM emp; > -- And this too. > > SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; > -- So why does this fail? > > I can do a COUNT(DISTINCT ename || job), but that doesn't seem to > be elegant > way of doing it. > > I have a feeling I might be missing some fairly basic syntax, but > feeling > dumb is better than suspense. > > Thanks & regards, > Charu. > > * > Disclaimer > > This message (including any attachments) contains > confidential information intended for a specific > individual and purpose, and is protected by law. > If you are not the intended recipient, you should > delete this message and are hereby notified that > any disclosure, copying, or distribution of this > message, or the taking of any action based on it, > is strictly prohibited. > > * > Visit us at http://www.mahindrabt.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Charu Joshi > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting > services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like > subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Fink, Dan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting > services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like > subscribing). > > * > Disclaimer > > This message (including any attachments) contains > confidential information intended for a specific > individual and purpose, and is protected by law. > If you are not the intended recipient, you should > delete this message and are hereby notified that > any disclosure, copying, or distribution of this > message, or the taking of any action based on it, > is strictly prohibited. > > * > Visit us at http://www.mahindrabt.com > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Charu Joshi > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting > services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the n
Re: SQL question
Vladimir, Thanks I hadn't considered || as a function, though it is. At first, I was going to take your word for it, but then decided this would be an interesting test. :) But first, I agree, you must know what you're looking for, neither of these would work in all situations. First, I built some test data: -- drop table emp; create table emp( ename, job ) as select table_name, column_name from dba_tab_columns where rownum < 1001 / alter table emp add ( mydate date ); update emp set mydate = sysdate; commit; declare v_date date; begin for f in 1 .. 5 loop insert into emp select ename, job, sysdate from emp; dbms_lock.sleep(1); end loop; insert into emp select ename, job, null from emp; end; / create index emp_idx on emp(ename, job, mydate); - This creates 64000 rows in emp. For testing, I'm using Tom Kytes run_stats.sql and test_harness.sql. The URL is something like govt.oracle.com/~tkyte/run_stats.html Not sure, because my internet connection is down as I write this. Below is the test harness code I used: --- -- test_harness.sql -- from Tom Kyte - asktom.oracle.com/~tkyte/runstats.html -- see ~/oracle/dba/run_stats for all files declare l_start number; --add any other variables you need here for the test... v_count integer; begin delete from run_stats; commit; -- start by getting a snapshot of the v$ tables insert into run_stats select 'before', stats.* from stats; -- and start timing... l_start := dbms_utility.get_time; -- for things that take a very small amount of time, I like to -- loop over it time and time again, to measure something "big" -- if what you are testing takes a long time, loop less or maybe -- not at all for i in 1 .. 10 loop select count(distinct(ename||job||mydate)) into v_count from emp; end loop; dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' ); -- get another snapshot and start timing again... insert into run_stats select 'after 1', stats.* from stats; l_start := dbms_utility.get_time; for i in 1 .. 10 loop SELECT COUNT(*) into v_count FROM ( SELECT DISTINCT ename, job, mydate FROM emp ); end loop; dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' ); insert into run_stats select 'after 2', stats.* from stats; end; / -- Now the results. Run 1 uses CONCAT, Run 2 uses an inline view with Group by. 22:13:02 sherlock - jkstill@ts01 SQL> @th 1691 hsecs 2032 hsecs PL/SQL procedure successfully completed. 22:13:49 sherlock - jkstill@ts01 SQL> @run_stats NAME RUN1 RUN2 DIFF -- -- -- STAT...consistent gets 3378 3379 1 STAT...db block changes 17 16 -1 LATCH.undo global data3 4 1 STAT...calls to get snapshot scn: kcmgss 23 22 -1 STAT...parse time elapsed 0 1 1 STAT...parse time cpu 0 1 1 STAT...deferred (CURRENT) block cleanout 3 2 -1 applications LATCH.active checkpoint queue latch 5 7 2 LATCH.virtual circuit queues 2 0 -2 LATCH.redo allocation13 18 5 LATCH.redo writing 22 27 5 LATCH.checkpoint queue latch 27 34 7 LATCH.messages 33 44 11 LATCH.session allocation 22 38 16 STAT...free buffer requested779761-18 LATCH.session idle bit 11 31 20 LATCH.shared pool 3 27 24 LATCH.multiblock read objects 312338 26 STAT...prefetched blocks607578-29 STAT...redo size 20964 21008 44 STAT...enqueue requests 441544103 STAT...enqueue releases 440544104 LATCH.sort extent pool 495599104 LATCH.library cache 241389148 LATCH.enqueue hash chains
RE: SQL question
Thanks all, My question was related more to the 'design' of SQL language. To my mind the expression COUNT(DISTINCT a,b) looked a natural extension of the syntax COUNT(DISTINCT a). Even COUNT(DISTINCT(a,b)) would look good enough to me. Probably it's too trivial a thing to bother about. Using the subquery would very well give the desired results. I have been thinking of reading CJ Date and other experts' articles on the design (and limitations) of SQL, but couldn't find any good resources on the net. If you know of any links, then can you please let me know? Thanks once again, Charu. -Original Message- Sent: Wednesday, January 29, 2003 10:04 PM To: Multiple recipients of list ORACLE-L Charu, The COUNT() function requires a single expression. "ename, job" is not a valid expression. "ename||job" is a valid expression since it will return a single value. Another alternative would be select count(*) from (select distinct ename, job from emp); Dan Fink -Original Message- Sent: Wednesday, January 29, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL> SELECT DISTINCT ename FROM emp; -- This works. SQL> SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL> SELECT DISTINCT ename, job FROM emp; -- And this too. SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks & regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
[EMAIL PROTECTED] wrote: I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. elegant = simple, concise, easy to understand. Looks elegant to me. Jared, it just looks that that... CONCAT = || yet another function call, yet another piece of code, yet another byte of memory... If you have more than two columns? If some of those are numeric, date? If ename is Smith and job is Smith and both can be nullable? :) NVLs? NVL2s? I think this approach is only valid when one really understands what she/he is looking for. Could be good for FBI, CHECK constraints but it's very risky and resource consuming (depends, can be neglected) for queries. It's better to write something that just looks ugly but works faster and reliably. Simple, fast, and covers all 'strange' cases: SELECT COUNT(*) FROM ( SELECT DISTINCT ename , job FROM emp ) / Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
> I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
elegant
> way of doing it.
elegant = simple, concise, easy to understand.
Looks elegant to me.
Jared
"Charu Joshi" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/29/2003 10:19 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:SQL question
Hello Listers,
How to find out the COUNT of DISTINCT values of multiple columns?
For eg.
SQL> SELECT DISTINCT ename FROM emp;
-- This works.
SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.
SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.
SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?
I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
elegant
way of doing it.
I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.
Thanks & regards,
Charu.
*
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL question
Title: RE: SQL question The non-working code in your example should be select count(*) from (select distinct ename,job from emp) / It appears that cound takes only one parameter ... not two. 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- From: Charu Joshi [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 29, 2003 1:19 PM To: Multiple recipients of list ORACLE-L Subject: SQL question Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL> SELECT DISTINCT ename FROM emp; -- This works. SQL> SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL> SELECT DISTINCT ename, job FROM emp; -- And this too. SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks & regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *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.*1
RE: SQL question
Title: RE: SQL question Elegant or not, here's how I'd do it select count(*) from (select distinct ename, job from emp); -Original Message- From: Charu Joshi [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 29, 2003 1:19 PM To: Multiple recipients of list ORACLE-L Subject: SQL question Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL> SELECT DISTINCT ename FROM emp; -- This works. SQL> SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL> SELECT DISTINCT ename, job FROM emp; -- And this too. SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks & regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
And, can you have two columns as arguements for COUNT? I guess its either one column or rows +Rachna - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 29, 2003 1:19 PM > Hello Listers, > > How to find out the COUNT of DISTINCT values of multiple columns? > > For eg. > > SQL> SELECT DISTINCT ename FROM emp; > -- This works. > > SQL> SELECT COUNT(DISTINCT ename) FROM emp; > -- So does this. > > SQL> SELECT DISTINCT ename, job FROM emp; > -- And this too. > > SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; > -- So why does this fail? > > I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant > way of doing it. > > I have a feeling I might be missing some fairly basic syntax, but feeling > dumb is better than suspense. > > Thanks & regards, > Charu. > > * > Disclaimer > > This message (including any attachments) contains > confidential information intended for a specific > individual and purpose, and is protected by law. > If you are not the intended recipient, you should > delete this message and are hereby notified that > any disclosure, copying, or distribution of this > message, or the taking of any action based on it, > is strictly prohibited. > > * > Visit us at http://www.mahindrabt.com > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Charu Joshi > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachna Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
Title: RE: SQL question Joshi, SELECT count(*) FROM (SELECT count(*) FROM flight_legs GROUP BY d_actual_time, event_type); SELECT count(*) FROM (SELECT DISTINCT d_actual_time, event_type FROM flight_legs ); The first one took about 37 seconds in returning a count of 357331. The second statement was about 10 seconds quicker. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Charu Joshi [SMTP:[EMAIL PROTECTED]] Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL> SELECT DISTINCT ename FROM emp; -- This works. SQL> SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL> SELECT DISTINCT ename, job FROM emp; -- And this too. SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks & regards, Charu.
RE: SQL question
Charu, The COUNT() function requires a single expression. "ename, job" is not a valid expression. "ename||job" is a valid expression since it will return a single value. Another alternative would be select count(*) from (select distinct ename, job from emp); Dan Fink -Original Message- Sent: Wednesday, January 29, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL> SELECT DISTINCT ename FROM emp; -- This works. SQL> SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL> SELECT DISTINCT ename, job FROM emp; -- And this too. SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks & regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan 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
Hello Listers, How to find out the COUNT of DISTINCT values of multiple columns? For eg. SQL> SELECT DISTINCT ename FROM emp; -- This works. SQL> SELECT COUNT(DISTINCT ename) FROM emp; -- So does this. SQL> SELECT DISTINCT ename, job FROM emp; -- And this too. SQL> SELECT COUNT(DISTINCT ename, job) FROM emp; -- So why does this fail? I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant way of doing it. I have a feeling I might be missing some fairly basic syntax, but feeling dumb is better than suspense. Thanks & regards, Charu. * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: SQL Question
The first query also says 'from user_group_members' and the second one 'from
app_users' ... I am not sure that the comparison is anything but confusing ...
Looks like the implicitly converted varchar2() column which contains '***', 'N/A' or
the like ...
>
>The first query says "where FK_USER in
>(44541,41402,41813)" and the second
>query says "where PEN_ID in (44541,41402,41813)"...
>
>
>- Original Message -
>To: "Multiple recipients of list ORACLE-L"
><[EMAIL PROTECTED]>
>Sent: Monday, January 27, 2003 11:43 PM
>
>
>> Hi,
>>
>> My brain is slow today Can someone help me ?
>>
>> I can do :
>>
>> select idu+1 from user_group_members where
>fk_user
>> in(44541,41402,41813) ;
>>
>> IDU+1
>> --
>> 41411
>> 41821
>> 44546
>>
>> But I can't do :
>> select 'insert into XXX
>(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
>('||IDU + 1
>||','||PEN_ID||',sysdate,'||FK_APPLICATION||');'
>from app_users
>> where pen_id in (44541,41402,41813) ;
>>
>> I've got on IDU+1 :
>>
>> ERROR at line 1:
>> ORA-01722: invalid number
>>
>>
>> Best Regards
>> Henrik
>>
>> --
>>
>-
>> There's fun in being serious.
>>
>> -- Wynton Marsalis
>>
>> Henrik Ekenberg
>Anoto AB
>>
>>
>> --
>> Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>> --
>> Author: Henrik Ekenber
>> INET: [EMAIL PROTECTED]>"
><[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: Tim Gorman
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051
>http://www.fatcity.com
>San Diego, California-- Mailing list and
>web hosting services
>To REMOVE yourself from this mailing list, send an
>E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of
>'ListGuru') and in
>the message BODY, include a line containing: UNSUB
>ORACLE-L
>(or the name of mailing list you want to be removed
>from). You may
>also send the HELP command for other information
>(like subscribing).
>---
>---
>---
>--
>---
>--
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroul
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: SV: SQL Question
Thanks that solved the problem
Regards
Henrik
--
---
Henrik EkenbergAnoto AB
On Tue, 28 Jan 2003, Johan Malmberg wrote:
-!-You might want to try using () around the "idu+1" part!
-!-
-!-like:
-!-
-!-select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION)
-!-values ('||(IDU + 1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');'
-!-from app_users
-!-where pen_id in (44541,41402,41813) ;
-!-
-!-That should do it!
-!-
-!-Best Regards
-!-Johan
-!-
-!-
-!-
-!-> -Ursprungligt meddelande-
-!-> Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Henrik Ekenberg
-!-> <[EMAIL PROTECTED]>
-!-> Skickat: den 28 januari 2003 07:44
-!-> Till: Multiple recipients of list ORACLE-L
-!-> Amne: SQL Question
-!->
-!->
-!-> Hi,
-!->
-!-> My brain is slow today Can someone help me ?
-!->
-!-> I can do :
-!->
-!-> select idu+1 from user_group_members where fk_user
-!-> in(44541,41402,41813) ;
-!->
-!-> IDU+1
-!-> --
-!-> 41411
-!-> 41821
-!-> 44546
-!->
-!-> But I can't do :
-!-> select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION)
-!-> values ('||IDU + 1
-!-> ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
-!-> where pen_id in (44541,41402,41813) ;
-!->
-!-> I've got on IDU+1 :
-!->
-!-> ERROR at line 1:
-!-> ORA-01722: invalid number
-!->
-!->
-!-> Best Regards
-!-> Henrik
-!->
-!-> --
-!-> --
-!-> -
-!-> There's fun in being serious.
-!->
-!-> -- Wynton Marsalis
-!->
-!-> Henrik EkenbergAnoto AB
-!->
-!->
-!-> --
-!-> Please see the official ORACLE-L FAQ: http://www.orafaq.net
-!-> --
-!-> Author: Henrik Ekenber
-!-> INET: [EMAIL PROTECTED]>" <[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: Henrik Ekenber
INET: [EMAIL PROTECTED]>" <[EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: SQL Question
The first query says "where FK_USER in (44541,41402,41813)" and the second
query says "where PEN_ID in (44541,41402,41813)"...
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, January 27, 2003 11:43 PM
> Hi,
>
> My brain is slow today Can someone help me ?
>
> I can do :
>
> select idu+1 from user_group_members where fk_user
> in(44541,41402,41813) ;
>
> IDU+1
> --
> 41411
> 41821
> 44546
>
> But I can't do :
> select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
> where pen_id in (44541,41402,41813) ;
>
> I've got on IDU+1 :
>
> ERROR at line 1:
> ORA-01722: invalid number
>
>
> Best Regards
> Henrik
>
> --
> --
-
> There's fun in being serious.
>
> -- Wynton Marsalis
>
> Henrik EkenbergAnoto AB
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Henrik Ekenber
> INET: [EMAIL PROTECTED]>" <[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: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: SQL Question
try this ("(IDU + 1)"):
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
(' || (IDU + 1)
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;
HEheac> Hi,
HEheac> My brain is slow today Can someone help me ?
HEheac> I can do :
HEheac> select idu+1 from user_group_members where fk_user
HEheac> in(44541,41402,41813) ;
HEheac> IDU+1
HEheac> --
HEheac> 41411
HEheac> 41821
HEheac> 44546
HEheac> But I can't do :
HEheac> select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU
+ 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
HEheac> where pen_id in (44541,41402,41813) ;
HEheac> I've got on IDU+1 :
HEheac> ERROR at line 1:
HEheac> ORA-01722: invalid number
HEheac> Best Regards
HEheac> Henrik
HEheac> --
HEheac> ---
HEheac> There's fun in being serious.
HEheac> -- Wynton Marsalis
HEheac> Henrik EkenbergAnoto AB
HEheac> --
HEheac> Please see the official ORACLE-L FAQ: http://www.orafaq.net
Best regards,
Dmitrii
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dmitrii CRETU
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL Question
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||(IDU + 1 )||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from
app_users
where pen_id in (44541,41402,41813) ;
IDU + 1 must be replaced by (IDU + 1).
HTH.
Nirmal.,
-Original Message-
Sent: Tuesday, January 28, 2003 9:44 AM
To: Multiple recipients of list ORACLE-L
Hi,
My brain is slow today Can someone help me ?
I can do :
select idu+1 from user_group_members where fk_user
in(44541,41402,41813) ;
IDU+1
--
41411
41821
44546
But I can't do :
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;
I've got on IDU+1 :
ERROR at line 1:
ORA-01722: invalid number
Best Regards
Henrik
--
---
There's fun in being serious.
-- Wynton Marsalis
Henrik EkenbergAnoto AB
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Henrik Ekenber
INET: [EMAIL PROTECTED]>" <[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: Nirmal Kumar Muthu Kumaran
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).
SV: SQL Question
You might want to try using () around the "idu+1" part!
like:
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION)
values ('||(IDU + 1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');'
from app_users
where pen_id in (44541,41402,41813) ;
That should do it!
Best Regards
Johan
> -Ursprungligt meddelande-
> Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Henrik Ekenberg
> <[EMAIL PROTECTED]>
> Skickat: den 28 januari 2003 07:44
> Till: Multiple recipients of list ORACLE-L
> Amne: SQL Question
>
>
> Hi,
>
> My brain is slow today Can someone help me ?
>
> I can do :
>
> select idu+1 from user_group_members where fk_user
> in(44541,41402,41813) ;
>
> IDU+1
> --
> 41411
> 41821
> 44546
>
> But I can't do :
> select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION)
> values ('||IDU + 1
> ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
> where pen_id in (44541,41402,41813) ;
>
> I've got on IDU+1 :
>
> ERROR at line 1:
> ORA-01722: invalid number
>
>
> Best Regards
> Henrik
>
> --
> --
> -
> There's fun in being serious.
>
> -- Wynton Marsalis
>
> Henrik EkenbergAnoto AB
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Henrik Ekenber
> INET: [EMAIL PROTECTED]>" <[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: Johan Malmberg
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL Question
Try
select 'insert into XXX
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU +
1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from
app_users
where pen_id in
(44541,41402,41813);
-Original Message-From: Henrik Ekenberg
<[EMAIL PROTECTED]>[mailto:[EMAIL PROTECTED]]Sent: Tuesday,
January 28, 2003 12:14 PMTo: Multiple recipients of list
ORACLE-LSubject: SQL QuestionHi,My brain is slow
today Can someone help me ?I can do :select idu+1 from
user_group_members where fk_userin(44541,41402,41813)
;
IDU+1--
41411 41821
44546But I can't do :select 'insert into XXX
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_userswhere pen_id
in (44541,41402,41813) ;I've got on IDU+1 :ERROR at line
1:ORA-01722: invalid numberBest
RegardsHenrik-There's
fun in being serious.-- Wynton MarsalisHenrik
Ekenberg
Anoto AB--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Henrik
Ekenber INET: [EMAIL PROTECTED]>"
<[EMAIL PROTECTED]Fat City Network Services --
858-538-5051 http://www.fatcity.comSan Diego,
California -- Mailing list and web
hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.
SQL Question
Hi,
My brain is slow today Can someone help me ?
I can do :
select idu+1 from user_group_members where fk_user
in(44541,41402,41813) ;
IDU+1
--
41411
41821
44546
But I can't do :
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;
I've got on IDU+1 :
ERROR at line 1:
ORA-01722: invalid number
Best Regards
Henrik
--
---
There's fun in being serious.
-- Wynton Marsalis
Henrik EkenbergAnoto AB
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Henrik Ekenber
INET: [EMAIL PROTECTED]>" <[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: Win2k/8.1.7/SQL Question
Title: RE: Win2k/8.1.7/SQL Question Have a look at the SQL Reference guide in the 8i docs. It says that 8i is broadly compatible with the ANSI SQL-99 Core specification, that explains why the SQL listed below works with 8i. Ade -Original Message-From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]]Sent: 15 January 2003 15:30To: Multiple recipients of list ORACLE-LSubject: RE: Win2k/8.1.7/SQL Question I looked at our copy of the 9i Docs, and did some hunting around on the web, and this code definitely looks like 9i SQL. However, the Siebel instance is 8.1.7.3 (or 4...I don't exactly remember). How can they be running this SQL? Thanks, Mike -Original Message-From: Orr, Steve [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 5:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Win2k/8.1.7/SQL Question Well that looks like ANSI compatible SQL that should run under Oracle9i. Take a look and the 9i docs to develop a strategy for the "retrofit." This is so weird for me... having to unlearn Oracle syntax in order to write ANSI SQL. Sigh... -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: Win2k/8.1.7/SQL Question My European customers are trying to optimize some SQL that is used in their Siebel implementation. It uses a syntax that I am unfamiliar with. The SQL looks like: SELECT ... FROM SIEBEL.S_PARTY T1 INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN SIEBEL.S_ORG_EXT T3 ON T2.MASTER_OU_ID = T3.PAR_ROW_ID INNER JOIN SIEBEL.S_ACCNT_POSTN T4 ON (T4.POSITION_ID = '1-6M10' 0.05) AND T2.ROW_ID = T4 INNER JOIN SIEBEL.S_PARTY T5 ON (T5.ROW_ID = T4.POSITION_ID, 0.05) LEFT OUTER JOIN SIEBEL.S_ORG_EXT T6 ON T2.PAR_OU_ID = T6.PAR_ROW_ID LEFT OUTER JOIN SIEBEL.S_ACCNT_POSTN T7 ON T1.ROW_ID = T7.OU_EXT_ID LEFT OUTER JOIN SIEBEL.S_ORG_PROMOPRFL T8 ON T2.ROW_ID = T8.ACCNT_ID AND T2.PR_PRFL_ID = ... WHERE ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND T2.ACCNT_FLG != 'N') ORDER BY T2.PRTNR_SALES_RANK I did a cut-and-paste, so if there is missing punctuation I don't know that either. Can anyone tell me how this is supposed to work, or how I can translate into Oracle-compatible SQL? Thanks, Mike -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. ==
RE: Win2k/8.1.7/SQL Question
Title: RE: Win2k/8.1.7/SQL Question I looked at our copy of the 9i Docs, and did some hunting around on the web, and this code definitely looks like 9i SQL. However, the Siebel instance is 8.1.7.3 (or 4...I don't exactly remember). How can they be running this SQL? Thanks, Mike -Original Message-From: Orr, Steve [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 14, 2003 5:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Win2k/8.1.7/SQL Question Well that looks like ANSI compatible SQL that should run under Oracle9i. Take a look and the 9i docs to develop a strategy for the "retrofit." This is so weird for me... having to unlearn Oracle syntax in order to write ANSI SQL. Sigh... -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: Win2k/8.1.7/SQL Question My European customers are trying to optimize some SQL that is used in their Siebel implementation. It uses a syntax that I am unfamiliar with. The SQL looks like: SELECT ... FROM SIEBEL.S_PARTY T1 INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN SIEBEL.S_ORG_EXT T3 ON T2.MASTER_OU_ID = T3.PAR_ROW_ID INNER JOIN SIEBEL.S_ACCNT_POSTN T4 ON (T4.POSITION_ID = '1-6M10' 0.05) AND T2.ROW_ID = T4 INNER JOIN SIEBEL.S_PARTY T5 ON (T5.ROW_ID = T4.POSITION_ID, 0.05) LEFT OUTER JOIN SIEBEL.S_ORG_EXT T6 ON T2.PAR_OU_ID = T6.PAR_ROW_ID LEFT OUTER JOIN SIEBEL.S_ACCNT_POSTN T7 ON T1.ROW_ID = T7.OU_EXT_ID LEFT OUTER JOIN SIEBEL.S_ORG_PROMOPRFL T8 ON T2.ROW_ID = T8.ACCNT_ID AND T2.PR_PRFL_ID = ... WHERE ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND T2.ACCNT_FLG != 'N') ORDER BY T2.PRTNR_SALES_RANK I did a cut-and-paste, so if there is missing punctuation I don't know that either. Can anyone tell me how this is supposed to work, or how I can translate into Oracle-compatible SQL? Thanks, Mike
RE: Win2k/8.1.7/SQL Question
Title: RE: Win2k/8.1.7/SQL Question Well that looks like ANSI compatible SQL that should run under Oracle9i. Take a look and the 9i docs to develop a strategy for the "retrofit." This is so weird for me... having to unlearn Oracle syntax in order to write ANSI SQL. Sigh... -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: Win2k/8.1.7/SQL Question My European customers are trying to optimize some SQL that is used in their Siebel implementation. It uses a syntax that I am unfamiliar with. The SQL looks like: SELECT ... FROM SIEBEL.S_PARTY T1 INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN SIEBEL.S_ORG_EXT T3 ON T2.MASTER_OU_ID = T3.PAR_ROW_ID INNER JOIN SIEBEL.S_ACCNT_POSTN T4 ON (T4.POSITION_ID = '1-6M10' 0.05) AND T2.ROW_ID = T4 INNER JOIN SIEBEL.S_PARTY T5 ON (T5.ROW_ID = T4.POSITION_ID, 0.05) LEFT OUTER JOIN SIEBEL.S_ORG_EXT T6 ON T2.PAR_OU_ID = T6.PAR_ROW_ID LEFT OUTER JOIN SIEBEL.S_ACCNT_POSTN T7 ON T1.ROW_ID = T7.OU_EXT_ID LEFT OUTER JOIN SIEBEL.S_ORG_PROMOPRFL T8 ON T2.ROW_ID = T8.ACCNT_ID AND T2.PR_PRFL_ID = ... WHERE ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND T2.ACCNT_FLG != 'N') ORDER BY T2.PRTNR_SALES_RANK I did a cut-and-paste, so if there is missing punctuation I don't know that either. Can anyone tell me how this is supposed to work, or how I can translate into Oracle-compatible SQL? Thanks, Mike
Win2k/8.1.7/SQL Question
My European customers are trying to optimize some SQL that is used in their Siebel implementation. It uses a syntax that I am unfamiliar with. The SQL looks like: SELECT ... FROM SIEBEL.S_PARTY T1 INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN SIEBEL.S_ORG_EXT T3 ON T2.MASTER_OU_ID = T3.PAR_ROW_ID INNER JOIN SIEBEL.S_ACCNT_POSTN T4 ON (T4.POSITION_ID = '1-6M10' 0.05) AND T2.ROW_ID = T4 INNER JOIN SIEBEL.S_PARTY T5 ON (T5.ROW_ID = T4.POSITION_ID, 0.05) LEFT OUTER JOIN SIEBEL.S_ORG_EXT T6 ON T2.PAR_OU_ID = T6.PAR_ROW_ID LEFT OUTER JOIN SIEBEL.S_ACCNT_POSTN T7 ON T1.ROW_ID = T7.OU_EXT_ID LEFT OUTER JOIN SIEBEL.S_ORG_PROMOPRFL T8 ON T2.ROW_ID = T8.ACCNT_ID AND T2.PR_PRFL_ID = ... WHERE ((T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG != 'N') AND T2.ACCNT_FLG != 'N') ORDER BY T2.PRTNR_SALES_RANK I did a cut-and-paste, so if there is missing punctuation I don't know that either. Can anyone tell me how this is supposed to work, or how I can translate into Oracle-compatible SQL? Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) 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: Perl DBI/SQL question - For those who use it...
in case this is a windog machine - install kind of unix shell. and the unix style commands work perfectly - just tried it. either use unix tools for windog or cygwin from rh. or dump the w... have fun. >>> [EMAIL PROTECTED] 01/03/03 18:40 PM >>> Hi everyone, This may be a stupid question. If so please humor me with a stupid answer. However: I FINALLY have the fun fun fun chance to change one of my data loads to use the DBI instead of the procedures I hacked together. In true ksh style I had written my loads to fire a sql script (calling a stored proc) that was stored separately. It seems to me the DBI wants the text of the sql script embedded piece by piece in the code. I have looked around for examples because even though the DBI seems straightforward, it doesn't take much to confuse me. I don't see examples of firing a sql script from the DBI (like this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, everything was SO EASY) So my questions to you, my learned friends, are: 1. is it not perl-style to store the sql in a separate file? I understand I may be missing the opportunity for more specific error handling here but honestly at this point it does not matter. The thing fails, I restart the whole script. 2. Does anyone have an example of firing the DBI and calling a sql script like I could so easily do in ksh? Any and all comments are welcome. Thank you I wish everyone a rested and relaxing weekend. Lisa Koivu Oracle Database Monkey Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Markus Reger 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: Perl DBI/SQL question - For those who use it...
Title: RE: Perl DBI/SQL question - For those who use it... Muchas Gracias Tim! I can't thank you enough. I will play with it, it looks like it may meet my needs. Lisa -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: Perl DBI/SQL question - For those who use it... Try dbish dbi:Oracle:tnsname < commands.sql The dbish is a "DBI shell" supplied with the DBI. The version supplied with the DBI is functional but basic. Tom Lowery is working on an extended version with plugins adding more functionality. One of his goals is an SQL*Plus clone... http://search.cpan.org/author/TLOWERY/DBI-Shell-11.91/lib/DBI/Shell.pm The dbi-users mailing list is probably the best place to get answers to DBI questions. Tim. On Fri, Jan 03, 2003 at 09:09:06AM -0800, Koivu, Lisa wrote: > Hi everyone, > > This may be a stupid question. If so please humor me with a stupid answer. > However: > > I FINALLY have the fun fun fun chance to change one of my data loads to use > the DBI instead of the procedures I hacked together. In true ksh style I > had written my loads to fire a sql script (calling a stored proc) that was > stored separately. It seems to me the DBI wants the text of the sql script > embedded piece by piece in the code. I have looked around for examples > because even though the DBI seems straightforward, it doesn't take much to > confuse me. I don't see examples of firing a sql script from the DBI (like > this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, > everything was SO EASY) > > So my questions to you, my learned friends, are: > > 1. is it not perl-style to store the sql in a separate file? I understand I > may be missing the opportunity for more specific error handling here but > honestly at this point it does not matter. The thing fails, I restart the > whole script. > > 2. Does anyone have an example of firing the DBI and calling a sql script > like I could so easily do in ksh? > > Any and all comments are welcome. Thank you > > I wish everyone a rested and relaxing weekend. > > Lisa Koivu > Oracle Database Monkey > Fairfield Resorts, Inc. > 5259 Coconut Creek Parkway > Ft. Lauderdale, FL, USA 33063 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Bunce 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: Perl DBI/SQL question - For those who use it...
Title: Perl DBI/SQL question - For those who use it...
Hi Lisa,
It's been awhile since I've used Perl DBI, but from
what you said, I think you're mixing up two different ideas. Ksh doesn't know
how to talk to a database, so you just use it to invoke sqlplus, which handles
the database communication and runs the script. Perl is also capable of doing
the _exact_ same thing, but you would not be using the DBI, you would just be
using Perl to invoke sqlplus. Here's a one-liner example:
cat > test.sql
select sysdate from
dual;
quit
perl -e 'system("sqlplus", "-s",
"scott/tiger", "\@test")'
This is completely different from using the DBI.
You use the DBI when you want more flow control and error handling, etc... You
can still keep your scripts in a separate file if you really want to, just read
them into a variable and prepare() and execute() it. Personally, I would define
my DBI SQL in the perl file -- easier to keep track of it
there.
If you like, you can contact me off-list and I can
show you some things I've written. In fact, you're right down the road from me
--- Do you go to the SF Oracle User Group meetings?
--Philip DouglassInternet Networking
GroupDatabase AdministratorSIRS Publishing, Inc.
1100 Holland Dr.
Boca Raton, FL 33487
- Original Message -
From:
Koivu, Lisa
To: Multiple recipients of list ORACLE-L
Sent: Friday, January 03, 2003 12:09
PM
Subject: Perl DBI/SQL question - For
those who use it...
Hi everyone,
This may be a stupid question. If so please
humor me with a stupid answer. However:
I FINALLY have the fun fun fun chance to change one
of my data loads to use the DBI instead of the procedures I hacked
together. In true ksh style I had written my loads to fire a sql script
(calling a stored proc) that was stored separately. It seems to me the
DBI wants the text of the sql script embedded piece by piece in the
code. I have looked around for examples because even though the DBI
seems straightforward, it doesn't take much to confuse me. I don't see
examples of firing a sql script from the DBI (like this sqlplus /@dbname <
@script.sql > logfile.log ... Gosh do I miss unix, everything was SO
EASY)
So my questions to you, my learned friends, are:
1. is it not perl-style to store the sql in a
separate file? I understand I may be missing the opportunity for more
specific error handling here but honestly at this point it does not
matter. The thing fails, I restart the whole script.
2. Does anyone have an example of firing the DBI
and calling a sql script like I could so easily do in ksh?
Any and all comments are welcome. Thank
you
I wish everyone a rested and relaxing weekend.
Lisa Koivu Oracle Database Monkey Fairfield
Resorts, Inc. 5259 Coconut Creek
Parkway Ft. Lauderdale, FL, USA
33063
Re: Perl DBI/SQL question - For those who use it...
What do you have so far.
You can read in a script like so...
#perl dbicode.pl < script.sql
while () {
chomp;
$sql .= $_;
}
print $sql;
On Fri, 3 Jan 2003, Koivu, Lisa wrote:
> Hi everyone,
>
> This may be a stupid question. If so please humor me with a stupid answer.
> However:
>
> I FINALLY have the fun fun fun chance to change one of my data loads to use
> the DBI instead of the procedures I hacked together. In true ksh style I
> had written my loads to fire a sql script (calling a stored proc) that was
> stored separately. It seems to me the DBI wants the text of the sql script
> embedded piece by piece in the code. I have looked around for examples
> because even though the DBI seems straightforward, it doesn't take much to
> confuse me. I don't see examples of firing a sql script from the DBI (like
> this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix,
> everything was SO EASY)
>
> So my questions to you, my learned friends, are:
>
> 1. is it not perl-style to store the sql in a separate file? I understand I
> may be missing the opportunity for more specific error handling here but
> honestly at this point it does not matter. The thing fails, I restart the
> whole script.
>
> 2. Does anyone have an example of firing the DBI and calling a sql script
> like I could so easily do in ksh?
>
> Any and all comments are welcome. Thank you
>
> I wish everyone a rested and relaxing weekend.
>
> Lisa Koivu
> Oracle Database Monkey
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alex
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: Perl DBI/SQL question - For those who use it...
Title: RE: Perl DBI/SQL question - For those who use it... I think I just answered my own question after reading through Charlie's example code... The errors that would be spit to the screen would be returned in $DBI::errstr. Lisa -Original Message- From: Koivu, Lisa Sent: Friday, January 03, 2003 1:09 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: Perl DBI/SQL question - For those who use it... Jared, thanks for your reply. One last question: Is SPOOL one of the commands that DBI does not understand? I would need to capture any errors spit out by sql*plus (like my famous ora-1410 error). I have a feeling the answer is yes. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 12:30 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Perl DBI/SQL question - For those who use it... Lisa, No, you can't call a sql script from Perl/DBI, sorry. The SQL scripts you refer to are for SQL*Plus, and contain a number of commands that DBI doesn't know what to do with. Your SQL can be stored in a different file to avoid script modification for different SQL, but that requires a little work on your part. Ask privately if you need examples. Then there's SQLMinus by Tom Lowery, a sqlplus emulator that is a work in progress. I don't know if it will yet read a sqlplus formatted sql script. Check search.cpan.org. I'm also working on a Perl module to allow sqlplus like breaks, computes and subtotals, but that too is a WIP. If fact, it's not even to alpha stage yet. Jared "Koivu, Lisa" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/03/2003 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Perl DBI/SQL question - For those who use it... Hi everyone, This may be a stupid question. If so please humor me with a stupid answer. However: I FINALLY have the fun fun fun chance to change one of my data loads to use the DBI instead of the procedures I hacked together. In true ksh style I had written my loads to fire a sql script (calling a stored proc) that was stored separately. It seems to me the DBI wants the text of the sql script embedded piece by piece in the code. I have looked around for examples because even though the DBI seems straightforward, it doesn't take much to confuse me. I don't see examples of firing a sql script from the DBI (like this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, everything was SO EASY) So my questions to you, my learned friends, are: 1. is it not perl-style to store the sql in a separate file? I understand I may be missing the opportunity for more specific error handling here but honestly at this point it does not matter. The thing fails, I restart the whole script. 2. Does anyone have an example of firing the DBI and calling a sql script like I could so easily do in ksh? Any and all comments are welcome. Thank you I wish everyone a rested and relaxing weekend. Lisa Koivu Oracle Database Monkey Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: Perl DBI/SQL question - For those who use it...
Title: RE: Perl DBI/SQL question - For those who use it... Jared, thanks for your reply. One last question: Is SPOOL one of the commands that DBI does not understand? I would need to capture any errors spit out by sql*plus (like my famous ora-1410 error). I have a feeling the answer is yes. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 12:30 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Perl DBI/SQL question - For those who use it... Lisa, No, you can't call a sql script from Perl/DBI, sorry. The SQL scripts you refer to are for SQL*Plus, and contain a number of commands that DBI doesn't know what to do with. Your SQL can be stored in a different file to avoid script modification for different SQL, but that requires a little work on your part. Ask privately if you need examples. Then there's SQLMinus by Tom Lowery, a sqlplus emulator that is a work in progress. I don't know if it will yet read a sqlplus formatted sql script. Check search.cpan.org. I'm also working on a Perl module to allow sqlplus like breaks, computes and subtotals, but that too is a WIP. If fact, it's not even to alpha stage yet. Jared "Koivu, Lisa" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/03/2003 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Perl DBI/SQL question - For those who use it... Hi everyone, This may be a stupid question. If so please humor me with a stupid answer. However: I FINALLY have the fun fun fun chance to change one of my data loads to use the DBI instead of the procedures I hacked together. In true ksh style I had written my loads to fire a sql script (calling a stored proc) that was stored separately. It seems to me the DBI wants the text of the sql script embedded piece by piece in the code. I have looked around for examples because even though the DBI seems straightforward, it doesn't take much to confuse me. I don't see examples of firing a sql script from the DBI (like this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, everything was SO EASY) So my questions to you, my learned friends, are: 1. is it not perl-style to store the sql in a separate file? I understand I may be missing the opportunity for more specific error handling here but honestly at this point it does not matter. The thing fails, I restart the whole script. 2. Does anyone have an example of firing the DBI and calling a sql script like I could so easily do in ksh? Any and all comments are welcome. Thank you I wish everyone a rested and relaxing weekend. Lisa Koivu Oracle Database Monkey Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
Re: Perl DBI/SQL question - For those who use it...
Try dbish dbi:Oracle:tnsname < commands.sql The dbish is a "DBI shell" supplied with the DBI. The version supplied with the DBI is functional but basic. Tom Lowery is working on an extended version with plugins adding more functionality. One of his goals is an SQL*Plus clone... http://search.cpan.org/author/TLOWERY/DBI-Shell-11.91/lib/DBI/Shell.pm The dbi-users mailing list is probably the best place to get answers to DBI questions. Tim. On Fri, Jan 03, 2003 at 09:09:06AM -0800, Koivu, Lisa wrote: > Hi everyone, > > This may be a stupid question. If so please humor me with a stupid answer. > However: > > I FINALLY have the fun fun fun chance to change one of my data loads to use > the DBI instead of the procedures I hacked together. In true ksh style I > had written my loads to fire a sql script (calling a stored proc) that was > stored separately. It seems to me the DBI wants the text of the sql script > embedded piece by piece in the code. I have looked around for examples > because even though the DBI seems straightforward, it doesn't take much to > confuse me. I don't see examples of firing a sql script from the DBI (like > this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, > everything was SO EASY) > > So my questions to you, my learned friends, are: > > 1. is it not perl-style to store the sql in a separate file? I understand I > may be missing the opportunity for more specific error handling here but > honestly at this point it does not matter. The thing fails, I restart the > whole script. > > 2. Does anyone have an example of firing the DBI and calling a sql script > like I could so easily do in ksh? > > Any and all comments are welcome. Thank you > > I wish everyone a rested and relaxing weekend. > > Lisa Koivu > Oracle Database Monkey > Fairfield Resorts, Inc. > 5259 Coconut Creek Parkway > Ft. Lauderdale, FL, USA 33063 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Bunce 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: Perl DBI/SQL question - For those who use it...
Lisa,
No, you can't call a sql script from Perl/DBI, sorry.
The SQL scripts you refer to are for SQL*Plus, and contain
a number of commands that DBI doesn't know what to do with.
Your SQL can be stored in a different file to avoid script modification
for different SQL, but that requires a little work on your part.
Ask privately if you need examples.
Then there's SQLMinus by Tom Lowery, a sqlplus emulator
that is a work in progress. I don't know if it will yet read a sqlplus
formatted sql script. Check search.cpan.org.
I'm also working on a Perl module to allow sqlplus like breaks,
computes and subtotals, but that too is a WIP.
If fact, it's not even to alpha stage yet.
Jared
"Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/03/2003 09:09 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Perl DBI/SQL question - For those who use it...
Hi everyone,
This may be a stupid question. If so please humor me with a stupid
answer. However:
I FINALLY have the fun fun fun chance to change one of my data loads to
use the DBI instead of the procedures I hacked together. In true ksh
style I had written my loads to fire a sql script (calling a stored proc)
that was stored separately. It seems to me the DBI wants the text of the
sql script embedded piece by piece in the code. I have looked around for
examples because even though the DBI seems straightforward, it doesn't
take much to confuse me. I don't see examples of firing a sql script from
the DBI (like this sqlplus /@dbname < @script.sql > logfile.log ... Gosh
do I miss unix, everything was SO EASY)
So my questions to you, my learned friends, are:
1. is it not perl-style to store the sql in a separate file? I understand
I may be missing the opportunity for more specific error handling here but
honestly at this point it does not matter. The thing fails, I restart the
whole script.
2. Does anyone have an example of firing the DBI and calling a sql script
like I could so easily do in ksh?
Any and all comments are welcome. Thank you
I wish everyone a rested and relaxing weekend.
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Perl DBI/SQL question - For those who use it...
Title: Perl DBI/SQL question - For those who use it... Hi everyone, This may be a stupid question. If so please humor me with a stupid answer. However: I FINALLY have the fun fun fun chance to change one of my data loads to use the DBI instead of the procedures I hacked together. In true ksh style I had written my loads to fire a sql script (calling a stored proc) that was stored separately. It seems to me the DBI wants the text of the sql script embedded piece by piece in the code. I have looked around for examples because even though the DBI seems straightforward, it doesn't take much to confuse me. I don't see examples of firing a sql script from the DBI (like this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, everything was SO EASY) So my questions to you, my learned friends, are: 1. is it not perl-style to store the sql in a separate file? I understand I may be missing the opportunity for more specific error handling here but honestly at this point it does not matter. The thing fails, I restart the whole script. 2. Does anyone have an example of firing the DBI and calling a sql script like I could so easily do in ksh? Any and all comments are welcome. Thank you I wish everyone a rested and relaxing weekend. Lisa Koivu Oracle Database Monkey Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
RE: SQL question avoiding 2 views and not in
Thanks for the where clause and to all who respond, I'll check into fine grained access control (dbms_rls). --- "Khedr, Waleed" <[EMAIL PROTECTED]> a écrit : > Add this to where clause: > > group <> decode(user,'typical',380,-100) > > Instead of -100 use any number not used by the > groups. > > Also read about contexts and grain level security. > > Waleed > > -Original Message- > Sent: Friday, December 13, 2002 2:59 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > We have a lot of views. Now the users have a new > requirement, only the user 'admin' can see all the > data from the views. The user 'typical' must see all > data except the one from group 380. > > A basic solution is to create 2 sets of views with > one > set having a group number <> 380. > > I'm looking for a solution to have only one set of > views. It's friday afternoon and I have no > inspiration > :-( > > TIA > > > = > Stéphane Paquette > DBA Oracle et DB2, consultant entrepôt de données > Oracle and DB2 DBA, datawarehouse consultant > [EMAIL PROTECTED] > > __ > Lèche-vitrine ou lèche-écran ? > magasinage.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Stephane=20Paquette?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: 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). > = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question avoiding 2 views and not in
I have also been on a project which used fine-grained access control. The project was a reporting application for a large organisation and one of the problems we found we dealing with the various levels of security. The organisation has ~10,000 cost centres and some users could see only a very small branch, whilst others could see the entire structure, whilst others could see almost everything except for a few very high level branches. There was also a different way of providing security (ie: not cost centre related) and many users had a combination of both to be applied. One of the biggest "gotchas" was performance tuning the application. Because of the complexity of our security implementation we initially fell in the trap of tuning the query with no security added (a couple of users had the clause "and 1 = 1"). Naturally when other users connected the explain plan changed and performance was pretty average. It took a while to index all tables in such a way that it worked well for every type of security clause. So, yes there can be a performance hit and that hit can vary dramatically depending on what type of clause you are adding. Having said that, it did work and was transparent to the front end, allowing users to create their own queries / reports and still be bound by the security model. Cheers, Mark. [EMAIL PROTECTED] .tenet.edu To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: RE: SQL question avoiding 2 views and not in 14/12/2002 10:03 Please respond to ORACLE-L Lisa, A couple of years ago, when I was a consultant, I implemented Application Context and Fine-Grained Access Control, AKA Row Level Security for a client. Since it causes a predicate to be appended to the Where clause of every SQL statement issued against the tables having a Security Policy, I guess performance could be impacted if you didn't index the columns referenced by the appended predicates. We never noticed a bit of degradation in our testing, but we were careful about the indexing. I left that project before it went into production, so I don't know the ultimate outcome. However, I'd sure use FGAC again, if the need arises, it works very well. Actually, I probably *will* use it on a couple of our 3rd Party apps here which don't enforce security to the degree that we require. I'll let y'all know how it performs. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] "Koivu, Lisa" <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: RE: SQL question avoiding 2 views and not in 12/13/2002 03:38 PM Please respond to ORACLE-L Has anyone used context and fine-grained security? I seem to remember the performance hit was not minimal when using this functionality. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to
RE: SQL question avoiding 2 views and not in
Lisa,
A couple of years ago, when I was a consultant, I implemented Application
Context and Fine-Grained Access Control, AKA Row Level Security for a
client.
Since it causes a predicate to be appended to the Where clause of every SQL
statement issued against the tables having a Security Policy, I guess
performance could be impacted if you didn't index the columns referenced by
the appended predicates. We never noticed a bit of degradation in our
testing, but we were careful about the indexing.
I left that project before it went into production, so I don't know the
ultimate outcome. However, I'd sure use FGAC again, if the need arises, it
works very well. Actually, I probably *will* use it on a couple of our 3rd
Party apps here which don't enforce security to the degree that we require.
I'll let y'all know how it performs.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
"Koivu, Lisa"
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: SQL question avoiding 2
views and not in
12/13/2002 03:38
PM
Please respond to
ORACLE-L
Has anyone used context and fine-grained security? I seem to remember the
performance hit was not minimal when using this functionality.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL question avoiding 2 views and not in
Title: RE: SQL question avoiding 2 views and not in Has anyone used context and fine-grained security? I seem to remember the performance hit was not minimal when using this functionality. -Original Message- From: Khedr, Waleed [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL question avoiding 2 views and not in Add this to where clause: group <> decode(user,'typical',380,-100) Instead of -100 use any number not used by the groups. Also read about contexts and grain level security. Waleed -Original Message- Sent: Friday, December 13, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hi, We have a lot of views. Now the users have a new requirement, only the user 'admin' can see all the data from the views. The user 'typical' must see all data except the one from group 380. A basic solution is to create 2 sets of views with one set having a group number <> 380. I'm looking for a solution to have only one set of views. It's friday afternoon and I have no inspiration :-( TIA = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: SQL question avoiding 2 views and not in
Title: RE: SQL question avoiding 2 views and not in dbms_rls is cheaper to use ... 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-From: Nick Wagner [mailto:[EMAIL PROTECTED]]Sent: Friday, December 13, 2002 4:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: SQL question avoiding 2 views and not in OLS -- Oracle Label Security... I think that's the key you are looking for. -Original Message- From: Stephane Paquette [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject: SQL question avoiding 2 views and not in Hi, We have a lot of views. Now the users have a new requirement, only the user 'admin' can see all the data from the views. The user 'typical' must see all data except the one from group 380. A basic solution is to create 2 sets of views with one set having a group number <> 380. I'm looking for a solution to have only one set of views. It's friday afternoon and I have no inspiration :-( TIA = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: SQL question avoiding 2 views and not in
Title: RE: SQL question avoiding 2 views and not in Hi Stephane, This may be more effort but have you considered having a security table to join to in the one view, instead of two views? Multiple views can really hose the optimizer, as I am sure you know. However adding a table then creates a task for someone, because the data must be maintained. And it would have to be a complete set of data if you are avoiding the not in clause. by the way, I wanted to say this earlier but resisted the urge... how do you answer the interview question about being in a stressful situation like a recovery when your last name is Panicker? Have a great weekend everyone! Lisa Koivu Oracle Datababy Administratikiss and Wild Bamboo Stick Wielder. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Stephane Paquette [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Subject: SQL question avoiding 2 views and not in Hi, We have a lot of views. Now the users have a new requirement, only the user 'admin' can see all the data from the views. The user 'typical' must see all data except the one from group 380. A basic solution is to create 2 sets of views with one set having a group number <> 380. I'm looking for a solution to have only one set of views. It's friday afternoon and I have no inspiration :-( TIA = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question avoiding 2 views and not in
Add this to where clause: group <> decode(user,'typical',380,-100) Instead of -100 use any number not used by the groups. Also read about contexts and grain level security. Waleed -Original Message- Sent: Friday, December 13, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hi, We have a lot of views. Now the users have a new requirement, only the user 'admin' can see all the data from the views. The user 'typical' must see all data except the one from group 380. A basic solution is to create 2 sets of views with one set having a group number <> 380. I'm looking for a solution to have only one set of views. It's friday afternoon and I have no inspiration :-( TIA = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: SQL question avoiding 2 views and not in
Title: RE: SQL question avoiding 2 views and not in OLS -- Oracle Label Security... I think that's the key you are looking for. -Original Message- From: Stephane Paquette [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject: SQL question avoiding 2 views and not in Hi, We have a lot of views. Now the users have a new requirement, only the user 'admin' can see all the data from the views. The user 'typical' must see all data except the one from group 380. A basic solution is to create 2 sets of views with one set having a group number <> 380. I'm looking for a solution to have only one set of views. It's friday afternoon and I have no inspiration :-( TIA = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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 avoiding 2 views and not in
Hi, We have a lot of views. Now the users have a new requirement, only the user 'admin' can see all the data from the views. The user 'typical' must see all data except the one from group 380. A basic solution is to create 2 sets of views with one set having a group number <> 380. I'm looking for a solution to have only one set of views. It's friday afternoon and I have no inspiration :-( TIA = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sql question : use of SUBSTR/INSTR functions
<> How about this... FUNCTION f_ip_to_number ( p_ipNVARCHAR2 ) RETURN NUMBER IS v_ip_segment1 NUMBER := SUBSTR (p_ip, 1, INSTR (p_ip, '.') - 1); v_ip_segment2 NUMBER := SUBSTR ( p_ip , INSTR (p_ip, '.', 1, 1) + 1 , INSTR (p_ip, '.', 1, 2) - INSTR (p_ip, '.', 1, 1) - 1 ); v_ip_segment3 NUMBER := SUBSTR ( p_ip , INSTR (p_ip, '.', 1, 2) + 1 , INSTR (p_ip, '.', 1, 3) - INSTR (p_ip, '.', 1, 2) - 1 ); v_ip_segment4 NUMBER := SUBSTR (p_ip, INSTR (p_ip, '.', -1) + 1); BEGIN RETURN ( ( (v_ip_segment1 * 256 + v_ip_segment2) * 256 ) + v_ip_segment3 ) * 256 + v_ip_segment4; END f_ip_to_number; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sql question : use of SUBSTR/INSTR functions
Hello, Try this (take a hard look first, as I cranked this out quickly while doing other things): substr(ip_addr,1,instr(ip_addr,'.',1,1)-1 http://www.orafaq.com -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sql question : use of SUBSTR/INSTR functions
Hi Johan,
Try this:
SELECT SUBSTR('127.0.0.1',1,INSTR('127.0.0.1','.')-1)
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.')+1,INSTR('127.0.0.1','.',1,2)-(INSTR('127.0.0.1','.')+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,2)+1,INSTR('127.0.0.1','.',1,3)-(INSTR('127.0.0.1','.',1,2)+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,3)+1,LENGTH('127.0.0.1')-INSTR('127.0.0.1','.',1,3)+1)
FROM DUAL
There may be a more elegant solution, but this was the quickest I could come up with.
--
Alan Davey
[EMAIL PROTECTED]
212-604-0200 x106
On 10/16/2002 4:32 PM, Johan Muller <[EMAIL PROTECTED]> wrote:
>Help!
>
>Anybody have a quick and dirty to parse the 4 octets of a typical
>IP address
>into 4 separate values. I will insert these into a table where database
>checks may verify that the data is in fact a number and also part
>of a
>valid ip range (the second thru fourth octets cannot be higher than
>255. The
>source data is very dirty and often fat-fingered, hence the painful
>solution):
>
>e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val
>4).
>
>I have used various flavors of substr/instr to unravel this, but
>the varying
>length of the octets (up to 3 bytes) defeats my rudimentary sql coding
>skills. I probably have to attack the IP with decode, and any input
>will be
>very welcome.
>
>Running V 8.1.6.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Johan Muller
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- 858-538-5051 http://www.fatcity.com
>San Diego, California-- Mailing list and web hosting services
>-
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
>in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Sql question : use of SUBSTR/INSTR functions
Try this. It uses the INSTR function to determine the start and end of the
SUBSTR.
1 select substr('333.22.1.000',1,instr('333.22.1.000','.')-1) octet1,
2 substr('333.22.1.000',
3(instr('333.22.1.000','.',1,1) + 1),
4(instr('333.22.1.000','.',1,2) -
instr('333.22.1.000','.',1,1))-1) octect2,
5 substr('333.22.1.000',
6(instr('333.22.1.000','.',1,2) + 1),
7(instr('333.22.1.000','.',1,3) -
instr('333.22.1.000','.',1,2))-1) octect3,
8 substr('333.22.1.000',(instr('333.22.1.000','.',1,3) + 1))
octect3
9* from dual
SQL> /
OCT OC O OCT
--- -- - ---
333 22 1 000
Dan Fink
-Original Message-
Sent: Wednesday, October 16, 2002 2:32 PM
To: Multiple recipients of list ORACLE-L
Help!
Anybody have a quick and dirty to parse the 4 octets of a typical IP address
into 4 separate values. I will insert these into a table where database
checks may verify that the data is in fact a number and also part of a
valid ip range (the second thru fourth octets cannot be higher than 255. The
source data is very dirty and often fat-fingered, hence the painful
solution):
e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 4).
I have used various flavors of substr/instr to unravel this, but the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input will be
very welcome.
Running V 8.1.6.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johan Muller
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
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 : use of SUBSTR/INSTR functions
Help! Anybody have a quick and dirty to parse the 4 octets of a typical IP address into 4 separate values. I will insert these into a table where database checks may verify that the data is in fact a number and also part of a valid ip range (the second thru fourth octets cannot be higher than 255. The source data is very dirty and often fat-fingered, hence the painful solution): e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 4). I have used various flavors of substr/instr to unravel this, but the varying length of the octets (up to 3 bytes) defeats my rudimentary sql coding skills. I probably have to attack the IP with decode, and any input will be very welcome. Running V 8.1.6. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL question
Title: RE: SQL question Maybe I think differently, I usually let server think about size or the number of clauses ... if you have codes in a table what's wrong with ... select distinct code from my_code_table minus select distinct code from my_data_table / ??? 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- From: Steven Haas [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 2:43 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL question Raj, It wasn't a question of coding the sql to create all of the unions, but hitting a limitation on the number of unions in the sql. But, thanks to all for the effort. steve This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SQL question
what are the 1700 values
if the are all alphabetic and not too long you could do something like the
below though it's all getting a bit long-winded
select
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
from addresses -- any table big enough
where rownum < 26*26*26
group by
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
having
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26))) in
('ABA','ACY','ABT'...) -- the 1700 values
minus
select code
from table
-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L
Good morning list,
Environment HP-UX 11.0 Oracle 8.1.6
Can anyone help with this SQL.
I can get a result set of values from a table
that match a given list of values -
select code
from table
where code in ('A','B','C','D','E')
I can get a result set of values from a table
that do not match a given list of values -
select code
from table
where code not in ('A','B','C','D','E')
So far so good.
Now, how do I get the set of values from the list
that do NOT have a matching value in the table?
I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.
Thanks in advance, folks.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Haas
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain \(Calanais\)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: SQL question
Sorry, forgot to provide a link:
http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, September 23, 2002 2:33 PM
> Jonathan Gennick has an excellent article in "Oracle" magazine
(sept./oct.),
> which should help.
> He demonstrates two approaches: with and without pivot table.
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Monday, September 23, 2002 1:28 PM
>
>
> > It is a little awkward, but a union in an inline query may do the trick:
> >
> > 1 select a.code
> > 2 from (select '10' code from dual union
> > 3select '20' code from dual union
> > 4select '30' code from dual union
> > 5select '40' code from dual union
> > 6select '50' code from dual ) a
> > 7* where a.code not in (select to_char(deptno) from emp)
> > SQL> /
> >
> > CO
> > --
> > 40
> > 50
> >
> > Dan Fink
> >
> > -Original Message-
> > Sent: Monday, September 23, 2002 10:28 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Good morning list,
> >
> > Environment HP-UX 11.0 Oracle 8.1.6
> >
> > Can anyone help with this SQL.
> >
> > I can get a result set of values from a table
> > that match a given list of values -
> >
> > select code
> > from table
> > where code in ('A','B','C','D','E')
> >
> > I can get a result set of values from a table
> > that do not match a given list of values -
> >
> > select code
> > from table
> > where code not in ('A','B','C','D','E')
> >
> > So far so good.
> >
> > Now, how do I get the set of values from the list
> > that do NOT have a matching value in the table?
> >
> > I cannot create any objects in the schema I am
> > working in otherwise I would create a table with
> > the values and do a minus, but I can't figure out
> > how to do it in SQL only.
> >
> > Thanks in advance, folks.
> >
> > Steve
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Steven Haas
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Fink, Dan
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Igor Neyman
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQL question
Raj,
It wasn't a question of coding the sql to create
all of the unions, but hitting a limitation on
the number of unions in the sql.
But, thanks to all for the effort.
steve
--- "Jamadagni, Rajendra"
<[EMAIL PROTECTED]> wrote:
> Steve,
>
> select 'select a.code ' || chr(10) || ' from('
> ||
> from dual
> union
> select distinct 'select ' || '''' || code ||
> '''' || ' code from dual ' ||
> chr(10) || 'union' ||
> from my_code_table
> union
> select ')' || chr(10) || 'minus' from dual
> /
> select 'select distinct code from my_table' ||
> chr(10) || '/'
> /
>
>
> drop off the last union before the closing
> parenthesis ...
>
> See ... now you don't have to type ...
>
> Raj
>
__
> Rajendra JamadagniMIS, 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-
> From: Steven Haas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 23, 2002 1:38 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL question
>
>
> Dan (and Charlie),
>
> Thanks.
> Good suggestions, but the IN clause contains
> just
> over 1700 values.
>
> Puzzling, huh?
>
> steve
> >
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
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Haas
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).
