a quick pl/sql question

[Guang Mei]

Hi:

In pl/sql, I want to add chr(10) into a string in every 70th position. The
string can be up to 2000 characters long. The follwoing code works. But is
there an even FASTER way to do this?

Thanks.

Guang

---
declare
  pos   number := 1;
  len   number;
  buf   varchar2(2000);
  x varchar2(2100);
begin
  buf :=
'012345678901234567890123456789012345678901234567890123456789012345678901234
5678901234567890123456789012345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890123456789A';

  len := length(buf);
  while pos=len loop
x := x || substr(buf, pos, 70) || chr(10);
pos := pos+70;
  end loop;

end;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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: sql question

[David Hau]

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
 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')
 OR (b.award_number = s.award_number AND b.project_number=
 s.project_number AND b.task_number = s.task_number)
eric

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 12, 2004 3:04 PM



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, 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)
However, when I try to count above query as following, it hangs.  Does
someone have any ideas?
SELECT count(b.award_number) 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)
_
High-speed users-be more efficient online with the new MSN Premium
Internet

Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
 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: DENNIS WILLIAMS
 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: David Hau
 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).

sql question

[David Boyd]

Hi List,

I have following sql that runs in 1 sec:

SELECT b.* 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)
However, when I try to count above query as following, it hangs.  Does 
someone have any ideas?

SELECT count(b.award_number) 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)
_
High-speed users—be more efficient online with the new MSN Premium Internet 
Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
 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: sql question

[DENNIS WILLIAMS]

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, 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)

However, when I try to count above query as following, it hangs.  Does 
someone have any ideas?

SELECT count(b.award_number) 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)

_
High-speed users-be more efficient online with the new MSN Premium Internet 
Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Boyd
  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: DENNIS WILLIAMS
  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: sql question

[eric king]

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')
 OR (b.award_number = s.award_number AND b.project_number=
 s.project_number AND b.task_number = s.task_number)


eric


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 12, 2004 3:04 PM


 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, 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')
 OR (b.award_number = s.award_number AND b.project_number=
 s.project_number AND b.task_number = s.task_number)

 However, when I try to count above query as following, it hangs.  Does
 someone have any ideas?

 SELECT count(b.award_number) 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')
 OR (b.award_number = s.award_number AND b.project_number=
 s.project_number AND b.task_number = s.task_number)

 _
 High-speed users-be more efficient online with the new MSN Premium
Internet
 Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1

 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: David Boyd
   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: DENNIS WILLIAMS
   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: eric king
  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: sql question

[Kevin Toepke]

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 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)

However, when I try to count above query as following, it hangs.  Does 
someone have any ideas?

SELECT count(b.award_number) 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')
OR (b.award_number = s.award_number AND b.project_number=
s.project_number AND b.task_number = s.task_number)

_
High-speed users-be more efficient online with the new MSN Premium Internet 
Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Boyd
  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: Kevin Toepke
  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: sql question

[Mark Richard]

Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of 
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply 
e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail 
for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the 
official business of Transurban Infrastructure Developments Limited and CityLink 
Melbourne Limited shall be understood as neither given nor endorsed by them.


--{56703FBA-6707-4823-B54F-C1F79FFDC9D6}
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: base64

DQoNCg0KDQpIaSBEYXZpZCwNCg0KRG9lcyB0aGUgZmlyc3QgcXVlcnkgInJ1biB0byBjb21wbGV0
aW9uIiBpbiAxIHNlY29uZCwgb3IgImJlZ2lucyByZXR1cm5pbmcNCnJlc3VsdHMiIGluIDEgc2Vj
b25kPz8/DQoNCklFOiBIYXZlIHlvdSBydW4gdGhlIHF1ZXJ5IGluIFNRTCpQbHVzIGFuZCB3YWl0
ZWQgZm9yIHRoZSBwcm9tcHQgdG8gcmV0dXJuPw0KSSBzdXNwZWN0IGl0IHdpbGwgc3Bvb2wgZm9y
IGEgd2hpbGUuDQoNClNvbWUgcHJvZ3JhbXMgKGxpa2UgVE9BRCBmb3IgZXhhbXBsZSkgYXBwZWFy
IHRvIGhhdmUgZmluaXNoZWQgdGhlIHF1ZXJ5IGFzDQpzb29uIGFzIHRoZSBmaXJzdCBwYWdlIG9m
IHJlc3VsdHMgYXJlIGluLCBidXQgd2hlbiB5b3Ugc2Nyb2xsIGRvd24gaXQNCnJlcXVlc3RzIG1v
cmUgcmVjb3JkcyBmcm9tIHRoZSBkYXRhYmFzZS4gIFNlbGVjdGluZyBDT1VOVCgqKSBjYW5ub3Qg
cmV0dXJuDQp1bnRpbCB0aGUgZW50aXJlIHNlYXJjaCBpcyBjb21wbGV0ZSBiZWNhdXNlIGl0IG5l
ZWRzIHRoZSB0b3RhbCBudW1iZXIuDQoNCklmIHRoZSBmaXJzdCBxdWVyeSBnZW51aW5lbHkgcnVu
cyB0byBjb21wbGV0aW9uIGluIDEgc2Vjb25kIHRob3VnaCwgdGhlbg0KcG9zdCB0aGUgZXhwbGFp
biBwbGFucyBhcyBzdWdnZXN0ZWQgYnkgRGVubmlzLiAgSSBzdXNwZWN0IHRob3VnaCB0aGF0IHlv
dQ0KYXJlIHNpbXBseSBtaXN1bmRlcnN0YW5kaW5nIHRoZSAxIHNlY29uZCByZXN1bHQuDQoNCklN
UE9SVEFOVCBTSURFIElTU1VFOg0KQWxzbywgbG9va2luZyBhdCB5b3VyIHF1ZXJ5IEkgc3VzcGVj
dCB5b3Ugd2lsbCBnZXQgYSBjYXJ0ZXNpYW4gam9pbiBpbiB0aGUNCmZpcnN0IHBhcnQgb2YgeW91
ciB3aGVyZSBjbGF1c2UgLSBGb3IgZWFjaCByZWNvcmQgaW4gInMiIHdoZXJlIGF3YXJkX251bWJl
cg0KPSAnQUxMJyB5b3Ugd2lsbCBnZXQgZXZlcnkgcm93IGZyb20gImIiIC0gYSBtYXNzaXZlIHJl
c3VsdCBzZXQgdGhhdA0KcHJvYmFibHkgZXhwbGFpbnMgeW91ciBsb25nIHJ1bm5pbmcgcXVlcnku
ICBTb21lb25lIGVsc2UgbWlnaHQgd2FudCB0bw0KY29uZmlybSBteSBzdXNwaWNpb24/ICBUaGUg
ZXhwbGFpbiBwbGFuIG1pZ2h0IGV2ZW4gaW5kaWNhdGUgdGhhdCBhDQpjYXJ0ZXNpYW4gam9pbiBp
cyBvY2N1cmluZywgYnV0IEknbSBub3Qgc3VyZSBiZWNhdXNlIG9mIHRoZSBvdGhlciBjbGF1c2Vz
Lg0KDQpSZWdhcmRzLA0KICAgICAgTWFyay4NCg0KDQoNCg0KICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAN
CiAgICAgICAgICAgICAgICAgICAgICAiRGF2aWQgQm95ZCIgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAgICAgICAgICAgPGRhdmlkYjE1OEBo
b3RtYWkgICAgICAgIFRvOiAgICAgICBNdWx0aXBsZSByZWNpcGllbnRzIG9mIGxpc3QgT1JBQ0xF
LUwgPE9SQUNMRS1MQGZhdGNpdHkuY29tPiAgICAgICAgICAgICAgICAgIA0KICAgICAgICAgICAg
ICAgICAgICAgIGwuY29tPiAgICAgICAgICAgICAgICAgICBjYzogICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBTZW50IGJ5OiAgICAgICAgICAgICAgICAg
U3ViamVjdDogIHNxbCBxdWVzdGlvbiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAgICAgICAgICAgbWwt
ZXJyb3JzQGZhdGNpdHkgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0KICAg
ICAgICAgICAgICAgICAgICAgIC5jb20gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgIA0KICAgICAgICAgICAgICAgICAgICAgIDEzLzAxLzIwMDQgMDY6MTQgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBQbGVhc2Ug
cmVzcG9uZCB0byAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAgICAg
ICAgICAgICAgICAgICAgT1JBQ0xFLUwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAg

Re: sql question (RESEND)

[Mark Richard]

Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of 
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply 
e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail 
for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the 
official business of Transurban Infrastructure Developments Limited and CityLink 
Melbourne Limited shall be understood as neither given nor endorsed by them.


--{E9F68FD4-42A7-47CE-A4DD-84FB99CBBCFE}
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: base64

DQoNCg0KDQpTb3JyeSBpZiB0aGlzIGFycml2ZXMgdHdpY2UgLSBJIHNlbnQgaXQgc2V2ZXJhbCBo
b3VycyBhZ28gYW5kIG5ldmVyIHNhdyBpdA0KYXBwZWFyIGV2ZW4gdGhvdWdoIG1lc3NhZ2VzIEkg
cG9zdGVkIGxhdGVyIGFwcGVhcmVkIHZlcnkgZmFzdC4NCg0KDQoNCiAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgDQogICAgICAgICAgICAgICAgICAgICAgTWFyayBSaWNoYXJkICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICBUbzogICAgICBPUkFDTEUtTEBmYXRjaXR5LmNvbSAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAg
ICAgICAgICAgICAgICAxMy8wMS8yMDA0IDA4OjMwICAgICAgICAgY2M6ICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgIFN1YmplY3Q6IFJlOiBzcWwgcXVlc3Rpb24oRG9jdW1lbnQgbGluazogTWFyayBSaWNoYXJk
KSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAN
Cg0KDQoNCkhpIERhdmlkLA0KDQpEb2VzIHRoZSBmaXJzdCBxdWVyeSAicnVuIHRvIGNvbXBsZXRp
b24iIGluIDEgc2Vjb25kLCBvciAiYmVnaW5zIHJldHVybmluZw0KcmVzdWx0cyIgaW4gMSBzZWNv
bmQ/Pz8NCg0KSUU6IEhhdmUgeW91IHJ1biB0aGUgcXVlcnkgaW4gU1FMKlBsdXMgYW5kIHdhaXRl
ZCBmb3IgdGhlIHByb21wdCB0byByZXR1cm4/DQpJIHN1c3BlY3QgaXQgd2lsbCBzcG9vbCBmb3Ig
YSB3aGlsZS4NCg0KU29tZSBwcm9ncmFtcyAobGlrZSBUT0FEIGZvciBleGFtcGxlKSBhcHBlYXIg
dG8gaGF2ZSBmaW5pc2hlZCB0aGUgcXVlcnkgYXMNCnNvb24gYXMgdGhlIGZpcnN0IHBhZ2Ugb2Yg
cmVzdWx0cyBhcmUgaW4sIGJ1dCB3aGVuIHlvdSBzY3JvbGwgZG93biBpdA0KcmVxdWVzdHMgbW9y
ZSByZWNvcmRzIGZyb20gdGhlIGRhdGFiYXNlLiAgU2VsZWN0aW5nIENPVU5UKCopIGNhbm5vdCBy
ZXR1cm4NCnVudGlsIHRoZSBlbnRpcmUgc2VhcmNoIGlzIGNvbXBsZXRlIGJlY2F1c2UgaXQgbmVl
ZHMgdGhlIHRvdGFsIG51bWJlci4NCg0KSWYgdGhlIGZpcnN0IHF1ZXJ5IGdlbnVpbmVseSBydW5z
IHRvIGNvbXBsZXRpb24gaW4gMSBzZWNvbmQgdGhvdWdoLCB0aGVuDQpwb3N0IHRoZSBleHBsYWlu
IHBsYW5zIGFzIHN1Z2dlc3RlZCBieSBEZW5uaXMuICBJIHN1c3BlY3QgdGhvdWdoIHRoYXQgeW91
DQphcmUgc2ltcGx5IG1pc3VuZGVyc3RhbmRpbmcgdGhlIDEgc2Vjb25kIHJlc3VsdC4NCg0KSU1Q
T1JUQU5UIFNJREUgSVNTVUU6DQpBbHNvLCBsb29raW5nIGF0IHlvdXIgcXVlcnkgSSBzdXNwZWN0
IHlvdSB3aWxsIGdldCBhIGNhcnRlc2lhbiBqb2luIGluIHRoZQ0KZmlyc3QgcGFydCBvZiB5b3Vy
IHdoZXJlIGNsYXVzZSAtIEZvciBlYWNoIHJlY29yZCBpbiAicyIgd2hlcmUgYXdhcmRfbnVtYmVy
DQo9ICdBTEwnIHlvdSB3aWxsIGdldCBldmVyeSByb3cgZnJvbSAiYiIgLSBhIG1hc3NpdmUgcmVz
dWx0IHNldCB0aGF0DQpwcm9iYWJseSBleHBsYWlucyB5b3VyIGxvbmcgcnVubmluZyBxdWVyeS4g
IFNvbWVvbmUgZWxzZSBtaWdodCB3YW50IHRvDQpjb25maXJtIG15IHN1c3BpY2lvbj8gIFRoZSBl
eHBsYWluIHBsYW4gbWlnaHQgZXZlbiBpbmRpY2F0ZSB0aGF0IGENCmNhcnRlc2lhbiBqb2luIGlz
IG9jY3VyaW5nLCBidXQgSSdtIG5vdCBzdXJlIGJlY2F1c2Ugb2YgdGhlIG90aGVyIGNsYXVzZXMu
DQoNClJlZ2FyZHMsDQogICAgICBNYXJrLg0KDQoNCg0KDQogICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0K
ICAgICAgICAgICAgICAgICAgICAgICJEYXZpZCBCb3lkIiAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICA8ZGF2aWRiMTU4QGhv
dG1haSAgICAgICAgVG86ICAgICAgIE11bHRpcGxlIHJlY2lwaWVudHMgb2YgbGlzdCBPUkFDTEUt
TCA8T1JBQ0xFLUxAZmF0Y2l0eS5jb20+ICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAg
ICAgICAgICAgbC5jb20+ICAgICAgICAgICAgICAgICAgIGNjOiAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgIFNlbnQgYnk6ICAgICAgICAgICAgICAgICBT
dWJqZWN0OiAgc3FsIHF1ZXN0aW9uICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBtbC1l
cnJvcnNAZmF0Y2l0eSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAg

Sql question

[roland . skoldblom]

Hallo,

I have this sql query. I would like to change it  so I could pick out the three forst 
three charachters in the between statements.  How can I do that in the most simply way?

and vgr in (between 101 and 101)
  and vgr in (between 104 and 104)  order by Avdnr ,Datum1

in the statement above I woul dlike to do the changing,

select substr(Avdnr,2) Avdnr , Dato Datum1,  Dato datum2 , lpad(vgr,3) VGRP from 
kunlin A where transfil_id in
 (select transfil_id from transfil_dag where avdnr between 20 and 29 and dato 
between to_date
  ('2003-12-07','-mm-dd') and to_date ('2003-12-07','-mm-dd'))

  and vgr in (between 101 and 101)
  and vgr in (between 104 and 104)  order by Avdnr ,Datum1




Thanks in advance


Roland


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

Simple SQL Question

[Jay Wade]

Hello:

I'm trying to figure out the new 9i outer joins.
I can get a single table outer join working without any issues. But seem to 
keep getting errors when trying to do a two table outer join.  I know it is 
just something with my syntax.  Could anyone provide a quick sample, thanks 
in advance.

A:=  Base Table
B:=  Child Table 1
B:=  Child Table 2
select a.emp,
b.ValueA
c.ValueB
From base_table A outer join child_Table1 B
on A.emp=B.Emp...

I know the old way of
select a.emp,
b.ValueA
c.ValueB
From base_table A, child_Table1 B, child_Table2 C
where A.emp=B.Emp(+)
and  A.emp=C.Emp(+)
_
Grab our best dial-up Internet access offer: 6 months @$9.95/month.  
http://join.msn.com/?page=dept/dialup

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Wade
 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: Simple SQL Question

[Kevin Toepke]

select a.emp,
 b.ValueA
 c.ValueB
From base_table A
RIGHT OUTER JOIN child_Table1 B ON b.emp = a.emp
RIGHT OUTER JOIN child_Table2 C ON c.emp = a.emp

-Original Message-
Sent: Thursday, December 18, 2003 2:20 PM
To: Multiple recipients of list ORACLE-L


Hello:

I'm trying to figure out the new 9i outer joins.
I can get a single table outer join working without any issues. But seem to 
keep getting errors when trying to do a two table outer join.  I know it is 
just something with my syntax.  Could anyone provide a quick sample, thanks 
in advance.


A:=  Base Table
B:=  Child Table 1
B:=  Child Table 2

select a.emp,
 b.ValueA
 c.ValueB
From base_table A outer join child_Table1 B
on A.emp=B.Emp...


I know the old way of
select a.emp,
 b.ValueA
 c.ValueB
From base_table A, child_Table1 B, child_Table2 C
where A.emp=B.Emp(+)
and  A.emp=C.Emp(+)

_
Grab our best dial-up Internet access offer: 6 months @$9.95/month.  
http://join.msn.com/?page=dept/dialup

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Wade
  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: Kevin Toepke
  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: 8i pl/sql question

[John Flack]

