RE: Simple SQL Question
select a.emp, b.ValueA c.ValueB From base_table A RIGHT OUTER JOIN child_Table1 B ON b.emp = a.emp RIGHT OUTER JOIN child_Table2 C ON c.emp = a.emp -Original Message- Sent: Thursday, December 18, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Hello: I'm trying to figure out the new 9i outer joins. I can get a single table outer join working without any issues. But seem to keep getting errors when trying to do a two table outer join. I know it is just something with my syntax. Could anyone provide a quick sample, thanks in advance. A:= Base Table B:= Child Table 1 B:= Child Table 2 select a.emp, b.ValueA c.ValueB From base_table A outer join child_Table1 B on A.emp=B.Emp... I know the old way of select a.emp, b.ValueA c.ValueB From base_table A, child_Table1 B, child_Table2 C where A.emp=B.Emp(+) and A.emp=C.Emp(+) _ Grab our best dial-up Internet access offer: 6 months @$9.95/month. http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade 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: Kevin Toepke 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: pl/sql question and owa_pattern question
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
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: pl/sql question and owa_pattern question
Hi Stephane:
Thanks for your good suggestion. I compared the method you suggested and the orginal
one and it indeed boosted the performance (in my simple test). However the ONLY
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new
method (you suggested) correctly extract the words (and sent for processing), But
after processing I need to put processed-words back to the orginal string with orginal
demilters un-changed. I tried to track to position of delimit from the orginal string
by doing
global_pos := global_pos + pos ;
in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos
wrong when ltrim trims '#'. Any work-around?
TIA.
Guang
-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
RE: pl/sql question and owa_pattern question
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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: 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
RE: pl/sql question and owa_pattern question
Guang,
Well you are almost there ... you need fifo structure namely a pl/sql array
1. create a local pl/sql array to store the delimiter (store the ascii value of the
delimiter to be safe) my_array (varchar2(5))
2. as you find a delimiter insert into the first position in the array and replace the
delimiting character with #
3. lather.rinse.repeat.
when it is time to put it back
use a loop
nIndex := 0;
nPos := 0;
loop
npos := instr(my_str,'#',1);
exit when npos := 0;
nIndex := nindex + 1;
my_str := substr(my_str,1,nPos-1) || chr(my_array(nIndex)) || sybstr(my_str, nPos+1);
end loop;
something like this should help, proof-read though ...
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Friday, November 21, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
Hi Stephane:
Thanks for your good suggestion. I compared the method you suggested and the orginal
one and it indeed boosted the performance (in my simple test). However the ONLY
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new
method (you suggested) correctly extract the words (and sent for processing), But
after processing I need to put processed-words back to the orginal string with orginal
demilters un-changed. I tried to track to position of delimit from the orginal string
by doing
global_pos := global_pos + pos ;
in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos
wrong when ltrim trims '#'. Any work-around?
TIA.
Guang
-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0
Re: pl/sql question and owa_pattern question
PL/SQL is the fastest thing of them all when it comes to executing
SQL commands, but there are things which simply aren't practical
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal
tradeoff between ease of use and performance. If performance is a
paramount, as in real time application processing, then you'll have to
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the
idea of enabling oracle to use PCRE but I gave up when I read that 10g
will have that included.
On 11/21/2003 11:59:31 AM, Guang Mei wrote:
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
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
Re: pl/sql question and owa_pattern question
Would extproc_perl fit well enough, though, until 10g is here?
On Fri, 21 Nov 2003, Mladen Gogala wrote:
PL/SQL is the fastest thing of them all when it comes to executing
SQL commands, but there are things which simply aren't practical
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal
tradeoff between ease of use and performance. If performance is a
paramount, as in real time application processing, then you'll have to
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the
idea of enabling oracle to use PCRE but I gave up when I read that 10g
will have that included.
On 11/21/2003 11:59:31 AM, Guang Mei wrote:
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City
Re: pl/sql question and owa_pattern question
I don't know about PL/SQL but here is how I would get separate words from a big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words into the
database, instead
of printing them. The bottom line is that perl is an excellent tool for parsing
strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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: Silly SQL Question
Jacques, I checked your example, I think there are some issues here: 1. Original queries provided below do use merge join. 2. We could have missing indexes which can exist on real system. 3. Timings below is not a criteria -- after gathering statistics and creation an index on val this both queries take about 1,3 seconds. So it means on your system you checked the *speed of sort operation* only -- because, most probably, merge was used. Even w/o index but with hash join it works much more faster -- 11.87 vs 1.25 (figures are not precise). 4. It'a all for nothing -- life is cruel and real-life examples are much more complex :) If you do not mind I would not continue this discussion. Thank you. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jacques Kilchoer wrote: Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there! In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise. With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice) If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32 -- 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: Silly SQL Question
Jacques --
Huh? I thought the question was, give me all the usrs where there exists a
record containing a 1,5, and 7. How can that return one record, when there
are two users -- and only two users -- who have the 1,5,7 combination? The
data provided shows that both GAP and GPA have a 1, 5 and 7 and that no
other users do. My query provides that answer. If that wasn't the
question, then that won't be the answer; but if it *was* the question, then
the query is correct.
HTH,
Bambi.
=
create table gab (usr char(3),val number);
insert into gab values ('GAP',1);
insert into gab values ('GAP',5);
insert into gab values ('GAP',5);
insert into gab values ('GAP',7);
insert into gab values ('PAG',1);
insert into gab values ('PAG',7);
insert into gab values ('PAG',2);
insert into gab values ('JKL',1);
insert into gab values ('JKL',5);
insert into gab values ('JKL',5);
insert into gab values ('GPA',1);
insert into gab values ('GPA',5);
insert into gab values ('GPA',7);
insert into gab values ('GPA',8);
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
USR
---
GAP
GPA
-Original Message-
Sent: Friday, November 14, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L
-Original Message-
Bellow, Bambi
Why not do it like this...
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
Because that way you would get the wrong answer. With the sample data as
kindly provided by Mr. Begun the correct query would return one row, but
your query returns two rows.
SQL select * from gab ;
USR VAL
-- -
GAP1
GAP5
GAP5
GAP7
PAG1
PAG7
PAG2
JKL1
JKL5
JKL5
GPA1
GPA5
GPA7
GPA8
14 ligne(s) sélectionnée(s).
SQL select usr from gab
2 where val=1
3 intersect
4 select usr from gab
5 where val=5
6 intersect
7 select usr from gab
8 where val=7;
USR
--
GAP
GPA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bellow, Bambi
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Silly SQL Question
Just a guess: select distinct usr from xxx where (select count(*) from xxx group by Usr) = (select count(*) from xxx group by Usr, val) -Original Message- Sent: Thursday, November 13, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Gabriel Aragon wrote: I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- 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). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan 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: Silly SQL Question
Using Bambi's table and values. Try this query, it's ugly but it works (kind of like the contributor...) Daniel 1 select o.usr, count(o.usr) 2 from (select distinct usr, val 3from gab 4where val in (1,5,7) 5 and usr not in (select usr 6 from gab 7 where val not in (1,5,7))) o 8 group by o.usr 9* having count(o.usr) = 3 Jacques Kilchoer wrote: The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Gabriel, How about this untested code? Alan. select usr from ( select usr, sum(decode(val,1,1,0)) look1, -- flag for 1 sum(decode(val,5,5,0)) look2, -- flag for 5 sum(decode(val,7,7,0)) look3, -- flag for 7 sum(decode(val,1,0,5,0,7,0,1)) look4 -- flag for others from the_table group by usr ) where look1 = 1 AND look2 = 1 AND look3 = 1 AND look4 = 0 / -Original Message- Sent: Thursday, November 13, 2003 2:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon 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: Alan Gano 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: Silly SQL Question
Ok, guys I have to apologize twice,
First: the delay to answer your very kind emails, (you
know the urgent problems dont let you time for the
important ones)
Second: maybe my question was not clear enough,
ciertanly what Bambi says is what I need give me all
the usrs where there exists a record containing 1 AND
5 AND 7, the criteria was the list, not the records,
so it does not matter if the user has many more
records, but if he/she has those records that are in
the list, that is what I want, the solution is as
simple as Bambi's query.
I really really appreciate all the solutions provided
for you guys, I swear I tested every one.
Below I have a copy of my original email.
Thank you very much!
Gabriel Aragon
+++
I have a table like this:
Usr val
--
GAP 1
GAP 5
GAP 7
JKL 8
JKL 5
I need a query that returns the user (GAP o JKL) that
has ALL the values in a list. Example: Having the
list: 1,5,7 the result will be GAP, but with the
values 1,5 or 1,5,7,8 there will be no result.
select distinct usr from xxx
where val = All (1,3,5)
I was trying the ALL operator but it works with part
of the list, I need the user that has (exactly) all
the values in the list. Any idea?
Maybe it's a simple solution, but after several hours
I feel blocked.
Gabriel
+++
--- Bellow, Bambi [EMAIL PROTECTED] wrote:
Jacques --
Huh? I thought the question was, give me all the
usrs where there exists a
record containing a 1,5, and 7. How can that
return one record, when there
are two users -- and only two users -- who have the
1,5,7 combination? The
data provided shows that both GAP and GPA have a 1,
5 and 7 and that no
other users do. My query provides that answer. If
that wasn't the
question, then that won't be the answer; but if it
*was* the question, then
the query is correct.
HTH,
Bambi.
=
create table gab (usr char(3),val number);
insert into gab values ('GAP',1);
insert into gab values ('GAP',5);
insert into gab values ('GAP',5);
insert into gab values ('GAP',7);
insert into gab values ('PAG',1);
insert into gab values ('PAG',7);
insert into gab values ('PAG',2);
insert into gab values ('JKL',1);
insert into gab values ('JKL',5);
insert into gab values ('JKL',5);
insert into gab values ('GPA',1);
insert into gab values ('GPA',5);
insert into gab values ('GPA',7);
insert into gab values ('GPA',8);
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
USR
---
GAP
GPA
-Original Message-
Sent: Friday, November 14, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L
-Original Message-
Bellow, Bambi
Why not do it like this...
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
Because that way you would get the wrong answer.
With the sample data as
kindly provided by Mr. Begun the correct query would
return one row, but
your query returns two rows.
SQL select * from gab ;
USR VAL
-- -
GAP1
GAP5
GAP5
GAP7
PAG1
PAG7
PAG2
JKL1
JKL5
JKL5
GPA1
GPA5
GPA7
GPA8
14 ligne(s) sélectionnée(s).
SQL select usr from gab
2 where val=1
3 intersect
4 select usr from gab
5 where val=5
6 intersect
7 select usr from gab
8 where val=7;
USR
--
GAP
GPA
--
=
Any dream worth having is a dream worth fighting for(Cualquier sueño que valga la
pena tener, es un sueño por el que vale la pena luchar)Charles Xavier
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gabriel Aragon
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: Silly SQL Question
Jacques Kilchoer wrote: Mr. Begun: I'm not convinced that your answer is quite the right one. I've provided two solutions but I'm still confused :). Jacques, does that mean that I understand English and the original query was Ok? :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Gabriel Aragon wrote: Ok, guys I have to apologize twice, ... -- 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: Silly SQL Question
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a
user-written PL/SQL function because I wanted to compare timings for both. I created a
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my)
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false,
so all this was just an intellectual exercise.
With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause,
the times were slightly longer but still the same: 14.71 vs. 14.32
VB query:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (SELECT DISTINCT element FROM (
SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
, INSTR(:list, ',', 1, ROWNUM) c
FROM gab
WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
)
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
GROUP BY
usr
, cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
from
(select distinct
b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
) a
where
val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
and cnt = 4
group by
usr, cnt
having
count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
declare
insert_cnt constant pls_integer := 20 ;
commit_cnt constant pls_integer := 2000 ;
i pls_integer ;
j pls_integer ;
k pls_integer ;
l pls_integer ;
n pls_integer ;
usr gab.usr%type ;
type usrt is table of gab.usr%type index by binary_integer ;
usra usrt ;
type valt is table of gab.val%type index by binary_integer ;
vala valt ;
begin
dbms_random.initialize (dbms_utility.get_time) ;
i := 1 ;
while i = insert_cnt
loop
usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
n := mod (abs (dbms_random.random), 5) + 1 ;
j := mod (i - 1, commit_cnt) + 1 ;
k := least (commit_cnt, j + n - 1) ;
for l in j..k
loop
usra (l) := usr ;
vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
end loop ;
i := i + k - j + 1 ;
if k = commit_cnt or i = insert_cnt
then
forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
commit ;
end if ;
end loop ;
commit ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
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: Silly SQL Question
Jacques,
you can use my first name -- Mr. is too official for this list :). You have modified
the query,
however I would suggest you to check execution plan (and present it here) and remove
LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) by replacing it using number of
elements in the
list i.e., in your case, 4. As I already said, it was just an example, in real life I
would think
is it Ok or not Ok to use it.
Timing is not everything you can check, consider statisticts. Did you consider
indexing val?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a
user-written PL/SQL function because I wanted to compare timings for both. I created a
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my)
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.
With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32
VB query:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (SELECT DISTINCT element FROM (
SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
, INSTR(:list, ',', 1, ROWNUM) c
FROM gab
WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
)
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
GROUP BY
usr
, cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
from
(select distinct
b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
) a
where
val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
and cnt = 4
group by
usr, cnt
having
count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
declare
insert_cnt constant pls_integer := 20 ;
commit_cnt constant pls_integer := 2000 ;
i pls_integer ;
j pls_integer ;
k pls_integer ;
l pls_integer ;
n pls_integer ;
usr gab.usr%type ;
type usrt is table of gab.usr%type index by binary_integer ;
usra usrt ;
type valt is table of gab.val%type index by binary_integer ;
vala valt ;
begin
dbms_random.initialize (dbms_utility.get_time) ;
i := 1 ;
while i = insert_cnt
loop
usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
n := mod (abs (dbms_random.random), 5) + 1 ;
j := mod (i - 1, commit_cnt) + 1 ;
k := least (commit_cnt, j + n - 1) ;
for l in j..k
loop
usra (l) := usr ;
vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
end loop ;
i := i + k - j + 1 ;
if k = commit_cnt or i = insert_cnt
then
forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
commit ;
end if ;
end loop ;
commit ;
end ;
/
--
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: Silly SQL Question
Why not do it like this...
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
-Original Message-
Sent: Thursday, November 13, 2003 7:35 PM
To: Multiple recipients of list ORACLE-L
Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for
usr.
Inspired by Tom Kyte's answer
varying elements in IN list
http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P
8_DISPLAYID,F4950_P8_CRITERIA:110612348061,
I propose this solution, using a str_to_tbl function (see function
definition after the proof of concept.)
SQL select * from gab ;
USR VAL
-- -
GAP1
GAP5
GAP7
GAP9
JKL8
JKL5
XXX1
XXX5
8 ligne(s) sélectionnée(s).
SQL variable num_list varchar2 (4000)
SQL select b.usr
2 from
3 (select distinct a.usr, a.val from gab a) b,
4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
5 where
6 b.usr = d.usr and
7 b.val in (select *
8from
9 the (select
10 cast (str_to_tbl (:num_list) as
my_number_table)
11 from dual
12 )
13 )
14 group by b.usr, d.num_usr_val
15 having
16 count(*) = d.num_usr_val
17 and count (*) = (select count (*)
18 from
19 the (select
20 cast (str_to_tbl (:num_list) as
my_number_table)
21 from dual
22 )
23 )
24
SQL execute :num_list := '1,5'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
XXX
SQL execute :num_list := ' 8 , 5 '
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
JKL
SQL execute :num_list := '1,5,7'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée
SQL execute :num_list := '1,5,7,8'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée
SQL execute :num_list := '1,5,7,9'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
GAP
SQL execute :num_list := '1,5,7,8,9'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée
script:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
insert into gab (usr, val) values ('GAP', 1) ;
insert into gab (usr, val) values ('GAP', 5) ;
insert into gab (usr, val) values ('GAP', 7) ;
insert into gab (usr, val) values ('GAP', 9) ;
insert into gab (usr, val) values ('JKL', 8) ;
insert into gab (usr, val) values ('JKL', 5) ;
insert into gab (usr, val) values ('XXX', 1) ;
insert into gab (usr, val) values ('XXX', 5) ;
commit ;
create or replace type my_number_table as table of number ;
/
create or replace function str_to_tbl (p_str in varchar2)
return my_number_table
as
l_str varchar2 (32760) default p_str || ',' ;
l_nnumber ;
l_pos pls_integer default 1 ;
l_data my_number_table := my_number_table () ;
begin
loop
l_n := instr (l_str, ',', l_pos) ;
exit when (nvl (l_n, 0) = 0) ;
l_data.extend ;
l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n -
l_pos))) ;
l_pos := l_n + 1 ;
end loop;
return l_data ;
end;
/
variable num_list varchar2 (4000)
select b.usr
from
(select distinct a.usr, a.val from gab a) b,
(select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
where
b.usr = d.usr and
b.val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
group by b.usr, d.num_usr_val
having
count(*) = d.num_usr_val
and count (*) = (select count (*)
from
the (select
cast (str_to_tbl (:num_list) as
my_number_table)
from dual
)
)
execute :num_list := '1,5'
/
execute :num_list := ' 8 , 5 '
/
execute :num_list := '1,5,7'
/
execute :num_list := '1,5,7,8'
/
execute :num_list := '1,5,7,9'
/
execute :num_list := '1,5,7,8,9'
/
-Original Message-
Vladimir Begun
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT
NULL --, CONSTRAINT gab$uq UNIQUE (usr, val)
);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab
RE: Silly SQL Question
Yes, your query was much better. I keep on forgetting about those analytic functions. Shame on me. I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) -Original Message- Vladimir Begun ... PL/SQL is not needed to solve this task as SQL task. There reason when it would be wise to rewrite it is out of scope of this topic (but the reason is obvious). I'm just thinking that the query proposed by you is a bit expensive. So, I've re-scribbled mine: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (1, 5, 7) AND cnt = 3 GROUP BY usr , cnt HAVING COUNT(*) = cnt / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Silly SQL Question
-Original Message- Bellow, Bambi Why not do it like this... select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; Because that way you would get the wrong answer. With the sample data as kindly provided by Mr. Begun the correct query would return one row, but your query returns two rows. SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP5 GAP7 PAG1 PAG7 PAG2 JKL1 JKL5 JKL5 GPA1 GPA5 GPA7 GPA8 14 ligne(s) sélectionnée(s). SQL select usr from gab 2 where val=1 3 intersect 4 select usr from gab 5 where val=5 6 intersect 7 select usr from gab 8 where val=7; USR -- GAP GPA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Silly SQL Question
Jacques Jacques Kilchoer wrote: I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. If the given list is created properly, which I think it's a must in this case, one would not need to use PL/SQL, the task can be solved in SQL only. Below is just *an example*, not a generic solution. VAR list VARCHAR2(30); -- number could be counted as well, not a big deal EXEC :list := '1,7,5,'; WITH numbers AS ( SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element FROM ( SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p , INSTR(:list, ',', 1, ROWNUM) c FROM gab WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) ) ) SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (SELECT DISTINCT element FROM numbers) AND cnt = (SELECT COUNT(DISTINCT element) FROM numbers) -- it's for nothing, because count can be give by caller GROUP BY usr , cnt HAVING COUNT(*) = (SELECT COUNT(DISTINCT element) FROM numbers) -- same / Again, it's not a generic solution but it's Ok to use it for this particular task -- the number of elements is limited anyway. One could add yet one condition to avoid troubles with TO_NUMBER conversion, it's easy but I'm leaving it as is. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) That's obviously nice :) but I think it's not a reason to use PL/SQL to solve this task. -- 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: Silly SQL Question
Gabriel Aragon wrote: I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- 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: Silly SQL Question
Here's one solution. SELECT usr FROM xxx GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 HTH Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon 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: Aponte, Tony 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: Silly SQL Question
Sorry I missed one. Try this. SELECT usr FROM bogus GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 AND SUM(DECODE(val,1,0,5,0,7,0,val)) = 0 Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Here's one solution. SELECT usr FROM xxx GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 HTH Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon 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: Aponte, Tony 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: Aponte, Tony 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: Silly SQL Question
Gabriel
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL --, CONSTRAINT gab$uq
UNIQUE (usr, val)
);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('JKL', 8);
INSERT INTO gab VALUES('JKL', 5);
COMMIT;
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
Depending on the existence of the constraint, here gab$uq, you can
either use inline view of run it against original table.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Gabriel Aragon wrote:
I have a table with like this:
Usr val
--
GAP 1
GAP 5
GAP 7
JKL 8
JKL 5
I need a query that returns the user (GAP o JKL) that
has ALL the values in a list. Example: Having the
list: 1,5,7 the result will be GAP, but with the
values 1,5 or 1,5,7,8 there will be no result.
select distinct usr
from xxx
where val = All (1,3,5)
I was trying the ALL operator but it works with part
of the list, I need the user that has (exactly) all
the values in the list. Any idea?
Maybe it's a simple solution, but after several hours
I feel blocked.
TIA
Gabriel
--
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: Silly SQL Question
Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.
Inspired by Tom Kyte's answer
varying elements in IN list
http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061,
I propose this solution, using a str_to_tbl function (see function definition after
the proof of concept.)
SQL select * from gab ;
USR VAL
-- -
GAP1
GAP5
GAP7
GAP9
JKL8
JKL5
XXX1
XXX5
8 ligne(s) sélectionnée(s).
SQL variable num_list varchar2 (4000)
SQL select b.usr
2 from
3 (select distinct a.usr, a.val from gab a) b,
4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
5 where
6 b.usr = d.usr and
7 b.val in (select *
8from
9 the (select
10 cast (str_to_tbl (:num_list) as my_number_table)
11 from dual
12 )
13 )
14 group by b.usr, d.num_usr_val
15 having
16 count(*) = d.num_usr_val
17 and count (*) = (select count (*)
18 from
19 the (select
20 cast (str_to_tbl (:num_list) as my_number_table)
21 from dual
22 )
23 )
24
SQL execute :num_list := '1,5'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
XXX
SQL execute :num_list := ' 8 , 5 '
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
JKL
SQL execute :num_list := '1,5,7'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée
SQL execute :num_list := '1,5,7,8'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée
SQL execute :num_list := '1,5,7,9'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
GAP
SQL execute :num_list := '1,5,7,8,9'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée
script:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
insert into gab (usr, val) values ('GAP', 1) ;
insert into gab (usr, val) values ('GAP', 5) ;
insert into gab (usr, val) values ('GAP', 7) ;
insert into gab (usr, val) values ('GAP', 9) ;
insert into gab (usr, val) values ('JKL', 8) ;
insert into gab (usr, val) values ('JKL', 5) ;
insert into gab (usr, val) values ('XXX', 1) ;
insert into gab (usr, val) values ('XXX', 5) ;
commit ;
create or replace type my_number_table as table of number ;
/
create or replace function str_to_tbl (p_str in varchar2)
return my_number_table
as
l_str varchar2 (32760) default p_str || ',' ;
l_nnumber ;
l_pos pls_integer default 1 ;
l_data my_number_table := my_number_table () ;
begin
loop
l_n := instr (l_str, ',', l_pos) ;
exit when (nvl (l_n, 0) = 0) ;
l_data.extend ;
l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n - l_pos))) ;
l_pos := l_n + 1 ;
end loop;
return l_data ;
end;
/
variable num_list varchar2 (4000)
select b.usr
from
(select distinct a.usr, a.val from gab a) b,
(select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
where
b.usr = d.usr and
b.val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
group by b.usr, d.num_usr_val
having
count(*) = d.num_usr_val
and count (*) = (select count (*)
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
execute :num_list := '1,5'
/
execute :num_list := ' 8 , 5 '
/
execute :num_list := '1,5,7'
/
execute :num_list := '1,5,7,8'
/
execute :num_list := '1,5,7,9'
/
execute :num_list := '1,5,7,8,9'
/
-Original Message-
Vladimir Begun
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT
NULL --, CONSTRAINT gab$uq UNIQUE (usr, val)
);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('JKL', 8);
INSERT INTO gab VALUES('JKL', 5);
COMMIT;
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of
Re: Silly SQL Question
Jacques
Yes, probably, you are right. I've overlooked example section,
given by Gabriel.
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('PAG', 1);
INSERT INTO gab VALUES('PAG', 7);
INSERT INTO gab VALUES('PAG', 2);
INSERT INTO gab VALUES('JKL', 1);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('GPA', 1);
INSERT INTO gab VALUES('GPA', 5);
INSERT INTO gab VALUES('GPA', 7);
INSERT INTO gab VALUES('GPA', 8);
COMMIT;
PL/SQL is not needed to solve this task as SQL task. There
reason when it would be wise to rewrite it is out of scope
of this topic (but the reason is obvious).
I'm just thinking that the query proposed by you is a bit
expensive. So, I've re-scribbled mine:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (1, 5, 7)
AND cnt = 3
GROUP BY
usr
, cnt
HAVING COUNT(*) = cnt
/
HTH,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
FROM (
SELECT DISTINCT usr, val FROM gab
)
WHERE val IN (1, 5, 7)
GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.
--
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: interesting sql question
Ralph, Assuming that there is no history in the BIDS table (meaning that there are no old records indicating a bid recorded last year), I think the following would work just fine. select name from person, (select distinct sid, count(*) bid_count from bids group by sid) bids where person.sid = bids.sid and bid_count = 3 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: interesting sql question
- --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 29 Sep 2003 05:19:39 Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. I would run an uncorrelated subquery on BOATS to count how many of them we have (mot likely to be a multimillion row table, and it's just a PK scan), which you can feed into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and supposing (which is often the case) that your FK is indexed it doesn't require anything but another index scan. Which can of course take *some* time if BIDS is really big but I don't see how to escape a group by here (or anything worse). 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: interesting sql question
select pn.name from (select /*+ no_merge */ count(*) boat_cnt from boat) bt, bid bd, person pn where bd.sid = pn.sid group by pn.name, boat_cnt having count(bd.boat_id) = boat_cnt Waleed -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: RE: interesting sql question
From: Stephane Faroult [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 09:59:39 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: interesting sql question - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 29 Sep 2003 05:19:39 Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. I would run an uncorrelated subquery on BOATS to count how many of them we have (mot likely to be a multimillion row table, and it's just a PK scan), which you can feed into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and supposing (which is often the case) that your FK is indexed it doesn't require anything but another index scan. Which can of course take *some* time if BIDS is really big but I don't see how to escape a group by here (or anything worse). Bitmap scan would be the fastest. Ive noticed that counts on those are incredibly fast. So your saying something like: how would you write the query? I dont quite see it. 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: interesting sql question
Title: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: interesting sql question
This would eliminate duplicate bids on the same boat by the same person
SELECT p.*
FROMPERSON p,
(
SELECT COUNT(*) boat_count
FROMBOAT
) c,
(
SELECT sid, COUNT(DISTINCT boat_id) bid_count
FROMBIDS
GROUP BY sid
) b
WHERE p.sid = b.sid
AND b.bid_count = c.boat_count;
-Original Message-
Sent: Monday, September 29, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L
Im taking a database theory class(no I dont need help with my homework).
There is an interesting query in the book that I have never seen posed
before. The solution would be hideously slow if there was even a moderate
amount of data in the tables. How would you write it?
Given 3 tables: and columns in the tables:
TABLE: Person
Primary Key: SID
COLUMN: NAME
TABLE: BIDS
Primary Key: BID
Foreign Key: SID
FOREIGN KEYT: BOAT_ID
Column: Date
Boat:
Primary Key: BOAT_ID
Column: Color
Find any person who has reserved all the boats. The
I dont have the solution with me, but there is a 'NOT EXISTS', then in the
subquery there is a minus and a correlated 'where' clause.'. That query
wouldnt move.
How would you solve this?
Also, according to the 'SQL Standard', SQL is supposed to support op codes
such as 'ALL' or 'ANY' So you can say:
Find all people who are older than any person with blue eyes. Or find all
the people who are older than 'ALL' the people with blue eyes.
Just to reiterate. Not looking for help with my homework. My professor isnt
an Oracle guy so he doesnt know.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chelur, Jayadas {PBSG}
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: interesting sql question
a user may request the same boat more than once. not sure that work. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Title: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: RE: interesting sql question
Title: RE: RE: interesting sql question Hey ... the question wasn't complete ... give us the full statement of the question ... g Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: interesting sql question a user may request the same boat more than once. not sure that work. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email 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: RE: interesting sql question
you could do this, but i would have concerns over the indexing strategy. select name from person, (select distinct sid, count(*) bid_count from bids group by sid HAVING count(*) = (SELECT COUNT(BOAT_ID FROM BOATS)) bids where person.sid = bids.sid; Now yours bids table is an intersect table and would have the most records of all three tables. I would create an extra field that never gets update and just put a default value in it. Then I would put a bitmap index on it. since they aer VERY faster on counts. my problem is with the group by. SID could be huge. That could lead to a massive slow down and alot of LIOs dont think there is a faster a solution though. No correlated sub-queries which are LIO intensive. From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 09:34:38 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: interesting sql question Ralph, Assuming that there is no history in the BIDS table (meaning that there are no old records indicating a bid recorded last year), I think the following would work just fine. select name from person, (select distinct sid, count(*) bid_count from bids group by sid) bids where person.sid = bids.sid and bid_count = 3 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: interesting sql question
no there are examples in the book using where 'not exists'. the query was horrible.
Ill post it later if you want to see how bad it is.
no its not homework. Id get the answer wrong if i did it this way, since Id have to
follow the model in the book. Which is terrible.
From: Mercadante, Thomas F [EMAIL PROTECTED]
Date: 2003/09/29 Mon PM 12:29:40 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: RE: interesting sql question
yeah! I think it *is* homework :)
Tom
-Original Message-
Sent: Monday, September 29, 2003 12:10 PM
To: Multiple recipients of list ORACLE-L
Hey ... the question wasn't complete ...
give us the full statement of the question ...
g
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Monday, September 29, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L
a user may request the same boat more than once. not sure that work.
From: Jamadagni, Rajendra [EMAIL PROTECTED]
Date: 2003/09/29 Mon AM 10:34:53 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: RE: interesting sql question
Here is an attempt ...
select p.*
from persons p
where sid in
(select sid, count(bid)
from bids
group by sid
having count(sid) = (select count(boad_id) from boats))
/
You wanted to find all persons who have booked all boats ... add criteria
for booked in the first sub-query.
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
Title: RE: RE: interesting sql question
yeah! I think it *is* homework :)
Tom
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003
12:10 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: RE: interesting sql question
Hey ... the question wasn't complete ...
give us the full statement of the question ...
g Raj
Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art
!
-Original Message- From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To:
Multiple recipients of list ORACLE-L Subject: RE: RE:
interesting sql question
a user may request the same boat more than once. not sure that
work. From:
"Jamadagni, Rajendra" [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: RE: interesting sql question
Here is an attempt
... select
p.* from persons p
where sid in
(select sid, count(bid) from bids
group by
sid having
count(sid) = (select count(boad_id) from boats))
/ You wanted to find
all persons who have booked all boats ... add criteria for booked in the first sub-query.
Raj
Rajendra dot Jamadagni
at nospamespn dot com All Views expressed in this
email are strictly personal. QOTD: Any clod can
have facts, having an opinion is an art !
RE: Antw: SQL question : How to retrieve the File_name without
eat this:
Is it chewable? -:)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Guido Konsolke
Sent: Wednesday, July 23, 2003 3:14 AM
To: Multiple recipients of list ORACLE-L
Hi Philippe,
eat this:
select
substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-
1)+1) from dual;
hth,
Guido
[EMAIL PROTECTED] 23.07.2003 09.59 Uhr
Hi Gurus!
a very simple problem for You :I just want to retrieve the .dbf name
from
file_name column in dba_data_files.
eg :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ?
Thank in advance !
Philippe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guido Konsolke
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: RE: Antw: SQL question : How to retrieve the File_name
Hi Igor,
yes, it is. But it would be better if we all
swallow the use of the builtin functions.
My weaknesses are with analytic functions.
I would give my colleague's right arm for
getting into them ;-))
Greetings,
Guido
[EMAIL PROTECTED] 23.07.2003 15.54 Uhr
eat this:
Is it chewable? -:)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Guido Konsolke
Sent: Wednesday, July 23, 2003 3:14 AM
To: Multiple recipients of list ORACLE-L
Hi Philippe,
eat this:
select
substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-
1)+1) from dual;
hth,
Guido
[EMAIL PROTECTED] 23.07.2003 09.59 Uhr
Hi Gurus!
a very simple problem for You :I just want to retrieve the .dbf name
from
file_name column in dba_data_files.
eg :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ?
Thank in advance !
Philippe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guido Konsolke
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guido Konsolke
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).
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
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
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
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
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
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
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
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
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
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, 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
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
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
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
Title: Re: A SQL Question SQL select A.c1, B.c2 2 from (select col1 c1, rownum r from tbl order by col1) A 3 , (select col2 c2, rownum r from tbl order by col2) b 4 where a.r = b.r 5 union all 6 select B.c2, A.c1 7 from (select col1 c1, rownum r from tbl order by col1) A 8 , (select col2 c2, rownum r from tbl order by col2) b 9 where a.r = b.r 10 order by 1 11 / C C - - A B B A C D D C E F F E G H H G At 05:23 AM 3/13/2003 -0800, you wrote: 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 Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier lectronique est une communication prive l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.
Re: Corrected SQL Question...
I think its easier if you do it cross-tab AUS DAL DAL AUS Is that acceptable? Or just select AUS DAL If it also has a DAL AUS Are either of those metods acceptable? If so, pick one and Ill show you how to do it. From: Deshpande, Kirti [EMAIL PROTECTED] Date: 2003/03/13 Thu AM 11:19:15 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Corrected SQL Question... Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Corrected SQL Question...
Title: Re: Corrected SQL Question... SQL select A.c1, B.c2 2 from (select col1 c1, rownum r from tbl order by col1) A 3 , (select col2 c2, rownum r from tbl order by col2) b 4 where a.r = b.r 5 union 6 select B.c2, A.c1 7 from (select col1 c1, rownum r from tbl order by col1) A 8 , (select col2 c2, rownum r from tbl order by col2) b 9 where a.r = b.r 10 order by 1 11 / C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL LIT HOU XYZ DAL XYZ HOU At 08:19 AM 3/13/2003 -0800, you wrote: AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier lectronique est une communication prive l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.
RE: Corrected SQL Question...
I think those solutions should be acceptable. Not sure if they are displaying any more information from the table. I was just given the test table to get the SQL script working Thanks a lot. - Kirti -Original Message- Sent: Thursday, March 13, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I think its easier if you do it cross-tab AUS DAL DAL AUS Is that acceptable? Or just select AUS DAL If it also has a DAL AUS Are either of those metods acceptable? If so, pick one and Ill show you how to do it. From: Deshpande, Kirti [EMAIL PROTECTED] Date: 2003/03/13 Thu AM 11:19:15 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Corrected SQL Question... Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - 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: Corrected SQL Question...
Kirti, Can you explain the required result order? It looks random to me - or like one of the tests we were forced to take in High School. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 9:31 AM To: Multiple recipients of list ORACLE-L Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - 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: Corrected SQL Question...
Tom, They wanted to 'pair up' the contents from c1 and c2. Those are supposed to be 3 char Airport codes. DAL-AUS followed by AUS-DAL (or vice-versa). That's all I was told. Thanks. - Kirti -Original Message- Sent: Thursday, March 13, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, Can you explain the required result order? It looks random to me - or like one of the tests we were forced to take in High School. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 9:31 AM To: Multiple recipients of list ORACLE-L Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - 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: Corrected SQL Question...
Not quite random. Note that the value is field 1 of the first record is the value in field 2 in the second. It looks like they want to pair up the cities if they appear in both columns. i.e. Since Dallas is in column 1 with Austin in Column 2 in one record, and Dallas is in Column 2 with Austin in column 1 in a seperate record, they want those records to follow each other. Could be a cleanup effort ... duplicate but reversed data -Original Message- Sent: Thursday, March 13, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Kirti, Can you explain the required result order? It looks random to me - or like one of the tests we were forced to take in High School. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 9:31 AM To: Multiple recipients of list ORACLE-L Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Lange 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: Corrected SQL Question...
Title: RE: Corrected SQL Question...
(see answer below)
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
Here is the test data:
SQL select c1,c2 from cp;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
HOU XYZ
LIT DAL
C1 C2
--- ---
LIT HOU
XYZ DAL
XYZ HOU
14 rows selected.
SQL
Here is what is required:
C1 C2
--- ---
AUS DAL
DAL AUS
AUS HOU
HOU AUS
DAL HOU
HOU DAL
DAL LIT
LIT DAL
DAL XYZ
XYZ DAL
HOU LIT
LIT HOU
HOU XYZ
XYZ HOU
This is not very elegant, but it works:
SQL select * from cp ;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
HOU XYZ
LIT DAL
LIT HOU
XYZ DAL
XYZ HOU
14 ligne(s) sélectionnée(s).
SQL -- desired result
SQL select
2 least (a.c1, a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2
3 from cp a
4 where not exists
5 (select * from cp b
6 where b.c2 = a.c1 and b.c1 = a.c2 and a.c1 b.c1)
7 union
8 select
9 least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2
10 from
11 cp c, cp d
12 where
13 c.c1 = d.c2 and c.c2 = d.c1 and c.c1 d.c1
14 order by 1, 2 ;
SORT_F C1 C2
-- --- ---
AUSDAL AUS DAL
AUSDAL DAL AUS
AUSHOU AUS HOU
AUSHOU HOU AUS
DALHOU DAL HOU
DALHOU HOU DAL
DALLIT DAL LIT
DALLIT LIT DAL
DALXYZ DAL XYZ
DALXYZ XYZ DAL
HOULIT HOU LIT
HOULIT LIT HOU
HOUXYZ HOU XYZ
HOUXYZ XYZ HOU
14 ligne(s) sélectionnée(s).
create table cp
(c1 varchar2 (3), c2 varchar2 (3)) ;
insert into cp values ('AUS', 'DAL') ;
insert into cp values ('AUS', 'HOU') ;
insert into cp values ('DAL', 'AUS') ;
insert into cp values ('DAL', 'HOU') ;
insert into cp values ('DAL', 'LIT') ;
insert into cp values ('DAL', 'XYZ') ;
insert into cp values ('HOU', 'AUS') ;
insert into cp values ('HOU', 'DAL') ;
insert into cp values ('HOU', 'LIT') ;
insert into cp values ('HOU', 'XYZ') ;
insert into cp values ('LIT', 'DAL') ;
insert into cp values ('LIT', 'HOU') ;
insert into cp values ('XYZ', 'DAL') ;
insert into cp values ('XYZ', 'HOU') ;
commit ;
select * from cp ;
-- desired result
select
least (a.c1, a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2
from cp a
where not exists
(select * from cp b
where b.c2 = a.c1 and b.c1 = a.c2 and a.c1 b.c1)
union
select
least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2
from
cp c, cp d
where
c.c1 = d.c2 and c.c2 = d.c1 and c.c1 d.c1
order by 1, 2 ;
RE: Corrected SQL Question...
Do mean something like this? It would be interesting to see if this could be done with some kind of tree walk. 1* select a.c1, a.c2, b.c1, b.c2 from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL / C1C2C1C2 - - - - DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Corrected SQL Question...
Title: RE: Corrected SQL Question...
Jacques,
Thanks a bunch.
Elegance was not one of the requirements ;)
Cheers!
-
Kirti
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003
12:53 PMTo: '[EMAIL PROTECTED]'Cc: Deshpande,
KirtiSubject: RE: Corrected SQL Question...
(see answer below)
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
Here is the test data:
SQL select c1,c2
from cp; C1
C2 --- --- AUS
DAL AUS HOU DAL
AUS DAL HOU DAL
LIT DAL XYZ HOU
AUS HOU DAL HOU
LIT HOU XYZ LIT
DAL C1
C2 --- --- LIT
HOU XYZ DAL XYZ
HOU 14 rows
selected. SQL
Here is what is
required: C1
C2 --- --- AUS
DAL DAL AUS AUS
HOU HOU AUS DAL
HOU HOU DAL DAL
LIT LIT DAL DAL
XYZ XYZ DAL HOU
LIT LIT HOU HOU
XYZ XYZ HOU
This is not very elegant, but it works: SQL select * from cp ; C1 C2
--- --- AUS DAL AUS HOU DAL AUS DAL
HOU DAL LIT DAL XYZ
HOU AUS HOU DAL HOU LIT HOU XYZ LIT
DAL LIT HOU XYZ DAL
XYZ HOU 14 ligne(s)
sélectionnée(s).
SQL -- desired result SQL
select 2 least (a.c1,
a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2 3 from cp a
4 where not exists
5 (select * from cp b
6 where b.c2 = a.c1 and b.c1 = a.c2 and
a.c1 b.c1) 7 union 8 select
9 least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field,
d.c1, d.c2 10 from
11 cp c, cp d
12 where 13 c.c1 = d.c2 and c.c2 = d.c1 and
c.c1 d.c1 14 order by 1, 2 ;
SORT_F C1 C2 -- ---
--- AUSDAL AUS DAL AUSDAL DAL
AUS AUSHOU AUS HOU AUSHOU HOU
AUS DALHOU DAL HOU DALHOU HOU
DAL DALLIT DAL LIT DALLIT LIT
DAL DALXYZ DAL XYZ DALXYZ XYZ
DAL HOULIT HOU LIT HOULIT LIT
HOU HOUXYZ HOU XYZ HOUXYZ XYZ
HOU 14 ligne(s) sélectionnée(s).
create table cp (c1 varchar2 (3), c2
varchar2 (3)) ; insert into cp values ('AUS', 'DAL')
; insert into cp values ('AUS', 'HOU') ;
insert into cp values ('DAL', 'AUS') ; insert into cp values ('DAL', 'HOU') ; insert
into cp values ('DAL', 'LIT') ; insert into cp values
('DAL', 'XYZ') ; insert into cp values ('HOU', 'AUS')
; insert into cp values ('HOU', 'DAL') ;
insert into cp values ('HOU', 'LIT') ; insert into cp values ('HOU', 'XYZ') ; insert
into cp values ('LIT', 'DAL') ; insert into cp values
('LIT', 'HOU') ; insert into cp values ('XYZ', 'DAL')
; insert into cp values ('XYZ', 'HOU') ;
commit ; select * from cp ;
-- desired result select
least (a.c1, a.c2) || greatest (a.c1, a.c2) as
sort_field, a.c1, a.c2 from cp a
where not exists
(select * from cp b where
b.c2 = a.c1 and b.c1 = a.c2 and a.c1 b.c1) union select
least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2
from cp c,
cp d where c.c1 = d.c2 and c.c2 = d.c1 and c.c1
d.c1 order by 1, 2 ;
RE: Corrected SQL Question...
Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Corrected SQL Question...
All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- 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: Corrected SQL Question...
Is this cheating? 1* select a.c1||' '||a.c2||CHR(10)||b.c1||' '||b.c2 RESULTS from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL / RESULTS --- DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Corrected SQL Question... All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- 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: Stephen Lee 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: Corrected SQL Question...
Kirti, What about solution suggested by Stephane Faroult: select * from (select * from T connect by col1 = prior col2 and col1 col2) x where rownum = (select count(*) from T) / ? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 3:24 PM All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- 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: Corrected SQL Question...
Igor Neyman wrote: Kirti, What about solution suggested by Stephane Faroult: select * from (select * from T connect by col1 = prior col2 and col1 col2) x where rownum = (select count(*) from T) / ? Igor Neyman, OCP DBA [EMAIL PROTECTED] Igor, I can answer that - col1 col2 worked with the first sample of data, not with the second one. The problem is with the loops in the tree - CONNECT BY doesn't like round-trips from an airport and back! And since you cannot put a subquery in a CONNECT BY, you're toast. I think, though, that you can probably use the tree walk if you do it in PL/SQL with a bulk select in an array. Previous experiments have shown to me that when the exception is raised, the data is returned anyway. Needless to say, it becomes messy :-). -- 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: Corrected SQL Question...
Unfortunately, it is. - Kirti -Original Message- Sent: Thursday, March 13, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Is this cheating? 1* select a.c1||' '||a.c2||CHR(10)||b.c1||' '||b.c2 RESULTS from crap a, crap b where a.c2 = b.c1 and b.c2 = a.c1 SQL / RESULTS --- DAL AUS AUS DAL HOU AUS AUS HOU AUS DAL DAL AUS HOU DAL DAL HOU LIT DAL DAL LIT XYZ DAL DAL XYZ AUS HOU HOU AUS DAL HOU HOU DAL LIT HOU HOU LIT XYZ HOU HOU XYZ DAL LIT LIT DAL HOU LIT LIT HOU DAL XYZ XYZ DAL HOU XYZ XYZ HOU 14 rows selected. -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Corrected SQL Question... All they wanted was to pair up those city codes. DAL -- AUS followed by AUS -- DAL, AUS -- HOU followed by HOU -- AUS etc... and on separate lines. So, cross-tab did not have the right format. I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the UNION), and it was acceptable. Problem solved, as there are no more questions :) - Kirti -Original Message- Sent: Thursday, March 13, 2003 1:46 PM To: Multiple recipients of list ORACLE-L Questions I would have for those who wrote the requirements: Of possible combinations of the form ABC XYZ XYZ ABC, which do they want? As can be seen from the answers sent to the list, there is more than one set of responses that give this pattern. If they only want half of the possible patterns, which half is the correct half? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] -- 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
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 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
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
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).
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 -- 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
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).
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: 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
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 -- 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
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: 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: 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: PL/Sql question
Dennis, I'd guess that the developer did not try it correctly. Ask to see the code. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB
RE: PL/Sql question
Tom - Thanks to you and everyone else for the great suggestions. He and I are sitting down tomorrow to straighten this out. I was concerned that there might be some PL/SQL oddity that I wasn't aware of (he is a pretty good PL/SQL programmer). I appreciate your ruling that out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 22, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Dennis, I'd guess that the developer did not try it correctly. Ask to see the code. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159)
RE: PL/Sql question
Sounds like in the table the field c.marketcode is a char(3) instead of varchar2(3). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *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: PL/SQl question
Is the field in question in table C defined as CHAR or VARCHAR2? If CHAR that is why it is blank padded. Check datatype of variables in pl/sql Rick DENNIS WILLIAMS DWILLIAMS@life To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] touch.comcc: Sent by: Subject: PL/SQl question [EMAIL PROTECTED] m 08/21/2002 10:28 AM Please respond to ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: PL/SQl question
Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQl question
Check the definition of table C. It sounds like it is defined as CHAR(3) instead of VARCHAR2(3). I would also check the PL/SQL for using CHAR instead of VARCHAR2 for storing the value -- the trim should have eliminated this problem if it was put in the right place. Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Wednesday, August 21, 2002 7:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQl question
I believe it is acting appropriately. You are trying to load a two-character byte filed into three-byte character field. Loader, if you don't terminate by whitespace or nulls, will add the blank into the field because it is character. Thus, you have two options: 1. Change the field to numeric. 2. Trim the data before it is loaded. Check the third position to see if it is a space or null; if so, only load n positions of data. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, August 21, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject:RE: PL/SQl question Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: PL/Sql question
Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE /MM/DD NULLIF ORIGINALDATERE, CMSNSTATUS POSITION(208:208) CHAR ) == All tables have the marketingcode field defined as varchar2(3) (none are char(3)) Bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services--
RE: PL/Sql question
Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE /MM/DD NULLIF ORIGINALDATERE, CMSNSTATUS
Re: Pl/sql question - if statement
You have messed up quotes in IF condition
Maybe You can use some coding style other than chaotic?
It helps in debugging, believe me.
Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
Roland.Skoldblom@
ica.se To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Pl/sql question - if
statement
2002.04.09 17:43
Please respond to
ORACLE-L
anyone whom can tell me why this statement fails in a pl/sqll code:
I get this error message
PLS-00103: Encountered the symbol ||AvdNr|| when expecting one of the
following:
. ( * @ % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe
when i run this statement
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ''
then
lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM A'||AvdNr||'.ICA_ARTIKEL@'
||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' ||
EanLPVara || ' ' ||
'AND ICA_ARTIKEL.DATUMTO_DATE('''
||inDatum||''',''-MM-DD'') ' ||
-- 'AND
ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' ||
'ORDER BY DATUM DESC';
--
DBMS_OUTPUT.PUT_LINE(lvSQL);
DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,1,250));
DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,251,250));
END IF;
Please help me. I must be blind. I have tried to look at the first code
line..
Would appreciate help very much.
Thanks in advance.
Roland
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Pl/sql question - if statement
Check your quotes. Better yet, create a variable as the string then check
the variable.
If I understand your code, the first line would look like this:
If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG' = 0
THEN
I suspect you're going to have problems with the rest of your quoting as
well.
David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002
Roland.Skoldbl
[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Pl/sql question - if statement
om
04/09/2002
09:43 AM
Please respond
to ORACLE-L
anyone whom can tell me why this statement fails in a pl/sqll code:
I get this error message
PLS-00103: Encountered the symbol ||AvdNr|| when expecting one of the
following:
. ( * @ % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe
when i run this statement
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ''
then
lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM A'||AvdNr||'.ICA_ARTIKEL@'
||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' ||
EanLPVara || ' ' ||
'AND ICA_ARTIKEL.DATUMTO_DATE('''
||inDatum||''',''-MM-DD'') ' ||
-- 'AND
ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' ||
'ORDER BY DATUM DESC';
--
DBMS_OUTPUT.PUT_LINE(lvSQL);
DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,1,250));
DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,251,250));
END IF;
Please help me. I must be blind. I have tried to look at the first code
line..
Would appreciate help very much.
Thanks in advance.
Roland
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Ang: RE: Pl/sql question - if statement
Yes but then it fails onthe word borttags_flagg, thi serrormessage :
PLS-00103: Encountered the symbol BORTTAGS_FLAGG when expecting one of the following:
. ( * @ % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
I reallydont see what the error is:
Roland
John Hallas [EMAIL PROTECTED]@fatcity.com den 2002-04-09 07:58 PST
Sänd svar till [EMAIL PROTECTED]
Sänt av: [EMAIL PROTECTED]
Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Kopia:
Don't you need to start of with a quote before the first A
If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ''
John
-Original Message-
[EMAIL PROTECTED]
Sent: 09 April 2002 15:43
To: Multiple recipients of list ORACLE-L
anyone whom can tell me why this statement fails in a pl/sqll code:
I get this error message
PLS-00103: Encountered the symbol ||AvdNr|| when expecting one of the
following:
. ( * @ % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe
when i run this statement
If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ''
then
lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPK
ORGEANREL.VARUTYP ' ||
'FROM
A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' ||
EanLPVara || ' ' ||
'AND
ICA_ARTIKEL.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- 'AND
ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' ||
'ORDER BY DATUM DESC';
--
DBMS_OUTPUT.PUT_LINE(lvSQL);
DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,1,250));
DBMS_OUTPUT.PUT_LINE(SUBSTR(lvSQL,251,250));
END IF;
Please help me. I must be blind. I have tried to look at the first code
line..
Would appreciate help very much.
Thanks in advance.
Roland
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Hallas
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL Question
create a function getSoftwares(p_licence_id ) which returns varchar2 string of softwares and then simply run query on licence table select licence_id , getSoftware(licenceid) from licence ; I hope you know what to write in getSoftwares . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 4:28 AM Hi all, i have 2 tables software and licence. 1 licence can have many softwares. softwares name platform Licence_id abc NT1 def WIN2K1 ghi all 2 i want to write a query that displays the results as follows licence_idsoftwares --- --- 1abc (NT), def(WIN2K) 2 all Any suggestions on how i can do this? cheers! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Big Planet INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
You could use a user function. For example, create or replace function lic_format (id in number) return varchar2 as tmp varchar2(4000); hold_tmp varchar2(50); cursor c1 is select name from software where license_id = id; begin open c1; loop fetch c1 into hold_tmp; exit when c1%notfound; tmp := tmp ||hold_tmp||','; end loop; close c1; return tmp; end; / Then, your select would be: select license_id, lic_format(license_id) from license; The output would be: LICENSE_ID FORMAT -- - 1 abc,def, 2 ghi, Granted, this doesn't do the platform in parentheses, but it could if you beef up the function, and you may want to make the function smarter so that it didn't print out that last comma, but at least this gives you an idea. - Jeff Young -Original Message- Sent: Tuesday, March 19, 2002 7:28 AM To: Multiple recipients of list ORACLE-L Hi all, i have 2 tables software and licence. 1 licence can have many softwares. softwares name platform Licence_id abc NT1 def WIN2K1 ghi all 2 i want to write a query that displays the results as follows licence_idsoftwares --- --- 1abc (NT), def(WIN2K) 2 all Any suggestions on how i can do this? cheers! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Young, Jeff A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question
Write a PL/SQL function which takes the licence_id as argument and returns a varchar2(... what you deem sufficient, up to 32K). In the function, loop on the appropriate table and concatenate. When you run select licence_id, my_ugly_func(licence_id) softwares from ... you more or less get what you want. Performance will even be decent if you have indexed by licence_id. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 19 Mar 2002 04:28:19 Hi all, i have 2 tables software and licence. 1 licence can have many softwares. softwares name platform Licence_id abc NT1 def WIN2K1 ghi all 2 i want to write a query that displays the results as follows licence_idsoftwares --- --- 1abc (NT), def(WIN2K) 2 all Any suggestions on how i can do this? cheers! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- - Stephane Faroult Oriole Corporation Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/sql question
- pick out the name of the procedure thatis currently running, check http://osi.oracle.com/~tkyte/who_called_me/index.html, dbms_utility.get_call_stack, dbms_utility.get_error_stack -pick out the start_time of the procedure discussed some days ago - pick outthe end_time of the procedure when it fails just sysdate - pick out the number of rows that were inserted inthe insertstatement inthe procedure. already discuseed - The error code sqlcode, dbms_utility.get_error_stack _Th errormessage sqlerrm dbms_utility.get_error_stack All these things I want to be inserted in a table. Give me a good example on how to write the code, please. You may use simple procedure that picks some arguments like start_time, end_time, proc_name, inserted/not inserted rows, error info and inserts it in a table Just use autonomous transactions. More - read docs, it is well documented in Oracle guides. Starting points - technet.oracle.com, docs.oracle.com, metalink.oracle.com (only to members :((( ) Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
