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
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
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
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
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,
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
A ZERO length varchar is treated as NULL
so your second query should be select count(*) from cli_clients
where trim(client_company) is null
and cli_id in (257, 396, 727);
At 12:09 PM 3/6/2003 -0800, you wrote:
Hi, I got a SQL question (9i on Red Hat), commands
shown below. The first sql returns
Why not just have Connection B trap the Unique Constrait Error and branch to some
different code? What would Connection B have done if it had found the record where
id=1?
--
Alan Davey
[EMAIL PROTECTED]
718-482-4200 x106
On 2/24/2003 2:49 PM, Rick Stephenson [EMAIL PROTECTED] wrote:
OS:
Rick - What about selecting the primary key for your table from a sequence?
Oracle will ensure each session receives a unique number.
What is your overall goal?
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, February 24,
Why not use a sequence to populate ID, and let it
fire of a before insert trigger. code example below:
create sequence TAB1_PKSEQ ;
create or replace trigger test_pkgenBEFORE
INSERT OR UPDATE OF col_id on TABLE_AFOR EACH ROWBEGINIF INSERTING
THEN SELECT TAB1_PKSEQ1.NextVal INTO :new.COL_ID
Sorry, I guess I could have been a little more clear.
Another example:
Table Employee:
Emp_id number primary key -- generated with a sequence
Emp_name varchar2(20) unique
Table Employee_log:
Emp_id number primary key
Time_stamp date primary key
Emp_stats varchar2(50)
A
Rick Stephenson wrote:
Sorry, I guess I could have been a little more clear.
Another example:
Table Employee:
Emp_id number primary key -- generated with a sequence
Emp_name varchar2(20) unique
Table Employee_log:
Emp_id number primary key
Time_stamp date primary
Try to separate the employee-lookup-and-create into separate procedure. In
the procedure, if the lookup does not find the employee, then call another
procedure with an autonomous transaction to create the employee, that way
the employee creation does not become part of the master transaction, is
Title: RE: SQL question
Just trap the error and ignore it or add some other code for that particular situation, i.e.
BEGIN
INSERT INTO A
VALUES (1);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN -- account already exists
NULL;
END;
-Original Message-
From: Rick Stephenson [mailto
Guang Mei wrote:
Hi:
I have a basic sql question about sql. I have the follwing four sqls and I
am wondering why #3 costs less than #4 in explain plan. #1 and #2 cost
the same. How is distinctand group by treated internally by Oracle? Is
#3 a better optimized sql than #4?
TIA.
Thanks all,
My question was related more to the 'design' of SQL language. To my mind the
expression COUNT(DISTINCT a,b) looked a natural extension of the syntax
COUNT(DISTINCT a). Even COUNT(DISTINCT(a,b)) would look good enough to me.
Probably it's too trivial a thing to bother about. Using the
Vladimir,
Thanks I hadn't considered || as a function, though it is.
At first, I was going to take your word for it, but then decided
this would be an interesting test. :)
But first, I agree, you must know what you're looking for, neither
of these would work in all situations.
First, I built
SELECT COUNT(*)
FROM ( SELECT DISTINCT col1, col2.
FROM ..)
--- Charu Joshi [EMAIL PROTECTED] wrote:
Thanks all,
My question was related more to the 'design' of SQL language. To my
mind the
expression COUNT(DISTINCT a,b) looked a natural extension of the
syntax
Jared
Jared Still wrote:
Though not a dramatic difference, the CONCAT was faster
and less resource intensive than the inline view with GROUP BY.
:)
Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I
What about:
select count(count(*))
from emp
group by ename, job
Have fun :)
Waleed
-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Jared
Jared Still wrote:
Though not a dramatic difference, the CONCAT was faster
and less
Jared
Windows 2k 9.2.0.1
534 hsecs
214 hsecs
Query I've used:
SELECT COUNT(
DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
|| RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1))
|| NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
)
)
: SQL question
What about:
select count(count(*))
from emp
group by ename, job
Have fun :)
Waleed
-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Jared
Jared Still wrote:
Though not a dramatic difference, the CONCAT was faster
Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:
Well, I've never claimed to be common.
And I
]
cc:
Subject:Re: SQL question
Jared
Windows 2k 9.2.0.1
534 hsecs
214 hsecs
Query I've used:
SELECT COUNT(
DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
|| RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1))
|| NVL(TO_CHAR
Jared
[EMAIL PROTECTED] wrote:
. never trust Vladimir Begun, check everything what he's saying :)
Trust?
I don't know you well enough to not trust you.
May be 'trust' is not a right word here :) Sorry.
. never use the sql that looks cool but does not work properly
. never tune a query that
Looks like you are a master of telepathy too... :)
Khedr, Waleed wrote:
What about:
select count(count(*))
from emp
group by ename, job
Have fun :)
We do... :)
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle
[EMAIL PROTECTED] wrote:
. never trust Vladimir Begun, check everything what he's saying :)
Trust?
I don't know you well enough to not trust you.
May be 'trust' is not a right word here :) Sorry.
Mine was supposed to have a :). Sorry.
. never use the sql that looks cool but does not
Charu,
The COUNT() function requires a single expression. ename, job is
not a valid expression. ename||job is a valid expression since it will
return a single value.
Another alternative would be
select count(*)
from (select distinct ename, job from emp);
Dan Fink
-Original
Title: RE: SQL question
Joshi,
SELECT count(*)
FROM (SELECT count(*)
FROM flight_legs
GROUP BY d_actual_time, event_type);
SELECT count(*)
FROM (SELECT DISTINCT d_actual_time, event_type
FROM flight_legs );
The first one took about 37 seconds in returning a count of 357331
And, can you have two columns as arguements for COUNT?
I guess its either one column or rows
+Rachna
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, January 29, 2003 1:19 PM
Hello Listers,
How to find out the COUNT of DISTINCT
Title: RE: SQL question
Elegant or not, here's how I'd do it
select count(*) from
(select distinct ename, job from emp);
-Original Message-
From: Charu Joshi [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 29, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
Subject
Title: RE: SQL question
The non-working code in your example should be
select count(*)
from (select distinct ename,job from emp)
/
It appears that cound takes only one parameter ... not two.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc
I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
elegant
way of doing it.
elegant = simple, concise, easy to understand.
Looks elegant to me.
Jared
Charu Joshi [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/29/2003 10:19 AM
Please respond to ORACLE-L
[EMAIL PROTECTED] wrote:
I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
elegant
way of doing it.
elegant = simple, concise, easy to understand.
Looks elegant to me.
Jared, it just looks that that...
CONCAT = || yet another function call, yet another piece of
code,
Try
select 'insert into XXX
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU +
1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from
app_users
where pen_id in
(44541,41402,41813);
-Original Message-From: Henrik Ekenberg
[EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]Sent:
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||(IDU + 1 )||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from
app_users
where pen_id in (44541,41402,41813) ;
IDU + 1 must be replaced by (IDU + 1).
HTH.
Nirmal.,
-Original Message-
Sent: Tuesday, January 28,
try this ((IDU + 1)):
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
(' || (IDU + 1)
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;
HEheac Hi,
HEheac My brain is slow today Can someone help me ?
HEheac I can do
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
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
Thanks for the where clause and to all who respond,
I'll check into fine grained access control
(dbms_rls).
--- Khedr, Waleed [EMAIL PROTECTED] a écrit :
Add this to where clause:
group decode(user,'typical',380,-100)
Instead of -100 use any number not used by the
groups.
Also read
:
[EMAIL PROTECTED] Subject: RE: SQL question
avoiding 2 views
Title: RE: SQL question avoiding 2 views and not in
OLS -- Oracle Label Security... I think that's the key you are looking for.
-Original Message-
From: Stephane Paquette [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
Add this to where clause:
group decode(user,'typical',380,-100)
Instead of -100 use any number not used by the groups.
Also read about contexts and grain level security.
Waleed
-Original Message-
Sent: Friday, December 13, 2002 2:59 PM
To: Multiple recipients of list ORACLE-L
Hi,
Title: RE: SQL question avoiding 2 views and not in
Hi Stephane,
This may be more effort but have you considered having a security table to join to in the one view, instead of two views? Multiple views can really hose the optimizer, as I am sure you know. However adding a table
Title: RE: SQL question avoiding 2 views and not in
dbms_rls is cheaper to use ...
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN
Title: RE: SQL question avoiding 2 views and not in
Has anyone used context and fine-grained security? I seem to remember the performance hit was not minimal when using this functionality.
-Original Message-
From: Khedr, Waleed [SMTP:[EMAIL PROTECTED]]
Sent: Friday, December 13
:
[EMAIL PROTECTED] Subject: RE: SQL question avoiding 2
views
Try this. It uses the INSTR function to determine the start and end of the
SUBSTR.
1 select substr('333.22.1.000',1,instr('333.22.1.000','.')-1) octet1,
2 substr('333.22.1.000',
3(instr('333.22.1.000','.',1,1) + 1),
4(instr('333.22.1.000','.',1,2)
Hi Johan,
Try this:
SELECT SUBSTR('127.0.0.1',1,INSTR('127.0.0.1','.')-1)
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.')+1,INSTR('127.0.0.1','.',1,2)-(INSTR('127.0.0.1','.')+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,2)+1,INSTR('127.0.0.1','.',1,3)-(INSTR('127.0.0.1','.',1,2)+1))
Hello,
Try this (take a hard look first, as I cranked this out quickly while doing
other things):
substr(ip_addr,1,instr(ip_addr,'.',1,1)-1
get 1st octet
substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,1)+1,1)-1
get 2nd octet
Anybody have a quick and dirty to parse the 4 octets of a typical IP
address
How about this...
FUNCTION f_ip_to_number (
p_ipNVARCHAR2
)
RETURN NUMBER
IS
v_ip_segment1 NUMBER
:= SUBSTR (p_ip, 1, INSTR
It is a little awkward, but a union in an inline query may do the trick:
1 select a.code
2 from (select '10' code from dual union
3select '20' code from dual union
4select '30' code from dual union
5select '40' code from dual union
6select '50' code
1700 values? I sure hope you like to type...
Could you create a text file of the table values and compare those against a
text files of the possible values? This would require O/S level privs.
-Original Message-
Sent: Monday, September 23, 2002 11:38 AM
To: Multiple recipients of list
If the set of values is not too big and fixed you could do the minus using
dual
e.g.
(select 'A'
from dual
union
select 'B'
from dual
union
...
select 'Z'
from dual)
minus
select code
from table
-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple
Dan (and Charlie),
Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.
Puzzling, huh?
steve
--- Fink, Dan [EMAIL PROTECTED] wrote:
It is a little awkward, but a union in an
inline query may do the trick:
1 select a.code
2 from (select '10' code from dual
files of the possible values? This would
require O/S level privs.
-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 11:38 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question
Dan (and Charlie),
Thanks.
Good
Title: RE: SQL question
select *
from(select 'a' from dual union select 'b' from dual union select 'c' from dual ...)
minus
select distinct code
from table
/
HTH
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
Jonathan Gennick has an excellent article in Oracle magazine (sept./oct.),
which should help.
He demonstrates two approaches: with and without pivot table.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent:
Title: RE: SQL question
Steve,
select 'select a.code ' || chr(10) || ' from(' ||
from dual
union
select distinct 'select ' || || code || || ' code from dual ' || chr(10) || 'union' ||
from my_code_table
union
select ')' || chr(10) || 'minus' from dual
/
select 'select distinct
: Any clod can have facts, but having an
opinion is an art!
-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 1:38 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question
Dan (and Charlie),
Thanks.
Good suggestions
Sorry, forgot to provide a link:
http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, September 23, 2002 2:33 PM
Jonathan Gennick has an excellent
what are the 1700 values
if the are all alphabetic and not too long you could do something like the
below though it's all getting a bit long-winded
select
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
from addresses -- any table big
Title: RE: SQL question
Maybe I think differently, I usually let server think about size or the number of clauses ...
if you have codes in a table what's wrong with ...
select distinct code
from my_code_table
minus
select distinct code
from my_data_table
/
???
Raj
Viktor wrote:
Hello All,
It looks as if I've hit a brick wall and I'd very much
appreciate if you can help.
desc Names
FIRST_INIT NOT NULL CHAR(4)
SECOND_INIT NOT NULL CHAR(1)
INIT_SEQUENCE NOT NULL NUMBER
LAST_NAMEVARCHAR2(30)
FIRST_NAME
Are you looking for something trivial like:
select n.first_name, 'E.' middle_initial,n.last_name,m.mem_init_sequence
from names n, member m
where n.first_init=m.mem_first_init and
n.second_init=m.mem_second_init
order by 1 desc, 2 asc;
On 2002.06.08 01:33 Viktor wrote:
Hello All,
Try this
select a.f1, a.d1, a.d2
from
(select field1 f1,date1 d1,max(date2) d2 from temp group by field1,date1)
a,
(select field1 f1,date1 d1,max(date2) d2 from temp group by field1,date1)
b
where a.f1 = b.f1
and a.d1 b.d1
and a.d2 b.d2
-Original Message-
Sent: Friday, May 31,
I'm sure you're already aware of this, but the
substr/instr is not as complicated as it looks since
instr takes 4 parms, the 4th of which makes cycling
through fields 1=8 easy.
hth
connor
--- Deshpande, Kirti [EMAIL PROTECTED]
wrote: Stephane,
Thanks.
Nice idea :)
I will pass on this
Hi Conner,
Yes, I agree.
But its the 'green bean' developers that I am dealing with :)
Regards,
- Kirti
PS : Your BCHR enhancer code is coming extremely handy :) Great Job, you
did !!
-Original Message-
Sent: Thursday, May 30, 2002 4:23 AM
To: Multiple recipients of list
What about
select commission_id, replace(com_text_msg,'~',chr(9))
from tab1
which would work if going to a tab separated file for something like excel.
Whats wrong with substr/instr?
Iain Nicoll
-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list
Thanks.
Substr/instr was rejected because it was a bit difficult to read the code.
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !!
- Kirti
-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple
Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email: [EMAIL PROTECTED]
Web:www.compuware.com
-Original Message-
Sent: Wednesday, May 29, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Question
What about
select
Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization.
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful
Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email: [EMAIL PROTECTED]
Web:www.compuware.com
-Original Message-
Sent: Wednesday, May 29, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Question
What about
select commission_id, replace
Dennis,
Thanks for the ideas, but...
1. Not going to happen. It's a production system already in place (Vendor
designed? But, of course!!)
2. See above.
This is what happens when someone decides to write their own reports against
tables that were not designed by themselves. Damagement
if you are going to use a shadow table, how about a trigger on the
original table that parses the field into separate columns and does an
insert into the shadow table? Update if necessary (not all that
difficult, just replace all the parsed fields in case) and delete,
depending on the types of
An oversimplification no doubt... But what about creating a snapshot
table for reporting?
It would be much less painfull then revisiting the column names every
time a report is requested.
Now, getting a spec of reporting fields can be a challenge it its own
right but... The snapshots do work
Deshpande, Kirti wrote:
Thanks.
Substr/instr was rejected because it was a bit difficult to read the code.
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !!
- Kirti
-Original Message-
Sent: Wednesday,
Would they allow you to create a view with substr/instr and then just code off
of the view?
-Original Message-
Sent: Wednesday, May 29, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L
Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for
What about creating a view and hiding 'unreadable SQL' in view definition,
and granting 'select on' view instead of table.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, May 29, 2002 4:00 PM
Another nice idea !
I will pass it on.
Looks like instr/substr can not be avoided... :(
Thanks.
- Kirti
-Original Message-
Sent: Wednesday, May 29, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L
Would they allow you to create a view with substr/instr and then just code
Stephane,
Thanks.
Nice idea :)
I will pass on this idea to them... Hope it flies..
Looks like either a function or a view around the 'ugly' code is the only
choice.
- Kirti
-Original Message-
Sent: Wednesday, May 29, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L
Stephane,
comma_to_table converts it to a pl/sql table.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod
Not sure if they would agree to snapshots, but I will suggest it anyway..
Thanks.
- Kirti
-Original Message-
Sent: Wednesday, May 29, 2002 3:26 PM
To: Multiple recipients of list ORACLE-L
An oversimplification no doubt... But what about creating a snapshot
table for reporting?
It
Kirti - My guess is that this application was not developed on Oracle
originally. My experience is that sometimes these transplanted applications
don't scale well at the enterprise level. Depending on your organization's
goals, this may be an issue to raise, whether it will support the
Kirti
I needed to do something similar but it isn't due to bad normalisation it
is to extract data in a load process so we don't have bad normalisation. I
decided to use Java and built a parser in that and I just feed it a line
and the code simply extracts that data on the selected delimiter.
Kirti,
my first thought and fwiw would be to write a PL/SQL routine.
Mike
-Original Message-
Sent: Wednesday, May 29, 2002 11:22 AM
To: Multiple recipients of list ORACLE-L
I need some help...
The database table has following structure.
commision_id number
com_text_msg
Mike,
They were looking for a SQL solution first.
Now a view (hiding substr/instr) looks like an acceptable thing :)
Thanks.
- Kirti
-Original Message-
Sent: Wednesday, May 29, 2002 9:38 PM
To: Multiple recipients of list ORACLE-L
Kirti,
my first thought and fwiw would be to
Peter,
Thanks. I am not sure if a Java solution would work.
Looks like they have settled on a view.
Nice to know how Java could help, but I am not sure if they can use it. I
will ask.
Thanks for your offer.
Regards,
- Kirti
-Original Message-
Sent: Wednesday, May 29, 2002 6:23 PM
To:
Thanx Stephane . I did the same
STAARSHIP TECHNOLOGIES
www.staarship.com
Kranti Pushkarna
Project Leader
Tel: +91-22-6931557
__
Failure to prepare is preparing to
Here's
a quick-n-dirtySQL that pivots the result set into one row. It has
its limits (you must know the number of rows that would be returned so that you
can adjust the grouping columns value01 through value12.
SELECT
g1
,MAX(DECODE(line_no,01,value,NULL)) value01
It cannot. You have to write a PL/SQL function which returns a VARCHAR for that.
- Original Message -
From: kranti pushkarna
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Mon, 29 Apr 2002 23:48:20
Hi List,
Can someone give a SQL query to
-How do I list all user accounts created in a database?
SELECT * FROM DBA_USERS
-And how do I list all user table indexes?
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'MY_LUSER'
Dave
-Original Message-
Sent: Wednesday, April 24, 2002 11:24 AM
To: Multiple recipients of list
David,
Look at DBA_USERS, DBA_TABLES, DBA_INDEXES and all other DBA_* views. All
the info you are asking about is provided in these views.
User: Select username from dba_users;
Indexes: select table_name,index_name from dba_indexes where owner not in
('SYS','SYSTEM')
Hope this helps.
Tom
David,
Basic sqlplus as the dba.
Select username from dba_users;
select owner,index_name from dba_indexes there owner not in
('SYS',SYSTEM');
Brush up on your reading skills.
ROR mô¿ôm
[EMAIL PROTECTED] 04/24/02 12:23PM
How do I list all user accounts created in a database? And how do I
list
Here's one that answers both questions in one query:
SELECT u.username, i.table_name, i.index_namd
FROM dba_users u, dba_indexes i
WHERE u.username = i.owner (+)
AND u.username not like 'SYS%'
This will show all users and IF they have a table with an index, it will
display these as well.
1 - 100 of 157 matches
Mail list logo