Does myFunction1 often get called with the same arguments?  In your example, the 
second argument is never repeated, but in the real thing, would the same second 
argument be likely to repeat?  If myFunction1 gets the same arguments, will it always 
return the same value?  If so, then it is a deterministic function, and you can 
declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. 
 This way the optimiser will know not to recalculate the function if it is called 
again with the same arguments, but will reuse the value it calculated before.

-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L


can you return multiple values from a modified version of myfunction(1) ?? If so, then 
you can replace multiple calls by only one. And no, bulk binds is only within 
dml/select statements.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));

myFunction1 (returns a varchar2 string) here is another function in the same package 
and
it calls a bunch of other functions.

Is there a way to speed up the performance of the above lines by using bulk bind?
I am brain-dead now and can not seem to find if it can be done and/or how it can be 
done.

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  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: 8i pl/sql question

[Guang Mei]

The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase it
probably can be used at a couple of places if it works as you described.
Thanks.

Guang

-Original Message-
John Flack
Sent: Tuesday, December 16, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


Does myFunction1 often get called with the same arguments?  In your example,
the second argument is never repeated, but in the real thing, would the same
second argument be likely to repeat?  If myFunction1 gets the same
arguments, will it always return the same value?  If so, then it is a
deterministic function, and you can declare it as one by putting the keyword
DETERMINISTIC before the IS/AS in its header.  This way the optimiser will
know not to recalculate the function if it is called again with the same
arguments, but will reuse the value it calculated before.

-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L


can you return multiple values from a modified version of myfunction(1) ??
If so, then you can replace multiple calls by only one. And no, bulk binds
is only within dml/select statements.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));

myFunction1 (returns a varchar2 string) here is another function in the same
package and
it calls a bunch of other functions.

Is there a way to speed up the performance of the above lines by using bulk
bind?
I am brain-dead now and can not seem to find if it can be done and/or how it
can be done.

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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: 8i pl/sql question

[Jamadagni, Rajendra]

what does myfunction1() do?

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Tuesday, December 16, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L


The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase it
probably can be used at a couple of places if it works as you described.
Thanks.

Guang

-Original Message-
John Flack
Sent: Tuesday, December 16, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


Does myFunction1 often get called with the same arguments?  In your example,
the second argument is never repeated, but in the real thing, would the same
second argument be likely to repeat?  If myFunction1 gets the same
arguments, will it always return the same value?  If so, then it is a
deterministic function, and you can declare it as one by putting the keyword
DETERMINISTIC before the IS/AS in its header.  This way the optimiser will
know not to recalculate the function if it is called again with the same
arguments, but will reuse the value it calculated before.

-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L


can you return multiple values from a modified version of myfunction(1) ??
If so, then you can replace multiple calls by only one. And no, bulk binds
is only within dml/select statements.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));

myFunction1 (returns a varchar2 string) here is another function in the same
package and
it calls a bunch of other functions.

Is there a way to speed up the performance of the above lines by using bulk
bind?
I am brain-dead now and can not seem to find if it can be done and/or how it
can be done.

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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).


**
This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  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: 8i pl/sql question

[Guang Mei]

Below is the code for myfunction1 in the package, It calls a bunch of other
functions, such as getBlastMatches, escapeGene, genes.gene2protein and
addItem.

Guang

---

type blastMatch is record (
  geneName  gene.name%type,
  percent   number
);

function myfunction1 (seqid in number,
   sid in number,
   secondsid in number default -1) return varchar2 is
  tbl   blastMatches;
  cnt   number;
  item  varchar2(256);
  str   varchar2(256);
begin
  cnt := getBlastMatches(seqid, sid, tbl, secondsid);
  if cnt1 then return null; end if;

  for i in 1..cnt loop
item := escapeGene(sid, tbl(i).geneName,
   genes.gene2protein(tbl(i).geneName, sid)) || ' ('
  || tbl(i).percent || '%)';
if not addItem(str, item, '; ', 80) then exit; end if;
  end loop;
  return str;
exception
  when others then return null;
end myfunction1;

--
function getBlastMatches (seqid in number,
  spid in number,
  matchTable out blastMatches,
  secondspid in number default -1) return number is
  cursor bcur is
select  queryid, subjid, 100.0*identity/matchlen pct
  from  blastresults
 where  ((subjspid in (spid,secondspid) and queryid = seqid) or
(queryspid in (spid,secondspid) and subjid = seqid)) and
(identity/matchlen = .200 or positive/matchlen = .400)
 order  by blast.pvalToNumber(pval) asc, score desc;
  match number;
  cnt   number := 0;
  gname gene.name%type;

begin

  for bmatch in bcur loop
if bmatch.queryid=seqid then match := bmatch.subjid;
else match := bmatch.queryid; end if;

BEGIN
  select name into gname from gene,seqtable
where geneid=gene.id and aaseqid = match and
gene.use = 'Y' and seqtable.use='Y';
EXCEPTION
  when no_data_Found then gname := NULL;
END;

if gname is not null then
  cnt := cnt + 1;

  matchTable(cnt).geneName := gname;
  matchTable(cnt).percent := round(bmatch.pct, 0);

  if cnt = maxMatches then return cnt; end if;
end if;

  end loop;

  return cnt;
exception
  when others then return 0;
end getBlastMatches;


-Original Message-
Jamadagni, Rajendra
Sent: Tuesday, December 16, 2003 10:45 AM
To: Multiple recipients of list ORACLE-L


what does myfunction1() do?

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Tuesday, December 16, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L


The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase it
probably can be used at a couple of places if it works as you described.
Thanks.

Guang

-Original Message-
John Flack
Sent: Tuesday, December 16, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L


Does myFunction1 often get called with the same arguments?  In your example,
the second argument is never repeated, but in the real thing, would the same
second argument be likely to repeat?  If myFunction1 gets the same
arguments, will it always return the same value?  If so, then it is a
deterministic function, and you can declare it as one by putting the keyword
DETERMINISTIC before the IS/AS in its header.  This way the optimiser will
know not to recalculate the function if it is called again with the same
arguments, but will reuse the value it calculated before.

-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L


can you return multiple values from a modified version of myfunction(1) ??
If so, then you can replace multiple calls by only one. And no, bulk binds
is only within dml/select statements.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  

8i pl/sql question

[Guang Mei]

Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));

myFunction1 (returns a varchar2 string) here is another function in the same package 
and
it calls a bunch of other functions.

Is there a way to speed up the performance of the above lines by using bulk bind?
I am brain-dead now and can not seem to find if it can be done and/or how it can be 
done.

TIA.

Guang


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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: 8i pl/sql question

[Jamadagni, Rajendra]

can you return multiple values from a modified version of myfunction(1) ?? If so, then 
you can replace multiple calls by only one. And no, bulk binds is only within 
dml/select statements.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 DB.

I have a package funtion, part of it is doing something like this:

  utl_file.put_line(fpn,  myFunction1(myID, 1));
  utl_file.put_line(fpn,  myFunction1(myID, 8));
  utl_file.put_line(fpn,  myFunction1(myID, 6));
  utl_file.put_line(fpn,  myFunction1(myID, 35));
  utl_file.put_line(fpn,  myFunction1(myID, 33));
  utl_file.put_line(fpn,  myFunction1(myID, 7));
  utl_file.put_line(fpn,  myFunction1(myID, 102));
  utl_file.put_line(fpn,  myFunction1(myID, 10));
  utl_file.put_line(fpn,  myFunction1(myID, 9));
  utl_file.put_line(fpn,  myFunction1(myID, 15));
  utl_file.put_line(fpn,  myFunction1(myID, 2));

myFunction1 (returns a varchar2 string) here is another function in the same package 
and
it calls a bunch of other functions.

Is there a way to speed up the performance of the above lines by using bulk bind?
I am brain-dead now and can not seem to find if it can be done and/or how it can be 
done.

TIA.

Guang


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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).


**
This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  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: pl/sql question and owa_pattern question

[Stephane Faroult]

Guang,

   I agree with your analysis, looping on characters is not the faster you can do, 
simply because there is a significant overhead (compared to C code for instance) in a 
language such as PL/SQL - which might be perfectly acceptable in some circumstances, 
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, 
might improve performance. However, in my view the best performance gains you may get 
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which 
isn't by the way exclusive of native compiling). Using a function such as INSTR() will 
be much more efficient than looping on characters.
 I would suggest something such as :
   - First use TRANSLATE() to replace all the characters you want to get rid of by a 
single, well identified character, say # (use CHR() || ... for non printable 
characters - you can build up the string of characters to translate in the 
initialisation section of a package rather than typing it).
   - Start with initializing your string to LTRIM(string, '#')
   - Then as long as pos := INSTR(string, '#') isn't 0,
 get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos 
+ 1), '#') to string (very similar to what you were planning to do with owa).

This will be probably much faster than a character-by-character loop and calls to an 
owa package.

HTH,

Stephane Faroult

- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55

Hi:

In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as

1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these:  '-.,/*_'

Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:

---
str :=  This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
  for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1)  then
  if word is not null then
-- do some processing to variable word !
word := null;-- reset it
  end if;
else
  word := word || ch;   -- concat ch to word
end if;
  end loop;

---

I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:


str :=  This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');   
-- how can I mask out
these  '-.,/*_'  ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
  newstr := substr(newstr, pos+1);
end if;
end loop;
--

My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: pl/sql question and owa_pattern question

[Guang Mei]

Hi Stephane:

Thanks for your good suggestion. I compared the method you suggested and the orginal 
one and it indeed boosted the performance (in my simple test). However the ONLY 
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new 
method (you suggested) correctly extract the words (and sent for processing), But 
after processing I need to put processed-words back to the orginal string with orginal 
demilters un-changed. I tried to track to position of delimit from the orginal string 
by doing

global_pos := global_pos + pos ;

in my while loop, but  ltrim(substr(string, pos + 1), '#')  will make global_pos 
wrong when ltrim trims '#'. Any work-around?

TIA.

Guang

-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L


Guang,

   I agree with your analysis, looping on characters is not the faster you can do, 
simply because there is a significant overhead (compared to C code for instance) in a 
language such as PL/SQL - which might be perfectly acceptable in some circumstances, 
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, 
might improve performance. However, in my view the best performance gains you may get 
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which 
isn't by the way exclusive of native compiling). Using a function such as INSTR() will 
be much more efficient than looping on characters.
 I would suggest something such as :
   - First use TRANSLATE() to replace all the characters you want to get rid of by a 
single, well identified character, say # (use CHR() || ... for non printable 
characters - you can build up the string of characters to translate in the 
initialisation section of a package rather than typing it).
   - Start with initializing your string to LTRIM(string, '#')
   - Then as long as pos := INSTR(string, '#') isn't 0,
 get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos 
+ 1), '#') to string (very similar to what you were planning to do with owa).

This will be probably much faster than a character-by-character loop and calls to an 
owa package.

HTH,

Stephane Faroult

- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55

Hi:

In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as

1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these:  '-.,/*_'

Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:

---
str :=  This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
  for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1)  then
  if word is not null then
-- do some processing to variable word !
word := null;-- reset it
  end if;
else
  word := word || ch;   -- concat ch to word
end if;
  end loop;

---

I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:


str :=  This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');   
-- how can I mask out
these  '-.,/*_'  ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
  newstr := substr(newstr, pos+1);
end if;
end loop;
--

My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: Guang Mei
  

RE: pl/sql question and owa_pattern question

[Guang Mei]

Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.

BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.

Guang

-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L


I don't know about PL/SQL but here is how I would get separate words from a
big string:

#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}

There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and  all sorts of string
manipulation.

On 2003.11.20 22:39, Guang Mei wrote:
 Hi:

 In my pl/sql program, I want to process each word in a string. The
 string is selected from a varchar2(300) column. The delimit that separates
 the words is not necessary space character. The definition of the delimit
 in this program is set as

 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
 and
 2. the character is not one of these:  '-.,/*_'

 Now my program is basically checking each character, find the delimit, and
 rebuild each word. After that I process each word. The code looks like
 this:

 ---
 str :=  This will be a long string with length upto 300 characters, it
 may contain some invisible characters';
 len := length(str)+1;
   for i in 1..len loop
 ch := substr(str,i,1);
 if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1)  then
   if word is not null then
 -- do some processing to variable word !
 word := null;-- reset it
   end if;
 else
   word := word || ch;   -- concat ch to word
 end if;
   end loop;

 ---

 I think It's taking too long because it loops through each characters. I
 hope I could find a way to speed it up. I don't have experiience in
 owa_pattern, but I thought there might be a way to do it here:

 
 str :=  This will be a long string with length upto 300 characters, it
 may contain some invisible characters';
 newstr := str;
 pos := 1;
 while pos != 0 loop
 pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
 these  '-.,/*_'  ???
 word := substr(newstr, 1, pos-1);
 -- do some processing to variable word !
 if pos != 0 then
   newstr := substr(newstr, pos+1);
 end if;
 end loop;
 --

 My simple tests showed that owa_pattern call is much slower than direct
 string manupilation. But I would like to try it in this case if I could
 easily get the wrods from the string. Any suggestions?

 TIA.

 Guang


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Guang Mei
   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).


--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
  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: Guang Mei
  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 

RE: pl/sql question and owa_pattern question

[Jamadagni, Rajendra]

Guang,

Well you are almost there ...  you need fifo structure  namely a pl/sql array

1. create a local pl/sql array to store the delimiter (store the ascii value of the 
delimiter to be safe) my_array (varchar2(5))
2. as you find a delimiter insert into the first position in the array and replace the 
delimiting character with #
3. lather.rinse.repeat.

when it is time to put it back
use a loop

nIndex := 0;
nPos   := 0;
loop
  npos := instr(my_str,'#',1);
  exit when npos := 0;
  nIndex := nindex + 1;
  my_str := substr(my_str,1,nPos-1) || chr(my_array(nIndex)) || sybstr(my_str, nPos+1);
end loop;


something like this should help, proof-read though ...

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Friday, November 21, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L


Hi Stephane:

Thanks for your good suggestion. I compared the method you suggested and the orginal 
one and it indeed boosted the performance (in my simple test). However the ONLY 
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new 
method (you suggested) correctly extract the words (and sent for processing), But 
after processing I need to put processed-words back to the orginal string with orginal 
demilters un-changed. I tried to track to position of delimit from the orginal string 
by doing

global_pos := global_pos + pos ;

in my while loop, but  ltrim(substr(string, pos + 1), '#')  will make global_pos 
wrong when ltrim trims '#'. Any work-around?

TIA.

Guang

-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L


Guang,

   I agree with your analysis, looping on characters is not the faster you can do, 
simply because there is a significant overhead (compared to C code for instance) in a 
language such as PL/SQL - which might be perfectly acceptable in some circumstances, 
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, 
might improve performance. However, in my view the best performance gains you may get 
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which 
isn't by the way exclusive of native compiling). Using a function such as INSTR() will 
be much more efficient than looping on characters.
 I would suggest something such as :
   - First use TRANSLATE() to replace all the characters you want to get rid of by a 
single, well identified character, say # (use CHR() || ... for non printable 
characters - you can build up the string of characters to translate in the 
initialisation section of a package rather than typing it).
   - Start with initializing your string to LTRIM(string, '#')
   - Then as long as pos := INSTR(string, '#') isn't 0,
 get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos 
+ 1), '#') to string (very similar to what you were planning to do with owa).

This will be probably much faster than a character-by-character loop and calls to an 
owa package.

HTH,

Stephane Faroult

- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55

Hi:

In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as

1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these:  '-.,/*_'

Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:

---
str :=  This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
  for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1)  then
  if word is not null then
-- do some processing to variable word !
word := null;-- reset it
  end if;
else
  word := word || ch;   -- concat ch to word
end if;
  end loop;

---

I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:


str :=  This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');   
-- how can I mask out
these  '-.,/*_'  ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 

Re: pl/sql question and owa_pattern question

[Mladen Gogala]

PL/SQL is the fastest thing of them all when it comes to executing 
SQL commands, but there are things which simply aren't practical 
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal 
tradeoff between ease of use and performance. If performance is a 
paramount, as in real time application processing, then you'll have to 
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the 
idea of enabling oracle to use PCRE but I gave up when I read that 10g 
will have that included.

On 11/21/2003 11:59:31 AM, Guang Mei wrote:
 Perl is a good tool for text processing. But our program is already written
 in pl/sql long time ago and there are intensive db calls in this pl/sql
 program. (text processing is only part of it). So I can not change that.
 
 BTW I did a comparison study a while ago for some of our pl/sql packages
 (specifically for our application). When there are lots of db calls (select,
 insert, update and delete), pl/sql package is faster than correponding perl
 program (I made sure sqls are prepared once and used bind variables in perl.
 All code were executed on the unix server, no other programs were running,
 etc). That's why we stick to pl/sql because our app need the performance.
 Others may have different results, it all depends on what the code does.
 
 Guang
 
 -Original Message-
 Mladen Gogala
 Sent: Thursday, November 20, 2003 11:14 PM
 To: Multiple recipients of list ORACLE-L
 
 
 I don't know about PL/SQL but here is how I would get separate words from a
 big string:
 
 #!/usr/bin/perl -w
 use strict;
 my (@ARR);
 while () {
 chomp;
 @ARR = split(/[^0-9a-zA-Z_\.,]/);
 foreach (@ARR) {
 print $_\n;
 }
 }
 
 There is something called DBI and it can be used to insert separated words
 into the database, instead
 of printing them. The bottom line is that perl is an excellent tool for
 parsing strings and  all sorts of string
 manipulation.
 
 On 2003.11.20 22:39, Guang Mei wrote:
  Hi:
 
  In my pl/sql program, I want to process each word in a string. The
  string is selected from a varchar2(300) column. The delimit that separates
  the words is not necessary space character. The definition of the delimit
  in this program is set as
 
  1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
  and
  2. the character is not one of these:  '-.,/*_'
 
  Now my program is basically checking each character, find the delimit, and
  rebuild each word. After that I process each word. The code looks like
  this:
 
  ---
  str :=  This will be a long string with length upto 300 characters, it
  may contain some invisible characters';
  len := length(str)+1;
