Hi:
In pl/sql, I want to add chr(10) into a string in every 70th position. The
string can be up to 2000 characters long. The follwoing code works. But is
there an even FASTER way to do this?
Thanks.
Guang
---
declare
pos number := 1;
len number;
buf varchar2(2000);
x
Bear in mind though that the original query will only count rows where
b.award_number is not null whereas this new query will count all rows in
the result set.
Regards,
Dave
[EMAIL PROTECTED] wrote:
Can you change it to this query:
SELECT count(1) FROM RF_BALANCE_T b, rf_security_by_dceid s
Hi List,
I have following sql that runs in 1 sec:
SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s
WHERE
(s.award_number = 'ALL')
OR (b.award_number = s.award_number AND s.project_number = 'ALL')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND s.task_number =
David - Can you post the EXPLAIN PLAN for both?
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, January 12, 2004 1:14 PM
To: Multiple recipients of list ORACLE-L
Hi List,
I have following sql that runs in 1 sec:
SELECT b.* FROM RF_BALANCE_T b,
Can you change it to this query:
SELECT count(1) FROM RF_BALANCE_T b, rf_security_by_dceid s
WHERE
(s.award_number = 'ALL')
OR (b.award_number = s.award_number AND s.project_number = 'ALL')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND s.task_number = 'ALL')
That's fairly typical behavior. Try the following
SELECT /*+ NO_MERGE(x) */ COUNT(*)
FROM (your 1 second query) x
Kevin
-Original Message-
Sent: Monday, January 12, 2004 2:14 PM
To: Multiple recipients of list ORACLE-L
Hi List,
I have following sql that runs in 1 sec:
SELECT b.*
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and
Hallo,
I have this sql query. I would like to change it so I could pick out the three forst
three charachters in the between statements. How can I do that in the most simply way?
and vgr in (between 101 and 101)
and vgr in (between 104 and 104) order by Avdnr ,Datum1
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
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
Does myFunction1 often get called with the same arguments? In your example, the
second argument is never repeated, but in the real thing, would the same second
argument be likely to repeat? If myFunction1 gets the same arguments, will it always
return the same value? If so, then it is a
The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase
what does myfunction1() do?
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
Below is the code for myfunction1 in the package, It calls a bunch of other
functions, such as getBlastMatches, escapeGene, genes.gene2protein and
addItem.
Guang
---
type blastMatch is record (
geneName gene.name%type,
percent number
);
function myfunction1 (seqid in number,
Hi:
Oracle 8173 DB.
I have a package funtion, part of it is doing something like this:
utl_file.put_line(fpn, myFunction1(myID, 1));
utl_file.put_line(fpn, myFunction1(myID, 8));
utl_file.put_line(fpn, myFunction1(myID, 6));
utl_file.put_line(fpn, myFunction1(myID, 35));
can you return multiple values from a modified version of myfunction(1) ?? If so, then
you can replace multiple calls by only one. And no, bulk binds is only within
dml/select statements.
Raj
Rajendra dot Jamadagni
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
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)
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
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
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
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
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,
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
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
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
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
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
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
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
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
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
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
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.,
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
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
-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
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
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
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
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
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
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
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
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
Im on 8.1.7. Is it possible to do something like this? Im getting errors:
create or replace procedure myproc is
TYPE myRecord is RECORD (
field_1 number,
field_2 number);
TYPE storageArray IS TABLE OF myRecord
INDEX BY BINARY_INTEGER;
myStorageArray storageArray;
i
You should be getting errors, because PL/SQL inside execute immediate
knows nothing about mystorageArray (or i for that matter) declared
in your stored procedure.
Probably, you could get by using package variables (and referring to
them properly: package_name.var_name, specifically inside your
Title: RE: interesting dynamic pl/sql question
Ryan,
what errors are you getting?
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can
I think
' begin
mystorageArray.field_''i'' := 1;
end; ';
will not recongize mystoragearray as a variable .
-ak
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 11:49 AM
Im on 8.1.7. Is it possible to do
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
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
- --- 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
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
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
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
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
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
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
: 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
, 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
Allright I am making some changes to some pl/sql code that handles batch
inserts into the database. I am making changes to correct an error where
our clients are sending us data with invalid state information in their
address fields. A constraint prohibits the insert with records with invalid
Steve,
It may be old fashion code, but if it works within the time frame it needs
to run in, why spend time recoding? At any rate, I don't see a problem.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Wednesday, September 17, 2003 4:05 PM
To:
Title: RE: is this a good practice...pl/sql question
Steve,
Nothing wrong with setting xrec.state to null ... your developer is avoiding hard coding of NULL in the insert statement. In fact if this is working fine then only thing I'd try to change is bulk inserts instead of one by one
Steve McClure wrote:
Allright I am making some changes to some pl/sql code that handles batch
inserts into the database. I am making changes to correct an error where
our clients are sending us data with invalid state information in their
address fields. A constraint prohibits the insert
Keeping aside all considerations about the loop, I see no problem
here. The cursor variable is just short-hand notation for defining a
table%ROWTYPE - you are not modifying some hidden Oracle internal state
if this is what you are fearing. No 'mutating cursor', if I guess you
correctly.
I
Title: RE: is this a good practice...pl/sql question
Yes it is a good practice when required.
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any
Title: SQL question : How to retrieve the File_name without Directorie Path ?
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
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.
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
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?
Phillipe,
Look at using INSTR and SUBSTR to calculate the position of the last / and
work from there.
Daniel
NGUYEN Philippe (Cetelem) wrote:
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
Hi
I have a table with the following structure.
CHECK_DATE DATE
PHYRDS NUMBER(20)
PHYWRTSNUMBER(20)
PHYBLKRD
There are several ways to handle this.
If you do a search for pivot on AskTom you will get a good sampling of them.
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: SQL question - crosstab in oracle
Date: Tue, 08 Jul 2003 06
Hi Madhavan,
You are always welcome. I had to write a similiar one
some time back and I remember it was quite a task then.
Thankfully I had a head start this time !. Hope you get
the stuff sorted out. Glad to be of help ...
Regards,
Jayadas
-Original Message-
Sent: Thursday, April 03,
Hi,
Sorry for reposting.
Just wanted to put in a subject...
I have been grappling with this for sometime and thought it will be best
for others to take a look at it.
I have a table a_user_groups
USER_ID SECURITY_GROUP_ID GROUP_ID
-- - --
1005
Madhavan,
I have created a similiar table and inserted the data
as follows :-
=
CREATE TABLE UT
(
U NUMBER(4),
S NUMBER(4),
G NUMBER(4)
);
INSERT INTO UT VALUES(2005,1012,1010);
INSERT INTO UT VALUES(2005,1012,1011);
INSERT
Hi Jaydas,
Thanks for the reply.
It gives me a good starting point to go with. The query handles cases
where there are multiple rows.
For eg: U=2006 has G=1010 and S=1013. If there were another U=2010 with
same G=1010, then a rum through
the query would generate a S=1012 for this combination also
Title: RE: a DIFFERENT sql question
SELECT DISTINCT
t1.category CAT
,t2.type TYP
,SUM(DISTINCT t1.amount1) OVER(PARTITION BY t1.category) Sum1
,SUM(t2.amount2) OVER(PARTITION BY t1.category, t2.type) Sum2
FROM
t1
,t2
WHERE
t1.mykey1 = t2.mykey1
/
CAT TYP SUM1 SUM2
AA x 8 27
AA
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
B
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.
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.
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
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
C
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
B
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
: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
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
G
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
F
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
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
E
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
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
.
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
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
C
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
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
since we're having fun with SQL today - here's one that's hurting my brain
at the moment. I need to sum columns at 2 different groupings in my
resultset. The first select is perfect; the 2nd is where i have trouble;
but i know i can do stuff like the 3rd example. How do I get the 2nd one to
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
1 - 100 of 421 matches
Mail list logo
