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
.
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
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
--
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 table
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
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 s
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
FE
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
13, 2003 7: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
&
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]
61
>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
>-
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
>
> A
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
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.
SQ
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.
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: Tricky SQL Question -- Solved
Jonathan,
Thanks for the tips ... let me see how I incorporate this ... things to do ...
1. write a *clever* routine to look at sys.mon_mods$ with dbms_stats.flush_database_monitoring_info to decide which tables to analyze in the next session.
2
Title: RE: Tricky SQL Question
Thanks Steven,
I believe Tom touched on this in his discussion at recent Hotsos conference. My requirements were slightly different, but the logic is still good for me.
Thanks
Raj
-
Rajendra dot
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
Jonathan Lewis wrote:
>
> Very cute -
>
> But it doesn't really cope well with
> a few outlying values at the top end
> of the range. Using double the count
> to invert the high/low distribution is
> neat - but only if the distribution is
> fairly smooth to start with.
>
> Regards
>
> Jonathan
Very clever !
Can I make a couple of suggestions:
You've got a very large number of tables
in one group - and the startup time for
the analyze might have a big impact on
this group - so how about adding in (say)
one second to the analyze type in order
to cater for startup.
Also - how about taki
Raj,
I may not be offering information useful in solving your specific stats
problem. If that's the case, Undskyld. However, this information is
certainly useful.
This link to the asktom website contains a method for dividing up large
tables into ranges of rowids so that multiple sessions can eff
Title: RE: Tricky SQL Question
Stephane,
Nice ... very nice script ... it is very close to what I came up with.
Thanks everyone
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD
Very cute -
But it doesn't really cope well with
a few outlying values at the top end
of the range. Using double the count
to invert the high/low distribution is
neat - but only if the distribution is
fairly smooth to start with.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming s
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_comp
pressed here are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !!
>
> -Original Message-
> From: Jonathan Lewis [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 06, 2003 12:44 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re
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) f
Title: RE: Tricky SQL Question -- Solved
Okay,
I cracked it ... if you are interested, read on ... it is not very optimal, but close to what I want. To me 8 streams is standard, so you'd see 8 as hardcoded. Also I found that
select sum(obj_last_analyze_time)/8 from statistics_info
/
Title: RE: Tricky SQL Question
Thanks Jonathan,
I'd like to assign the tables to a group, but need to do that periodically. Also what I do is load all tables that belong to a group in a pl/sql table (bulk updates/bulk collects). That's why I don't want to do read-from-tabl
Thinking back to university days, I think this
was called the knapsack problem, and at the
time there was no algorithm guaranteed to
give an optimal solution.
If there is no simple non-procedural algorithm -
how about a strategy that simply allows each
slave to take the longest task that has not
Title: Tricky SQL Question
Hi all,
I have a tricky situation ... I have a table
columns are
owner varchar2(),
name varchar2(),
ana_tm number
ana_tm represents how much time it took to perform statistics collection for owner.name value. the number ranges from 0 to about 12000 right
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
Stephenson
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, February 25, 2003 6:49
AM
Subject: SQL question
OS: Solaris 2.8
Database: Oracle
9.2.0.2
Situation in chronological
order
Connection A: select * from table
A where id = 1; Result: no rows
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
OS: Solaris 2.8
Database: Oracle 9.2.0.2
Situation in chronological order
Connection A: select * from table A where id = 1; Result:
no rows returned -- This means I need to insert the row, as it does not
exists yet.
Connection B: select * from table A where id = 1;
Result: no rows
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
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"
>>[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
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:SQL question
Hello Listers,
How to find out the COUNT of DISTINCT values of multiple columns?
For eg.
SQL> SELECT DISTINCT ename FROM emp;
-- This work
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
Hello Listers,
How to find out the COUNT of DISTINCT values of multiple columns?
For eg.
SQL> SELECT DISTINCT ename FROM emp;
-- This works.
SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.
SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.
SQL> SELECT COUNT(DISTINCT ename
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
egards
-!-Johan
-!-
-!-
-!-
-!-> -Ursprungligt meddelande-
-!-> Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Henrik Ekenberg
-!-> <[EMAIL PROTECTED]>
-!-> Skickat: den 28 januari 2003 07:44
-!-> Till: Multiple recipients of list ORACLE-L
-!-> Amne: SQL Questio
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
2,41813) ;
That should do it!
Best Regards
Johan
> -Ursprungligt meddelande-
> Fran: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]For Henrik Ekenberg
> <[EMAIL PROTECTED]>
> Skickat: den 28 januari 2003 07:44
> Till: Multiple recipients of list ORACLE-L
> Amne: SQ
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
Hi,
My brain is slow today Can someone help me ?
I can do :
select idu+1 from user_group_members where fk_user
in(44541,41402,41813) ;
IDU+1
--
41411
41821
44546
But I can't do :
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1
Title: RE: Win2k/8.1.7/SQL Question
Have a look at the SQL
Reference guide in the 8i docs. It says that 8i is broadly compatible with
the ANSI SQL-99 Core specification, that explains why the SQL listed below works
with 8i.
Ade
-Original Message-From:
Vergara, Michael (TEM
Title: RE: Win2k/8.1.7/SQL Question
I looked at our copy of the 9i Docs, and did some hunting around on
the
web, and this code definitely looks like 9i SQL. However, the
Siebel
instance is 8.1.7.3 (or 4...I don't exactly remember). How can they
be
running this SQL?
Thanks,
Title: RE: Win2k/8.1.7/SQL Question
Well that looks like ANSI compatible SQL that should run under Oracle9i. Take a look and the 9i docs to develop a strategy for the "retrofit." This is so weird for me... having to unlearn Oracle syntax in order to write ANSI SQL. Sigh...
---
My European customers are trying to optimize some SQL that is
used in their Siebel implementation. It uses a syntax that I
am unfamiliar with. The SQL looks like:
SELECT ...
FROM
SIEBEL.S_PARTY T1
INNER JOIN SIEBEL.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID
INNER JOIN SIEBEL.S_ORG_E
in case this is a windog machine - install kind of unix shell. and the unix style
commands work perfectly - just tried it. either use unix tools for windog or cygwin
from rh.
or dump the w...
have fun.
>>> [EMAIL PROTECTED] 01/03/03 18:40 PM >>>
Hi everyone,
This may be a stupid question. If
Title: RE: Perl DBI/SQL question - For those who use it...
Muchas Gracias Tim! I can't thank you enough. I will play with it, it looks like it may meet my needs.
Lisa
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 03, 2003 1:09
Title: Perl DBI/SQL question - For those who use it...
Hi Lisa,
It's been awhile since I've used Perl DBI, but from
what you said, I think you're mixing up two different ideas. Ksh doesn't know
how to talk to a database, so you just use it to invoke sqlplus, which h
What do you have so far.
You can read in a script like so...
#perl dbicode.pl < script.sql
while () {
chomp;
$sql .= $_;
}
print $sql;
On Fri, 3 Jan 2003, Koivu, Lisa wrote:
> Hi everyone,
>
> This may be a stupid question. If so please humor me with a stupid answer.
> However:
>
> I F
Title: RE: Perl DBI/SQL question - For those who use it...
I think I just answered my own question after reading through Charlie's example code...
The errors that would be spit to the screen would be returned in $DBI::errstr.
Lisa
-Original Message-
From: Koivu, Lisa
Title: RE: Perl DBI/SQL question - For those who use it...
Jared, thanks for your reply.
One last question: Is SPOOL one of the commands that DBI does not understand? I would need to capture any errors spit out by sql*plus (like my famous ora-1410 error). I have a feeling the answer is
Try
dbish dbi:Oracle:tnsname < commands.sql
The dbish is a "DBI shell" supplied with the DBI.
The version supplied with the DBI is functional but basic.
Tom Lowery is working on an extended version with plugins
adding more functionality. One of his goals is an SQL*Plus clone...
http://sea
f list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Perl DBI/SQL question - For those who use it...
Hi everyone,
This may be a stupid question. If so please humor me with a stupid
answer. However:
I FINALLY have the fun fun fun chance to change one of my data load
Title: Perl DBI/SQL question - For those who use it...
Hi everyone,
This may be a stupid question. If so please humor me with a stupid answer. However:
I FINALLY have the fun fun fun chance to change one of my data loads to use the DBI instead of the procedures I hacked together. 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.
>
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
Hi,
We have a lot of views. Now the users have a new
requirement, only the user 'admin' can see all the
data from the views. The user 'typical' must see all
data except the one from group 380.
A basic solution is to create 2 sets of views with one
set having a group number <> 380.
I'm looking fo
<>
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)
Help!
Anybody have a quick and dirty to parse the 4 octets of a typical IP address
into 4 separate values. I will insert these into a table where database
checks may verify that the data is in fact a number and also part of a
valid ip range (the second thru fourth octets cannot be higher than 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
101 - 200 of 438 matches
Mail list logo