for i in 1..len loop
  ch := substr(str,i,1);
  if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1)  then
if word is not null then
  -- do some processing to variable word !
  word := null;-- reset it
end if;
  else
word := word || ch;   -- concat ch to word
  end if;
end loop;
 
  ---
 
  I think It's taking too long because it loops through each characters. I
  hope I could find a way to speed it up. I don't have experiience in
  owa_pattern, but I thought there might be a way to do it here:
 
  
  str :=  This will be a long string with length upto 300 characters, it
  may contain some invisible characters';
  newstr := str;
  pos := 1;
  while pos != 0 loop
  pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
  these  '-.,/*_'  ???
  word := substr(newstr, 1, pos-1);
  -- do some processing to variable word !
  if pos != 0 then
newstr := substr(newstr, pos+1);
  end if;
  end loop;
  --
 
  My simple tests showed that owa_pattern call is much slower than direct
  string manupilation. But I would like to try it in this case if I could
  easily get the wrods from the string. Any suggestions?
 
  TIA.
 
  Guang
 
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Guang Mei
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 

Re: pl/sql question and owa_pattern question

[Daniel Hanks]

Would extproc_perl fit well enough, though, until 10g is here?

On Fri, 21 Nov 2003, Mladen Gogala wrote:

 PL/SQL is the fastest thing of them all when it comes to executing 
 SQL commands, but there are things which simply aren't practical 
 in 9.2 PL/SQL. Regular expression processing is one of those things.
 Fortunately, you can mix the two. Without DBI, perl scripts simply
 woudn't be very useful. Of course, there are things that are faster
 then even the fastest perl script. Lexer written in C is one of them
 and you don't need much work to write one, either, but using OCI is
 not easy. OCI is a library written to confuse the enemy, not to help
 developer. Using plain and simple regex or PCRE within a C program
 is the same thing as above, but slightly more complicated then a lexer.
 For the specific task of manipulating patterns and resolving regular
 expressions, I use perl almost exclusively because I find it an optimal 
 tradeoff between ease of use and performance. If performance is a 
 paramount, as in real time application processing, then you'll have to 
 resort to C and, possibly, write an external procedure and, thus,
 enabling oracle to use C regex calls or even pcre. I was toying with the 
 idea of enabling oracle to use PCRE but I gave up when I read that 10g 
 will have that included.
 
 On 11/21/2003 11:59:31 AM, Guang Mei wrote:
  Perl is a good tool for text processing. But our program is already written
  in pl/sql long time ago and there are intensive db calls in this pl/sql
  program. (text processing is only part of it). So I can not change that.
  
  BTW I did a comparison study a while ago for some of our pl/sql packages
  (specifically for our application). When there are lots of db calls (select,
  insert, update and delete), pl/sql package is faster than correponding perl
  program (I made sure sqls are prepared once and used bind variables in perl.
  All code were executed on the unix server, no other programs were running,
  etc). That's why we stick to pl/sql because our app need the performance.
  Others may have different results, it all depends on what the code does.
  
  Guang
  
  -Original Message-
  Mladen Gogala
  Sent: Thursday, November 20, 2003 11:14 PM
  To: Multiple recipients of list ORACLE-L
  
  
  I don't know about PL/SQL but here is how I would get separate words from a
  big string:
  
  #!/usr/bin/perl -w
  use strict;
  my (@ARR);
  while () {
  chomp;
  @ARR = split(/[^0-9a-zA-Z_\.,]/);
  foreach (@ARR) {
  print $_\n;
  }
  }
  
  There is something called DBI and it can be used to insert separated words
  into the database, instead
  of printing them. The bottom line is that perl is an excellent tool for
  parsing strings and  all sorts of string
  manipulation.
  
  On 2003.11.20 22:39, Guang Mei wrote:
   Hi:
  
   In my pl/sql program, I want to process each word in a string. The
   string is selected from a varchar2(300) column. The delimit that separates
   the words is not necessary space character. The definition of the delimit
   in this program is set as
  
   1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
   and
   2. the character is not one of these:  '-.,/*_'
  
   Now my program is basically checking each character, find the delimit, and
   rebuild each word. After that I process each word. The code looks like
   this:
  
   ---
   str :=  This will be a long string with length upto 300 characters, it
   may contain some invisible characters';
   len := length(str)+1;
 for i in 1..len loop
   ch := substr(str,i,1);
   if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1)  then
 if word is not null then
   -- do some processing to variable word !
   word := null;-- reset it
 end if;
   else
 word := word || ch;   -- concat ch to word
   end if;
 end loop;
  
   ---
  
   I think It's taking too long because it loops through each characters. I
   hope I could find a way to speed it up. I don't have experiience in
   owa_pattern, but I thought there might be a way to do it here:
  
   
   str :=  This will be a long string with length upto 300 characters, it
   may contain some invisible characters';
   newstr := str;
   pos := 1;
   while pos != 0 loop
   pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
   these  '-.,/*_'  ???
   word := substr(newstr, 1, pos-1);
   -- do some processing to variable word !
   if pos != 0 then
 newstr := substr(newstr, pos+1);
   end if;
   end loop;
   --
  
   My simple tests showed that owa_pattern call is much slower than direct
   string manupilation. But I would like to try it in this case if I could
   easily get the wrods from the string. Any suggestions?
  
   TIA.
  
   Guang
  
  
   --
   Please see the official ORACLE-L FAQ: http://www.orafaq.net
   --
   Author: Guang Mei
 INET: [EMAIL PROTECTED]
  
   Fat City 

pl/sql question and owa_pattern question

[Guang Mei]

Hi:

In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as

1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these:  '-.,/*_'

Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:

---
str :=  This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
  for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1)  then
  if word is not null then
-- do some processing to variable word !
word := null;-- reset it
  end if;
else
  word := word || ch;   -- concat ch to word
end if;
  end loop;

---

I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:


str :=  This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these  '-.,/*_'  ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
  newstr := substr(newstr, pos+1);
end if;
end loop;
--

My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?

TIA.

Guang


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  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: pl/sql question and owa_pattern question

[Mladen Gogala]

I don't know about PL/SQL but here is how I would get separate words from a big string:

#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}

There is something called DBI and it can be used to insert separated words into the 
database, instead
of printing them. The bottom line is that perl is an excellent tool for parsing 
strings and  all sorts of string
manipulation.

On 2003.11.20 22:39, Guang Mei wrote:
 Hi:
 
 In my pl/sql program, I want to process each word in a string. The
 string is selected from a varchar2(300) column. The delimit that separates
 the words is not necessary space character. The definition of the delimit
 in this program is set as
 
 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
 and
 2. the character is not one of these:  '-.,/*_'
 
 Now my program is basically checking each character, find the delimit, and
 rebuild each word. After that I process each word. The code looks like
 this:
 
 ---
 str :=  This will be a long string with length upto 300 characters, it
 may contain some invisible characters';
 len := length(str)+1;
   for i in 1..len loop
 ch := substr(str,i,1);
 if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1)  then
   if word is not null then
 -- do some processing to variable word !
 word := null;-- reset it
   end if;
 else
   word := word || ch;   -- concat ch to word
 end if;
   end loop;
 
 ---
 
 I think It's taking too long because it loops through each characters. I
 hope I could find a way to speed it up. I don't have experiience in
 owa_pattern, but I thought there might be a way to do it here:
 
 
 str :=  This will be a long string with length upto 300 characters, it
 may contain some invisible characters';
 newstr := str;
 pos := 1;
 while pos != 0 loop
 pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
 these  '-.,/*_'  ???
 word := substr(newstr, 1, pos-1);
 -- do some processing to variable word !
 if pos != 0 then
   newstr := substr(newstr, pos+1);
 end if;
 end loop;
 --
 
 My simple tests showed that owa_pattern call is much slower than direct
 string manupilation. But I would like to try it in this case if I could
 easily get the wrods from the string. Any suggestions?
 
 TIA.
 
 Guang
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Guang Mei
   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).
 

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  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: Silly SQL Question

[Vladimir Begun]

Jacques,

I checked your example, I think there are some issues here:

1. Original queries provided below do use merge join.

2. We could have missing indexes which can exist on real system.

3. Timings below is not a criteria -- after gathering statistics and
creation an index on val this both queries take about 1,3 seconds. So
it means on your system you checked the *speed of sort operation* only --
because, most probably, merge was used. Even w/o index but with hash
join it works much more faster -- 11.87 vs 1.25 (figures are not precise).
4. It'a all for nothing -- life is cruel and real-life examples are
much more complex :)
If you do not mind I would not continue this discussion.

Thank you.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:

Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a 
user-written PL/SQL function because I wanted to compare timings for both. I created a 
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) 
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the 
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.

With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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: Silly SQL Question

[Bellow, Bambi]

Jacques --

Huh?  I thought the question was, give me all the usrs where there exists a
record containing a 1,5, and 7.  How can that return one record, when there
are two users -- and only two users -- who have the 1,5,7 combination?  The
data provided shows that both GAP and GPA have a 1, 5 and 7 and that no
other users do.  My query provides that answer.  If that wasn't the
question, then that won't be the answer; but if it *was* the question, then
the query is correct.  

HTH,
Bambi.
=

create table gab (usr char(3),val number);
insert into gab values ('GAP',1);
insert into gab values ('GAP',5);
insert into gab values ('GAP',5);
insert into gab values ('GAP',7);
insert into gab values ('PAG',1);
insert into gab values ('PAG',7);
insert into gab values ('PAG',2);
insert into gab values ('JKL',1);
insert into gab values ('JKL',5);
insert into gab values ('JKL',5);
insert into gab values ('GPA',1);
insert into gab values ('GPA',5);
insert into gab values ('GPA',7);
insert into gab values ('GPA',8);

 select usr from gab
 where val=1
 intersect
 select usr from gab
 where val=5
 intersect
 select usr from gab
 where val=7;

USR
---
GAP
GPA



-Original Message-
Sent: Friday, November 14, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L


 -Original Message-
 Bellow, Bambi
 
 Why not do it like this...
 
 select usr from gab
 where val=1
 intersect
 select usr from gab 
 where val=5
 intersect
 select usr from gab
 where val=7;


Because that way you would get the wrong answer. With the sample data as
kindly provided by Mr. Begun the correct query would return one row, but
your query returns two rows.
SQL select * from gab ;

USR  VAL
-- -
GAP1
GAP5
GAP5
GAP7
PAG1
PAG7
PAG2
JKL1
JKL5
JKL5
GPA1
GPA5
GPA7
GPA8

14 ligne(s) sélectionnée(s).

SQL select usr from gab
  2  where val=1
  3  intersect
  4  select usr from gab 
  5  where val=5
  6  intersect
  7  select usr from gab
  8  where val=7;

USR
--
GAP
GPA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  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: Bellow, Bambi
  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: Silly SQL Question

[Jacques Kilchoer]

The original question was show me the users who have ALL the values in the list but 
NOT MORE than the values in the list.

 -Original Message-
 Bellow, Bambi
 
 Huh?  I thought the question was, give me all the usrs where 
 there exists a
 record containing a 1,5, and 7.  How can that return one 
 record, when there
 are two users -- and only two users -- who have the 1,5,7 
 combination?  The
 data provided shows that both GAP and GPA have a 1, 5 and 7 
 and that no
 other users do.  My query provides that answer.  If that wasn't the
 question, then that won't be the answer; but if it *was* the 
 question, then
 the query is correct.  
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  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: Silly SQL Question

[Michael Milligan]

Just a guess:

select distinct 
  usr 
from 
  xxx
where 
 (select 
count(*) 
  from 
xxx
  group by Usr) 
  = 
 (select 
count(*) 
from 
  xxx
group by Usr, val)

-Original Message-
Sent: Thursday, November 13, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L


Gabriel Aragon wrote:
 
 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
 

select usr
from XXX
where val in (list)
group by usr
having count(*) = number of values in list

does it but assumes that (usr, val) is unique (which can be easily
worked-around :
select usr
from (select distinct usr, val
  from XXX)
group by ... )

and also that you know both the list and the number of items in the
list, which looks reasonable.
If your intent is to build the queries and the list dynamically, I'd
rather suggest storing the list into a temporary table.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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).


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Milligan
  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: Silly SQL Question

[Daniel Fink]

Using Bambi's table and values. Try this query, it's ugly but it works (kind of like 
the contributor...)

Daniel


  1  select o.usr, count(o.usr)
  2  from (select distinct usr, val
  3from gab
  4where val in (1,5,7)
  5  and usr not in (select usr
  6  from gab
  7  where val not in (1,5,7))) o
  8  group by o.usr
  9* having count(o.usr) = 3



Jacques Kilchoer wrote:

 The original question was show me the users who have ALL the values in the list but 
 NOT MORE than the values in the list.

  -Original Message-
  Bellow, Bambi
 
  Huh?  I thought the question was, give me all the usrs where
  there exists a
  record containing a 1,5, and 7.  How can that return one
  record, when there
  are two users -- and only two users -- who have the 1,5,7
  combination?  The
  data provided shows that both GAP and GPA have a 1, 5 and 7
  and that no
  other users do.  My query provides that answer.  If that wasn't the
  question, then that won't be the answer; but if it *was* the
  question, then
  the query is correct.
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Jacques Kilchoer
   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: Daniel Fink
  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: Silly SQL Question

[Alan Gano]

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: Silly SQL Question

[Gabriel Aragon]

Ok, guys I have to apologize twice, 

First: the delay to answer your very kind emails, (you
know the urgent problems dont let you time for the
important ones) 

Second: maybe my question was not clear enough,
ciertanly what Bambi says is what I need give me all
the usrs where there exists a record containing 1 AND
5 AND 7, the criteria was the list, not the records,
so it does not matter if the user has many more
records, but if he/she has those records that are in
the list, that is what I want, the solution is as
simple as Bambi's query.

I really really appreciate all the solutions provided
for you guys, I swear I tested every one.

Below I have a copy of my original email.

Thank you very much!
Gabriel Aragon

+++
I have a table 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.

Gabriel
+++


--- Bellow, Bambi [EMAIL PROTECTED] wrote:
 Jacques --
 
 Huh?  I thought the question was, give me all the
 usrs where there exists a
 record containing a 1,5, and 7.  How can that
 return one record, when there
 are two users -- and only two users -- who have the
 1,5,7 combination?  The
 data provided shows that both GAP and GPA have a 1,
 5 and 7 and that no
 other users do.  My query provides that answer.  If
 that wasn't the
 question, then that won't be the answer; but if it
 *was* the question, then
 the query is correct.  
 
 HTH,
 Bambi.
 =
 
 create table gab (usr char(3),val number);
 insert into gab values ('GAP',1);
 insert into gab values ('GAP',5);
 insert into gab values ('GAP',5);
 insert into gab values ('GAP',7);
 insert into gab values ('PAG',1);
 insert into gab values ('PAG',7);
 insert into gab values ('PAG',2);
 insert into gab values ('JKL',1);
 insert into gab values ('JKL',5);
 insert into gab values ('JKL',5);
 insert into gab values ('GPA',1);
 insert into gab values ('GPA',5);
 insert into gab values ('GPA',7);
 insert into gab values ('GPA',8);
 
  select usr from gab
  where val=1
  intersect
  select usr from gab
  where val=5
  intersect
  select usr from gab
  where val=7;
 
 USR
 ---
 GAP
 GPA
 
 
 
 -Original Message-
 Sent: Friday, November 14, 2003 7:24 PM
 To: Multiple recipients of list ORACLE-L
 
 
  -Original Message-
  Bellow, Bambi
  
  Why not do it like this...
  
  select usr from gab
  where val=1
  intersect
  select usr from gab 
  where val=5
  intersect
  select usr from gab
  where val=7;
 
 
 Because that way you would get the wrong answer.
 With the sample data as
 kindly provided by Mr. Begun the correct query would
 return one row, but
 your query returns two rows.
 SQL select * from gab ;
 
 USR  VAL
 -- -
 GAP1
 GAP5
 GAP5
 GAP7
 PAG1
 PAG7
 PAG2
 JKL1
 JKL5
 JKL5
 GPA1
 GPA5
 GPA7
 GPA8
 
 14 ligne(s) sélectionnée(s).
 
 SQL select usr from gab
   2  where val=1
   3  intersect
   4  select usr from gab 
   5  where val=5
   6  intersect
   7  select usr from gab
   8  where val=7;
 
 USR
 --
 GAP
 GPA
 -- 



=
Any dream worth having is a dream worth fighting for(Cualquier sueño que valga la 
pena tener, es un sueño por el que vale la pena luchar)Charles Xavier

__
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).

Re: Silly SQL Question

[Vladimir Begun]

Jacques Kilchoer wrote:
 Mr. Begun: I'm not convinced that your answer is quite the right one.
