Title: Re: Corrected SQL Question...
SQL select A.c1, B.c2
2 from (select col1 c1, rownum r from tbl order by col1) A
3 , (select col2 c2, rownum r from tbl order by col2) b
4 where a.r = b.r
5 union
6 select B.c2, A.c1
7 from (select col1 c1, rownum r from tbl order by col1) A
8
/13 Thu AM 11:19:15 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Corrected SQL Question...
Okay, let me do this right this time,... (Now that I have my hot tea going;)
Here is the test data:
SQL select c1,c2 from cp;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL
Kirti,
Can you explain the required result order? It looks random to me - or like
one of the tests we were forced to take in High School.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, March 13, 2003 9:31 AM
To: Multiple recipients of list ORACLE-L
Tom,
They wanted to 'pair up' the contents from c1 and c2.
Those are supposed to be 3 char Airport codes. DAL-AUS followed by AUS-DAL (or
vice-versa).
That's all I was told.
Thanks.
- Kirti
-Original Message-
Sent: Thursday, March 13, 2003 11:55 AM
To: Multiple recipients of list
Not quite random. Note that the value is field 1 of the first record is
the value in field 2 in the second. It looks like they want to pair up the
cities if they appear in both columns.
i.e. Since Dallas is in column 1 with Austin in Column 2 in one record,
and Dallas is in Column 2 with
Title: RE: Corrected SQL Question...
(see answer below)
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
Here is the test data:
SQL select c1,c2 from cp;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
Do mean something like this? It would be interesting to see if this could
be done with some kind of tree walk.
1* select a.c1, a.c2, b.c1, b.c2 from crap a, crap b where a.c2 = b.c1 and
b.c2 = a.c1
SQL /
C1C2C1C2
- - - -
DAL AUS AUS DAL
HOU AUS AUS HOU
Title: RE: Corrected SQL Question...
Jacques,
Thanks a bunch.
Elegance was not one of the requirements ;)
Cheers!
-
Kirti
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003
12:53 PMTo: '[EMAIL PROTECTED]'Cc: Deshpande
ok - i came up with a solution. but in real life i have a lot of amount1's
in t1 so it becomes an ugly brute force looking query. anybody have a more
elegant solution?
1 select a.category
2 , (select sum(s.amount1) from t1 s where a.category = s.category)
as amount1sum
3 ,
Questions I would have for those who wrote the requirements:
Of possible combinations of the form ABC XYZ XYZ ABC, which do they want?
As can be seen from the answers sent to the list, there is more than one set
of responses that give this pattern. If they only want half of the
possible
All they wanted was to pair up those city codes.
DAL -- AUS followed by AUS -- DAL,
AUS -- HOU followed by HOU -- AUS
etc...
and on separate lines.
So, cross-tab did not have the right format.
I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the
UNION),
: Corrected SQL Question...
All they wanted was to pair up those city codes.
DAL -- AUS followed by AUS -- DAL,
AUS -- HOU followed by HOU -- AUS
etc...
and on separate lines.
So, cross-tab did not have the right format.
I sent them Jacques Kilchoer's solution (he also sent me
Kirti,
What about solution suggested by Stephane Faroult:
select *
from (select *
from T
connect by col1 = prior col2
and col1 col2) x
where rownum = (select count(*) from T)
/
?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple
Igor Neyman wrote:
Kirti,
What about solution suggested by Stephane Faroult:
select *
from (select *
from T
connect by col1 = prior col2
and col1 col2) x
where rownum = (select count(*) from T)
/
?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
Igor,
I can
: Deshpande, Kirti [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 2:24 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Corrected SQL Question...
All they wanted was to pair up those city codes.
DAL -- AUS followed by AUS -- DAL,
AUS -- HOU followed by HOU -- AUS
etc
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
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: 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 now
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: 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-table ... do-action
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
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 cli_clients where
cli_id in (257, 396
!!
-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: Tricky SQL Question
Thinking back to university days, I think this
was called the knapsack problem, and at the
time
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
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
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
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
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 Lewis
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
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
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,
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 returned
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
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.
Guang Mei
1. select
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
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,
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
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 works.
SQL SELECT COUNT(DISTINCT ename) FROM emp
[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,
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
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:
-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 Question
Hi,
My brain is slow today Can someone help me ?
I can do
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
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 Question
-!-
-!-
-!- Hi,
-!-
-!- My brain is slow today Can someone help me ?
-!-
-!- I
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
Title: RE: Win2k/8.1.7/SQL Question
Have a look at the SQL
Referenceguide 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) [mailto
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,
Mike
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
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...
-Original
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 so
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
:
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 loads to
use the DBI instead of the procedures I hacked together
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...
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 yes
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
Sent
What do you have so far.
You can read in a script like so...
#perl dbicode.pl script.sql
while (STDIN) {
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
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 handles
the database
Title: RE: Perl DBI/SQL question - For those who use it...
Muchas Gracias Tim! bowing deeply 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 PM
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
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 for
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
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
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
Good morning list,
Environment HP-UX 11.0 Oracle 8.1.6
Can anyone help with this SQL.
I can get a result set of values from a table
that match a given list of values -
select code
from table
where code in ('A','B','C','D','E')
I can get a result set of values from a table
that do not match a
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
-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL question
Good morning list,
Environment HP-UX 11.0 Oracle 8.1.6
Can anyone help with this SQL.
I can get a result set
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
Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL question
Good morning list,
Environment HP-UX 11.0 Oracle 8.1.6
Can anyone help with this SQL.
I can get a result set of values
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
101 - 200 of 421 matches
Mail list logo
