RE: Silly SQL Question
Gabriel, How about this untested code? Alan. select usr from ( select usr, sum(decode(val,1,1,0)) look1, -- flag for 1 sum(decode(val,5,5,0)) look2, -- flag for 5 sum(decode(val,7,7,0)) look3, -- flag for 7 sum(decode(val,1,0,5,0,7,0,1)) look4 -- flag for others from the_table group by usr ) where look1 = 1 AND look2 = 1 AND look3 = 1 AND look4 = 0 / -Original Message- Sent: Thursday, November 13, 2003 2:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Gano INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-911 during DBD::Oracle prepare
Rich!
Take the ending semi-colon off. It's not SQL, but a end-of-statment flag to
Sqlplus, which you are not using.
Alan.
-Original Message-
Sent: Thursday, November 06, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L
Hey all,
I'm trying to get a simple query running in Perl 5.6.1, DBI 1.30,
DBD::Oracle 1.14, Oracle 8.1.7 on HPUX 11.0 talking to a 9.2.0.4 RAC DB on
Linux (whew!). Here's the pertinent part of the code:
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my ($dbh, $sth);
$dbh = DBI-connect(dbi:Oracle:mysid,myuser,mypass);
$sth = $dbh-prepare(q{
SELECT machine, program, SYS_CONTEXT(:userenv, :ipaddress),
SYS_CONTEXT(:userenv, :osuser)
FROM v$session VS;
});
At this point, I get an ORA-911: invalid character on the prepare. I
thought perhaps the $ was hosing me in v$session, so I tried escaping it
to v\$session (along with the underscores and parens) and using qq
instead of q, but to no avail.
I knew I shouldn't have unsub'd from the DBI mailing list...
Anyone?
TIA,
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alan Gano
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ORA-911 during DBD::Oracle prepare
DBI is able to use named binds
e.g.,
my $cursor=$$self{conn}-prepare(q{
select column_name from
dba_cons_columns
where
(owner,constraint_name) = (
select owner, constraint_name
from dba_constraints
where
owner = :table_owner AND
table_name = :table_name AND
constraint_type = 'P'
)
});
$cursor-bind_param(:table_owner,uc($owner));
$cursor-bind_param(:table_name,uc($table));
$cursor-execute();
$$self{pk_columns}=[];
my @columns;
while(my $row=$cursor-fetchrow_arrayref())
{
push @columns,$$row[0];
}
[EMAIL PROTECTED];
Alan.
-Original Message-
Sent: Thursday, November 06, 2003 1:40 PM
To: Multiple recipients of list ORACLE-L
Don't you want SELECT machine, program, SYS_CONTEXT(?, ?),
SYS_CONTEXT(?, ?)...
DBI uses positional binds as opposed to named binds.
On Thu, 2003-11-06 at 16:29, Jesse, Rich wrote:
Hey all,
I'm trying to get a simple query running in Perl 5.6.1, DBI 1.30,
DBD::Oracle 1.14, Oracle 8.1.7 on HPUX 11.0 talking to a 9.2.0.4 RAC DB on
Linux (whew!). Here's the pertinent part of the code:
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my ($dbh, $sth);
$dbh = DBI-connect(dbi:Oracle:mysid,myuser,mypass);
$sth = $dbh-prepare(q{
SELECT machine, program, SYS_CONTEXT(:userenv, :ipaddress),
SYS_CONTEXT(:userenv, :osuser)
FROM v$session VS;
});
At this point, I get an ORA-911: invalid character on the prepare. I
thought perhaps the $ was hosing me in v$session, so I tried escaping
it
to v\$session (along with the underscores and parens) and using qq
instead of q, but to no avail.
I knew I shouldn't have unsub'd from the DBI mailing list...
Anyone?
TIA,
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
Richard Quintin, DBA
Information Systems Computing, DBMS
Virginia Tech
--
If you would stand well with a great mind, leave him with a favorable
impression of yourself; if with a little mind, leave him with a
favorable impression of himself. -- Samuel Taylor Coleridge
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Quintin, Richard
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alan Gano
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: USERENV('SESSIONID') on RAC
I often do this:
select * from v$session where sid = (select distinct sid from v$mystat);
Alan.
-Original Message-
Sent: Thursday, September 25, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L
Hey all,
Is there a way to get your own executing program from a 9.2.0.4 RAC node?
USERENV('SESSIONID') and SYS_CONTEXT('USERENV','SESSIONID') each return a
big fat zero on RAC.
My ultimate goal is to get the executing session's program, and the only
place I can find that info is in V$SESSION. And the only way I know to get
the current session's row from V$SESSION is to join it with
USERENV('SESSIONID'). If there's a better/different way to do this, I'm
listening.
Thanks!
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Alan Gano
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: select distinct values
Try this (in 8i or above): select * from ( select testid, name, status, rank() over( partition by testid order by rownum ) dup_rank from testing ) where dup_rank = 1 -Original Message- Sent: Tuesday, September 23, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Hi, Could someone shed some light on this. I'm trying to formulate a query to return distinct value on a column - testid. select * from testing; TESTID NAME STATUS -- --- 1 MIKE Y 1 JOE Y 1 JIMY 2 AMY Y The output I'm expecting is TESTID NAME STATUS -- --- 1 MIKE Y 2 AMY Y The query should display the first occurence of the testid and ignore records with the same testid. thanks! elain _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Gano INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