I've provided two solutions but I'm still confused :). Jacques, does
that mean that I understand English and the original query was Ok? :)
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Gabriel Aragon wrote:
Ok, guys I have to apologize twice, 
...

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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: Silly SQL Question

[Jacques Kilchoer]

Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a 
user-written PL/SQL function because I wanted to compare timings for both. I created a 
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) 
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the 
performance is the same. So there!

In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, 
so all this was just an intellectual exercise.

With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)

If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, 
the times were slightly longer but still the same: 14.71 vs. 14.32

VB query:
SELECT usr
   FROM (
 SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
   FROM gab
)
  WHERE val IN (SELECT DISTINCT element FROM (
   SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
 FROM (
  SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
   , INSTR(:list, ',', 1, ROWNUM) c
FROM gab
   WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
  )
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
  GROUP BY
usr
  , cnt
HAVING COUNT(*) = 4 ;


JRK query:
select a.usr
 from
  (select distinct
  b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
  ) a
 where
   val in (select *
 from
   the (select
   cast (str_to_tbl (:num_list) as my_number_table)
 from dual
)
)
   and cnt = 4
 group by
   usr, cnt
 having
   count(*) = cnt ;


Test data creation:
drop table gab;
create table gab
   (usr varchar2(10) not null, val number not null) ;
declare
   insert_cnt constant pls_integer := 20 ;
   commit_cnt constant pls_integer := 2000 ;
   i pls_integer ;
   j pls_integer ;
   k pls_integer ;
   l pls_integer ;
   n pls_integer ;
   usr gab.usr%type ;

   type usrt is table of gab.usr%type index by binary_integer ;
   usra usrt ;
   type valt is table of gab.val%type index by binary_integer ;
   vala valt ;

begin
   dbms_random.initialize (dbms_utility.get_time) ;
   i := 1 ;
   while i = insert_cnt
   loop
  usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
 || chr (ascii ('A') + mod (abs (dbms_random.random), 26))
 || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
  n := mod (abs (dbms_random.random), 5) + 1 ;
  j := mod (i - 1, commit_cnt) + 1 ;
  k := least (commit_cnt, j + n - 1) ;
  for l in j..k
  loop
 usra (l) := usr ;
 vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
  end loop ;
  i := i + k - j + 1 ;
  if k = commit_cnt or i = insert_cnt
  then
 forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
 commit ;
  end if ;
   end loop ;
   commit ;
end ;
/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  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: Silly SQL Question

[Vladimir Begun]

Jacques,

you can use my first name -- Mr. is too official for this list :). You have modified 
the query,
however I would suggest you to check execution plan (and present it here) and remove
LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) by replacing it using number of 
elements in the
list i.e., in your case, 4. As I already said, it was just an example, in real life I 
would think
is it Ok or not Ok to use it.
Timing is not everything you can check, consider statisticts. Did you consider 
indexing val?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.


Jacques Kilchoer wrote:
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a 
user-written PL/SQL function because I wanted to compare timings for both. I created a 
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) 
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the 
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.

With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32

VB query:
SELECT usr
   FROM (
 SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
   FROM gab
)
  WHERE val IN (SELECT DISTINCT element FROM (
   SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
 FROM (
  SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
   , INSTR(:list, ',', 1, ROWNUM) c
FROM gab
   WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
  )
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
  GROUP BY
usr
  , cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
 from
  (select distinct
  b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
  ) a
 where
   val in (select *
 from
   the (select
   cast (str_to_tbl (:num_list) as my_number_table)
 from dual
)
)
   and cnt = 4
 group by
   usr, cnt
 having
   count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
   (usr varchar2(10) not null, val number not null) ;
declare
   insert_cnt constant pls_integer := 20 ;
   commit_cnt constant pls_integer := 2000 ;
   i pls_integer ;
   j pls_integer ;
   k pls_integer ;
   l pls_integer ;
   n pls_integer ;
   usr gab.usr%type ;
   type usrt is table of gab.usr%type index by binary_integer ;
   usra usrt ;
   type valt is table of gab.val%type index by binary_integer ;
   vala valt ;
begin
   dbms_random.initialize (dbms_utility.get_time) ;
   i := 1 ;
   while i = insert_cnt
   loop
  usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
 || chr (ascii ('A') + mod (abs (dbms_random.random), 26))
 || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
  n := mod (abs (dbms_random.random), 5) + 1 ;
  j := mod (i - 1, commit_cnt) + 1 ;
  k := least (commit_cnt, j + n - 1) ;
  for l in j..k
  loop
 usra (l) := usr ;
 vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
  end loop ;
  i := i + k - j + 1 ;
  if k = commit_cnt or i = insert_cnt
  then
 forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
 commit ;
  end if ;
   end loop ;
   commit ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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: Silly SQL Question

[Bellow, Bambi]

Why not do it like this...

select usr from gab
where val=1
intersect
select usr from gab 
where val=5
intersect
select usr from gab
where val=7;

-Original Message-
Sent: Thursday, November 13, 2003 7:35 PM
To: Multiple recipients of list ORACLE-L


Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
   FROM (
SELECT DISTINCT usr, val FROM gab
)
  WHERE val IN (1, 5, 7)
  GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for
usr.

Inspired by Tom Kyte's answer
varying elements in IN list
http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P
8_DISPLAYID,F4950_P8_CRITERIA:110612348061,

I propose this solution, using a str_to_tbl function (see function
definition after the proof of concept.)
SQL select * from gab ;
USR  VAL
-- -
GAP1
GAP5
GAP7
GAP9
JKL8
JKL5
XXX1
XXX5
8 ligne(s) sélectionnée(s).

SQL variable num_list varchar2 (4000)
SQL select b.usr
  2   from
  3 (select distinct a.usr, a.val from gab a) b,
  4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
  5   where
  6 b.usr = d.usr and
  7 b.val in (select *
  8from
  9  the (select
 10  cast (str_to_tbl (:num_list) as
my_number_table)
 11   from dual
 12  )
 13  )
 14   group by b.usr, d.num_usr_val
 15   having
 16 count(*) = d.num_usr_val
 17 and count (*) = (select count (*)
 18   from
 19 the (select
 20 cast (str_to_tbl (:num_list) as
my_number_table)
 21  from dual
 22 )
 23 )
 24  

SQL execute :num_list := '1,5'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
XXX

SQL execute :num_list := ' 8 , 5 '
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
JKL

SQL execute :num_list := '1,5,7'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée

SQL execute :num_list := '1,5,7,8'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée

SQL execute :num_list := '1,5,7,9'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
GAP

SQL execute :num_list := '1,5,7,8,9'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée 

script:
drop table gab;
create table gab
   (usr varchar2(10) not null, val number not null) ;
insert into gab (usr, val) values ('GAP', 1) ;
insert into gab (usr, val) values ('GAP', 5) ;
insert into gab (usr, val) values ('GAP', 7) ;
insert into gab (usr, val) values ('GAP', 9) ;
insert into gab (usr, val) values ('JKL', 8) ;
insert into gab (usr, val) values ('JKL', 5) ;
insert into gab (usr, val) values ('XXX', 1) ;
insert into gab (usr, val) values ('XXX', 5) ;
commit ;
create or replace type my_number_table as table of number ;
/
create or replace function str_to_tbl (p_str in varchar2)
 return my_number_table
as
   l_str  varchar2 (32760) default p_str || ',' ;
   l_nnumber ;
   l_pos  pls_integer default 1 ;
   l_data my_number_table := my_number_table () ;
begin
   loop
  l_n := instr (l_str, ',', l_pos) ;
  exit when (nvl (l_n, 0) = 0) ;
  l_data.extend ;
  l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n -
l_pos))) ;
  l_pos := l_n + 1 ;
   end loop;
   return l_data ;
end;
/
variable num_list varchar2 (4000)
select b.usr
 from
   (select distinct a.usr, a.val from gab a) b,
   (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
 where
   b.usr = d.usr and
   b.val in (select *
  from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
 from dual
)
)
 group by b.usr, d.num_usr_val
 having
   count(*) = d.num_usr_val
   and count (*) = (select count (*)
 from
   the (select
   cast (str_to_tbl (:num_list) as
my_number_table)
from dual
   )
   )

execute :num_list := '1,5'
/
execute :num_list := ' 8 , 5 '
/
execute :num_list := '1,5,7'
/
execute :num_list := '1,5,7,8'
/
execute :num_list := '1,5,7,9'
/
execute :num_list := '1,5,7,8,9'
/

 -Original Message-
 Vladimir Begun
 
 DROP TABLE gab;
 CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT 
 NULL --, CONSTRAINT gab$uq UNIQUE (usr, val)
 );
 INSERT INTO gab VALUES('GAP', 1);
 INSERT INTO gab VALUES('GAP', 5);
 INSERT INTO gab 

RE: Silly SQL Question

[Jacques Kilchoer]

Yes, your query was much better. I keep on forgetting about those analytic functions. 
Shame on me.
I still think using a PL/SQL function to be able to easily change the IN list is worth 
the time and trouble. Plus it makes the explain plan is more interesting with the 
str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH)

 -Original Message-
 Vladimir Begun

 ...
 
 PL/SQL is not needed to solve this task as SQL task. There
 reason when it would be wise to rewrite it is out of scope
 of this topic (but the reason is obvious).
 
 I'm just thinking that the query proposed by you is a bit
 expensive. So, I've re-scribbled mine:
 
 SELECT usr
FROM (
 SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER 
 (PARTITION BY usr) cnt
   FROM gab
 )
   WHERE val IN (1, 5, 7)
 AND cnt = 3
   GROUP BY
 usr
   , cnt
 HAVING COUNT(*) = cnt
 /
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  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: Silly SQL Question

[Jacques Kilchoer]

 -Original Message-
 Bellow, Bambi
 
 Why not do it like this...
 
 select usr from gab
 where val=1
 intersect
 select usr from gab 
 where val=5
 intersect
 select usr from gab
 where val=7;


Because that way you would get the wrong answer. With the sample data as kindly 
provided by Mr. Begun the correct query would return one row, but your query returns 
two rows.
SQL select * from gab ;

USR  VAL
-- -
GAP1
GAP5
GAP5
GAP7
PAG1
PAG7
PAG2
JKL1
JKL5
JKL5
GPA1
GPA5
GPA7
GPA8

14 ligne(s) sélectionnée(s).

SQL select usr from gab
  2  where val=1
  3  intersect
  4  select usr from gab 
  5  where val=5
  6  intersect
  7  select usr from gab
  8  where val=7;

USR
--
GAP
GPA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  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: Silly SQL Question

[Vladimir Begun]

Jacques

Jacques Kilchoer wrote:
I still think using a PL/SQL function to be able to easily change the
 IN list is worth the time and trouble.

If the given list is created properly, which I think it's a must in
this case, one would not need to use PL/SQL, the task can be solved
in SQL only. Below is just *an example*, not a generic solution.
VAR list VARCHAR2(30);
-- number could be counted as well, not a big deal
EXEC :list := '1,7,5,';
WITH numbers AS (
  SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
 SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
  , INSTR(:list, ',', 1, ROWNUM) c
   FROM gab
  WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
 )
)
SELECT usr
  FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
  FROM gab
   )
 WHERE val IN (SELECT DISTINCT element FROM numbers)
   AND cnt = (SELECT COUNT(DISTINCT element) FROM numbers) -- it's for nothing, 
because count can be give by caller
 GROUP BY
   usr
 , cnt
HAVING COUNT(*) = (SELECT COUNT(DISTINCT element) FROM numbers) -- same
/
Again, it's not a generic solution but it's Ok to use it for this
particular task -- the number of elements is limited anyway. One
could add yet one condition to avoid troubles with TO_NUMBER conversion,
it's easy but I'm leaving it as is.
 Plus it makes the explain plan is more interesting with the str_to_tbl
 function, you get to see the COLLECTION ITERATOR (PICKLER FETCH)
That's obviously nice :) but I think it's not a reason to use PL/SQL to
solve this task.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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).

Silly SQL Question

[Gabriel Aragon]

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).

Re: Silly SQL Question

[Stephane Faroult]

Gabriel Aragon wrote:
 
 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
 

select usr
from XXX
where val in (list)
group by usr
having count(*) = number of values in list

does it but assumes that (usr, val) is unique (which can be easily
worked-around :
select usr
from (select distinct usr, val
  from XXX)
group by ... )

and also that you know both the list and the number of items in the
list, which looks reasonable.
If your intent is to build the queries and the list dynamically, I'd
rather suggest storing the list into a temporary table.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: Silly SQL Question

[Aponte, Tony]

Here's one solution.

SELECT 
 usr  
FROM 
 xxx
GROUP BY
 usr 
HAVING 
 SUM(DECODE(val,1,1,0))  0
AND
 SUM(DECODE(val,5,1,0))  0
AND
 SUM(DECODE(val,7,1,0))  0

HTH
Tony Aponte

-Original Message-
Sent: Thursday, November 13, 2003 5: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: Aponte, Tony
  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: Silly SQL Question

[Aponte, Tony]

Sorry I missed one.  Try this.

SELECT 
 usr  
FROM 
  bogus
GROUP BY
 usr 
HAVING 
 SUM(DECODE(val,1,1,0))  0
AND
 SUM(DECODE(val,5,1,0))  0
AND
 SUM(DECODE(val,7,1,0))  0
AND
 SUM(DECODE(val,1,0,5,0,7,0,val)) = 0

Tony Aponte

-Original Message-
Sent: Thursday, November 13, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L


Here's one solution.

SELECT 
 usr  
FROM 
 xxx
GROUP BY
 usr 
HAVING 
 SUM(DECODE(val,1,1,0))  0
AND
 SUM(DECODE(val,5,1,0))  0
AND
 SUM(DECODE(val,7,1,0))  0

HTH
Tony Aponte

-Original Message-
Sent: Thursday, November 13, 2003 5: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: Aponte, Tony
  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: Aponte, Tony
  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: Silly SQL Question

[Vladimir Begun]

Gabriel

DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL --, CONSTRAINT gab$uq 
UNIQUE (usr, val)
);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('JKL', 8);
INSERT INTO gab VALUES('JKL', 5);
COMMIT;
SELECT usr
  FROM (
   SELECT DISTINCT usr, val FROM gab
   )
 WHERE val IN (1, 5, 7)
 GROUP BY
   usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
Depending on the existence of the constraint, here gab$uq, you can
either use inline view of run it against original table.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Gabriel Aragon wrote:
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


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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: Silly SQL Question

[Jacques Kilchoer]

Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
   FROM (
SELECT DISTINCT usr, val FROM gab
)
  WHERE val IN (1, 5, 7)
  GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.

Inspired by Tom Kyte's answer
varying elements in IN list
http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061,

I propose this solution, using a str_to_tbl function (see function definition after 
the proof of concept.)
SQL select * from gab ;
USR  VAL
-- -
GAP1
GAP5
GAP7
GAP9
JKL8
JKL5
XXX1
XXX5
8 ligne(s) sélectionnée(s).

SQL variable num_list varchar2 (4000)
SQL select b.usr
  2   from
  3 (select distinct a.usr, a.val from gab a) b,
  4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
  5   where
  6 b.usr = d.usr and
  7 b.val in (select *
  8from
  9  the (select
 10  cast (str_to_tbl (:num_list) as my_number_table)
 11   from dual
 12  )
 13  )
 14   group by b.usr, d.num_usr_val
 15   having
 16 count(*) = d.num_usr_val
 17 and count (*) = (select count (*)
 18   from
 19 the (select
 20 cast (str_to_tbl (:num_list) as my_number_table)
 21  from dual
 22 )
 23 )
 24  

SQL execute :num_list := '1,5'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
XXX

SQL execute :num_list := ' 8 , 5 '
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
JKL

SQL execute :num_list := '1,5,7'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée

SQL execute :num_list := '1,5,7,8'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée

SQL execute :num_list := '1,5,7,9'
Procédure PL/SQL terminée avec succès.
SQL /
USR
--
GAP

SQL execute :num_list := '1,5,7,8,9'
Procédure PL/SQL terminée avec succès.
SQL /
aucune ligne sélectionnée 

script:
drop table gab;
create table gab
   (usr varchar2(10) not null, val number not null) ;
insert into gab (usr, val) values ('GAP', 1) ;
insert into gab (usr, val) values ('GAP', 5) ;
insert into gab (usr, val) values ('GAP', 7) ;
insert into gab (usr, val) values ('GAP', 9) ;
insert into gab (usr, val) values ('JKL', 8) ;
insert into gab (usr, val) values ('JKL', 5) ;
insert into gab (usr, val) values ('XXX', 1) ;
insert into gab (usr, val) values ('XXX', 5) ;
commit ;
create or replace type my_number_table as table of number ;
/
create or replace function str_to_tbl (p_str in varchar2)
 return my_number_table
as
   l_str  varchar2 (32760) default p_str || ',' ;
   l_nnumber ;
   l_pos  pls_integer default 1 ;
   l_data my_number_table := my_number_table () ;
begin
   loop
  l_n := instr (l_str, ',', l_pos) ;
  exit when (nvl (l_n, 0) = 0) ;
  l_data.extend ;
  l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n - l_pos))) ;
  l_pos := l_n + 1 ;
   end loop;
   return l_data ;
