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
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
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
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
/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
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
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
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
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
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
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
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
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),
: 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
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
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
: 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
17 matches
Mail list logo
