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).
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...
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).