end;
/
variable num_list varchar2 (4000)
select b.usr
 from
   (select distinct a.usr, a.val from gab a) b,
   (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
 where
   b.usr = d.usr and
   b.val in (select *
  from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
 from dual
)
)
 group by b.usr, d.num_usr_val
 having
   count(*) = d.num_usr_val
   and count (*) = (select count (*)
 from
   the (select
   cast (str_to_tbl (:num_list) as my_number_table)
from dual
   )
   )

execute :num_list := '1,5'
/
execute :num_list := ' 8 , 5 '
/
execute :num_list := '1,5,7'
/
execute :num_list := '1,5,7,8'
/
execute :num_list := '1,5,7,9'
/
execute :num_list := '1,5,7,8,9'
/

 -Original Message-
 Vladimir Begun
 
 DROP TABLE gab;
 CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT 
 NULL --, CONSTRAINT gab$uq UNIQUE (usr, val)
 );
 INSERT INTO gab VALUES('GAP', 1);
 INSERT INTO gab VALUES('GAP', 5);
 INSERT INTO gab VALUES('GAP', 7);
 INSERT INTO gab VALUES('JKL', 8);
 INSERT INTO gab VALUES('JKL', 5);
 COMMIT;
 
 SELECT usr
FROM (
 SELECT DISTINCT usr, val FROM gab
 )
   WHERE val IN (1, 5, 7)
   GROUP BY
 usr
 HAVING COUNT(*) = 3 -- number of 

Re: Silly SQL Question

[Vladimir Begun]

Jacques

Yes, probably, you are right. I've overlooked example section,
given by Gabriel.
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('PAG', 1);
INSERT INTO gab VALUES('PAG', 7);
INSERT INTO gab VALUES('PAG', 2);
INSERT INTO gab VALUES('JKL', 1);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('GPA', 1);
INSERT INTO gab VALUES('GPA', 5);
INSERT INTO gab VALUES('GPA', 7);
INSERT INTO gab VALUES('GPA', 8);
COMMIT;
PL/SQL is not needed to solve this task as SQL task. There
reason when it would be wise to rewrite it is out of scope
of this topic (but the reason is obvious).
I'm just thinking that the query proposed by you is a bit
expensive. So, I've re-scribbled mine:
SELECT usr
  FROM (
   SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
 FROM gab
   )
 WHERE val IN (1, 5, 7)
   AND cnt = 3
 GROUP BY
   usr
 , cnt
HAVING COUNT(*) = cnt
/
HTH,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:

Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried
INSERT INTO gab VALUES ('GAP', 9) ;
and then this query
SELECT usr
   FROM (
SELECT DISTINCT usr, val FROM gab
)
  WHERE val IN (1, 5, 7)
  GROUP BY
usr
HAVING COUNT(*) = 3 -- number of elements in the list
/
returned the value 'GAP' even though 'GAP' has 4 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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).

interesting dynamic pl/sql question

[rgaffuri]

Im on 8.1.7. Is it possible to do something like this? Im getting errors:

create or replace procedure myproc is
   TYPE myRecord is RECORD (
 field_1 number,
 field_2 number);
  TYPE storageArray IS TABLE OF myRecord
INDEX BY BINARY_INTEGER;
myStorageArray storageArray;
   i number;
begin
 i := 1;
execute immediate 
   ' begin
  mystorageArray.field_''i'' := 1;
  end; ';
end;
/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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: interesting dynamic pl/sql question

[Igor Neyman]

You should be getting errors, because PL/SQL inside execute immediate
knows nothing about mystorageArray (or i for that matter) declared
in your stored procedure.
Probably, you could get by using package variables (and referring to
them properly: package_name.var_name, specifically inside your dynamic
sql).

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 1:49 PM
To: Multiple recipients of list ORACLE-L

Im on 8.1.7. Is it possible to do something like this? Im getting
errors:

create or replace procedure myproc is
   TYPE myRecord is RECORD (
 field_1 number,
 field_2 number);
  TYPE storageArray IS TABLE OF myRecord
INDEX BY BINARY_INTEGER;
myStorageArray storageArray;
   i number;
begin
 i := 1;
execute immediate 
   ' begin
  mystorageArray.field_''i'' := 1;
  end; ';
end;
/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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: Igor Neyman
  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: interesting dynamic pl/sql question

[Jamadagni, Rajendra]

Title: RE: interesting dynamic pl/sql question





Ryan,


what errors are you getting?


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Igor Neyman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 09, 2003 4:14 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: interesting dynamic pl/sql question



You should be getting errors, because PL/SQL inside execute immediate
knows nothing about mystorageArray (or i for that matter) declared
in your stored procedure.
Probably, you could get by using package variables (and referring to
them properly: package_name.var_name, specifically inside your dynamic
sql).


Igor Neyman, OCP DBA
[EMAIL PROTECTED]




-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 1:49 PM
To: Multiple recipients of list ORACLE-L


Im on 8.1.7. Is it possible to do something like this? Im getting
errors:


create or replace procedure myproc is
 TYPE myRecord is RECORD (
 field_1 number,
 field_2 number);
 TYPE storageArray IS TABLE OF myRecord
 INDEX BY BINARY_INTEGER;
 myStorageArray storageArray;
 i number;
begin
i := 1;
execute immediate 
 ' begin
 mystorageArray.field_''i'' := 1;
 end; ';
end;
/


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
 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: Igor Neyman
 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).


**This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**5

Re: interesting dynamic pl/sql question

[AK]

I think 
  ' begin
   mystorageArray.field_''i'' := 1;
   end; ';

will not recongize mystoragearray as a variable . 

-ak


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 11:49 AM


 Im on 8.1.7. Is it possible to do something like this? Im getting errors:
 
 create or replace procedure myproc is
TYPE myRecord is RECORD (
  field_1 number,
  field_2 number);
   TYPE storageArray IS TABLE OF myRecord
 INDEX BY BINARY_INTEGER;
 myStorageArray storageArray;
i number;
 begin
  i := 1;
 execute immediate 
' begin
   mystorageArray.field_''i'' := 1;
   end; ';
 end;
 /
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: [EMAIL PROTECTED]
   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: AK
  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).

interesting sql question

[rgaffuri]

Im taking a database theory class(no I dont need help with my homework). There is an 
interesting query in the book that I have never seen posed before. The solution would 
be hideously slow if there was even a moderate amount of data in the tables. How would 
you write it? 

Given 3 tables: and columns in the tables:

TABLE: Person
Primary Key: SID
COLUMN: NAME

TABLE: BIDS
Primary Key: BID
Foreign Key: SID
FOREIGN KEYT: BOAT_ID
Column: Date

Boat:
Primary Key: BOAT_ID
Column: Color

Find any person who has reserved all the boats. The 

I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery 
there is a minus and a correlated 'where' clause.'. That query wouldnt move.

How would you solve this? 

Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 
'ALL' or 'ANY' So you can say:

Find all people who are older than any person with blue eyes. Or find all the people 
who are older than 'ALL' the people with blue eyes.

Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle 
guy so he doesnt know.  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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: interesting sql question

[Mercadante, Thomas F]

Ralph,

Assuming that there is no history in the BIDS table (meaning that there are
no old records indicating a bid recorded last year), I think the following
would work just fine.


select name
from person, 
(select distinct sid, count(*) bid_count
  from bids
  group by sid) bids
where person.sid = bids.sid
and bid_count = 3


Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Monday, September 29, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L


Im taking a database theory class(no I dont need help with my homework).
There is an interesting query in the book that I have never seen posed
before. The solution would be hideously slow if there was even a moderate
amount of data in the tables. How would you write it? 

Given 3 tables: and columns in the tables:

TABLE: Person
Primary Key: SID
COLUMN: NAME

TABLE: BIDS
Primary Key: BID
Foreign Key: SID
FOREIGN KEYT: BOAT_ID
Column: Date

Boat:
Primary Key: BOAT_ID
Column: Color

Find any person who has reserved all the boats. The 

I dont have the solution with me, but there is a 'NOT EXISTS', then in the
subquery there is a minus and a correlated 'where' clause.'. That query
wouldnt move.

How would you solve this? 

Also, according to the 'SQL Standard', SQL is supposed to support op codes
such as 'ALL' or 'ANY' So you can say:

Find all people who are older than any person with blue eyes. Or find all
the people who are older than 'ALL' the people with blue eyes.

Just to reiterate. Not looking for help with my homework. My professor isnt
an Oracle guy so he doesnt know.  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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: Mercadante, Thomas F
  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: interesting sql question

[Stephane Faroult]

- --- Original Message --- -
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Mon, 29 Sep 2003 05:19:39

Im taking a database theory class(no I dont need
help with my homework). There is an interesting
query in the book that I have never seen posed
before. The solution would be hideously slow if
there was even a moderate amount of data in the
tables. How would you write it? 

Given 3 tables: and columns in the tables:

TABLE: Person
Primary Key: SID
COLUMN: NAME

TABLE: BIDS
Primary Key: BID
Foreign Key: SID
FOREIGN KEYT: BOAT_ID
Column: Date

Boat:
Primary Key: BOAT_ID
Column: Color

Find any person who has reserved all the boats. The


I dont have the solution with me, but there is a
'NOT EXISTS', then in the subquery there is a minus
and a correlated 'where' clause.'. That query
wouldnt move.

How would you solve this? 

Also, according to the 'SQL Standard', SQL is
supposed to support op codes such as 'ALL' or 'ANY'
So you can say:

Find all people who are older than any person with
blue eyes. Or find all the people who are older
than 'ALL' the people with blue eyes.

Just to reiterate. Not looking for help with my
homework. My professor isnt an Oracle guy so he
doesnt know.  


