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
WHER
<<>>
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 m
<<>>
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 m
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.* FROM
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')
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, r
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 :'/orac
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, 20
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
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 I
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
Andrea Oracle wrote:
>
> Hi, I got a SQL question (9i on Red Hat), commands
> shown below. The first sql returns 3 rows with value
> 1, so trim(client_company) = '', how come the 2nd sql
> doesn't return anything??
>
> SQL> select decode(trim(client_company), '', 1, ' ',
> 2, null, 3, 4) from c
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
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
li
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 da
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 proc
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_
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 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
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 "distinct"and "group by" treated internally by Oracle? Is
> #3 a better "optimized" sql than #4?
>
>>[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 bu
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 Corporation.
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
ORACLE-L <[EMAIL PROTECTED]>
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, '
> 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
cc:
Subject:RE: 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 drama
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*')
)
) A
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 reso
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 m
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
> sy
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 s
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 s
[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, y
> 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
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
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
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 DISTIN
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
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
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
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
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
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, 20
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: Tue
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.
>
by: cc:
[EMAIL PROTECTED] Subject: RE: SQL question
a
"Koivu, Lisa"
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL
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
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 re
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 then
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
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 ORA
<>
How about this...
FUNCTION f_ip_to_number (
p_ipNVARCHAR2
)
RETURN NUMBER
IS
v_ip_segment1 NUMBER
:= SUBSTR (p_ip, 1, INSTR (p_ip, '.') -
1);
v_ip_segment2 NUMBER
:= SUBSTR (
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
http://www.orafaq.com
--
Author: Johan Muller
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, Cal
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))
,SUBSTR('127.0.
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)
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
/
??
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 enough
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 excel
e to type ...
>
> Raj
>
__
> Rajendra JamadagniMIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and
> doesn't reflect that of ESPN Inc.
>
> QOTD: A
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
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
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
alues and compare those against a
> text 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 ORAC
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 fro
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 recipient
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 O
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 f
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, 2
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 ORACL
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
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:
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 write
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 varchar2(50
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 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
anticipated
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 wou
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 ca
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
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
off
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
>
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 th
"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: W
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
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 dml
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 thinks
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 co
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 t
oduct 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
s
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
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
Here's
a quick-n-dirty SQL 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
,MAX(DECODE(line_n
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 fai
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 S
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.
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
>-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 ORA
1 - 100 of 164 matches
Mail list logo