I would run an uncorrelated subquery on BOATS to count how many of them we have (mot 
likely to be a multimillion row table, and it's just a PK scan), which you can feed 
into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and 
supposing (which is often the case) that your FK is indexed it doesn't require 
anything but another index scan. Which can of course take *some* time if BIDS is 
really big but I don't see how to escape a group by here (or anything worse).

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: interesting sql question

[Khedr, Waleed]

select pn.name 
from (select /*+ no_merge */ count(*) boat_cnt from  boat) bt,  bid  bd,
person pn
where bd.sid = pn.sid 
group by pn.name, boat_cnt
having count(bd.boat_id) = boat_cnt


Waleed

-Original Message-
Sent: Monday, September 29, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L


Im taking a database theory class(no I dont need help with my homework).
There is an interesting query in the book that I have never seen posed
before. The solution would be hideously slow if there was even a moderate
amount of data in the tables. How would you write it? 

Given 3 tables: and columns in the tables:

TABLE: Person
Primary Key: SID
COLUMN: NAME

TABLE: BIDS
Primary Key: BID
Foreign Key: SID
FOREIGN KEYT: BOAT_ID
Column: Date

Boat:
Primary Key: BOAT_ID
Column: Color

Find any person who has reserved all the boats. The 

I dont have the solution with me, but there is a 'NOT EXISTS', then in the
subquery there is a minus and a correlated 'where' clause.'. That query
wouldnt move.

How would you solve this? 

Also, according to the 'SQL Standard', SQL is supposed to support op codes
such as 'ALL' or 'ANY' So you can say:

Find all people who are older than any person with blue eyes. Or find all
the people who are older than 'ALL' the people with blue eyes.

Just to reiterate. Not looking for help with my homework. My professor isnt
an Oracle guy so he doesnt know.  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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: Khedr, Waleed
  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: RE: interesting sql question

[rgaffuri]

 
 From: Stephane Faroult [EMAIL PROTECTED]
 Date: 2003/09/29 Mon AM 09:59:39 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: interesting sql question
 
 
 
 - --- Original Message --- -
 From: [EMAIL PROTECTED]
 To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 Sent: Mon, 29 Sep 2003 05:19:39
 
 Im taking a database theory class(no I dont need
 help with my homework). There is an interesting
 query in the book that I have never seen posed
 before. The solution would be hideously slow if
 there was even a moderate amount of data in the
 tables. How would you write it? 
 
 Given 3 tables: and columns in the tables:
 
 TABLE: Person
 Primary Key: SID
 COLUMN: NAME
 
 TABLE: BIDS
 Primary Key: BID
 Foreign Key: SID
 FOREIGN KEYT: BOAT_ID
 Column: Date
 
 Boat:
 Primary Key: BOAT_ID
 Column: Color
 
 Find any person who has reserved all the boats. The
 
 
 I dont have the solution with me, but there is a
 'NOT EXISTS', then in the subquery there is a minus
 and a correlated 'where' clause.'. That query
 wouldnt move.
 
 How would you solve this? 
 
 Also, according to the 'SQL Standard', SQL is
 supposed to support op codes such as 'ALL' or 'ANY'
 So you can say:
 
 Find all people who are older than any person with
 blue eyes. Or find all the people who are older
 than 'ALL' the people with blue eyes.
 
 Just to reiterate. Not looking for help with my
 homework. My professor isnt an Oracle guy so he
 doesnt know.  
 
 
 I would run an uncorrelated subquery on BOATS to count how many of them we have (mot 
 likely to be a multimillion row table, and it's just a PK scan), which you can feed 
 into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and 
 supposing (which is often the case) that your FK is indexed it doesn't require 
 anything but another index scan. Which can of course take *some* time if BIDS is 
 really big but I don't see how to escape a group by here (or anything worse).

Bitmap scan would be the fastest. Ive noticed that counts on those are incredibly 
fast. So your saying something like:

how would you write the query? I dont quite see it. 
 
 Regards,
 
 Stephane Faroult
 Oriole
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Stephane Faroult
   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: [EMAIL PROTECTED]
  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: RE: interesting sql question

[Jamadagni, Rajendra]

Title: RE: RE: interesting sql question





Here is an attempt ...


select p.*
from persons p
where sid in 
 (select sid, count(bid)
 from bids
 group by sid
 having count(sid) = (select count(boad_id) from boats))
/


You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query.


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

RE: interesting sql question

[Chelur, Jayadas {PBSG}]

This would eliminate duplicate bids on the same boat by the same person

SELECT  p.*
FROMPERSON p,
(
SELECT  COUNT(*) boat_count
FROMBOAT
) c,
(
SELECT  sid, COUNT(DISTINCT boat_id) bid_count 
FROMBIDS 
GROUP   BY sid
) b
WHERE   p.sid   = b.sid
AND b.bid_count = c.boat_count;


-Original Message-
Sent: Monday, September 29, 2003 9:20 AM
To: Multiple recipients of list ORACLE-L


Im taking a database theory class(no I dont need help with my homework).
There is an interesting query in the book that I have never seen posed
before. The solution would be hideously slow if there was even a moderate
amount of data in the tables. How would you write it? 

Given 3 tables: and columns in the tables:

TABLE: Person
Primary Key: SID
COLUMN: NAME

TABLE: BIDS
Primary Key: BID
Foreign Key: SID
FOREIGN KEYT: BOAT_ID
Column: Date

Boat:
Primary Key: BOAT_ID
Column: Color

Find any person who has reserved all the boats. The 

I dont have the solution with me, but there is a 'NOT EXISTS', then in the
subquery there is a minus and a correlated 'where' clause.'. That query
wouldnt move.

How would you solve this? 

Also, according to the 'SQL Standard', SQL is supposed to support op codes
such as 'ALL' or 'ANY' So you can say:

Find all people who are older than any person with blue eyes. Or find all
the people who are older than 'ALL' the people with blue eyes.

Just to reiterate. Not looking for help with my homework. My professor isnt
an Oracle guy so he doesnt know.  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  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: Chelur, Jayadas {PBSG}
  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: RE: interesting sql question

[rgaffuri]

a user may request the same boat more than once. not sure that work. 
 
 From: Jamadagni, Rajendra [EMAIL PROTECTED]
 Date: 2003/09/29 Mon AM 10:34:53 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: interesting sql question
 
 Here is an attempt ...
 
 select p.*
 from persons p
 where sid in 
  (select sid, count(bid)
 from bids
group by sid
   having count(sid) = (select count(boad_id) from boats))
 /
 
 You wanted to find all persons who have booked all boats ... add criteria
 for booked in the first sub-query.
 
 Raj
 
 
 Rajendra dot Jamadagni at nospamespn dot com
 All Views expressed in this email are strictly personal.
 QOTD: Any clod can have facts, having an opinion is an art !
 
 
Title: RE: RE: interesting sql question





Here is an attempt ...


select p.*
from persons p
where sid in 
 (select sid, count(bid)
 from bids
 group by sid
 having count(sid) = (select count(boad_id) from boats))
/


You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query.


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

RE: RE: interesting sql question

[Jamadagni, Rajendra]

Title: RE: RE: interesting sql question





Hey ... the question wasn't complete ... 


give us the full statement of the question ...
g
Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 29, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: RE: interesting sql question



a user may request the same boat more than once. not sure that work. 
 
 From: Jamadagni, Rajendra [EMAIL PROTECTED]
 Date: 2003/09/29 Mon AM 10:34:53 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: interesting sql question
 
 Here is an attempt ...
 
 select p.*
 from persons p
 where sid in 
 (select sid, count(bid)
 from bids
 group by sid
 having count(sid) = (select count(boad_id) from boats))
 /
 
 You wanted to find all persons who have booked all boats ... add criteria
 for booked in the first sub-query.
 
 Raj
 
 
 Rajendra dot Jamadagni at nospamespn dot com
 All Views expressed in this email are strictly personal.
 QOTD: Any clod can have facts, having an opinion is an art !
 
 



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

Re: RE: interesting sql question

[rgaffuri]

you could do this, but i would have concerns over the indexing strategy. 


select name
from person, 
(select distinct sid, count(*) bid_count
  from bids
  group by sid
  HAVING count(*) = (SELECT COUNT(BOAT_ID FROM BOATS)) bids
where person.sid = bids.sid;



Now yours bids table is an intersect table and would have the most records of all 
three tables. I would create an extra field that never gets update and just put a 
default value in it. Then I would put a bitmap index on it. since they aer VERY faster 
on counts. 

my problem is with the group by. SID could be huge. That could lead to a massive slow 
down and alot of LIOs dont think there is a faster a solution though. No 
correlated sub-queries which are LIO intensive. 
 
 From: Mercadante, Thomas F [EMAIL PROTECTED]
 Date: 2003/09/29 Mon AM 09:34:38 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: interesting sql question
 
 Ralph,
 
 Assuming that there is no history in the BIDS table (meaning that there are
 no old records indicating a bid recorded last year), I think the following
 would work just fine.
 
 
 select name
 from person, 
 (select distinct sid, count(*) bid_count
   from bids
   group by sid) bids
 where person.sid = bids.sid
 and bid_count = 3
 
 
 Tom Mercadante
 Oracle Certified Professional
 
 
 -Original Message-
 Sent: Monday, September 29, 2003 9:20 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Im taking a database theory class(no I dont need help with my homework).
 There is an interesting query in the book that I have never seen posed
 before. The solution would be hideously slow if there was even a moderate
 amount of data in the tables. How would you write it? 
 
 Given 3 tables: and columns in the tables:
 
 TABLE: Person
 Primary Key: SID
 COLUMN: NAME
 
 TABLE: BIDS
 Primary Key: BID
 Foreign Key: SID
 FOREIGN KEYT: BOAT_ID
 Column: Date
 
 Boat:
 Primary Key: BOAT_ID
 Column: Color
 
 Find any person who has reserved all the boats. The 
 
 I dont have the solution with me, but there is a 'NOT EXISTS', then in the
 subquery there is a minus and a correlated 'where' clause.'. That query
 wouldnt move.
 
 How would you solve this? 
 
 Also, according to the 'SQL Standard', SQL is supposed to support op codes
 such as 'ALL' or 'ANY' So you can say:
 
 Find all people who are older than any person with blue eyes. Or find all
 the people who are older than 'ALL' the people with blue eyes.
 
 Just to reiterate. Not looking for help with my homework. My professor isnt
 an Oracle guy so he doesnt know.  
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: [EMAIL PROTECTED]
   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: Mercadante, Thomas F
   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: [EMAIL PROTECTED]
  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: RE: interesting sql question

[rgaffuri]

no there are examples in the book using where 'not exists'. the query was horrible. 
Ill post it later if you want to see how bad it is. 

no its not homework. Id get the answer wrong if i did it this way, since Id have to 
follow the model in the book. Which is terrible. 
 
 From: Mercadante, Thomas F [EMAIL PROTECTED]
 Date: 2003/09/29 Mon PM 12:29:40 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: RE: RE: interesting sql question
 
 yeah!  I think it *is* homework  :)
  
 Tom 
  
  
  -Original Message-
 Sent: Monday, September 29, 2003 12:10 PM
 To: Multiple recipients of list ORACLE-L
 
 
 
 Hey ... the question wasn't complete ... 
 
 give us the full statement of the question ... 
 g 
 Raj 
 
  
 Rajendra dot Jamadagni at nospamespn dot com 
 All Views expressed in this email are strictly personal. 
 QOTD: Any clod can have facts, having an opinion is an art ! 
 
 
 -Original Message- 
 
 Sent: Monday, September 29, 2003 11:55 AM 
 To: Multiple recipients of list ORACLE-L 
 
 
 a user may request the same boat more than once. not sure that work. 
  
  From: Jamadagni, Rajendra [EMAIL PROTECTED] 
  Date: 2003/09/29 Mon AM 10:34:53 EDT 
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
  Subject: RE: RE: interesting sql question 
  
  Here is an attempt ... 
  
  select p.* 
  from persons p 
  where sid in 
   (select sid, count(bid) 
  from bids 
 group by sid 
having count(sid) = (select count(boad_id) from boats)) 
  / 
  
  You wanted to find all persons who have booked all boats ... add criteria 
  for booked in the first sub-query. 
  
  Raj 
 
 
 
   
  Rajendra dot Jamadagni at nospamespn dot com 
  All Views expressed in this email are strictly personal. 
  QOTD: Any clod can have facts, having an opinion is an art ! 
  
  
 
 
 
Title: RE: RE: interesting sql question



yeah! I think it *is* homework :)

Tom

-Original Message-From: Jamadagni, Rajendra 
[mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 
12:10 PMTo: Multiple recipients of list ORACLE-LSubject: 
RE: RE: interesting sql question

  Hey ... the question wasn't complete ... 
  give us the full statement of the question ... 
  g Raj  
  Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. 
  QOTD: Any clod can have facts, having an opinion is an art 
  ! 
  -Original Message- From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: 
  Multiple recipients of list ORACLE-L Subject: RE: RE: 
  interesting sql question 
  a user may request the same boat more than once. not sure that 
  work.   From: 
  "Jamadagni, Rajendra" [EMAIL PROTECTED]  Date: 2003/09/29 Mon AM 10:34:53 EDT  
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
   Subject: RE: RE: interesting sql question 
Here is an attempt 
  ...   select 
  p.*  from persons p  
  where sid in  
  (select sid, count(bid)  from bids 
   group by 
  sid  having 
  count(sid) = (select count(boad_id) from boats))  
  /   You wanted to find 
  all persons who have booked all boats ... add criteria  for booked in the first sub-query.  
   Raj  
   
     Rajendra dot Jamadagni 
  at nospamespn dot com  All Views expressed in this 
  email are strictly personal.  QOTD: Any clod can 
  have facts, having an opinion is an art !  
   

is this a good practice...pl/sql question

[Steve McClure]

Allright I am making some changes to some pl/sql code that handles batch
inserts into the database.  I am making changes to correct an error where
our clients are sending us data with invalid state information in their
address fields.  A constraint prohibits the insert with records with invalid
states, nulls are however allowed.  The decision was made to insert the rest
of the address information, leaving the state column null.  OK that is
enough background.  Here is an example of how I am handling this.

for x_rec in driving_cur loop
   if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing
design implementation
  x_rec.state:=NULL;
   end if;
   insert into address(other_columns,..state)
values(x_rec.other_cols,...x_rec.state);
end loop;

I know I am asking a best practices question, and blatantly using old
fashioned insert in the middle of a loop style code.  This was originally
developed in 7.3, and hasn't been recoded to take advantage of the bulk
enhancements.

My question is regarding the practice of changing the value of a record's
attribute(setting x_rec.state to null) after I have selected that record in
a cursor.  I have been doing this for some time, and it just dawned on me
that it might not be a good idea to do this.  My thinking is it might be
confusing to a developer, or the fear that at some point Oracle might
say..that was obviously not an intended feature, that usage no longer
allowed.  I am wondering if instead I should test the state column of the
record and then assign that value or NULL to a local variable.  I would then
insert the local variable instead of the attribute from the record.

Just sort of a bouncing the ball off the wall here, in fact I think I may
have resolved the question internally while asking it.  In any case I am
wondering what others think.

Steve McClure


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
  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: is this a good practice...pl/sql question

[Goulet, Dick]

Steve,

It may be old fashion code, but if it works within the time frame it needs 
to run in, why spend time recoding?  At any rate, I don't see a problem.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Wednesday, September 17, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


Allright I am making some changes to some pl/sql code that handles batch
inserts into the database.  I am making changes to correct an error where
our clients are sending us data with invalid state information in their
address fields.  A constraint prohibits the insert with records with invalid
states, nulls are however allowed.  The decision was made to insert the rest
of the address information, leaving the state column null.  OK that is
enough background.  Here is an example of how I am handling this.

for x_rec in driving_cur loop
   if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing
design implementation
  x_rec.state:=NULL;
   end if;
   insert into address(other_columns,..state)
values(x_rec.other_cols,...x_rec.state);
end loop;

I know I am asking a best practices question, and blatantly using old
fashioned insert in the middle of a loop style code.  This was originally
developed in 7.3, and hasn't been recoded to take advantage of the bulk
enhancements.

My question is regarding the practice of changing the value of a record's
attribute(setting x_rec.state to null) after I have selected that record in
a cursor.  I have been doing this for some time, and it just dawned on me
that it might not be a good idea to do this.  My thinking is it might be
confusing to a developer, or the fear that at some point Oracle might
say..that was obviously not an intended feature, that usage no longer
allowed.  I am wondering if instead I should test the state column of the
record and then assign that value or NULL to a local variable.  I would then
insert the local variable instead of the attribute from the record.

Just sort of a bouncing the ball off the wall here, in fact I think I may
have resolved the question internally while asking it.  In any case I am
wondering what others think.

Steve McClure


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
  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: Goulet, Dick
  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: is this a good practice...pl/sql question

[Jamadagni, Rajendra]

Title: RE: is this a good practice...pl/sql question





Steve,


Nothing wrong with setting xrec.state to null ... your developer is avoiding hard coding of NULL in the insert statement. In fact if this is working fine then only thing I'd try to change is bulk inserts instead of one by one ...

Don't worry  it is normal.
Raj
-Original Message-
From: Steve McClure [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 17, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L
Subject: is this a good practice...pl/sql question



Allright I am making some changes to some pl/sql code that handles batch
inserts into the database. I am making changes to correct an error where
our clients are sending us data with invalid state information in their
address fields. A constraint prohibits the insert with records with invalid
states, nulls are however allowed. The decision was made to insert the rest
of the address information, leaving the state column null. OK that is
enough background. Here is an example of how I am handling this.


for x_rec in driving_cur loop
 if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing
design implementation
 x_rec.state:=NULL;
 end if;
 insert into address(other_columns,..state)
values(x_rec.other_cols,...x_rec.state);
end loop;


I know I am asking a best practices question, and blatantly using old
fashioned insert in the middle of a loop style code. This was originally
developed in 7.3, and hasn't been recoded to take advantage of the bulk
enhancements.


My question is regarding the practice of changing the value of a record's
attribute(setting x_rec.state to null) after I have selected that record in
a cursor. I have been doing this for some time, and it just dawned on me
that it might not be a good idea to do this. My thinking is it might be
confusing to a developer, or the fear that at some point Oracle might
say..that was obviously not an intended feature, that usage no longer
allowed. I am wondering if instead I should test the state column of the
record and then assign that value or NULL to a local variable. I would then
insert the local variable instead of the attribute from the record.


Just sort of a bouncing the ball off the wall here, in fact I think I may
have resolved the question internally while asking it. In any case I am
wondering what others think.


Steve McClure



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
 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).



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

Re: is this a good practice...pl/sql question

[Stephane Faroult]

Steve McClure wrote:
 
 Allright I am making some changes to some pl/sql code that handles batch
 inserts into the database.  I am making changes to correct an error where
 our clients are sending us data with invalid state information in their
 address fields.  A constraint prohibits the insert with records with invalid
 states, nulls are however allowed.  The decision was made to insert the rest
 of the address information, leaving the state column null.  OK that is
 enough background.  Here is an example of how I am handling this.
 
 for x_rec in driving_cur loop
if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing
 design implementation
   x_rec.state:=NULL;
end if;
insert into address(other_columns,..state)
 values(x_rec.other_cols,...x_rec.state);
 end loop;
 
 I know I am asking a best practices question, and blatantly using old
 fashioned insert in the middle of a loop style code.  This was originally
 developed in 7.3, and hasn't been recoded to take advantage of the bulk
 enhancements.
 
 My question is regarding the practice of changing the value of a record's
 attribute(setting x_rec.state to null) after I have selected that record in
 a cursor.  I have been doing this for some time, and it just dawned on me
 that it might not be a good idea to do this.  My thinking is it might be
 confusing to a developer, or the fear that at some point Oracle might
 say..that was obviously not an intended feature, that usage no longer
 allowed.  I am wondering if instead I should test the state column of the
 record and then assign that value or NULL to a local variable.  I would then
 insert the local variable instead of the attribute from the record.
 
 Just sort of a bouncing the ball off the wall here, in fact I think I may
 have resolved the question internally while asking it.  In any case I am
 wondering what others think.
 
 Steve McClure
 


Steve,

   Keeping aside all considerations about the loop, I see no problem
here. The cursor variable is just short-hand notation for defining a
table%ROWTYPE - you are not modifying some hidden Oracle internal state
if this is what you are fearing. No 'mutating cursor', if I guess you
correctly.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: is this a good practice...pl/sql question

[Steve McClure]

   Keeping aside all considerations about the loop, I see no problem
here. The cursor variable is just short-hand notation for defining a
table%ROWTYPE - you are not modifying some hidden Oracle internal state
if this is what you are fearing. No 'mutating cursor', if I guess you
correctly.

  I guess I am really wondering if it is a good practice to modify the
attributes of a record previously selected via a cursor.  It is not just a
typical variable that is defined and used in a manner that suits the
algorithm.  I am just wondering if it would be a better idea to treat this
type of a record as a constant.  To elevate it conceptually.  The reason
being that it does(should?)represent what was actually selected from the
database.  If another developer, or myself for that matter, were to come
along at a later date, and use that attribute in another section of code not
knowing, or forgetting, it had been altered above.  I have actually used
this technique extensively in a couple of routines more complex than the one
I have described above.  It was just as I made this little code change, I
felt a pang of conscience, and wanted to ask this philosophical question.

  I am not worried about mucking up the database, or any such thing.  I am
aware that variable is just like any other attribute in any record I might
have explicitly created myself.  It is just that since the database
instantiated this record to represent the results of my query, I am
wondering if it should grant it an elevated postition.

Sorry for rambling on and on etc.

Steve


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
  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: is this a good practice...pl/sql question

[Jamadagni, Rajendra]

Title: RE: is this a good practice...pl/sql question





Yes it is a good practice when required.


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Steve McClure [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 17, 2003 5:45 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: is this a good practice...pl/sql question



 Keeping aside all considerations about the loop, I see no problem
here. The cursor variable is just short-hand notation for defining a
table%ROWTYPE - you are not modifying some hidden Oracle internal state
if this is what you are fearing. No 'mutating cursor', if I guess you
correctly.


 I guess I am really wondering if it is a good practice to modify the
attributes of a record previously selected via a cursor. It is not just a
typical variable that is defined and used in a manner that suits the
algorithm. I am just wondering if it would be a better idea to treat this
type of a record as a constant. To elevate it conceptually. The reason
being that it does(should?)represent what was actually selected from the
database. If another developer, or myself for that matter, were to come
along at a later date, and use that attribute in another section of code not
knowing, or forgetting, it had been altered above. I have actually used
this technique extensively in a couple of routines more complex than the one
I have described above. It was just as I made this little code change, I
felt a pang of conscience, and wanted to ask this philosophical question.


 I am not worried about mucking up the database, or any such thing. I am
aware that variable is just like any other attribute in any record I might
have explicitly created myself. It is just that since the database
instantiated this record to represent the results of my query, I am
wondering if it should grant it an elevated postition.


Sorry for rambling on and on etc.


Steve



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
 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).



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

SQL question : How to retrieve the File_name without Directorie P

[NGUYEN Philippe (Cetelem)]

Title: SQL question : How to retrieve the File_name without Directorie Path ?





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 :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ? 
Thank in advance !
Philippe

Antw: SQL question : How to retrieve the File_name without

[Guido Konsolke]

Hi Philippe,

eat this:

select substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-1)+1) 
from dual;

hth,
Guido

 [EMAIL PROTECTED] 23.07.2003  09.59 Uhr 
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 :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ? 
Thank in advance !
Philippe

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guido Konsolke
  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: Antw: SQL question : How to retrieve the File_name without

[Igor Neyman]

 eat this:

Is it chewable? -:)

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
Guido Konsolke
Sent: Wednesday, July 23, 2003 3:14 AM
To: Multiple recipients of list ORACLE-L

Hi Philippe,

eat this:

select
substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-
1)+1) from dual;

hth,
Guido

 [EMAIL PROTECTED] 23.07.2003  09.59 Uhr 
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 :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ? 
Thank in advance !
Philippe

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guido Konsolke
  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: Igor Neyman
  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: RE: Antw: SQL question : How to retrieve the File_name

[Guido Konsolke]

Hi Igor,

yes, it is. But it would be better if we all
swallow the use of the builtin functions.

My weaknesses are with analytic functions.

I would give my colleague's right arm for
getting into them ;-))

Greetings,
Guido

 [EMAIL PROTECTED] 23.07.2003  15.54 Uhr 
 eat this:

Is it chewable? -:)

Igor Neyman, OCP DBA
[EMAIL PROTECTED] 



-Original Message-
Guido Konsolke
Sent: Wednesday, July 23, 2003 3:14 AM
To: Multiple recipients of list ORACLE-L

Hi Philippe,

eat this:

select
substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-
1)+1) from dual;

hth,
Guido

 [EMAIL PROTECTED] 23.07.2003  09.59 Uhr 
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 :'/oracle/d0/data/user.dbf' -- user.dbf
Maybe using translate function ? 
Thank in advance !
Philippe

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Guido Konsolke
  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: Igor Neyman
  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: Guido Konsolke
  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: SQL question : How to retrieve the File_name without Directorie P

[Daniel Fink]

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 :'/oracle/d0/data/user.dbf' -- user.dbf
 Maybe using translate function ?
 Thank in advance !
 Philippebegin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

SQL question - crosstab in oracle

[Stephen . HODGKINSON]

Hi

I have a table with the following structure.

 CHECK_DATE DATE
 PHYRDS NUMBER(20)
 PHYWRTSNUMBER(20)
 PHYBLKRD   NUMBER(20)
 PHYBLKWRT  NUMBER(20)
 FILE#   NUMBER
 FILE_NAME  VARCHAR2(120)

This records the IO on the different datafiles every 3 minutes.

I want to run a report on this table but I want the output to display on
file# as a heading.Similar to iostat on UNIX.

So my output   might be:



Check DATE   FIle1  File2
 File3 etc...
   PHYRDS   PHYWRTS   PHYRDS   PHYWRTS

11:14:341270820218 14
11:18:261303821118 14
11:33:441429024318 14
11:40:451500824518 14
11:45:371545924918 14
11:48:381577625018 14
11:51:371586425118 14
11:54:371602925418 14
11:57:391621025418 14
12:00:401631125418 14
12:03:401643925618 14


I could do this as a bit of plsq but  Is it possible to generate this
report with one bit of sql. (views,inline views, decodes.  etc)

thanks stephen



**
This email contains information which is confidential. It is for the 
exclusive use of the addressee. If you are not an addressee please 
contact us immediately on 01737 275500. Please note that any 
distribution, copying or use of this communication or its contents is 
prohibited.

This footnote also confirms that this email message and all associated
attachments have been swept by Network Associates VirusScan for the 
presence of computer viruses.
**

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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: SQL question - crosstab in oracle

[Jay Wade]

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:34:24 -0800


Hi

I have a table with the following structure.

 CHECK_DATE DATE
 PHYRDS NUMBER(20)
 PHYWRTSNUMBER(20)
 PHYBLKRD   NUMBER(20)
 PHYBLKWRT  NUMBER(20)
 FILE#   NUMBER
 FILE_NAME  VARCHAR2(120)
This records the IO on the different datafiles every 3 minutes.

I want to run a report on this table but I want the output to display on
file# as a heading.Similar to iostat on UNIX.
So my output   might be:



Check DATE   FIle1  File2
 File3 etc...
   PHYRDS   PHYWRTS   PHYRDS   PHYWRTS
11:14:341270820218 14
11:18:261303821118 14
11:33:441429024318 14
11:40:451500824518 14
11:45:371545924918 14
11:48:381577625018 14
11:51:371586425118 14
11:54:371602925418 14
11:57:391621025418 14
12:00:401631125418 14
12:03:401643925618 14
I could do this as a bit of plsq but  Is it possible to generate this
report with one bit of sql. (views,inline views, decodes.  etc)
thanks stephen



**
This email contains information which is confidential. It is for the
exclusive use of the addressee. If you are not an addressee please
contact us immediately on 01737 275500. Please note that any
distribution, copying or use of this communication or its contents is
prohibited.
This footnote also confirms that this email message and all associated
attachments have been swept by Network Associates VirusScan for the
presence of computer viruses.
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  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).
_
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Wade
 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: SQL Question

[Chelur, Jayadas {PBSG}]

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, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L


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 as the min
checks for group_id in () and that will
evaluate any U belonging to a single group that is part of the multiple
groups that a U belongs to.

But I will take this query as a starting point and will work on getting
that resolved.
Thanks for your time and appreciate your help
Regards,
Madhavan
http://www.dpapps.com

On Thu, 03 Apr 2003 12:28:42 -0800, Chelur, Jayadas {PBSG}
[EMAIL PROTECTED] said:
 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 INTO UT VALUES(2006,1013,1010);
 INSERT INTO UT VALUES(2007,1017,1016);
 INSERT INTO UT VALUES(2008,1018,1010);
 INSERT INTO UT VALUES(2008,1018,1011);
 
 INSERT INTO UT VALUES(2009,1019,1016);
 INSERT INTO UT VALUES(2001,1020,1010);
 INSERT INTO UT VALUES(2001,1020,1011);
 
 COMMIT;
 
 ===
 
 this query will identify all the security groups and the
 minimum security group id of the identical one ...
 
 
 SELECT  DISTINCT
 S2.SORIGINAL_SG,/* original security group  */
 S3.SEQUIV_SG/* equivalent security group*/
 FROM(
 SELECT  S, COUNT(*) RECS
 FROMUT
 GROUP   BY S
 ) S1,   /* security groups and their group counts - table1 */
 (
 SELECT  S, COUNT(*) RECS
 FROMUT
 GROUP   BY S
 ) S2,   /* security groups and their group counts - table2 */
 (
 SELECT  DISTINCT S
 FROMUT
 ) S3/* just the unique security groups  */
 WHERE   S1.RECS = S2.RECS   /* match the sec. groups with the same record
 counts */
 AND S1.S S2.S /* make sure they are NOT the same security
 group*/
 AND NOT EXISTS  /* make sure they include identical group ids
 */
 (
 SELECT  G FROM UT WHERE S = S1.S 
 MINUS
 SELECT  G FROM UT WHERE S = S2.S
 )
 AND S3.S = ( /* see note */
SELECT MIN(S)
FROM   UT
WHERE  G IN
   (
   SELECT  G
   FROMUT
   WHERE   S = S1.S
   )
)
 
 /* note :
 this is to find the minimum value of the security id which has the same
 group
 id records as that any of the matching security groups. this minimum
 value
 can
 be used to update the security group ids of all other identical security
 groups
 at a later point of time
 */
 


 
 
 you can either change the query to update all the eligible security id to
 their corresponding minimum values or generate equivalent update
 statements
 using this query and run them as a batch ...
 
 HTH ...
 
-- 
Madhavan Amruthur
DecisionPoint Applications

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Madhavan Amruthur
  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: Chelur, Jayadas {PBSG}
  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 

SQL Question

[Madhavan Amruthur]

Hi,
Sorry for reposting.
Just wanted to put in a subject...

I have been grappling with this for sometime and thought it will be best
for others to take a look at it.

I have a table a_user_groups

   USER_ID SECURITY_GROUP_ID   GROUP_ID
-- - --
  1005  1012   1010
  1005  1012   1011
  1006  1013   1010
  1007  1017   1016
  1008  1018   1010
  1008  1018   1011

The security_group_id currently is uniquely generated every time a user
is added and a group_id is associated with the user_id.
For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008
has the same combination but the security_group_id is generated
differently.
The generation happens for C code and there is an option to correct the
problem in the C code but I am trying to see if I can prevent that

The requirement is that user_id 1008 and any other users with the same
group_id combination should have the same security_group_id 1012, 
basically the first occurrence for the combination. 
In the case of user_id 1006 the value for security_group_id is 1013 and
the group_id the user_id belongs to is 1016. So all the user_ids with a
group_id association 
of 1016 (1016 in a combination does not count) down the line will have to
be updated to 1013.

I found a solution for the case where I associate a group to a user_id in
this existing table by creating another table that converted the above
table into a hierarchy

Table b_hier_user_groups

   USER_ID   CGID PARENT_VALUE CHILD_VALUE
-- --  ---
  1005   1012 1010
  1005   1012 10101011
  1006   1013 1010
  1007   1017 1016
  1008   1018 1010
  1008   1018 10101011

Then using a PL/SQL script I generated the tree using sys_connect_by_path
I determined if the user had a path that already. 
For eg: in the above case if the user 1006 was being associated with
group_id 1011, then I would check the exsiting trees to see if there was
a path already as in 1010,1011.
In this case it does exist and the cgid (equivalent to security_group_id
in the above table) is 1012 and update the user_id 1006 to cgid 1012. But
I am not for some reason able
to apply this solution to the existing rows. I have a feeling that I am
missing something simple

The requirement that I am grappling with is to update the values in the
existing table. I can get the table b_hier_user_groups created from the
a_user_groups.

Please let me know if you need more information
The table structures are as below

a_user_groups

Name  Null?Type
 - 
 
 USER_ID   NOT NULL NUMBER
 SECURITY_GROUP_ID NOT NULL NUMBER
 GROUP_ID  NOT NULL NUMBER


Table b_hier_user_groups

Name  Null?Type
 - 
 
 USER_IDNUMBER
 CGID  --- same as security_Group_id from above)   NUMBER
 PARENT_VALUE   NUMBER
 CHILD_VALUENUMBER

Thanks for your time and help in advance.
Regards,
Madhavan
http://www.dpapps.com



-- 
Madhavan Amruthur
DecisionPoint Applications

-- 
http://www.fastmail.fm - I mean, what is it about a decent email service?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Madhavan Amruthur
  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: SQL Question

[Chelur, Jayadas {PBSG}]

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 INTO UT VALUES(2006,1013,1010);
INSERT INTO UT VALUES(2007,1017,1016);
INSERT INTO UT VALUES(2008,1018,1010);
INSERT INTO UT VALUES(2008,1018,1011);

INSERT INTO UT VALUES(2009,1019,1016);
INSERT INTO UT VALUES(2001,1020,1010);
INSERT INTO UT VALUES(2001,1020,1011);

COMMIT;

===

this query will identify all the security groups and the
minimum security group id of the identical one ...


SELECT  DISTINCT
S2.SORIGINAL_SG,/* original security group  */
S3.SEQUIV_SG/* equivalent security group*/
FROM(
SELECT  S, COUNT(*) RECS
FROMUT
GROUP   BY S
) S1,   /* security groups and their group counts - table1 */
(
SELECT  S, COUNT(*) RECS
FROMUT
GROUP   BY S
) S2,   /* security groups and their group counts - table2 */
(
SELECT  DISTINCT S
FROMUT
) S3/* just the unique security groups  */
WHERE   S1.RECS = S2.RECS   /* match the sec. groups with the same record
counts */
AND S1.S S2.S /* make sure they are NOT the same security
group*/
AND NOT EXISTS  /* make sure they include identical group ids
*/
(
SELECT  G FROM UT WHERE S = S1.S 
MINUS
SELECT  G FROM UT WHERE S = S2.S
)
AND S3.S = ( /* see note */
   SELECT MIN(S)
   FROM   UT
   WHERE  G IN
  (
  SELECT  G
  FROMUT
  WHERE   S = S1.S
  )
   )

/* note :
this is to find the minimum value of the security id which has the same
group
id records as that any of the matching security groups. this minimum value
can
be used to update the security group ids of all other identical security
groups
at a later point of time
*/




you can either change the query to update all the eligible security id to
their corresponding minimum values or generate equivalent update statements
using this query and run them as a batch ...

HTH ...


-Original Message-
Sent: Thursday, April 03, 2003 1:15 PM
To: Multiple recipients of list ORACLE-L


Hi,
Sorry for reposting.
Just wanted to put in a subject...

I have been grappling with this for sometime and thought it will be best
for others to take a look at it.

I have a table a_user_groups

   USER_ID SECURITY_GROUP_ID   GROUP_ID
-- - --
  1005  1012   1010
  1005  1012   1011
  1006  1013   1010
  1007  1017   1016
  1008  1018   1010
  1008  1018   1011

The security_group_id currently is uniquely generated every time a user
is added and a group_id is associated with the user_id.
For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008
has the same combination but the security_group_id is generated
differently.
The generation happens for C code and there is an option to correct the
problem in the C code but I am trying to see if I can prevent that

The requirement is that user_id 1008 and any other users with the same
group_id combination should have the same security_group_id 1012, 
basically the first occurrence for the combination. 
In the case of user_id 1006 the value for security_group_id is 1013 and
the group_id the user_id belongs to is 1016. So all the user_ids with a
group_id association 
of 1016 (1016 in a combination does not count) down the line will have to
be updated to 1013.

I found a solution for the case where I associate a group to a user_id in
this existing table by creating another table that converted the above
table into a hierarchy

Table b_hier_user_groups

   USER_ID   CGID PARENT_VALUE CHILD_VALUE
-- --  ---
  1005   1012 1010
  1005   1012 10101011
  1006   1013 1010
  1007   1017 1016
  1008   1018 1010
  1008   1018 10101011

Then using a PL/SQL script I generated the tree using sys_connect_by_path
I determined if the user had a path that already. 
For eg: in the above case if the user 1006 was being associated with
group_id 1011, then I would check the exsiting trees to see if there was
a path already as in 1010,1011.
In this case it does exist and the cgid (equivalent to security_group_id
in the above table) is 1012 and 

RE: SQL Question

[Madhavan Amruthur]

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 as the min
checks for group_id in () and that will
evaluate any U belonging to a single group that is part of the multiple
groups that a U belongs to.

But I will take this query as a starting point and will work on getting
that resolved.
Thanks for your time and appreciate your help
Regards,
Madhavan
http://www.dpapps.com

On Thu, 03 Apr 2003 12:28:42 -0800, Chelur, Jayadas {PBSG}
[EMAIL PROTECTED] said:
 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 INTO UT VALUES(2006,1013,1010);
 INSERT INTO UT VALUES(2007,1017,1016);
 INSERT INTO UT VALUES(2008,1018,1010);
 INSERT INTO UT VALUES(2008,1018,1011);
 
 INSERT INTO UT VALUES(2009,1019,1016);
 INSERT INTO UT VALUES(2001,1020,1010);
 INSERT INTO UT VALUES(2001,1020,1011);
 
 COMMIT;
 
 ===
 
 this query will identify all the security groups and the
 minimum security group id of the identical one ...
 
 
 SELECT  DISTINCT
 S2.SORIGINAL_SG,/* original security group  */
 S3.SEQUIV_SG/* equivalent security group*/
 FROM(
 SELECT  S, COUNT(*) RECS
 FROMUT
 GROUP   BY S
 ) S1,   /* security groups and their group counts - table1 */
 (
 SELECT  S, COUNT(*) RECS
 FROMUT
 GROUP   BY S
 ) S2,   /* security groups and their group counts - table2 */
 (
 SELECT  DISTINCT S
 FROMUT
 ) S3/* just the unique security groups  */
 WHERE   S1.RECS = S2.RECS   /* match the sec. groups with the same record
 counts */
 AND S1.S S2.S /* make sure they are NOT the same security
 group*/
 AND NOT EXISTS  /* make sure they include identical group ids
 */
 (
 SELECT  G FROM UT WHERE S = S1.S 
 MINUS
 SELECT  G FROM UT WHERE S = S2.S
 )
 AND S3.S = ( /* see note */
SELECT MIN(S)
FROM   UT
WHERE  G IN
   (
   SELECT  G
   FROMUT
   WHERE   S = S1.S
   )
)
 
 /* note :
 this is to find the minimum value of the security id which has the same
 group
 id records as that any of the matching security groups. this minimum
 value
 can
 be used to update the security group ids of all other identical security
 groups
 at a later point of time
 */
 
 
 
 
 you can either change the query to update all the eligible security id to
 their corresponding minimum values or generate equivalent update
 statements
 using this query and run them as a batch ...
 
 HTH ...
 
-- 
Madhavan Amruthur
DecisionPoint Applications

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Madhavan Amruthur
  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: a DIFFERENT sql question

[Aponte, Tony]

Title: RE: a DIFFERENT sql question






SELECT DISTINCT 

t1.category CAT

,t2.type TYP

,SUM(DISTINCT t1.amount1) OVER(PARTITION BY t1.category) Sum1

,SUM(t2.amount2) OVER(PARTITION BY t1.category, t2.type) Sum2

FROM

t1

,t2

WHERE

t1.mykey1 = t2.mykey1

/


CAT TYP SUM1 SUM2

AA x 8 27

AA y 8 6

AA z 8 10

BB y 50 27



HTH

Tony APonte

Home Shopping Network



-Original Message-

From: STEVE OLLIG [mailto:[EMAIL PROTECTED]]

Sent: Thursday, March 13, 2003 2:24 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: a DIFFERENT sql question



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 , b.type

 4 , sum(b.amount2)

 5 from t1 a

 6 , t2 b

 7 where a.mykey1 = b.mykey1

 8 group by

 9 a.category

10* , b.type

SQL /


CATEG AMOUNT1SUM TYPE SUM(B.AMOUNT2)

- -- - --

AA 8 x 27

AA 8 y 6

AA 8 z 10

BB 50 y 27


4 rows selected.


SQL 


-Original Message-

Sent: Thursday, March 13, 2003 10:49 AM

To: Multiple recipients of list ORACLE-L



since we're having fun with SQL today - here's one that's hurting my brain

at the moment. I need to sum columns at 2 different groupings in my

resultset. The first select is perfect; the 2nd is where i have trouble;

but i know i can do stuff like the 3rd example. How do I get the 2nd one to

work?


SQL SCRIPT:

drop table t1;

drop table t2;

create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5));

create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5),

amount2 number(5));

insert into t1 values (1, 'AA', 5);

insert into t1 values (2, 'AA', 3);

insert into t1 values (3, 'BB', 50);

insert into t2 values (1, 1, 'x', 1);

insert into t2 values (2, 1, 'x', 2);

insert into t2 values (3, 1, 'y', 6);

insert into t2 values (4, 2, 'x', 4);

insert into t2 values (5, 2, 'z', 10);

insert into t2 values (6, 2, 'x', 20);

insert into t2 values (7, 3, 'y', 12);

insert into t2 values (8, 3, 'y', 15);

select a.category

 , a.mykey1

 , sum(distinct a.amount1)

 , b.type

 , sum(b.amount2)

 from t1 a

 , t2 b

where a.mykey1 = b.mykey1

group by

 a.category

 , a.mykey1

 , a.amount1

 , b.type

/

select a.category

-- , a.mykey1

 , sum(distinct a.amount1)

 , b.type

 , sum(b.amount2)

 from t1 a

 , t2 b

where a.mykey1 = b.mykey1

group by

 a.category

-- , a.mykey1

-- , a.amount1

 , b.type

/

select decode(grouping(a.category), 1, 'All', a.category) as category

 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job

 , decode(grouping(b.type), 1, 'All', b.type) as type

 , count(*)

 , sum(distinct a.amount1)

 , sum(b.amount2)

 from t1 a

 , t2 b

where a.mykey1 = b.mykey1

group by rollup

 ( a.category

 , a.mykey1

 , b.type

 )

order by

 a.category

 , a.mykey1

 , b.type

/


here's what i get:


SQL select a.category

 2 , a.mykey1

 3 , sum(distinct a.amount1)

 4 , b.type

 5 , sum(b.amount2)

 6 from t1 a

 7 , t2 b

 8 where a.mykey1 = b.mykey1

 9 group by

10 a.category

11 , a.mykey1

12 , a.amount1

13 , b.type

14 /


CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2)

- -- -- - --

AA 1 5 x 3

AA 1 5 y 6

AA 2 3 x 24

AA 2 3 z 10

BB 3 50 y 27


5 rows selected.


perfect.


but this is the problem query:


SQL select a.category

 2 -- , a.mykey1

 3 , sum(distinct a.amount1)

 4 , b.type

 5 , sum(b.amount2)

 6 from t1 a

 7 , t2 b

 8 where a.mykey1 = b.mykey1

 9 group by

10 a.category

11 -- , a.mykey1

12 -- , a.amount1

13 , b.type

14 /


CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2)

- -- - --

AA 8 x 27

AA 5 y 6

AA 3 z 10

BB 50 y 27


4 rows selected.


wrong. i want the resultset to look like this:


CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2)

- -- - --

AA 8 x 27

AA 8 y 6

AA 8 z 10

BB 50 y 27



then this is cool, but not what i want:


SQL select decode(grouping(a.category), 1, 'All', a.category) as category

 2 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job

 3 , decode(grouping(b.type), 1, 'All', b.type) as type

 4 , count(*)

 5 , sum(distinct a.amount1)

 6 , sum(b.amount2)

 7 from t1 a

 8 , t2 b

 9 where a.mykey1 = b.mykey1

10 group by rollup

11 ( a.category

12 , a.mykey1

13 , b.type

14 )

15 order by

16 a.category

17 , a.mykey1

18 , b.type

19 /


CATEG JOB TYPE COUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2)

- - - -- -- --

AA 1 x 2 5 3

AA 1 y 1 5 6

AA 1 All 3 5 9

AA 2 x 2 3 24

AA 2 z 1 3 10

AA 2 All 3 3 34

AA All All 6 8 43

BB 3 y 2 50 27

BB 3 All 2 50 27

BB All All 2 50 27

All All All 8 58 70


11 rows selected.



Steve Ollig

[EMAIL PROTECTED]

(952)826-4241

A SQL Question

[Deshpande, Kirti]

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
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: A SQL Question

[Igor Neyman]

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.
SQLWKS
SQLWKS insert into test (col1, col2) values ('A', 'B');
1 row processed.
SQLWKS insert into test (col1, col2) values ('C', 'D');
1 row processed.
SQLWKS insert into test (col1, col2) values ('E', 'F');
1 row processed.
SQLWKS insert into test (col1, col2) values ('G', 'H');
1 row processed.
SQLWKS insert into test (col1, col2) values ('B', 'A');
1 row processed.
SQLWKS insert into test (col1, col2) values ('F', 'E');
1 row processed.
SQLWKS insert into test (col1, col2) values ('D', 'C');
1 row processed.
SQLWKS insert into test (col1, col2) values ('H', 'G');
1 row processed.
SQLWKS commit;
Statement processed.
SQLWKS
SQLWKS select * from test;
COL1   COL2
-- --
A  B
C  D
E  F
G  H
B  A
F  E
D  C
H  G
8 rows selected.

SQLWKS select * from test order by col1;
COL1   COL2
-- --
A  B
B  A
C  D
D  C
E  F
F  E
G  H
H  G
8 rows selected.



Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 8:23 AM


 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
 BA
 CD
 DC
 EF
 FE
 G   H
 H   G

 Thanks for your help.

 - Kirti

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Deshpande, Kirti
   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: Igor Neyman
  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: A SQL Question

[DENNIS WILLIAMS]

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.

SQL select * from test order by col1;

C C
- -
A B
B A
C D
D C
E F
F E
G H
H G

8 rows selected.
 

-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
CD
HG

With a PK on (Col1, Col2). 

How do I write a SQL script to get following result? 

Col1Col2

AB
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: DENNIS WILLIAMS
  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: A SQL Question

[Whittle Jerome Contr NCI]

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


-Original Message-

From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED]


Hi SQL Developers, 


I have a table as follows:


Col1 Col2



A B

C D

E F

G H

B A

E F

C D

H G


With a PK on (Col1, Col2). 


How do I write a SQL script to get following result? 


Col1 Col2



A B

B A

C D

D C

E F

F E

G H

H G


Thanks for your help.


- Kirti 

Re: A SQL Question

[mkb]

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
 
 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
 BA
 CD
 DC
 EF
 FE
 G   H
 H   G
 
 Thanks for your help.
 
 - Kirti 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 -- 
 Author: Deshpande, Kirti
   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).
 


__
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mkb
  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: A SQL Question

[Stephane Faroult]

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
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

Kirti,

   On your example 'ORDER BY COL1' should be enough :-).
I have a solution which is not excellent (I dislike the way I prevent the query from 
returning too many rows), but seems to be working even when there is no transitivity. 
May at least give you an idea on which to start work :

select *
from (select *
  from T
  connect by col1 = prior col2
  and col1  col2) x
where rownum = (select count(*) from T)
/


Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: A SQL Question

[Whittle Jerome Contr NCI]

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]

618-622-4145


-Original Message-

From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED]


Hi SQL Developers, 


I have a table as follows:


Col1 Col2



A B

C D

E F

G H

B A

E F

C D

H G


With a PK on (Col1, Col2). 


How do I write a SQL script to get following result? 


Col1 Col2



A B

B A

C D

D C

E F

F E

G H

H G


Thanks for your help.


- Kirti 

RE: A SQL Question

[Deshpande, Kirti]

I messed up typing the data for the table. It has no dups.
The second  occurrence of C, D and E, F should actually be D, C and F, E.
Sorry about that...

Need more hot tea to wake me up !! 

- Kirti 

  -Original Message-
 From: Deshpande, Kirti  
 Sent: Thursday, March 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
 CD
 HG
 
 With a PK on (Col1, Col2). 
 
 How do I write a SQL script to get following result? 
 
 Col1Col2
 
 AB
 BA
 CD
 DC
 EF
 FE
 G   H
 H   G
 
 Thanks for your help.
 
 - Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: A SQL Question

[Darrell Landrum]

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
GH
BA
EF
CD
HG

With a PK on (Col1, Col2). 

How do I write a SQL script to get following result? 

Col1Col2

AB
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Deshpande, Kirti
  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: Darrell Landrum
  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: A SQL Question

[mkb]

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
GH
HG

8 rows selected.

mohammed

--- Deshpande, Kirti [EMAIL PROTECTED]
wrote:
 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
 BA
 CD
 DC
 EF
 FE
 G   H
 H   G
 
 Thanks for your help.
 
 - Kirti 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 -- 
 Author: Deshpande, Kirti
   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).
 


__
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mkb
  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: A SQL Question

[Igor Neyman]

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 suspected, that the question isn't that simple -:)


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 8:23 AM


 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
 BA
 CD
 DC
 EF
 FE
 G   H
 H   G

 Thanks for your help.

 - Kirti

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Deshpande, Kirti
   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: Igor Neyman
  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: A SQL Question

[Ron Rogers]

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
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
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Deshpande, Kirti
  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: Ron Rogers
  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: A SQL Question

[Mercadante, Thomas F]

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 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
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: Mercadante, Thomas F
  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: A SQL Question

[Naveen Nahata]

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

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
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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).



DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged. 
Before opening attachments please check them for viruses and defects. MindTree 
Consulting Private Limited (MindTree) will not be responsible for any viruses or 
defects or any forwarded attachments emanating either from within MindTree or outside. 
If you have received this message by mistake please notify the sender by return  
e-mail and delete this message from your system. Any unauthorized use or dissemination 
of this message in whole or in part is strictly prohibited.  Please note that e-mails 
are susceptible to change and MindTree shall not be liable for any improper, untimely 
or incomplete transmission.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen Nahata
  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: A SQL Question

[Deshpande, Kirti]

Igor (and all):

Yes, our SPAM Cops and their filters are very strict with the wording in the e-mail 
footers. 

Unfortunately, FatCity.com uses the footer that gets caught by these filters. 

When replying to me directly, using list message, you need to remove the old footers 
from the e-mail. 

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
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 suspected, that the question isn't that simple -:)


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 8:23 AM


 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
 BA
 CD
 DC
 EF
 FE
 G   H
 H   G

 Thanks for your help.

 - Kirti

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: A SQL Question

[Nelson, Allan]

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
CD
HG

With a PK on (Col1, Col2). 

How do I write a SQL script to get following result? 

Col1Col2

AB
BA
CD
DC
EF
FE
G   H
H   G

Thanks for your help.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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).



__
This email is intended solely for the person or entity to which it is addressed and 
may contain confidential and/or privileged information.  Copying, forwarding or 
distributing this message by persons or entities other than the addressee is 
prohibited. If you have received this email in error, please contact the sender 
immediately and delete the material from any computer.  This email may have been 
monitored for policy compliance.  [021216]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nelson, Allan
  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).

Corrected SQL Question...

[Deshpande, Kirti]

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 AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
HOU XYZ
LIT DAL

C1  C2
--- ---
LIT HOU
XYZ DAL
XYZ HOU

14 rows selected.

SQL 

Here is what is required:

C1  C2
--- ---
AUS DAL
DAL AUS
AUS HOU
HOU AUS
DAL HOU
HOU DAL
DAL LIT
LIT DAL
DAL XYZ
XYZ DAL
HOU LIT
LIT HOU
HOU XYZ
XYZ HOU


I think I am clear now... 
Sorry about the wrong test data earlier... 


Thanks,

- Kirti 





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: A SQL Question

[Wolfgang Breitling]

Title: Re: A 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 all
 6 select B.c2, A.c1
 7 from (select col1 c1, rownum r from tbl order by col1) A
 8 , (select col2 c2, rownum r from tbl order by col2) b
 9 where a.r = b.r
 10 order by 1
 11 /


C C
- -
A B
B A
C D
D C
E F
F E
G H
H G


At 05:23 AM 3/13/2003 -0800, you wrote:
Hi SQL Developers,

I have a table as follows:

Col1 Col2

A B
C D
E F
G H
B A
E F
C D
H G

With a PK on (Col1, Col2).

How do I write a SQL script to get following result?

Col1 Col2

A B
B A
C D
D C
E F
F E
G H
H G

Thanks for your help.

- Kirti


Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



 


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier lectronique est une communication prive  l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris  recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.

a DIFFERENT sql question

[STEVE OLLIG]

since we're having fun with SQL today - here's one that's hurting my brain
at the moment.  I need to sum columns at 2 different groupings in my
resultset.  The first select is perfect; the 2nd is where i have trouble;
but i know i can do stuff like the 3rd example.  How do I get the 2nd one to
work?

SQL SCRIPT:
drop table t1;
drop table t2;
create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5));
create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5),
amount2 number(5));
insert into t1 values (1, 'AA', 5);
insert into t1 values (2, 'AA', 3);
insert into t1 values (3, 'BB', 50);
insert into t2 values (1, 1, 'x', 1);
insert into t2 values (2, 1, 'x', 2);
insert into t2 values (3, 1, 'y', 6);
insert into t2 values (4, 2, 'x', 4);
insert into t2 values (5, 2, 'z', 10);
insert into t2 values (6, 2, 'x', 20);
insert into t2 values (7, 3, 'y', 12);
insert into t2 values (8, 3, 'y', 15);
select a.category
 , a.mykey1
 , sum(distinct a.amount1)
 , b.type
 , sum(b.amount2)
  from t1 a
 , t2 b
 where a.mykey1 = b.mykey1
 group by
   a.category
 , a.mykey1
 , a.amount1
 , b.type
/
select a.category
--   , a.mykey1
 , sum(distinct a.amount1)
 , b.type
 , sum(b.amount2)
  from t1 a
 , t2 b
 where a.mykey1 = b.mykey1
 group by
   a.category
--   , a.mykey1
--   , a.amount1
 , b.type
/
select decode(grouping(a.category), 1, 'All', a.category) as category
 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job
 , decode(grouping(b.type), 1, 'All', b.type) as type
 , count(*)
 , sum(distinct a.amount1)
 , sum(b.amount2)
  from t1 a
 , t2 b
 where a.mykey1 = b.mykey1
 group by rollup
 ( a.category
 , a.mykey1
 , b.type
 )
order by
   a.category
 , a.mykey1
 , b.type
/

here's what i get:

SQL select a.category
  2   , a.mykey1
  3   , sum(distinct a.amount1)
  4   , b.type
  5   , sum(b.amount2)
  6from t1 a
  7   , t2 b
  8   where a.mykey1 = b.mykey1
  9   group by
 10 a.category
 11   , a.mykey1
 12   , a.amount1
 13   , b.type
 14  /

CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE  SUM(B.AMOUNT2)
- -- -- - --
AA 1  5 x  3
AA 1  5 y  6
AA 2  3 x 24
AA 2  3 z 10
BB 3 50 y 27

5 rows selected.

perfect.

but this is the problem query:

SQL select a.category
  2  --   , a.mykey1
  3   , sum(distinct a.amount1)
  4   , b.type
  5   , sum(b.amount2)
  6from t1 a
  7   , t2 b
  8   where a.mykey1 = b.mykey1
  9   group by
 10 a.category
 11  --   , a.mykey1
 12  --   , a.amount1
 13   , b.type
 14  /

CATEG SUM(DISTINCTA.AMOUNT1) TYPE  SUM(B.AMOUNT2)
- -- - --
AA 8 x 27
AA 5 y  6
AA 3 z 10
BB50 y 27

4 rows selected.

wrong.  i want the resultset to look like this:

CATEG SUM(DISTINCTA.AMOUNT1) TYPE  SUM(B.AMOUNT2)
- -- - --
AA 8 x 27
AA 8 y  6
AA 8 z 10
BB50 y 27


then this is cool, but not what i want:

SQL select decode(grouping(a.category), 1, 'All', a.category) as category
  2   , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job
  3   , decode(grouping(b.type), 1, 'All', b.type) as type
  4   , count(*)
  5   , sum(distinct a.amount1)
  6   , sum(b.amount2)
  7from t1 a
  8   , t2 b
  9   where a.mykey1 = b.mykey1
 10   group by rollup
 11   ( a.category
 12   , a.mykey1
 13   , b.type
 14   )
 15  order by
 16 a.category
 17   , a.mykey1
 18   , b.type
 19  /

CATEG JOB   TYPECOUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2)
- - - -- -- --
AA1 x  2  5  3
AA1 y  1  5  6
AA1 All3  5  9
AA2 x  2  3 24
AA2 z  1  3 10
AA2 All3  3 34
AAAll   All6  8 43
BB3 y  2 50 27
BB3 All2 50 27
BBAll   All2 50 27
All   All   

Corrected SQL Question...

[Deshpande, Kirti]

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 AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
HOU XYZ
LIT DAL

C1  C2
--- ---
LIT HOU
XYZ DAL
XYZ HOU

14 rows selected.

SQL 

Here is what is required:

C1  C2
--- ---
AUS DAL
DAL AUS
AUS HOU
HOU AUS
DAL HOU
HOU DAL
DAL LIT
LIT DAL
DAL XYZ
XYZ DAL
HOU LIT
LIT HOU
HOU XYZ
XYZ HOU


I think I am clear now... 
Sorry about the wrong test data earlier... 


Thanks,

- Kirti 





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  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: Corrected SQL Question...

[rgaffuri]

I think its easier if you do it cross-tab

AUS DAL DAL AUS

Is that acceptable? 
Or just select

AUS DAL

If it also has a DAL AUS

Are either of those metods acceptable? If so, pick one and Ill show you how to do it. 
 
 From: Deshpande, Kirti [EMAIL PROTECTED]
 Date: 2003/03/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 AUS
 DAL HOU
 DAL LIT
 DAL XYZ
 HOU AUS
 HOU DAL
 HOU LIT
 HOU XYZ
 LIT DAL
 
 C1  C2
 --- ---
 LIT HOU
 XYZ DAL
 XYZ HOU
 
 14 rows selected.
 
 SQL 
 
 Here is what is required:
 
 C1  C2
 --- ---
 AUS DAL
 DAL AUS
 AUS HOU
 HOU AUS
 DAL HOU
 HOU DAL
 DAL LIT
 LIT DAL
 DAL XYZ
 XYZ DAL
 HOU LIT
 LIT HOU
 HOU XYZ
 XYZ HOU
 
 
 I think I am clear now... 
 Sorry about the wrong test data earlier... 
 
 
 Thanks,
 
 - Kirti 
 
 
 
 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Deshpande, Kirti
   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: [EMAIL PROTECTED]
  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).