Re: sql question

2004-01-13 Thread 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-us&page=byoa/prem&ST=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).


Re: sql question (RESEND)

2004-01-12 Thread 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
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBt

Re: sql question

2004-01-12 Thread 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
ICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg

RE: sql question

2004-01-12 Thread 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-us&page=byoa/prem&ST=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

2004-01-12 Thread 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-us&page=byoa/prem&ST=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

2004-01-12 Thread 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-us&page=byoa/prem&ST=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 : How to retrieve the File_name without Directorie P

2003-07-23 Thread 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


Re: SQL question - crosstab in oracle

2003-07-08 Thread 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

2003-04-04 Thread 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 o

RE: SQL Question

2003-04-03 Thread 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: SQL Question

2003-04-03 Thread 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 ???

2003-03-06 Thread Wolfgang Breitling
A ZERO length varchar is treated as NULL

so your second query should be select count(*) from cli_clients
where trim(client_company) is null
and cli_id in  (257, 396, 727);
At 12:09 PM 3/6/2003 -0800, you wrote:
Hi, I got a SQL question (9i on Red Hat), commands
shown below.  The first sql returns 3 rows with value
1, so trim(client_company) = '', how come the 2nd sql
doesn't return anything??
SQL>  select decode(trim(client_company), '', 1, ' ',
2, null, 3, 4) from cli_clients where
cli_id in  (257, 396, 727);
DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4)
---
  1
  1
  1
3 rows selected.

SQL>  select count(*) from cli_clients where
trim(client_company) = '' and cli_id in  (257,
396, 727);
 COUNT(*)
-
0
1 row selected.


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

2003-03-06 Thread Stephane Faroult
Andrea Oracle wrote:
> 
> Hi, I got a SQL question (9i on Red Hat), commands
> shown below.  The first sql returns 3 rows with value
> 1, so trim(client_company) = '', how come the 2nd sql
> doesn't return anything??
> 
> SQL>  select decode(trim(client_company), '', 1, ' ',
> 2, null, 3, 4) from cli_clients where
> cli_id in  (257, 396, 727);
> 
> DECODE(TRIM(CLIENT_COMPANY),'',1,'',2,NULL,3,4)
> ---
>   1
>   1
>   1
> 
> 3 rows selected.
> 
> SQL>  select count(*) from cli_clients where
> trim(client_company) = '' and cli_id in  (257,
> 396, 727);
> 
>  COUNT(*)
> -
> 0
> 
> 1 row selected.
> 


Because NULLs (and '' is a NULL string) can be DECODEdbut are never
equal to anything.

-- 
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: SQL question

2003-02-24 Thread Richard Huntley
Title: RE: SQL question





Just trap the error and ignore it or add some other code for that particular situation, i.e.


BEGIN
 INSERT INTO A
    VALUES (1);
 
 EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN   -- account already exists
   NULL; 
END;


-Original Message-
From: Rick Stephenson [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 24, 2003 3:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question



Sorry, I guess I could have been a little more clear.


Another example:


Table Employee:
  Emp_id number primary key -- generated with a sequence
  Emp_name   varchar2(20) unique


Table Employee_log:
  Emp_id number primary key
  Time_stamp date primary key
  Emp_stats  varchar2(50)


A process receives the employee name, and other information that needs to be
stored in the table employee_log.  The process needs to retrieve the emp_id
from the employee table, so it does a lookup.  If the employee exists, the
emp_id is retrieved and the information is then inserted into the
employee_log table.  If the employee does not currently exist, a new
employee is added to the table employee.  


We run into problems when we have many concurrent processes running and more
than one process receives the same employee name.  They both do a lookup and
they both conclude the employee does not exist.  Thus, they both try and do
an insert into the employee table.  One will succeed and the other will
fail.


Is there away to avoid this scenario?


I hope I made this a little clearer.


Thanks,


Rick Stephenson


-Original Message-
Sent: Monday, February 24, 2003 1:05 PM
To: Multiple recipients of list ORACLE-L


Rick - What about selecting the primary key for your table from a sequence?
Oracle will ensure each session receives a unique number. 
 
What is your overall goal? 




Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, February 24, 2003 1:50 PM
To: Multiple recipients of list ORACLE-L




OS: Solaris 2.8


Database: Oracle 9.2.0.2


 


Situation in chronological order


Connection A: select * from table A where id = 1;  Result: no rows returned
-- This means I need to insert the row, as it does not exists yet.


Connection B: select * from table A where id = 1;  Result: no rows returned
-- This means I need to insert the row, as it does not exists yet.


Connection A: insert into table A(id) values = 1;  Result: 1 row inserted


Connection B: insert into table A(id) values = 1;  Result: Unique constraint
violated  --  This is the problem.  How do I avoid this happening?


 


Question:  How can I force connection B to wait for connection A to insert
the new row before it does the select?


 


If I were updating the row, I could use the "for update" clause to force the
wait.  Is there a clean way to do that for an insert?


 


Thanks for your help,


 


Rick Stephenson


 




This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to which they are addressed.
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. If you are not the intended recipient you are
notified that disclosing, copying, forwarding or otherwise distributing or
taking any action in reliance on the contents of this information is
strictly prohibited. 




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



This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to which they are addressed.
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. If you are not the intended recipient you are
notified that disclosing, copying, forwarding or otherwise distributing or
taking any action in reliance on the contents of

Re: SQL question

2003-02-24 Thread Ferenc Mantfeld
Try to separate the employee-lookup-and-create into separate procedure. In
the procedure, if the lookup does not find the employee, then call another
procedure with an autonomous transaction to create the employee, that way
the employee creation does not become part of the master transaction, is
lightning quick, and greatly reduces the possibility of two processes
creating the exact same employee record. Not guaranteed, but in 99.99% of
the cases, this should work fine.

Is this a packaged application, or can you make changes to the code that is
invoked ?

Ferenc Mantfeld
Dreaming costs you nothing. Not dreaming costs you everything.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, February 25, 2003 7:54 AM


> Sorry, I guess I could have been a little more clear.
>
> Another example:
>
> Table Employee:
>   Emp_id number primary key -- generated with a sequence
>   Emp_name   varchar2(20) unique
>
> Table Employee_log:
>   Emp_id number primary key
>   Time_stamp date primary key
>   Emp_stats  varchar2(50)
>
> A process receives the employee name, and other information that needs to
be
> stored in the table employee_log.  The process needs to retrieve the
emp_id
> from the employee table, so it does a lookup.  If the employee exists, the
> emp_id is retrieved and the information is then inserted into the
> employee_log table.  If the employee does not currently exist, a new
> employee is added to the table employee.
>
> We run into problems when we have many concurrent processes running and
more
> than one process receives the same employee name.  They both do a lookup
and
> they both conclude the employee does not exist.  Thus, they both try and
do
> an insert into the employee table.  One will succeed and the other will
> fail.
>
> Is there away to avoid this scenario?
>
> I hope I made this a little clearer.
>
> Thanks,
>
> Rick Stephenson
>
> -Original Message-
> Sent: Monday, February 24, 2003 1:05 PM
> To: Multiple recipients of list ORACLE-L
>
> Rick - What about selecting the primary key for your table from a
sequence?
> Oracle will ensure each session receives a unique number.
>
> What is your overall goal?
>
>
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> -Original Message-
> Sent: Monday, February 24, 2003 1:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> OS: Solaris 2.8
>
> Database: Oracle 9.2.0.2
>
>
>
> Situation in chronological order
>
> Connection A: select * from table A where id = 1;  Result: no rows
returned
> -- This means I need to insert the row, as it does not exists yet.
>
> Connection B: select * from table A where id = 1;  Result: no rows
returned
> -- This means I need to insert the row, as it does not exists yet.
>
> Connection A: insert into table A(id) values = 1;  Result: 1 row inserted
>
> Connection B: insert into table A(id) values = 1;  Result: Unique
constraint
> violated  --  This is the problem.  How do I avoid this happening?
>
>
>
> Question:  How can I force connection B to wait for connection A to insert
> the new row before it does the select?
>
>
>
> If I were updating the row, I could use the "for update" clause to force
the
> wait.  Is there a clean way to do that for an insert?
>
>
>
> Thanks for your help,
>
>
>
> Rick Stephenson
>
>
>
>
>
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to which they are
addressed.
> This message contains confidential information and is intended only for
the
> individual named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately by e-mail if you have received this e-mail by mistake and
delete
> this e-mail from your system. If you are not the intended recipient you
are
> notified that disclosing, copying, forwarding or otherwise distributing or
> taking any action in reliance on the contents of this information is
> strictly prohibited.
>
>
>
> --
> 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).
>
>
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to which they are
addressed.
> This message contains confidential information and is intended only 

Re: SQL question

2003-02-24 Thread Stephane Faroult
Rick Stephenson wrote:
> 
> Sorry, I guess I could have been a little more clear.
> 
> Another example:
> 
> Table Employee:
>   Emp_id number primary key -- generated with a sequence
>   Emp_name   varchar2(20) unique
> 
> Table Employee_log:
>   Emp_id number primary key
>   Time_stamp date primary key
>   Emp_stats  varchar2(50)
> 
> A process receives the employee name, and other information that needs to be
> stored in the table employee_log.  The process needs to retrieve the emp_id
> from the employee table, so it does a lookup.  If the employee exists, the
> emp_id is retrieved and the information is then inserted into the
> employee_log table.  If the employee does not currently exist, a new
> employee is added to the table employee.
> 
> We run into problems when we have many concurrent processes running and more
> than one process receives the same employee name.  They both do a lookup and
> they both conclude the employee does not exist.  Thus, they both try and do
> an insert into the employee table.  One will succeed and the other will
> fail.
> 
> Is there away to avoid this scenario?
> 
> I hope I made this a little clearer.
> 
> Thanks,
> 
> Rick Stephenson
> 
> -Original Message-
> Sent: Monday, February 24, 2003 1:05 PM
> To: Multiple recipients of list ORACLE-L
> 
> Rick - What about selecting the primary key for your table from a sequence?
> Oracle will ensure each session receives a unique number.
> 
> What is your overall goal?
> 
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
> 
> -Original Message-
> Sent: Monday, February 24, 2003 1:50 PM
> To: Multiple recipients of list ORACLE-L
> 
> OS: Solaris 2.8
> 
> Database: Oracle 9.2.0.2
> 
> 
> 
> Situation in chronological order
> 
> Connection A: select * from table A where id = 1;  Result: no rows returned
> -- This means I need to insert the row, as it does not exists yet.
> 
> Connection B: select * from table A where id = 1;  Result: no rows returned
> -- This means I need to insert the row, as it does not exists yet.
> 
> Connection A: insert into table A(id) values = 1;  Result: 1 row inserted
> 
> Connection B: insert into table A(id) values = 1;  Result: Unique constraint
> violated  --  This is the problem.  How do I avoid this happening?
> 
> 
> 
> Question:  How can I force connection B to wait for connection A to insert
> the new row before it does the select?
> 
> 
> 
> If I were updating the row, I could use the "for update" clause to force the
> wait.  Is there a clean way to do that for an insert?
> 
> 
> 
> Thanks for your help,
> 
> 
> 
> Rick Stephenson

Rick,

 Given what I currently know of the state of the economy, I guess that
the insert will be a fairly rare occurrence ? I think that therefore
locking the employee table in exclusive mode is acceptable ?

I'd rather code something along the following lines :

   done := false;
   while not done
   loop
 insert into employee_log
 select emp_id, sysdate, your_data_here
 from employee
 where emp_name = input_name;
 if (sql%rowcount = 0)
 then
   begin
 lock table employee in exclusive mode nowait;
 insert into employee yadda yadda
 done :=true;
   exception
 when table_already_locked then null;
   end;
 else
   done := true;
 end if;
end loop;
commit;


   May be a bit hard on CPU; perhaps that adding a short pause when the
'table already locked by another session' exception is hit would be the
thing to do. Depends on how intensive all this is.

-- 
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: SQL question

2003-02-24 Thread Rick Stephenson
Sorry, I guess I could have been a little more clear.

Another example:

Table Employee:
  Emp_id number primary key -- generated with a sequence
  Emp_name   varchar2(20) unique

Table Employee_log:
  Emp_id number primary key
  Time_stamp date primary key
  Emp_stats  varchar2(50)

A process receives the employee name, and other information that needs to be
stored in the table employee_log.  The process needs to retrieve the emp_id
from the employee table, so it does a lookup.  If the employee exists, the
emp_id is retrieved and the information is then inserted into the
employee_log table.  If the employee does not currently exist, a new
employee is added to the table employee.  

We run into problems when we have many concurrent processes running and more
than one process receives the same employee name.  They both do a lookup and
they both conclude the employee does not exist.  Thus, they both try and do
an insert into the employee table.  One will succeed and the other will
fail.

Is there away to avoid this scenario?

I hope I made this a little clearer.

Thanks,

Rick Stephenson

-Original Message-
Sent: Monday, February 24, 2003 1:05 PM
To: Multiple recipients of list ORACLE-L

Rick - What about selecting the primary key for your table from a sequence?
Oracle will ensure each session receives a unique number. 
 
What is your overall goal? 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Monday, February 24, 2003 1:50 PM
To: Multiple recipients of list ORACLE-L



OS: Solaris 2.8

Database: Oracle 9.2.0.2

 

Situation in chronological order

Connection A: select * from table A where id = 1;  Result: no rows returned
-- This means I need to insert the row, as it does not exists yet.

Connection B: select * from table A where id = 1;  Result: no rows returned
-- This means I need to insert the row, as it does not exists yet.

Connection A: insert into table A(id) values = 1;  Result: 1 row inserted

Connection B: insert into table A(id) values = 1;  Result: Unique constraint
violated  --  This is the problem.  How do I avoid this happening?

 

Question:  How can I force connection B to wait for connection A to insert
the new row before it does the select?

 

If I were updating the row, I could use the "for update" clause to force the
wait.  Is there a clean way to do that for an insert?

 

Thanks for your help,

 

Rick Stephenson

 



This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to which they are addressed.
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. If you are not the intended recipient you are
notified that disclosing, copying, forwarding or otherwise distributing or
taking any action in reliance on the contents of this information is
strictly prohibited. 



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


This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to which they are addressed.
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. If you are not the intended recipient you are
notified that disclosing, copying, forwarding or otherwise distributing or
taking any action in reliance on the contents of this information is
strictly prohibited. 


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

Re: SQL question

2003-02-24 Thread Ferenc Mantfeld



Why not use a sequence to populate ID, and let it 
fire of a before insert trigger. code example below:
 
 
create sequence TAB1_PKSEQ ;
create or replace trigger test_pkgenBEFORE 
INSERT OR UPDATE OF col_id on TABLE_AFOR EACH ROWBEGINIF INSERTING 
THEN  SELECT TAB1_PKSEQ1.NextVal INTO :new.COL_ID FROM 
DUAL;
  /* or something like the above */END 
IF;END;/
I am assuming you have other columns to populate 
too, which is not the PK of the table.
 
Cheers :
Ferenc MantfeldDreaming costs you nothing. Not dreaming costs you 
everything.

  - Original Message - 
  From: 
  Rick 
  Stephenson 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Tuesday, February 25, 2003 6:49 
  AM
  Subject: SQL question
  
  
  OS: Solaris 2.8
  Database: Oracle 
  9.2.0.2
   
  Situation in chronological 
  order
  Connection A: select * from table 
  A where id = 1;  Result: no rows returned  -- This means I need to 
  insert the row, as it does not exists yet.
  Connection B: select * from table 
  A where id = 1;  Result: no rows returned  -- This means I need to 
  insert the row, as it does not exists yet.
  Connection A: insert into table 
  A(id) values = 1;  Result: 1 row inserted
  Connection B: insert into table 
  A(id) values = 1;  Result: Unique constraint violated  --  This 
  is the problem.  How do I avoid this happening?
   
  Question:  How can I force connection B to wait 
  for connection A to insert the new row before it does the 
  select?
   
  If I were updating the row, I could use the "for 
  update" clause to force the wait.  Is there a clean way to do that for an 
  insert?
   
  Thanks for your help,
   
  Rick Stephenson
   
  This email and any files transmitted with it are 
  confidential and intended solely for the use of the individual or entity to 
  which they are addressed. This message contains confidential information and 
  is intended only for the individual named. If you are not the named addressee 
  you should not disseminate, distribute or copy this e-mail. Please notify the 
  sender immediately by e-mail if you have received this e-mail by mistake and 
  delete this e-mail from your system. If you are not the intended recipient you 
  are notified that disclosing, copying, forwarding or otherwise distributing or 
  taking any action in reliance on the contents of this information is strictly 
  prohibited. 


RE: SQL question

2003-02-24 Thread DENNIS WILLIAMS
Rick - What about selecting the primary key for your table from a sequence?
Oracle will ensure each session receives a unique number. 
 
What is your overall goal? 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Monday, February 24, 2003 1:50 PM
To: Multiple recipients of list ORACLE-L



OS: Solaris 2.8

Database: Oracle 9.2.0.2

 

Situation in chronological order

Connection A: select * from table A where id = 1;  Result: no rows returned
-- This means I need to insert the row, as it does not exists yet.

Connection B: select * from table A where id = 1;  Result: no rows returned
-- This means I need to insert the row, as it does not exists yet.

Connection A: insert into table A(id) values = 1;  Result: 1 row inserted

Connection B: insert into table A(id) values = 1;  Result: Unique constraint
violated  --  This is the problem.  How do I avoid this happening?

 

Question:  How can I force connection B to wait for connection A to insert
the new row before it does the select?

 

If I were updating the row, I could use the "for update" clause to force the
wait.  Is there a clean way to do that for an insert?

 

Thanks for your help,

 

Rick Stephenson

 



This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to which they are addressed.
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. If you are not the intended recipient you are
notified that disclosing, copying, forwarding or otherwise distributing or
taking any action in reliance on the contents of this information is
strictly prohibited. 



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

2003-02-24 Thread Alan Davey
Why not just have Connection B trap the Unique Constrait Error and branch to some 
different code?  What would Connection B have done if it had found the record where 
id=1?

-- 

Alan Davey
[EMAIL PROTECTED]
718-482-4200 x106


On 2/24/2003 2:49 PM, Rick Stephenson <[EMAIL PROTECTED]> wrote:
>
>OS: Solaris 2.8
>Database: Oracle 9.2.0.2
> 
>Situation in chronological order
>Connection A: select * from table A where id = 1;  Result: no rows 
>returned  -- This means I need to insert the row, as it does not 
>exists yet.
>Connection B: select * from table A where id = 1;  Result: no rows 
>returned  -- This means I need to insert the row, as it does not 
>exists yet.
>Connection A: insert into table A(id) values = 1;  Result: 1 row 
>inserted
>Connection B: insert into table A(id) values = 1;  Result: Unique 
>constraint violated  --  This is the problem.  How do I avoid this 
>happening?
> 
>Question:  How can I force connection B to wait for connection A 
>to insert the new row before it does the select?
> 
>If I were updating the row, I could use the "for update" clause to 
>force the wait.  Is there a clean way to do that for an insert?
> 
>Thanks for your help,
> 
>Rick Stephenson
> 
>
>
>This email and any files transmitted with it are confidential and 
>intended solely for the use of the individual or entity to which 
>they are addressed. This message contains confidential information 
>and is intended only for the individual named. If you are not the 
>named addressee you should not disseminate, distribute or copy this 
>e-mail. Please notify the sender immediately by e-mail if you have 
>received this e-mail by mistake and delete this e-mail from your 
>system. If you are not the intended recipient you are notified that 
>disclosing, copying, forwarding or otherwise distributing or taking 
>any action in reliance on the contents of this information is strictly 
>prohibited.
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Alan Davey
  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 -- distinct, group by and order by

2003-02-07 Thread Stephane Faroult
Guang Mei wrote:
> 
> Hi:
> 
> I have a basic sql question about sql. I have the follwing four sqls and I
> am wondering why #3 "costs" less than #4 in explain plan.  #1 and #2 cost
> the same. How is "distinct"and "group by" treated internally by Oracle? Is
> #3 a better "optimized" sql than #4?
> 
> TIA.
> 
> Guang Mei
> 
> 1.  select id,NAME from project group by id,name ;
> 2. select distinct id,name from project ;
> 3. select id,NAME from project group by id,name order by id;
> 4. select distinct id,name from project order by id;
> 
> MT@atlas-SQL> desc project;
> Name  Null?Type
> - 
> 
> IDNOT NULL NUMBER
> NAME  NOT NULL VARCHAR2(128)
> 
> MT@atlas-SQL> select distinct id,name from project ;
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216
>   )
> 
>10   SORT (UNIQUE) (Cost=54 Card=12 Bytes=216)
>21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
>   16)
> 
> COMMONNAMENOT NULL VARCHAR2(16)
> MNEMONIC  NOT NULL CHAR(1)
> USE   NOT NULL CHAR(1)
> 
> MT@atlas-SQL> select id,NAME from project group by id,name ;
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216
>   )
> 
>10   SORT (GROUP BY) (Cost=54 Card=12 Bytes=216)
>21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
>   16)
> 
> MT@atlas-SQL> select id,NAME from project group by id,name order by id;
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216
>   )
> 
>10   SORT (GROUP BY) (Cost=54 Card=12 Bytes=216)
>21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
>   16)
> 
> MT@atlas-SQL>  select distinct id,name from project order by id;
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=107 Card=12 Bytes=21
>   6)
> 
>10   SORT (UNIQUE) (Cost=54 Card=12 Bytes=216)
>21 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
>   16)
> 

Mei,

I would disregard 'cost'; this is just what a more or less educated
guess in some algorithm coded by some developer. Elapsed time is real
(if nobody is computing finite elements or gzip'ing a few terabytes on
your machine while you are testing), and, other things being equal, go
for what requires the fewer buffer gets. Otherwise the plan answers your
question - noticed 'SORT' ? What is in between parentheses only
indicates what triggered the very same processing.

-- 
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: SQL question

2003-01-30 Thread Jared . Still
>>[EMAIL PROTECTED] wrote:
>> . never trust Vladimir Begun, check everything what he's saying :)
>> 
>> Trust?
>> 
>> I don't know you well enough to not trust you.

>May be 'trust' is not a right word here :) Sorry.

Mine was supposed to have a :).  Sorry.

> . never use the sql that looks cool but does not work properly
> . never tune a query that returns wrong result and compare its
>performance with that one that works correctly but slowly.
> 
> As you will see in another post, both queries
> return identical results for me on 8.1.7.

Check default NLS_DATE_FORMAT parameters of your session.


Yup, that was it.

Though I tend to always use mm/dd/ hh24:mi:ss unless
there's some particular reason to do otherwise.

Jared

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

2003-01-30 Thread Vladimir Begun
Looks like you are a master of telepathy too... :)

Khedr, Waleed wrote:

What about:

select count(count(*))
from emp
group by ename, job

Have fun :)


We do... :)
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
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: SQL question

2003-01-30 Thread Vladimir Begun
Jared

[EMAIL PROTECTED] wrote:

. never trust Vladimir Begun, check everything what he's saying :)

Trust?

I don't know you well enough to not trust you.


May be 'trust' is not a right word here :) Sorry.


. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
   performance with that one that works correctly but slowly.

As you will see in another post, both queries
return identical results for me on 8.1.7.


Check default NLS_DATE_FORMAT parameters of your session.

To summarize, agregation can be done using:

1. (for this particular case)
SELECT COUNT(
  DISTINCT(
 RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
  || RPAD(NVL(  job, ' '), 30 + NVL2(  job, 0, 1))
  || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
  )
) AS l
   FROM emp
/
2.
SELECT COUNT(*)
  FROM (
   SELECT DISTINCT
  ename
, job
, mydate
 FROM emp
   )
/
3.
SELECT COUNT(COUNT(*))
  FROM emp
 GROUP BY
   ename
 , job
 , mydate
/
4. SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;
unreliable solution (does not handle nulls and dates
properly)

SQL> SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
   2000

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM. HH24:MI:SS';

Session altered

SQL> SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
   6000

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




Re: SQL question

2003-01-30 Thread Jared . Still
. never trust Vladimir Begun, check everything what he's saying :)

Trust?

I don't know you well enough to not trust you.

. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
   performance with that one that works correctly but slowly.


As you will see in another post, both queries
return identical results for me on 8.1.7.

Jared






Vladimir Begun <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/30/2003 12:16 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
        Subject:    Re: SQL question


Jared

Windows 2k 9.2.0.1

534 hsecs
214 hsecs

Query I've used:

SELECT COUNT(
  DISTINCT(
 RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
  || RPAD(NVL(  job, ' '), 30 + NVL2(  job, 0, 1))
  || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
  )
) AS l
   FROM emp
/

As you can see it's tightly bound to table definition one
has to handle nulls for varchars/chars.

 L
-
  7000

Check the resources -- I have doubts that this query is a
winner :)

So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :)
. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
   performance with that one that works correctly but slowly.

Thanks!

Regards,
-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Vladimir Begun wrote:
> Jared
> 
> Jared Still wrote:
> 
>> Though not a dramatic difference, the CONCAT was faster
>> and less resource intensive than the inline view with GROUP BY.
>>
>> :)
> 
> 
> Ok, let it be like that, but your test does not check some
> other things, like common sense, logic, and session memory.
> Performance can vary as I mentioned sometimes can be
> neglected, however let's consider the tricks you made before
> your test:
> 
> 1. create index emp_idx on emp(ename, job, mydate); -- what for do
> you need it? It was not in the original problem definition. It's
> not used, however you created it, what's that for?
> 
> 2. Both queries give different results, that's what I mentioned --
> you just proved my words :) One must be very careful with that.
> Even DISTINCT can lose sometimes like in your example, but it does
> not mean that the logic of the application works correctly in
> case of || = CONCAT is used. Think also about an artificial limit
> your create -- each and every varchar has to be padded to it's
> maximum length (become CHAR) -- that optional and case dependent,
> however; all date and numeric columns have to be formatted
> otherwise you can face the same case like your your example.
> 
> SQL> SELECT COUNT(*) FROM emp;
> 
>COUNT(*)
> --
>   64000
> 
> SQL> select count(distinct(ename||job||mydate)) FROM emp;
> 
> COUNT(DISTINCT(ENAME||JOB||MYDATE))
> ---
> 2000
> 
> SQL> SELECT COUNT(*)
>2   FROM (
>3SELECT DISTINCT
>4 ename, job, mydate
>5FROM emp
>6   );
> 
>COUNT(*)
> --
>7000
> 

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




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

2003-01-30 Thread Jared . Still
> Ok, let it be like that, but your test does not check some
> other things, like common sense, logic, and session memory.
> Performance can vary as I mentioned sometimes can be
> neglected, however let's consider the tricks you made before
> your test:

Well, I've never claimed to be common.

And I didn't ignore session memory.  If the PGA and UGA memory
numbers are the same between runs, they don't appear in the
run_stats.sql query.

> 1. create index emp_idx on emp(ename, job, mydate); -- what for do
> you need it? It was not in the original problem definition. It's
> not used, however you created it, what's that for?

An index seemed reasonable to me.

But in the cause of fairness, I dropped the index and reran it
a couple times to allow for re-caching the blocks.  Similar results.

13:17:17 SQL>@th
389 hsecs
257 hsecs

PL/SQL procedure successfully completed.
13:17:25 SQL>@run_stats

NAME   RUN1   RUN2   DIFF
 -- -- --
LATCH.active checkpoint queue latch   2  1 -1
LATCH.redo writing6  5 -1
STAT...calls to get snapshot scn: kcmgss 12 11 -1
STAT...redo entries   8  7 -1
STAT...messages sent  1  0 -1
STAT...deferred (CURRENT) block cleanout  3  2 -1
 applications

LATCH.library cache  74 71 -3
STAT...db block changes  17 14 -3
STAT...consistent gets 3346   3343 -3
LATCH.redo allocation 9 12  3
STAT...session logical reads   3458   3462  4
LATCH.messages   11  6 -5
STAT...db block gets112119  7
STAT...enqueue requests  41 50  9
LATCH.sort extent pool   90100 10
STAT...enqueue releases  40 50 10
LATCH.enqueue hash chains80100 20
LATCH.cache buffers lru chain63 23-40
LATCH.loader state object freelist   20 60 40
LATCH.enqueues  160200 40
LATCH.checkpoint queue latch 90  7-83
LATCH.cache buffers chains 7662   7783121
STAT...redo size  20888  20756   -132
STAT...recursive cpu usage  391258   -133
STAT...physical reads   552792240
STAT...physical reads direct550790240
STAT...physical writes  550790240
STAT...physical writes direct   550790240
STAT...physical writes non checkpoint   550790240
STAT...session pga memory 0  15848  15848
STAT...session pga memory max 0  15848  15848

31 rows selected.


> 2. Both queries give different results, that's what I mentioned --
> you just proved my words :) One must be very careful with that.
> Even DISTINCT can lose sometimes like in your example, but it does
> not mean that the logic of the application works correctly in
> case of || = CONCAT is used. Think also about an artificial limit
> your create -- each and every varchar has to be padded to it's
> maximum length (become CHAR) -- that optional and case dependent,
> however; all date and numeric columns have to be formatted
> otherwise you can face the same case like your your example.
>
> SQL> SELECT COUNT(*) FROM emp;
>
>COUNT(*)
>--
>   64000
>
>SQL> select count(distinct(ename||job||mydate)) FROM emp;
>
>COUNT(DISTINCT(ENAME||JOB||MYDATE))
>---
> 2000
>SQL> SELECT COUNT(*)
>2   FROM (
>3SELECT DISTINCT
>4 ename, job, mydate
>5FROM emp
>6   );
>
>COUNT(*)
>--
>7000

Interesting. My results correspond, I don't know why the difference.

13:11:04 SQL>set echo on
13:11:08 SQL>@q1
13:11:09 SQL>select count(distinct(ename||job||mydate))
13:11:09   2  from emp
13:11:09   3  /

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
   7000

1 row selected.

13:11:09 SQL>@q2
13:11:12 SQL>
13:11:12 SQL>
13:11:12 SQL>SELECT COUNT(*)
13:11:12   2  FROM (
13:11:12   3  SELECT DISTINCT
13:11:12   4  ename, job, mydate
13:11:12   5  FROM emp
13:11:12   6  )
13:11:12   7  /

  COUNT(*)
--
   

RE: SQL question

2003-01-30 Thread Jared . Still
Nah, I'm tired of it now.

It was an interesting diversion.

Jared






"Khedr, Waleed" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/30/2003 11:34 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
        Subject:RE: SQL question


What about:

select count(count(*))
from emp
group by ename, job



Have fun :)

Waleed

-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L


Jared

Jared Still wrote:
> Though not a dramatic difference, the CONCAT was faster
> and less resource intensive than the inline view with GROUP BY.
> 
> :)

Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
--
   64000

SQL> select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
 2000

SQL> SELECT COUNT(*)
2   FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6   );

COUNT(*)
--
7000

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




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

2003-01-30 Thread Vladimir Begun
Jared

Windows 2k 9.2.0.1

534 hsecs
214 hsecs

Query I've used:

SELECT COUNT(
 DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
 || RPAD(NVL(  job, ' '), 30 + NVL2(  job, 0, 1))
 || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
 )
   ) AS l
  FROM emp
/

As you can see it's tightly bound to table definition one
has to handle nulls for varchars/chars.

L
-
 7000

Check the resources -- I have doubts that this query is a
winner :)

So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :)
. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
  performance with that one that works correctly but slowly.

Thanks!

Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Vladimir Begun wrote:

Jared

Jared Still wrote:


Though not a dramatic difference, the CONCAT was faster
and less resource intensive than the inline view with GROUP BY.

:)



Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL> SELECT COUNT(*) FROM emp;

   COUNT(*)
--
  64000

SQL> select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
2000

SQL> SELECT COUNT(*)
   2   FROM (
   3SELECT DISTINCT
   4 ename, job, mydate
   5FROM emp
   6   );

   COUNT(*)
--
   7000



--
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: SQL question

2003-01-30 Thread Khedr, Waleed
What about:

select count(count(*))
from emp
group by ename, job



Have fun :)

Waleed

-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L


Jared

Jared Still wrote:
> Though not a dramatic difference, the CONCAT was faster
> and less resource intensive than the inline view with GROUP BY.
> 
> :)

Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
--
   64000

SQL> select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
 2000

SQL> SELECT COUNT(*)
2   FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6   );

COUNT(*)
--
7000

-- 
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).
-- 
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: SQL question

2003-01-30 Thread Vladimir Begun
Jared

Jared Still wrote:

Though not a dramatic difference, the CONCAT was faster
and less resource intensive than the inline view with GROUP BY.

:)


Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL> SELECT COUNT(*) FROM emp;

   COUNT(*)
--
  64000

SQL> select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
2000

SQL> SELECT COUNT(*)
   2   FROM (
   3SELECT DISTINCT
   4 ename, job, mydate
   5FROM emp
   6   );

   COUNT(*)
--
   7000

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




RE: SQL question

2003-01-30 Thread sundeep maini

SELECT COUNT(*)
  FROM ( SELECT DISTINCT col1, col2.
   FROM ..)

--- Charu Joshi <[EMAIL PROTECTED]> wrote:
> Thanks all,
> 
> My question was related more to the 'design' of SQL language. To my
> mind the
> expression COUNT(DISTINCT a,b) looked a natural extension of the
> syntax
> COUNT(DISTINCT a). Even COUNT(DISTINCT(a,b)) would look good enough
> to me.
> Probably it's too trivial a thing to bother about. Using the
> subquery would
> very well give the desired results.
> 
> I have been thinking of reading CJ Date and other experts' articles
> on the
> design (and limitations) of SQL, but couldn't find any good
> resources on the
> net. If you know of any links, then can you please let me know?
> 
> Thanks once again,
> Charu.
> 
> -Original Message-
> Sent: Wednesday, January 29, 2003 10:04 PM
> To: Multiple recipients of list ORACLE-L
> 
> Charu,
>   The COUNT() function requires a single expression. "ename, job" is
> not a valid expression. "ename||job" is a valid expression since it
> will
> return a single value.
>   Another alternative would be
> select count(*)
> from (select distinct ename, job from emp);
> 
> Dan Fink
> 
> -Original Message-
> Sent: Wednesday, January 29, 2003 11:19 AM
> To: Multiple recipients of list ORACLE-L
> 
> Hello Listers,
> 
> How to find out the COUNT of DISTINCT values of multiple columns?
> 
> For eg.
> 
> SQL> SELECT DISTINCT ename FROM emp;
> -- This works.
> 
> SQL> SELECT COUNT(DISTINCT ename) FROM emp;
> -- So does this.
> 
> SQL> SELECT DISTINCT ename, job FROM emp;
> -- And this too.
> 
> SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
> -- So why does this fail?
> 
> I can do a COUNT(DISTINCT ename || job), but that doesn't seem to
> be elegant
> way of doing it.
> 
> I have a feeling I might be missing some fairly basic syntax, but
> feeling
> dumb is better than suspense.
> 
> Thanks & regards,
> Charu.
> 
> *
> Disclaimer
> 
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
> 
> *
> Visit us at http://www.mahindrabt.com
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Charu Joshi
>   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: Fink, Dan
>   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 any attachments) contains 
> confidential information intended for a specific 
> individual and purpose, and is protected by law. 
> If you are not the intended recipient, you should 
> delete this message and are hereby notified that 
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it, 
> is strictly prohibited.
> 
> *
> Visit us at http://www.mahindrabt.com
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Charu Joshi
>   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 n

Re: SQL question

2003-01-30 Thread Jared Still

Vladimir,

Thanks I hadn't considered || as a function, though it is.

At first, I was going to take your word for it, but then decided
this would be an interesting test. :)

But first, I agree, you must know what you're looking for, neither
of these would work in all situations.

First, I built some test data:

--
drop table emp;

create table emp( ename, job  )
as
select table_name, column_name
from dba_tab_columns
where rownum < 1001
/

alter table emp add ( mydate date );
update emp set mydate = sysdate;
commit;

declare
   v_date date;
begin

   for f in 1 .. 5
   loop
  insert into emp
  select ename, job, sysdate
  from emp;
  dbms_lock.sleep(1);
   end loop;

   insert into emp
   select ename, job, null
   from emp;

end;
/

create index emp_idx on emp(ename, job, mydate);
-

This creates 64000 rows in emp.

For testing, I'm using Tom Kytes run_stats.sql and test_harness.sql.

The URL is something like govt.oracle.com/~tkyte/run_stats.html

Not sure, because my internet connection is down as I write this.

Below is the test harness code I used:

---
-- test_harness.sql
-- from Tom Kyte - asktom.oracle.com/~tkyte/runstats.html
-- see ~/oracle/dba/run_stats for all files


declare
l_start number;
--add any other variables you need here for the test... 
v_count integer;
begin
delete from run_stats;
commit;
-- start by getting a snapshot of the v$ tables
insert into run_stats select 'before', stats.* from stats;

-- and start timing...
l_start := dbms_utility.get_time;

-- for things that take a very small amount of time, I like to 
-- loop over it time and time again, to measure something "big"
-- if what you are testing takes a long time, loop less or maybe
-- not at all
for i in 1 .. 10
loop 
select count(distinct(ename||job||mydate)) into v_count
from emp;
end loop;

dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
   
-- get another snapshot and start timing again... 
insert into run_stats select 'after 1', stats.* from stats;

l_start := dbms_utility.get_time;

for i in 1 .. 10
loop 
SELECT COUNT(*) into v_count
FROM (
SELECT DISTINCT
ename, job, mydate
FROM emp
);
end loop;

dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
insert into run_stats select 'after 2', stats.* from stats;
end;
/

--

Now the results.  Run 1 uses CONCAT, Run 2 uses 
an inline view with Group by.

22:13:02 sherlock - jkstill@ts01 SQL> @th
1691 hsecs
2032 hsecs

PL/SQL procedure successfully completed.

22:13:49 sherlock - jkstill@ts01 SQL> @run_stats

NAME   RUN1   RUN2   DIFF
 -- -- --
STAT...consistent gets 3378   3379  1
STAT...db block changes  17 16 -1
LATCH.undo global data3  4  1
STAT...calls to get snapshot scn: kcmgss 23 22 -1
STAT...parse time elapsed 0  1  1
STAT...parse time cpu 0  1  1
STAT...deferred (CURRENT) block cleanout  3  2 -1
 applications

LATCH.active checkpoint queue latch   5  7  2
LATCH.virtual circuit queues  2  0 -2
LATCH.redo allocation13 18  5
LATCH.redo writing   22 27  5
LATCH.checkpoint queue latch 27 34  7
LATCH.messages   33 44 11
LATCH.session allocation 22 38 16
STAT...free buffer requested779761-18
LATCH.session idle bit   11 31 20
LATCH.shared pool 3 27 24
LATCH.multiblock read objects   312338 26
STAT...prefetched blocks607578-29
STAT...redo size  20964  21008 44
STAT...enqueue requests 441544103
STAT...enqueue releases 440544104
LATCH.sort extent pool  495599104
LATCH.library cache 241389148
LATCH.enqueue hash chains

RE: SQL question

2003-01-30 Thread Charu Joshi
Thanks all,

My question was related more to the 'design' of SQL language. To my mind the
expression COUNT(DISTINCT a,b) looked a natural extension of the syntax
COUNT(DISTINCT a). Even COUNT(DISTINCT(a,b)) would look good enough to me.
Probably it's too trivial a thing to bother about. Using the subquery would
very well give the desired results.

I have been thinking of reading CJ Date and other experts' articles on the
design (and limitations) of SQL, but couldn't find any good resources on the
net. If you know of any links, then can you please let me know?

Thanks once again,
Charu.

-Original Message-
Sent: Wednesday, January 29, 2003 10:04 PM
To: Multiple recipients of list ORACLE-L

Charu,
The COUNT() function requires a single expression. "ename, job" is
not a valid expression. "ename||job" is a valid expression since it will
return a single value.
Another alternative would be
select count(*)
from (select distinct ename, job from emp);

Dan Fink

-Original Message-
Sent: Wednesday, January 29, 2003 11:19 AM
To: Multiple recipients of list ORACLE-L

Hello Listers,

How to find out the COUNT of DISTINCT values of multiple columns?

For eg.

SQL> SELECT DISTINCT ename FROM emp;
-- This works.

SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.

SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.

SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.

I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.

Thanks & regards,
Charu.

*
Disclaimer

This message (including any attachments) contains
confidential information intended for a specific
individual and purpose, and is protected by law.
If you are not the intended recipient, you should
delete this message and are hereby notified that
any disclosure, copying, or distribution of this
message, or the taking of any action based on it,
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Charu Joshi
  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: Fink, Dan
  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 any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com



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

2003-01-29 Thread Vladimir Begun
[EMAIL PROTECTED] wrote:

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be 

elegant


way of doing it.


elegant = simple, concise, easy to understand.

Looks elegant to me.


Jared, it just looks that that...

CONCAT = || yet another function call, yet another piece of
code, yet another byte of memory... If you have more than
two columns? If some of those are numeric, date? If ename
is Smith and job is Smith and both can be nullable? :)
NVLs? NVL2s? I think this approach is only valid when one
really understands what she/he is looking for. Could be
good for FBI, CHECK constraints but it's very risky and
resource consuming (depends, can be neglected) for
queries.

It's better to write something that just looks ugly but
works faster and reliably. Simple, fast, and covers all
'strange' cases:

SELECT COUNT(*)
  FROM (
   SELECT DISTINCT
  ename
, job
 FROM emp
   )
/

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




Re: SQL question

2003-01-29 Thread Jared . Still
> I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be 
elegant
> way of doing it.

elegant = simple, concise, easy to understand.

Looks elegant to me.

Jared






"Charu Joshi" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/29/2003 10:19 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:SQL question


Hello Listers,

How to find out the COUNT of DISTINCT values of multiple columns?

For eg.

SQL> SELECT DISTINCT ename FROM emp;
-- This works.

SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.

SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.

SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be 
elegant
way of doing it.

I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.

Thanks & regards,
Charu.

*


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

2003-01-29 Thread Jamadagni, Rajendra
Title: RE: SQL question





The non-working code in your example should be 


select count(*)
from (select distinct ename,job from emp)
/


It appears that cound takes only one parameter ... not two.
Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Charu Joshi [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 29, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL question



Hello Listers,


How to find out the COUNT of DISTINCT values of multiple columns?


For eg.


SQL> SELECT DISTINCT ename FROM emp;
-- This works.


SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.


SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.


SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?


I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.


I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.


Thanks & regards,
Charu.


*
Disclaimer


This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.


*
Visit us at http://www.mahindrabt.com




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



RE: SQL question

2003-01-29 Thread Koivu, Lisa
Title: RE: SQL question





Elegant or not, here's how I'd do it


select count(*) from 
(select distinct ename, job from emp);



-Original Message-
From: Charu Joshi [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 29, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL question



Hello Listers,


How to find out the COUNT of DISTINCT values of multiple columns?


For eg.


SQL> SELECT DISTINCT ename FROM emp;
-- This works.


SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.


SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.


SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?


I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.


I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.


Thanks & regards,
Charu.


*
Disclaimer


This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.


*
Visit us at http://www.mahindrabt.com




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

2003-01-29 Thread Rachna Vaidya
And, can you have two columns as arguements for COUNT?
I guess its either one column or rows

+Rachna

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 29, 2003 1:19 PM


> Hello Listers,
>
> How to find out the COUNT of DISTINCT values of multiple columns?
>
> For eg.
>
> SQL> SELECT DISTINCT ename FROM emp;
> -- This works.
>
> SQL> SELECT COUNT(DISTINCT ename) FROM emp;
> -- So does this.
>
> SQL> SELECT DISTINCT ename, job FROM emp;
> -- And this too.
>
> SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
> -- So why does this fail?
>
> I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
elegant
> way of doing it.
>
> I have a feeling I might be missing some fairly basic syntax, but feeling
> dumb is better than suspense.
>
> Thanks & regards,
> Charu.
>
> *
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *
> Visit us at http://www.mahindrabt.com
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Charu Joshi
>   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: Rachna Vaidya
  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

2003-01-29 Thread Whittle Jerome Contr NCI
Title: RE: SQL question






Joshi,


SELECT count(*) 

FROM (SELECT count(*) 

  FROM flight_legs 

  GROUP BY d_actual_time, event_type);


SELECT count(*) 

FROM (SELECT DISTINCT d_actual_time, event_type

  FROM flight_legs );


The first one took about 37 seconds in returning a count of 357331. The second statement was about 10 seconds quicker.


Jerry Whittle

ASIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   Charu Joshi [SMTP:[EMAIL PROTECTED]]


Hello Listers,


How to find out the COUNT of DISTINCT values of multiple columns?


For eg.


SQL> SELECT DISTINCT ename FROM emp;

-- This works.


SQL> SELECT COUNT(DISTINCT ename) FROM emp;

-- So does this.


SQL> SELECT DISTINCT ename, job FROM emp;

-- And this too.


SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;

-- So why does this fail?


I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant

way of doing it.


I have a feeling I might be missing some fairly basic syntax, but feeling

dumb is better than suspense.


Thanks & regards,

Charu.





RE: SQL question

2003-01-29 Thread Fink, Dan
Charu,
The COUNT() function requires a single expression. "ename, job" is
not a valid expression. "ename||job" is a valid expression since it will
return a single value.
Another alternative would be
select count(*)
from (select distinct ename, job from emp);

Dan Fink

-Original Message-
Sent: Wednesday, January 29, 2003 11:19 AM
To: Multiple recipients of list ORACLE-L


Hello Listers,

How to find out the COUNT of DISTINCT values of multiple columns?

For eg.

SQL> SELECT DISTINCT ename FROM emp;
-- This works.

SQL> SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.

SQL> SELECT DISTINCT ename, job FROM emp;
-- And this too.

SQL> SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.

I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.

Thanks & regards,
Charu.

*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Charu Joshi
  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: Fink, Dan
  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: SQL Question

2003-01-28 Thread Stephane Faroult
The first query also says 'from user_group_members' and the second one 'from 
app_users' ... I am not sure that the comparison is anything but confusing ...

Looks like the implicitly converted varchar2() column which contains '***', 'N/A' or 
the like ...

>
>The first query says "where FK_USER in
>(44541,41402,41813)" and the second
>query says "where PEN_ID in (44541,41402,41813)"...
>
>
>- Original Message -
>To: "Multiple recipients of list ORACLE-L"
><[EMAIL PROTECTED]>
>Sent: Monday, January 27, 2003 11:43 PM
>
>
>> Hi,
>>
>> My brain is slow today Can someone help me ?
>>
>> I can do :
>>
>> select idu+1 from user_group_members where
>fk_user
>> in(44541,41402,41813) ;
>>
>>  IDU+1
>> --
>>  41411
>>  41821
>>  44546
>>
>> But I can't do :
>> select 'insert into XXX
>(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
>('||IDU + 1
>||','||PEN_ID||',sysdate,'||FK_APPLICATION||');'
>from app_users
>> where pen_id in (44541,41402,41813) ;
>>
>> I've got on IDU+1 :
>>
>> ERROR at line 1:
>> ORA-01722: invalid number
>>
>>
>> Best Regards
>> Henrik
>>
>> --
>>
>-
>> There's fun in being serious.
>>
>> -- Wynton Marsalis
>>
>> Henrik Ekenberg   
>Anoto AB
>>
>>
>> --
>> Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>> --
>> Author: Henrik Ekenber
>>   INET: [EMAIL PROTECTED]>"
><[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: Tim Gorman
>  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).
>---
>---
>---
>--
>---
>--


Regards,

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

2003-01-28 Thread Tim Gorman
The first query says "where FK_USER in (44541,41402,41813)" and the second
query says "where PEN_ID in (44541,41402,41813)"...

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, January 27, 2003 11:43 PM


> Hi,
>
> My brain is slow today Can someone help me ?
>
> I can do :
>
> select idu+1 from user_group_members where fk_user
> in(44541,41402,41813) ;
>
>  IDU+1
> --
>  41411
>  41821
>  44546
>
> But I can't do :
> select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
> where pen_id in (44541,41402,41813) ;
>
> I've got on IDU+1 :
>
> ERROR at line 1:
> ORA-01722: invalid number
>
>
> Best Regards
> Henrik
>
> --
> --
-
> There's fun in being serious.
>
> -- Wynton Marsalis
>
> Henrik EkenbergAnoto AB
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Henrik Ekenber
>   INET: [EMAIL PROTECTED]>" <[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: Tim Gorman
  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

2003-01-28 Thread Dmitrii CRETU
try this ("(IDU + 1)"):

select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
(' || (IDU + 1)
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;

HEheac> Hi,

HEheac> My brain is slow today Can someone help me ?

HEheac> I can do :

HEheac> select idu+1 from user_group_members where fk_user
HEheac> in(44541,41402,41813) ;

HEheac>  IDU+1
HEheac> --
HEheac>  41411
HEheac>  41821
HEheac>  44546

HEheac> But I can't do :
HEheac> select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU 
+ 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
HEheac> where pen_id in (44541,41402,41813) ;

HEheac> I've got on IDU+1 :

HEheac> ERROR at line 1:
HEheac> ORA-01722: invalid number


HEheac> Best Regards
HEheac> Henrik

HEheac> -- 
HEheac> ---
HEheac> There's fun in being serious.

HEheac> -- Wynton Marsalis

HEheac> Henrik EkenbergAnoto AB


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


Best regards,
 Dmitrii

[EMAIL PROTECTED]


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

2003-01-28 Thread Nirmal Kumar Muthu Kumaran
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||(IDU + 1 )||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from
app_users
where pen_id in (44541,41402,41813) ;

IDU + 1 must be replaced by (IDU + 1).

HTH.
Nirmal.,

-Original Message-
Sent: Tuesday, January 28, 2003 9:44 AM
To: Multiple recipients of list ORACLE-L


Hi,

My brain is slow today Can someone help me ?

I can do :

select idu+1 from user_group_members where fk_user
in(44541,41402,41813) ;

 IDU+1
--
 41411
 41821
 44546

But I can't do :
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;

I've got on IDU+1 :

ERROR at line 1:
ORA-01722: invalid number


Best Regards
Henrik

-- 
---
There's fun in being serious.

-- Wynton Marsalis

Henrik EkenbergAnoto AB


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Henrik Ekenber
  INET: [EMAIL PROTECTED]>" <[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: Nirmal Kumar  Muthu Kumaran
  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

2003-01-28 Thread Naveen Nahata



Try
select 'insert into XXX 
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU + 
1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from 
app_users
where pen_id in 
(44541,41402,41813);
-Original Message-From: Henrik Ekenberg 
<[EMAIL PROTECTED]>[mailto:[EMAIL PROTECTED]]Sent: Tuesday, 
January 28, 2003 12:14 PMTo: Multiple recipients of list 
ORACLE-LSubject: SQL QuestionHi,My brain is slow 
today Can someone help me ?I can do :select idu+1 from 
user_group_members where fk_userin(44541,41402,41813) 
; 
IDU+1-- 
41411 41821 
44546But I can't do :select 'insert into XXX 
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_userswhere pen_id 
in (44541,41402,41813) ;I've got on IDU+1 :ERROR at line 
1:ORA-01722: invalid numberBest 
RegardsHenrik-There's 
fun in being serious.-- Wynton MarsalisHenrik 
Ekenberg    
Anoto AB--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Henrik 
Ekenber  INET: [EMAIL PROTECTED]>" 
<[EMAIL PROTECTED]Fat City Network Services    -- 
858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso 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.


RE: SQL question avoiding 2 views and not in

2002-12-16 Thread Stephane Paquette
Thanks for the where clause and to all who respond,
I'll check into fine grained access control
(dbms_rls). 

 --- "Khedr, Waleed" <[EMAIL PROTECTED]> a écrit :
> Add this to where clause:
> 
>  group <> decode(user,'typical',380,-100)
> 
> Instead of -100 use any number not used by the
> groups.
> 
> Also read about contexts and grain level security.
> 
> Waleed
> 
> -Original Message-
> Sent: Friday, December 13, 2002 2:59 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi,
> 
> We have a lot of views. Now the users have a new
> requirement, only the user 'admin' can see all the
> data from the views. The user 'typical' must see all
> data except the one from group 380.
> 
> A basic solution is to create 2 sets of views with
> one
> set having a group number <> 380.
> 
> I'm looking for a solution to have only one set of
> views. It's friday afternoon and I have no
> inspiration
> :-(
> 
> TIA
> 
> 
> =
> Stéphane Paquette
> DBA Oracle et DB2, consultant entrepôt de données
> Oracle and DB2 DBA, datawarehouse consultant
> [EMAIL PROTECTED]
> 
>
__
> Lèche-vitrine ou lèche-écran ?
> magasinage.yahoo.ca
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?Stephane=20Paquette?=
>   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.com
> -- 
> 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).
>  

=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  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 avoiding 2 views and not in

2002-12-15 Thread Mark Richard
I have also been on a project which used fine-grained access control.  The
project was a reporting application for a large organisation and one of the
problems we found we dealing with the various levels of security.  The
organisation has ~10,000 cost centres and some users could see only a very
small branch, whilst others could see the entire structure, whilst others
could see almost everything except for a few very high level branches.
There was also a different way of providing security (ie: not cost centre
related) and many users had a combination of both to be applied.

One of the biggest "gotchas" was performance tuning the application.
Because of the complexity of our security implementation we initially fell
in the trap of tuning the query with no security added (a couple of users
had the clause "and 1 = 1").  Naturally when other users connected the
explain plan changed and performance was pretty average.  It took a while
to index all tables in such a way that it worked well for every type of
security clause.  So, yes there can be a performance hit and that hit can
vary dramatically depending on what type of clause you are adding.

Having said that, it did work and was transparent to the front end,
allowing users to create their own queries / reports and still be bound by
the security model.

Cheers,
 Mark.



   

[EMAIL PROTECTED] 

.tenet.edu   To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
Sent by: cc:   

[EMAIL PROTECTED]         Subject: RE: SQL question 
avoiding 2 views and not in 
   

   

14/12/2002 10:03   

Please respond to  

ORACLE-L   

   

   






Lisa,

A couple of years ago, when I was a consultant, I implemented Application
Context and Fine-Grained Access Control, AKA Row Level Security for a
client.

Since it causes a predicate to be appended to the Where clause of every SQL
statement issued against the tables having a Security Policy, I guess
performance could be impacted if you didn't index the columns referenced by
the appended predicates.  We never noticed a bit of degradation in our
testing, but we were careful about the indexing.

I left that project before it went into production, so I don't know the
ultimate outcome.  However, I'd sure use FGAC again, if the need arises, it
works very well.  Actually, I probably *will* use it on a couple of our 3rd
Party apps here which don't enforce security to the degree that we require.
I'll let y'all know how it performs.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]




  "Koivu, Lisa"

  <[EMAIL PROTECTED]>

  Sent by:         cc:

  [EMAIL PROTECTED] Subject:  RE: SQL question
avoiding 2 views and not in


  12/13/2002 03:38

  PM

  Please respond to

  ORACLE-L






Has anyone used context and fine-grained security?  I seem to remember the
performance hit was not minimal when using this functionality.





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

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread JApplewhite

Lisa,

A couple of years ago, when I was a consultant, I implemented Application
Context and Fine-Grained Access Control, AKA Row Level Security for a
client.

Since it causes a predicate to be appended to the Where clause of every SQL
statement issued against the tables having a Security Policy, I guess
performance could be impacted if you didn't index the columns referenced by
the appended predicates.  We never noticed a bit of degradation in our
testing, but we were careful about the indexing.

I left that project before it went into production, so I don't know the
ultimate outcome.  However, I'd sure use FGAC again, if the need arises, it
works very well.  Actually, I probably *will* use it on a couple of our 3rd
Party apps here which don't enforce security to the degree that we require.
I'll let y'all know how it performs.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



   

  "Koivu, Lisa"

  <[EMAIL PROTECTED]> 

  Sent by: cc: 

          [EMAIL PROTECTED] Subject:  RE: SQL question avoiding 2 
views and not in  
   

   

  12/13/2002 03:38 

  PM   

  Please respond to

  ORACLE-L 

   

   




Has anyone used context and fine-grained security?  I seem to remember the
performance hit was not minimal when using this functionality.





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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 avoiding 2 views and not in

2002-12-13 Thread Koivu, Lisa
Title: RE: SQL question avoiding 2 views and not in 






Has anyone used context and fine-grained security?  I seem to remember the performance hit was not minimal when using this functionality. 

-Original Message-

From:   Khedr, Waleed [SMTP:[EMAIL PROTECTED]]

Sent:   Friday, December 13, 2002 4:14 PM

To: Multiple recipients of list ORACLE-L

Subject:    RE: SQL question avoiding 2 views and not in 


Add this to where clause:


 group <> decode(user,'typical',380,-100)


Instead of -100 use any number not used by the groups.


Also read about contexts and grain level security.


Waleed


-Original Message-

Sent: Friday, December 13, 2002 2:59 PM

To: Multiple recipients of list ORACLE-L



Hi,


We have a lot of views. Now the users have a new

requirement, only the user 'admin' can see all the

data from the views. The user 'typical' must see all

data except the one from group 380.


A basic solution is to create 2 sets of views with one

set having a group number <> 380.


I'm looking for a solution to have only one set of

views. It's friday afternoon and I have no inspiration

:-(


TIA



=

Stéphane Paquette

DBA Oracle et DB2, consultant entrepôt de données

Oracle and DB2 DBA, datawarehouse consultant

[EMAIL PROTECTED]


__

Lèche-vitrine ou lèche-écran ?

magasinage.yahoo.ca

-- 

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

-- 

Author: =?iso-8859-1?q?Stephane=20Paquette?=

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

-- 

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: SQL question avoiding 2 views and not in

2002-12-13 Thread Jamadagni, Rajendra
Title: RE: SQL question avoiding 2 views and not in



dbms_rls is cheaper to use ...
 
Raj
__
Rajendra 
Jamadagni  
    MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot 
com
Any opinion expressed here is 
personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but 
having an opinion is an art!

  -Original Message-From: Nick Wagner 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, December 13, 2002 4:10 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  SQL question avoiding 2 views and not in 
  OLS -- Oracle Label Security...  I think that's the key 
  you are looking for.  
  -Original Message- From: 
  Stephane Paquette [mailto:[EMAIL PROTECTED]] 
  Sent: Friday, December 13, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject: SQL question avoiding 2 views and not in 
  Hi, 
  We have a lot of views. Now the users have a new 
  requirement, only the user 'admin' can see all the 
  data from the views. The user 'typical' must see all 
  data except the one from group 380. 
  A basic solution is to create 2 sets of views with one 
  set having a group number <> 380. 
  I'm looking for a solution to have only one set of 
  views. It's friday afternoon and I have no inspiration 
  :-( 
  TIA 
  = Stéphane Paquette 
  DBA Oracle et DB2, consultant entrepôt de données 
  Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] 
  __ 
  Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 
  =?iso-8859-1?q?Stephane=20Paquette?=   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



RE: SQL question avoiding 2 views and not in

2002-12-13 Thread Koivu, Lisa
Title: RE: SQL question avoiding 2 views and not in 






Hi Stephane, 


This may be more effort but have you considered having a security table to join to in the one view, instead of two views?  Multiple views can really hose the optimizer, as I am sure you know.  However adding a table then creates a task for someone, because the data must be maintained.  And it would have to be a complete set of data if you are avoiding the not in clause. 

by the way, I wanted to say this earlier but resisted the urge... how do you answer the interview question about being in a stressful situation like a recovery when your last name is Panicker? 

Have a great weekend everyone!


Lisa Koivu

Oracle Datababy Administratikiss and Wild Bamboo Stick Wielder. 

Fairfield Resorts, Inc.

5259 Coconut Creek Parkway

Ft. Lauderdale, FL, USA  33063




-Original Message-

From:   Stephane Paquette [SMTP:[EMAIL PROTECTED]]

Sent:   Friday, December 13, 2002 2:59 PM

To: Multiple recipients of list ORACLE-L

Subject:    SQL question avoiding 2 views and not in 


Hi,


We have a lot of views. Now the users have a new

requirement, only the user 'admin' can see all the

data from the views. The user 'typical' must see all

data except the one from group 380.


A basic solution is to create 2 sets of views with one

set having a group number <> 380.


I'm looking for a solution to have only one set of

views. It's friday afternoon and I have no inspiration

:-(


TIA



=

Stéphane Paquette

DBA Oracle et DB2, consultant entrepôt de données

Oracle and DB2 DBA, datawarehouse consultant

[EMAIL PROTECTED]


__

Lèche-vitrine ou lèche-écran ?

magasinage.yahoo.ca

-- 

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

-- 

Author: =?iso-8859-1?q?Stephane=20Paquette?=

  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 avoiding 2 views and not in

2002-12-13 Thread Khedr, Waleed
Add this to where clause:

 group <> decode(user,'typical',380,-100)

Instead of -100 use any number not used by the groups.

Also read about contexts and grain level security.

Waleed

-Original Message-
Sent: Friday, December 13, 2002 2:59 PM
To: Multiple recipients of list ORACLE-L


Hi,

We have a lot of views. Now the users have a new
requirement, only the user 'admin' can see all the
data from the views. The user 'typical' must see all
data except the one from group 380.

A basic solution is to create 2 sets of views with one
set having a group number <> 380.

I'm looking for a solution to have only one set of
views. It's friday afternoon and I have no inspiration
:-(

TIA


=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  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.com
-- 
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: SQL question avoiding 2 views and not in

2002-12-13 Thread Nick Wagner
Title: RE: SQL question avoiding 2 views and not in 





OLS -- Oracle Label Security...  I think that's the key you are looking for.  


-Original Message-
From: Stephane Paquette [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL question avoiding 2 views and not in 



Hi,


We have a lot of views. Now the users have a new
requirement, only the user 'admin' can see all the
data from the views. The user 'typical' must see all
data except the one from group 380.


A basic solution is to create 2 sets of views with one
set having a group number <> 380.


I'm looking for a solution to have only one set of
views. It's friday afternoon and I have no inspiration
:-(


TIA



=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]


__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  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 : use of SUBSTR/INSTR functions

2002-10-16 Thread Mirsky, Greg

<>

How about this...

FUNCTION f_ip_to_number (
   p_ipNVARCHAR2
)
   RETURN NUMBER
IS
   v_ip_segment1 NUMBER
 := SUBSTR (p_ip, 1, INSTR (p_ip, '.') -
1);
   v_ip_segment2 NUMBER
   := SUBSTR (
 p_ip
   , INSTR (p_ip, '.', 1, 1) + 1
   , INSTR (p_ip, '.', 1, 2) - INSTR (p_ip, '.', 1, 1) - 1
  );
   v_ip_segment3 NUMBER
   := SUBSTR (
 p_ip
   , INSTR (p_ip, '.', 1, 2) + 1
   , INSTR (p_ip, '.', 1, 3) - INSTR (p_ip, '.', 1, 2) - 1
  );
   v_ip_segment4 NUMBER
 := SUBSTR (p_ip, INSTR (p_ip, '.', -1)
+ 1);
BEGIN
   RETURN (  (  (v_ip_segment1 * 256 + v_ip_segment2)
  * 256
 )
   + v_ip_segment3
  )
* 256
  + v_ip_segment4;
END f_ip_to_number;
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mirsky, Greg
  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 : use of SUBSTR/INSTR functions

2002-10-16 Thread Sherman, Paul R.

Hello,

Try this (take a hard look first, as I cranked this out quickly while doing
other things):

substr(ip_addr,1,instr(ip_addr,'.',1,1)-1
http://www.orafaq.com
-- 
Author: Johan Muller
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, 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.com
-- 
Author: Sherman, Paul R.
  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 : use of SUBSTR/INSTR functions

2002-10-16 Thread Alan Davey

Hi Johan,

Try this:
SELECT SUBSTR('127.0.0.1',1,INSTR('127.0.0.1','.')-1)
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.')+1,INSTR('127.0.0.1','.',1,2)-(INSTR('127.0.0.1','.')+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,2)+1,INSTR('127.0.0.1','.',1,3)-(INSTR('127.0.0.1','.',1,2)+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,3)+1,LENGTH('127.0.0.1')-INSTR('127.0.0.1','.',1,3)+1)
FROM DUAL


There may be a more elegant solution, but this was the quickest I could come up with.
-- 

Alan Davey
[EMAIL PROTECTED]
212-604-0200  x106


On 10/16/2002 4:32 PM, Johan Muller <[EMAIL PROTECTED]> wrote:
>Help!
>
>Anybody have a quick and dirty to parse the 4 octets of a typical 
>IP address
>into 4 separate values. I will insert these into a table where  database
>checks may  verify that the data is in fact a number and also part 
>of a
>valid ip range (the second thru fourth octets cannot be higher than 
>255. The
>source data is very dirty and often fat-fingered, hence the painful
>solution):
>
>e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 
>4).
>
>I have used various flavors of substr/instr to unravel this, but 
>the varying
>length of the octets (up to 3 bytes) defeats my rudimentary sql coding
>skills. I probably have to attack the IP with decode, and any input 
>will be
>very welcome.
>
>Running V 8.1.6.
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Johan Muller
>  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.com
--
Author: Alan Davey
  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 : use of SUBSTR/INSTR functions

2002-10-16 Thread Fink, Dan

Try this. It uses the INSTR function to determine the start and end of the
SUBSTR.

  1  select substr('333.22.1.000',1,instr('333.22.1.000','.')-1) octet1,
  2 substr('333.22.1.000',
  3(instr('333.22.1.000','.',1,1) + 1),
  4(instr('333.22.1.000','.',1,2) -
instr('333.22.1.000','.',1,1))-1) octect2,
  5 substr('333.22.1.000',
  6(instr('333.22.1.000','.',1,2) + 1),
  7(instr('333.22.1.000','.',1,3) -
instr('333.22.1.000','.',1,2))-1) octect3,
  8 substr('333.22.1.000',(instr('333.22.1.000','.',1,3) + 1))
octect3
  9* from dual
SQL> /

OCT OC O OCT
--- -- - ---
333 22 1 000


Dan Fink

-Original Message-
Sent: Wednesday, October 16, 2002 2:32 PM
To: Multiple recipients of list ORACLE-L


Help!

Anybody have a quick and dirty to parse the 4 octets of a typical IP address
into 4 separate values. I will insert these into a table where  database
checks may  verify that the data is in fact a number and also part of a
valid ip range (the second thru fourth octets cannot be higher than 255. The
source data is very dirty and often fat-fingered, hence the painful
solution):

e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 4).

I have used various flavors of substr/instr to unravel this, but the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input will be
very welcome.

Running V 8.1.6.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Muller
  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.com
-- 
Author: Fink, Dan
  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

2002-09-23 Thread Jamadagni, Rajendra
Title: RE: SQL question





Maybe I think differently, I usually let server think about size or the number of clauses ...


if you have codes in a table what's wrong with ...


select distinct code
from my_code_table
minus
select distinct code
  from my_data_table
/


???
Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 2:43 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question



Raj,


It wasn't a question of coding the sql to create
all of the unions, but hitting a limitation on
the number of unions in the sql.


But, thanks to all for the effort.


steve




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: SQL question

2002-09-23 Thread Nicoll, Iain \(Calanais\)

what are the 1700 values

if the are all alphabetic and not too long you could do something like the
below though it's all getting a bit long-winded


select
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
from addresses -- any table big enough
where rownum < 26*26*26
group by
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
having
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26))) in 
  ('ABA','ACY','ABT'...)  -- the 1700 values
minus
select code 
from table



-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table
that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table
that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list
that do NOT have a matching value in the table?

I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.

Thanks in advance, folks.

Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  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.com
-- 
Author: Nicoll, Iain \(Calanais\)
  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

2002-09-23 Thread Igor Neyman

Sorry, forgot to provide a link:

http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, September 23, 2002 2:33 PM


> Jonathan Gennick has an excellent article in "Oracle" magazine
(sept./oct.),
> which should help.
> He demonstrates two approaches: with and without pivot table.
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Monday, September 23, 2002 1:28 PM
>
>
> > It is a little awkward, but a union in an inline query may do the trick:
> >
> >   1  select a.code
> >   2  from (select '10' code from dual union
> >   3select '20' code from dual union
> >   4select '30' code from dual union
> >   5select '40' code from dual union
> >   6select '50' code from dual ) a
> >   7* where a.code not in (select to_char(deptno) from emp)
> > SQL> /
> >
> > CO
> > --
> > 40
> > 50
> >
> > Dan Fink
> >
> > -Original Message-
> > Sent: Monday, September 23, 2002 10:28 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Good morning list,
> >
> > Environment HP-UX 11.0 Oracle 8.1.6
> >
> > Can anyone help with this SQL.
> >
> > I can get a result set of values from a table
> > that match a given list of values -
> >
> > select code
> > from table
> > where code in ('A','B','C','D','E')
> >
> > I can get a result set of values from a table
> > that do not match a given list of values -
> >
> > select code
> > from table
> > where code not in ('A','B','C','D','E')
> >
> > So far so good.
> >
> > Now, how do I get the set of values from the list
> > that do NOT have a matching value in the table?
> >
> > I cannot create any objects in the schema I am
> > working in otherwise I would create a table with
> > the values and do a minus, but I can't figure out
> > how to do it in SQL only.
> >
> > Thanks in advance, folks.
> >
> > Steve
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Steven Haas
> >   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.com
> > --
> > Author: Fink, Dan
> >   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.com
> --
> 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.com
-- 
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: SQL question

2002-09-23 Thread Steven Haas

Raj,

It wasn't a question of coding the sql to create
all of the unions, but hitting a limitation on
the number of unions in the sql.

But, thanks to all for the effort.

steve

--- "Jamadagni, Rajendra"
<[EMAIL PROTECTED]> wrote:
> Steve,
> 
> select 'select a.code ' || chr(10) || ' from('
> ||
> from dual
> union
> select distinct 'select ' || '''' || code ||
> '''' || ' code from dual ' ||
> chr(10) || 'union' ||
>   from my_code_table 
> union
> select ')' || chr(10) || 'minus' from dual
> /
> select 'select distinct code from my_table' ||
> chr(10) || '/'
> /
> 
> 
> drop off the last union before the closing
> parenthesis ...
> 
> See ... now you don't have to type ...
> 
> Raj
>
__
> Rajendra JamadagniMIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and
> doesn't reflect that of ESPN Inc.
> 
> QOTD: Any clod can have facts, but having an
> opinion is an art!
> 
> 
> -Original Message-
> From: Steven Haas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 23, 2002 1:38 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL question
> 
> 
> Dan (and Charlie),
> 
> Thanks.
> Good suggestions, but the IN clause contains
> just
> over 1700 values.
> 
> Puzzling, huh?
> 
> steve
> >
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
> 

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

2002-09-23 Thread Jamadagni, Rajendra
Title: RE: SQL question





Steve,


select 'select a.code ' || chr(10) || ' from(' ||
from dual
union
select distinct 'select ' || '''' || code || '''' || ' code from dual ' || chr(10) || 'union' ||
  from my_code_table 
union
select ')' || chr(10) || 'minus' from dual
/
select 'select distinct code from my_table' || chr(10) || '/'
/



drop off the last union before the closing parenthesis ...


See ... now you don't have to type ...


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-----
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 1:38 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question



Dan (and Charlie),


Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.


Puzzling, huh?


steve




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: SQL question

2002-09-23 Thread Igor Neyman

Jonathan Gennick has an excellent article in "Oracle" magazine (sept./oct.),
which should help.
He demonstrates two approaches: with and without pivot table.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, September 23, 2002 1:28 PM


> It is a little awkward, but a union in an inline query may do the trick:
>
>   1  select a.code
>   2  from (select '10' code from dual union
>   3select '20' code from dual union
>   4select '30' code from dual union
>   5select '40' code from dual union
>   6select '50' code from dual ) a
>   7* where a.code not in (select to_char(deptno) from emp)
> SQL> /
>
> CO
> --
> 40
> 50
>
> Dan Fink
>
> -Original Message-
> Sent: Monday, September 23, 2002 10:28 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Good morning list,
>
> Environment HP-UX 11.0 Oracle 8.1.6
>
> Can anyone help with this SQL.
>
> I can get a result set of values from a table
> that match a given list of values -
>
> select code
> from table
> where code in ('A','B','C','D','E')
>
> I can get a result set of values from a table
> that do not match a given list of values -
>
> select code
> from table
> where code not in ('A','B','C','D','E')
>
> So far so good.
>
> Now, how do I get the set of values from the list
> that do NOT have a matching value in the table?
>
> I cannot create any objects in the schema I am
> working in otherwise I would create a table with
> the values and do a minus, but I can't figure out
> how to do it in SQL only.
>
> Thanks in advance, folks.
>
> Steve
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Steven Haas
>   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.com
> --
> Author: Fink, Dan
>   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.com
-- 
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: SQL question

2002-09-23 Thread Jamadagni, Rajendra
Title: RE: SQL question





select *
  from(select 'a' from dual union select 'b' from dual union select 'c' from dual ...)
minus
select distinct code
from table
/


HTH
Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 12:28 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL question



Good morning list,


Environment HP-UX 11.0 Oracle 8.1.6


Can anyone help with this SQL.


I can get a result set of values from a table
that match a given list of values -


select code
from table
where code in ('A','B','C','D','E')


I can get a result set of values from a table
that do not match a given list of values -


select code
from table
where code not in ('A','B','C','D','E')


So far so good.


Now, how do I get the set of values from the list
that do NOT have a matching value in the table?


I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.


Thanks in advance, folks.


Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  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



RE: SQL question

2002-09-23 Thread Steven Haas

Dan et al,

I guess that is where I may head with it now.  I
couldn't come up with an easier way.

Thanks all...

steve



--- "Fink, Dan" <[EMAIL PROTECTED]> wrote:
> 1700 values? I sure hope you like to type...
> 
> Could you create a text file of the table
> values and compare those against a
> text files of the possible values? This would
> require O/S level privs.
> 
> 
> -Original Message-
> From: Steven Haas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 23, 2002 11:38 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL question
> 
> 
> Dan (and Charlie),
> 
> Thanks.
> Good suggestions, but the IN clause contains
> just
> over 1700 values.
> 
> Puzzling, huh?
> 
> steve
> 
> 
> --- "Fink, Dan" <[EMAIL PROTECTED]> wrote:
> > It is a little awkward, but a union in an
> > inline query may do the trick:
> > 
> >   1  select a.code
> >   2  from (select '10' code from dual union
> >   3select '20' code from dual union
> >   4select '30' code from dual union
> >   5select '40' code from dual union
> >   6select '50' code from dual ) a
> >   7* where a.code not in (select
> > to_char(deptno) from emp)
> > SQL> /
> > 
> > CO
> > --
> > 40
> > 50
> > 
> > Dan Fink
> > 
> > -Original Message-
> > From: Steven Haas
> [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, September 23, 2002 10:28 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: SQL question
> > 
> > 
> > Good morning list,
> > 
> > Environment HP-UX 11.0 Oracle 8.1.6
> > 
> > Can anyone help with this SQL.
> > 
> > I can get a result set of values from a table
> > that match a given list of values -
> > 
> > select code
> > from table
> > where code in ('A','B','C','D','E')
> > 
> > I can get a result set of values from a table
> > that do not match a given list of values -
> > 
> > select code
> > from table
> > where code not in ('A','B','C','D','E')
> > 
> > So far so good.
> > 
> > Now, how do I get the set of values from the
> > list
> > that do NOT have a matching value in the
> table?
> > 
> > I cannot create any objects in the schema I
> am
> > working in otherwise I would create a table
> > with
> > the values and do a minus, but I can't figure
> > out
> > how to do it in SQL only.
> > 
> > Thanks in advance, folks.
> > 
> > Steve
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Steven Haas
> >   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.com
> -- 
> Author: Steven Haas
>   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.com
-- 
Author: Steven Haas
  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

2002-09-23 Thread Steven Haas

Dan (and Charlie),

Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.

Puzzling, huh?

steve


--- "Fink, Dan" <[EMAIL PROTECTED]> wrote:
> It is a little awkward, but a union in an
> inline query may do the trick:
> 
>   1  select a.code
>   2  from (select '10' code from dual union
>   3select '20' code from dual union
>   4select '30' code from dual union
>   5select '40' code from dual union
>   6select '50' code from dual ) a
>   7* where a.code not in (select
> to_char(deptno) from emp)
> SQL> /
> 
> CO
> --
> 40
> 50
> 
> Dan Fink
> 
> -Original Message-
> From: Steven Haas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 23, 2002 10:28 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL question
> 
> 
> Good morning list,
> 
> Environment HP-UX 11.0 Oracle 8.1.6
> 
> Can anyone help with this SQL.
> 
> I can get a result set of values from a table
> that match a given list of values -
> 
> select code
> from table
> where code in ('A','B','C','D','E')
> 
> I can get a result set of values from a table
> that do not match a given list of values -
> 
> select code
> from table
> where code not in ('A','B','C','D','E')
> 
> So far so good.
> 
> Now, how do I get the set of values from the
> list
> that do NOT have a matching value in the table?
> 
> I cannot create any objects in the schema I am
> working in otherwise I would create a table
> with
> the values and do a minus, but I can't figure
> out
> how to do it in SQL only.
> 
> Thanks in advance, folks.
> 
> Steve
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Steven Haas
>   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.com
-- 
Author: Steven Haas
  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

2002-09-23 Thread Nicoll, Iain \(Calanais\)

If the set of values is not too big and fixed you could do the minus using
dual


e.g.

(select 'A'
 from dual
 union
 select 'B'
 from dual
 union
 ...
 select 'Z'
 from dual)
minus
select code
from table


-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table
that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table
that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list
that do NOT have a matching value in the table?

I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.

Thanks in advance, folks.

Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  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.com
-- 
Author: Nicoll, Iain \(Calanais\)
  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

2002-09-23 Thread Fink, Dan

1700 values? I sure hope you like to type...

Could you create a text file of the table values and compare those against a
text files of the possible values? This would require O/S level privs.


-Original Message-
Sent: Monday, September 23, 2002 11:38 AM
To: Multiple recipients of list ORACLE-L


Dan (and Charlie),

Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.

Puzzling, huh?

steve


--- "Fink, Dan" <[EMAIL PROTECTED]> wrote:
> It is a little awkward, but a union in an
> inline query may do the trick:
> 
>   1  select a.code
>   2  from (select '10' code from dual union
>   3select '20' code from dual union
>   4select '30' code from dual union
>   5select '40' code from dual union
>   6select '50' code from dual ) a
>   7* where a.code not in (select
> to_char(deptno) from emp)
> SQL> /
> 
> CO
> --
> 40
> 50
> 
> Dan Fink
> 
> -Original Message-
> From: Steven Haas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 23, 2002 10:28 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL question
> 
> 
> Good morning list,
> 
> Environment HP-UX 11.0 Oracle 8.1.6
> 
> Can anyone help with this SQL.
> 
> I can get a result set of values from a table
> that match a given list of values -
> 
> select code
> from table
> where code in ('A','B','C','D','E')
> 
> I can get a result set of values from a table
> that do not match a given list of values -
> 
> select code
> from table
> where code not in ('A','B','C','D','E')
> 
> So far so good.
> 
> Now, how do I get the set of values from the
> list
> that do NOT have a matching value in the table?
> 
> I cannot create any objects in the schema I am
> working in otherwise I would create a table
> with
> the values and do a minus, but I can't figure
> out
> how to do it in SQL only.
> 
> Thanks in advance, folks.
> 
> Steve
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Steven Haas
>   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.com
-- 
Author: Steven Haas
  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.com
-- 
Author: Fink, Dan
  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

2002-09-23 Thread Fink, Dan

It is a little awkward, but a union in an inline query may do the trick:

  1  select a.code
  2  from (select '10' code from dual union
  3select '20' code from dual union
  4select '30' code from dual union
  5select '40' code from dual union
  6select '50' code from dual ) a
  7* where a.code not in (select to_char(deptno) from emp)
SQL> /

CO
--
40
50

Dan Fink

-Original Message-
Sent: Monday, September 23, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L


Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table
that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table
that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list
that do NOT have a matching value in the table?

I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.

Thanks in advance, folks.

Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  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.com
-- 
Author: Fink, Dan
  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 (DISREGARD 1ST MESSAGE, SORRY)

2002-06-08 Thread Stephane Faroult

Viktor wrote:
> 
> Hello All,
> 
> It looks as if I've hit a brick wall and I'd very much
> appreciate if you can help.
> 
> desc Names
> 
> FIRST_INIT  NOT NULL CHAR(4)
> SECOND_INIT NOT NULL CHAR(1)
> INIT_SEQUENCE   NOT NULL NUMBER
> LAST_NAMEVARCHAR2(30)
> FIRST_NAME   VARCHAR2(20)
> FLAGNOT NULL NUMBER
> 
> desc MEMBER
> 
> MEM_FIRST_INIT   CHAR(4)
> MEM_SECOND_INIT  CHAR(1)
> MEM_INIT_SEQUENCENUMBER
> 
> Member table references Names table on FIRST_INIT,
> SECOND_INIT, INIT_SEQUENCE (FOREIGN KEYS).
> 
> Names table has NOT NULL column flag. It can only be 0
> or 1 -- means Name is a member.
> 
> But, not all NAME records with FLAG 1 are in MEMBER.
> In other words, records in MEMBER usually represent
> other some other types of memership.
> 
> But, in this case, I need to get those NAME(parent)
> records that have FLAG = 1, and those MEMBER(child)
> records that reference NAMES via foreign keys.
> Sometimes NAME record with FLAG = 1, also has a MEMBER
> record, and it could be that record with FLAG = 1 does
> not have a record in MEMBER.
> 
> I need all those with FLAG = 1 in NAMES + all records
> that are in MEMBER.
> 
> Is there another way besides the UNION:
> 
> SELECT a.first_init||a.second_init||a.init_sequence
> INITIALS,
>a.last_name LAST_NAME,
>a.first_name FIRST_NAME,
>a.flag MEMBER_FLAG,
> FROM names a
> WHERE a.advisor_flag = 1
> UNION
> SELECT a.first_init||a.second_init||a.init_sequence
> INITIALS,
>a.last_name LAST_NAME,
>a.first_name FIRST_NAME,
>a.flag MEMBER_FLAG
> FROM names a,
>  members m
> WHERE a.first_init = m.mem_first_init
>  and a.second_init = m.mem_second_init
>  and a.init_sequence = m.mem_init_sequence
> 
> Thanks a lot in advance!
> 
> Regards,
> 
> Viktor
> 

The UNION is perfect. You could possibly do something with external
joins and an 'OR', but it would not be more legible.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-06-07 Thread Mladen Gogala

Are you looking for something trivial like:

select n.first_name, 'E.' middle_initial,n.last_name,m.mem_init_sequence
from   names n, member m
where  n.first_init=m.mem_first_init and
n.second_init=m.mem_second_init
order by 1 desc, 2 asc;



On 2002.06.08 01:33 Viktor wrote:
> Hello All,
> 
> It looks as if I've hit a brick wall and I'd very much
> appreciate if you can help. I've got a query that has
> to get some names and tie them to members. Name table
> is the main table with and member table is child
> table.
> 
> desc Names
> 
> FIRST_INIT  NOT NULL CHAR(4)
> SECOND_INIT NOT NULL CHAR(1)
> INIT_SEQUENCE   NOT NULL NUMBER
> LAST_NAMEVARCHAR2(30)
> FIRST_NAME   VARCHAR2(20)
> FLAGNOT NULL NUMBER
> 
> desc MEMBER
> 
> MEM_FIRST_INIT   CHAR(4)
> MEM_SECOND_INIT  CHAR(1)
> MEM_INIT_SEQUENCENUMBER
> 
> Member table references Names table on FIRST_INIT,
> SECOND_INIT, INIT_SEQUENCE.
> 
> 
> 
> 
> 
> __
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viktor
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-31 Thread Kevin Lange

Try this

select a.f1, a.d1, a.d2
from
  (select field1 f1,date1 d1,max(date2) d2 from temp group by field1,date1)
a,
  (select field1 f1,date1 d1,max(date2) d2 from temp group by field1,date1)
b
where a.f1 = b.f1
and a.d1 < b.d1
and a.d2 > b.d2


-Original Message-
Sent: Friday, May 31, 2002 10:59 AM
To: Multiple recipients of list ORACLE-L


Hi,

I have a table with 1 field and 2 dates: field1, date1, date2. I need to
find the max value of date2 for all the field1, date1 combinations. Then I
want to join the table to itself on field1 and find all the rows where
field1 matches, date1 < date1, and max(date2) > max(date2). I did this in 2
queries. First I created a view as follows:

create view v1 (f1, d1, d2)
as select field1,date1,max(date2) from table1
group by field1,date1;

Then I joined the 2 views together like this:

select a.f1,a.d1,a.d2 from v1 a, v1 b
where a.f1 = b.f1
and a.d1 < b.d1
and a.d2 > b.d2;

This worked fine, but I was wondering if there was a way to do this in
one query without having to create a view.


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-30 Thread Deshpande, Kirti

Hi Conner,
 Yes, I agree.
 But its the 'green bean' developers that I am dealing with :) 

 Regards,

- Kirti 

PS : Your BCHR enhancer code is coming extremely handy :)  Great Job, you
did !!  


-Original Message-
Sent: Thursday, May 30, 2002 4:23 AM
To: Multiple recipients of list ORACLE-L


I'm sure you're already aware of this, but the
substr/instr is not as complicated as it looks since
instr takes 4 parms, the 4th of which makes cycling
through fields 1=>8 easy.

hth
connor

 --- "Deshpande, Kirti" <[EMAIL PROTECTED]>
wrote: > Stephane,
>  Thanks. 
>  Nice idea :) 
>  I will pass on this idea to them... Hope it flies..
> 
> 
>  Looks like either a function or a view around the
> 'ugly' code is the only
> choice. 
> 
> - Kirti 
>  
> 
> 
> -
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-30 Thread Connor McDonald

I'm sure you're already aware of this, but the
substr/instr is not as complicated as it looks since
instr takes 4 parms, the 4th of which makes cycling
through fields 1=>8 easy.

hth
connor

 --- "Deshpande, Kirti" <[EMAIL PROTECTED]>
wrote: > Stephane,
>  Thanks. 
>  Nice idea :) 
>  I will pass on this idea to them... Hope it flies..
> 
> 
>  Looks like either a function or a view around the
> 'ugly' code is the only
> choice. 
> 
> - Kirti 
>  
> 
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 3:32 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> 
> Kirti,
> 
>I had a vague remembrance of something which
> might have been helpful
> in dbms_utility but the closer you get to is a
> comma_to_table()
> procedure. Even without your reluctance to
> REPLACE(), you would be lucky
> if your data could bear this kind of transformation.
>   Well, its a bit like sweeping dirt under the
> carpet, but I think that
> a function
>  getfield(string in varchar2, pos in
> number, separator
> in char)
>  return varchar2;
>  full of substr and instr should make the upper
> level query more legible
> ...
> 
>  
> Regards,
> 
> Stephane Faroult
> Oriole Software
> -- 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"Some days you're the pigeon, some days you're the statue"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Deshpande, Kirti

Peter,
Thanks. I am not sure if a Java solution would work.
Looks like they have settled on a view.
Nice to know how Java could help, but I am not sure if they can use it. I
will ask. 
Thanks for your offer. 

Regards,
- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 6:23 PM
To: Multiple recipients of list ORACLE-L


Kirti 
I needed to do something similar but it isn't due to bad normalisation it 
is to extract data in a load process so we don't have bad normalisation. I 
decided to use Java and built a parser in that and I just feed it a line 
and the code simply extracts that data on the selected delimiter. Mine the 
delimiter is a fixed value in code, it could be altered to use a runtime 
selected value. I can provide the source if it would be of use. 

Cheers


--
=
Peter McLarty   E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane,  AustraliaMobile: +61 (0)402 094 238
Facsimile: +61 (0)7 3303 3048
=
A great pleasure in life is doing what people say you cannot do.

- Walter Bagehot (1826-1877 British Economist)
=
Mincom "The People, The Experience, The Vision"

=

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 






"Deshpande, Kirti" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
30-05-2002 04:22 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Fax to: 
Subject:SQL Question


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column. 

Is there a way in SQL, other than substr/instr combinations, to extract 
each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Deshpande, Kirti

Mike,
 They were looking for a SQL solution first.
 Now a view (hiding substr/instr) looks like an acceptable thing :)

Thanks.

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 9:38 PM
To: Multiple recipients of list ORACLE-L


Kirti,

my first thought and fwiw would be to write a PL/SQL routine.

Mike

-Original Message-
Sent: Wednesday, May 29, 2002 11:22 AM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Johnson, Michael 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Johnson, Michael

Kirti,

my first thought and fwiw would be to write a PL/SQL routine.

Mike

-Original Message-
Sent: Wednesday, May 29, 2002 11:22 AM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Johnson, Michael 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Peter . McLarty

Kirti 
I needed to do something similar but it isn't due to bad normalisation it 
is to extract data in a load process so we don't have bad normalisation. I 
decided to use Java and built a parser in that and I just feed it a line 
and the code simply extracts that data on the selected delimiter. Mine the 
delimiter is a fixed value in code, it could be altered to use a runtime 
selected value. I can provide the source if it would be of use. 

Cheers


--
=
Peter McLarty   E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane,  AustraliaMobile: +61 (0)402 094 238
Facsimile: +61 (0)7 3303 3048
=
A great pleasure in life is doing what people say you cannot do.

- Walter Bagehot (1826-1877 British Economist)
=
Mincom "The People, The Experience, The Vision"

=

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 






"Deshpande, Kirti" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
30-05-2002 04:22 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Fax to: 
Subject:SQL Question


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column. 

Is there a way in SQL, other than substr/instr combinations, to extract 
each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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





STG17766
Description: Binary data


RE: SQL Question

2002-05-29 Thread DENNIS WILLIAMS

Kirti - My guess is that this application was not developed on Oracle
originally. My experience is that sometimes these transplanted applications
don't scale well at the enterprise level. Depending on your organization's
goals, this may be an issue to raise, whether it will support the
anticipated future usage. Another idea is to ask the vendor how to do
reporting on this table (especially now that you have some good suggestions
to bounce back in case the vendor doesn't provide a good idea). Depending on
the politics at your organization, you can give the vendor a little static
about their bad design. Also, tell us the vendor's name so if someone
proposes that application at my site I can roll my eyes and say "good grief,
not those people".
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 1:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Deshpande, Kirti

Not sure if they would agree to snapshots, but I will suggest it anyway..

Thanks.

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 3:26 PM
To: Multiple recipients of list ORACLE-L


An oversimplification no doubt... But what about creating a snapshot
table for reporting?
It would be much less painfull then revisiting the column names every
time a report is requested.

Now, getting a spec of reporting fields can be a challenge it its own
right but... The snapshots do work

Bob


> Dennis,
> 
> Thanks for the ideas, but... 
> 
> 1. Not going to happen. It's a production system already in 
> place (Vendor designed? But, of course!!)
> 
> 2. See above. 
> 
> This is what happens when someone decides to write their own 
> reports against tables that were not designed by themselves. 
> Damagement thinks it is just a simple SELECT !!! 
> 
> Looks like they just will have to live with unreadable SQL ! 
> 
> Thanks. 
> - Kirti
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 2:30 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Kirti - We have a denormalized table like this in one 
> database. An excellent moral lesson for those who doubt the 
> wisdom of normalization. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Jamadagni, Rajendra

Stephane,

comma_to_table converts it to a pl/sql table.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!



*2

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

2002-05-29 Thread Deshpande, Kirti

Stephane,
 Thanks. 
 Nice idea :) 
 I will pass on this idea to them... Hope it flies.. 

 Looks like either a function or a view around the 'ugly' code is the only
choice. 

- Kirti 
 


-Original Message-
Sent: Wednesday, May 29, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L




Kirti,

   I had a vague remembrance of something which might have been helpful
in dbms_utility but the closer you get to is a comma_to_table()
procedure. Even without your reluctance to REPLACE(), you would be lucky
if your data could bear this kind of transformation.
  Well, its a bit like sweeping dirt under the carpet, but I think that
a function
 getfield(string in varchar2, pos in number, separator
in char)
 return varchar2;
 full of substr and instr should make the upper level query more legible
...

 
Regards,

Stephane Faroult
Oriole Software
-- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Deshpande, Kirti

Another nice idea !
I will pass it on. 

Looks like instr/substr can not be avoided... :( 

Thanks.

- Kirti

-Original Message-
Sent: Wednesday, May 29, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L




Would they allow you to create a view with substr/instr and then just code
off
of the view?

-Original Message-
Sent: Wednesday, May 29, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L

Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

Re: SQL Question

2002-05-29 Thread Igor Neyman

What about creating a view and hiding 'unreadable SQL' in view definition,
and granting 'select on' view instead of table.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 29, 2002 4:00 PM


> Dennis,
>
> Thanks for the ideas, but...
>
> 1. Not going to happen. It's a production system already in place (Vendor
> designed? But, of course!!)
>
> 2. See above.
>
> This is what happens when someone decides to write their own reports
against
> tables that were not designed by themselves. Damagement thinks it is just
a
> simple SELECT !!!
>
> Looks like they just will have to live with unreadable SQL !
>
> Thanks.
> - Kirti
>
> -Original Message-
> Sent: Wednesday, May 29, 2002 2:30 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Kirti - We have a denormalized table like this in one database. An
excellent
> moral lesson for those who doubt the wisdom of normalization.
> My first choice would be to lobby to redesign this table. The longer
> it remains and the more programs are built around this design, the more
> painful the eventual redesign will be.
> My second choice would be something along the lines of a shadow
> table to move all the records periodically for reporting. If you need to
> search on these fields, you could create a function-based index on each
> column. One idea that occurs to me that I haven't tested would be whether
> you could write a stored procedure that would parse the field and return
the
> columns. This would be usable by any utility that could execute a PL/SQL
> procedure.
> Kirti - you have my sympathy.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, May 29, 2002 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Thanks.
> Substr/instr was rejected because it was a bit difficult to read the code.
>
> Also, they wanted to extract the fields in their own column headings (new
> requirement). So 'replace' may not fly much !!
>
> - Kirti
>
> -Original Message-
> Sent: Wednesday, May 29, 2002 1:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What about
>
> select commission_id, replace(com_text_msg,'~',chr(9))
> from tab1
>
> which would work if going to a tab separated file for something like
excel.
>
>
> Whats wrong with substr/instr?
>
> Iain Nicoll
>
> -Original Message-
> Sent: Wednesday, May 29, 2002 7:22 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I need some help...
>
> The database table has following structure.
> commision_id  number
> com_text_msg  varchar2(500)
>
> The second column contains data fields that are delimited by ~ and
> delimiter's position varies. But there are only eight data fields in the
> column.
>
> Is there a way in SQL, other than substr/instr combinations, to extract
each
> data field to report?
>
> Thanks.
>
> - Kirti
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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.com
> --
> Author: Nicoll, Iain (Calanais)
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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.com
> --
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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).
> --
> P

RE: SQL Question

2002-05-29 Thread Terrian, Tom



Would they allow you to create a view with substr/instr and then just code off
of the view?

-Original Message-
Sent: Wednesday, May 29, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L

Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Terrian, Tom
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-

Re: SQL Question

2002-05-29 Thread Stephane Faroult

"Deshpande, Kirti" wrote:
> 
> Thanks.
> Substr/instr was rejected because it was a bit difficult to read the code.
> 
> Also, they wanted to extract the fields in their own column headings (new
> requirement). So 'replace' may not fly much !!
> 
> - Kirti
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 1:45 PM
> To: Multiple recipients of list ORACLE-L
> 
> What about
> 
> select commission_id, replace(com_text_msg,'~',chr(9))
> from tab1
> 
> which would work if going to a tab separated file for something like excel.
> 
> Whats wrong with substr/instr?
> 
> Iain Nicoll
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 7:22 PM
> To: Multiple recipients of list ORACLE-L
> 
> I need some help...
> 
> The database table has following structure.
> commision_id  number
> com_text_msg  varchar2(500)
> 
> The second column contains data fields that are delimited by ~ and
> delimiter's position varies. But there are only eight data fields in the
> column.
> 
> Is there a way in SQL, other than substr/instr combinations, to extract each
> data field to report?
> 
> Thanks.
> 
> - Kirti
> 

Kirti,

   I had a vague remembrance of something which might have been helpful
in dbms_utility but the closer you get to is a comma_to_table()
procedure. Even without your reluctance to REPLACE(), you would be lucky
if your data could bear this kind of transformation.
  Well, its a bit like sweeping dirt under the carpet, but I think that
a function
 getfield(string in varchar2, pos in number, separator
in char)
 return varchar2;
 full of substr and instr should make the upper level query more legible
...

 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Bob Metelsky

An oversimplification no doubt... But what about creating a snapshot
table for reporting?
It would be much less painfull then revisiting the column names every
time a report is requested.

Now, getting a spec of reporting fields can be a challenge it its own
right but... The snapshots do work

Bob


> Dennis,
> 
> Thanks for the ideas, but... 
> 
> 1. Not going to happen. It's a production system already in 
> place (Vendor designed? But, of course!!)
> 
> 2. See above. 
> 
> This is what happens when someone decides to write their own 
> reports against tables that were not designed by themselves. 
> Damagement thinks it is just a simple SELECT !!! 
> 
> Looks like they just will have to live with unreadable SQL ! 
> 
> Thanks. 
> - Kirti
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 2:30 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Kirti - We have a denormalized table like this in one 
> database. An excellent moral lesson for those who doubt the 
> wisdom of normalization. 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Rachel Carmichael

if you are going to use a shadow table, how about a trigger on the
original table that parses the field into separate columns and does an
insert into the shadow table? Update if necessary (not all that
difficult, just replace all the parsed fields in case) and delete,
depending on the types of dml done on the original table.

not elegant but it would give you the normalized table.


--- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote:
> Kirti - We have a denormalized table like this in one database. An
> excellent
> moral lesson for those who doubt the wisdom of normalization. 
>   My first choice would be to lobby to redesign this table. The longer
> it remains and the more programs are built around this design, the
> more
> painful the eventual redesign will be. 
>   My second choice would be something along the lines of a shadow
> table to move all the records periodically for reporting. If you need
> to
> search on these fields, you could create a function-based index on
> each
> column. One idea that occurs to me that I haven't tested would be
> whether
> you could write a stored procedure that would parse the field and
> return the
> columns. This would be usable by any utility that could execute a
> PL/SQL
> procedure. 
>   Kirti - you have my sympathy.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 2:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Thanks. 
> Substr/instr was rejected because it was a bit difficult to read the
> code. 
>  
> Also, they wanted to extract the fields in their own column headings
> (new
> requirement). So 'replace' may not fly much !! 
> 
> - Kirti 
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 1:45 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> What about
> 
> select commission_id, replace(com_text_msg,'~',chr(9))
> from tab1
> 
> which would work if going to a tab separated file for something like
> excel.
> 
> 
> Whats wrong with substr/instr?
> 
> Iain Nicoll
> 
> -Original Message-
> Sent: Wednesday, May 29, 2002 7:22 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I need some help... 
> 
> The database table has following structure.
> commision_id  number
> com_text_msg  varchar2(500)
> 
> The second column contains data fields that are delimited by ~ and
> delimiter's position varies. But there are only eight data fields in
> the
> column.  
> 
> Is there a way in SQL, other than substr/instr combinations, to
> extract each
> data field to report? 
> 
> Thanks.
> 
> - Kirti 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> 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.com
> -- 
> Author: Nicoll, Iain (Calanais)
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> 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.com
> -- 
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> 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.com
> -- 
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> --

RE: SQL Question

2002-05-29 Thread Deshpande, Kirti

Dennis,

Thanks for the ideas, but... 

1. Not going to happen. It's a production system already in place (Vendor
designed? But, of course!!)

2. See above. 

This is what happens when someone decides to write their own reports against
tables that were not designed by themselves. Damagement thinks it is just a
simple SELECT !!! 

Looks like they just will have to live with unreadable SQL ! 

Thanks. 
- Kirti

-Original Message-
Sent: Wednesday, May 29, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L


Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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 con

RE: SQL Question

2002-05-29 Thread Deshpande, Kirti

Steve:
 I (too) miss COBOL when it comes to something like this!!! 

 I will let the Developers of the 'type' solution. It's good that it is an
8i database :)

 Thanks.
- Kirti

-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Kirti:

  If this was Cobol, you could implement a record with a varying size
REDEFINES clause.  Using Iain's idea, you could change the separator string
and then redefine into a record.

  What about defining a type with the tilde as the separator?

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com

 -Original Message-
Sent:   Wednesday, May 29, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Question

What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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 contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Karniotis, Stephen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread DENNIS WILLIAMS

Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Karniotis, Stephen

Kirti:

  If this was Cobol, you could implement a record with a varying size
REDEFINES clause.  Using Iain's idea, you could change the separator string
and then redefine into a record.

  What about defining a type with the tilde as the separator?

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com

 -Original Message-
Sent:   Wednesday, May 29, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L
Subject:        RE: SQL Question

What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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 contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Karniotis, Stephen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Deshpande, Kirti

Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-29 Thread Nicoll, Iain (Calanais)

What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-05-01 Thread Aponte, Tony



Here's 
a quick-n-dirty SQL that pivots the result set into one row.  It has 
its limits (you must know the number of rows that would be returned so that you 
can adjust the grouping columns value01 through value12.  

 
SELECT 
g1 
,MAX(DECODE(line_no,01,value,NULL)) value01 
,MAX(DECODE(line_no,02,value,NULL)) value02 
,MAX(DECODE(line_no,03,value,NULL)) value03 
,MAX(DECODE(line_no,04,value,NULL)) value04 
,MAX(DECODE(line_no,05,value,NULL)) value05 
,MAX(DECODE(line_no,06,value,NULL)) value06 
,MAX(DECODE(line_no,07,value,NULL)) value07 
,MAX(DECODE(line_no,08,value,NULL)) value08 
,MAX(DECODE(line_no,09,value,NULL)) value09 
,MAX(DECODE(line_no,10,value,NULL)) value10 
,MAX(DECODE(line_no,11,value,NULL)) value11 
,MAX(DECODE(line_no,12,value,NULL)) value12 
FROM (SELECT 
g1,value,row_number() 
over(partition by g1 order 
by g1 nulls last) line_no 
FROM (SELECT 'DEPTNO' g1,deptno value from 
dept) 
) 
GROUP BY 
g1; 
 

 
I took 
it a little further and came up with the following:
 
SELECT 
g1 
,MAX(DECODE(line_no,01,value,NULL)) ||','|| 
MAX(DECODE(line_no,02,value,NULL)) ||','|| 
MAX(DECODE(line_no,03,value,NULL)) ||','|| 
MAX(DECODE(line_no,04,value,NULL)) ||','|| 
MAX(DECODE(line_no,05,value,NULL)) ||','|| 
MAX(DECODE(line_no,06,value,NULL)) ||','|| 
MAX(DECODE(line_no,07,value,NULL)) ||','|| 
MAX(DECODE(line_no,08,value,NULL)) ||','|| 
MAX(DECODE(line_no,09,value,NULL)) ||','|| 
MAX(DECODE(line_no,10,value,NULL)) ||','|| 
MAX(DECODE(line_no,11,value,NULL)) ||','|| 
MAX(DECODE(line_no,12,value,NULL)) 
FROM (SELECT 
g1,value,row_number() 
over(partition by g1 order 
by g1 nulls last) line_no 

FROM (SELECT 'DEPTNO' g1,deptno value from dept) 
) 

GROUP BY 
g1; 

 
I'll 
leave the trimming of the trailing commas to you.
 
HTH
Tony 
Aponte
 

  -Original Message-From: kranti pushkarna 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 30, 
  2002 3:48 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Sql Question
  Hi 
  List,
      
  Can someone give a SQL query to retuen all values in paricular column in comma 
  separed format.
  e.g. suppose I 
  fire "select deptno from dept" the output would be like
   
  Deptno
  10
  20
  30
  40
   
  I want the 
  output like 10,20,30,40. 
   
  I am just 
  wondering can it be done in a single query.
   
   
  TIA
  Kranti
   


RE: Sql Question

2002-04-30 Thread kranti pushkarna

Thanx Stephane . I did the same

 
STAARSHIP TECHNOLOGIES
www.staarship.com


Kranti Pushkarna
Project Leader
Tel: +91-22-6931557
__
 " Failure to prepare is preparing to fail " 
__


-Original Message-
Sent: Tuesday, April 30, 2002 2:13 PM
To: Multiple recipients of list ORACLE-L


It cannot. You have to write a PL/SQL function which returns a VARCHAR for
that.

>- Original Message -
>From: kranti pushkarna
><[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Mon, 29 Apr 2002 23:48:20
>
>Hi List,
>Can someone give a SQL query to retuen
>all values in paricular
>column in comma separed format.
>e.g. suppose I fire "select deptno from dept" the
>output would be like
> 
>Deptno
>10
>20
>30
>40
> 
>I want the output like 10,20,30,40. 
> 
>I am just wondering can it be done in a single
>query.
> 
> 
>TIA
>Kranti
> 


Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: kranti pushkarna
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-04-30 Thread Stephane Faroult

It cannot. You have to write a PL/SQL function which returns a VARCHAR for that.

>- Original Message -
>From: kranti pushkarna
><[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Mon, 29 Apr 2002 23:48:20
>
>Hi List,
>Can someone give a SQL query to retuen
>all values in paricular
>column in comma separed format.
>e.g. suppose I fire "select deptno from dept" the
>output would be like
> 
>Deptno
>10
>20
>30
>40
> 
>I want the output like 10,20,30,40. 
> 
>I am just wondering can it be done in a single
>query.
> 
> 
>TIA
>Kranti
> 


Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-04-24 Thread Daniel W. Fink

Here's one that answers both questions in one query:

SELECT u.username, i.table_name, i.index_namd
FROM dba_users u, dba_indexes i
WHERE u.username = i.owner (+)
  AND u.username not like 'SYS%'

This will show all users and IF they have a table with an index, it will
display these as well.

-Original Message-
[EMAIL PROTECTED]
Sent: Wednesday, April 24, 2002 11:19 AM
To: Multiple recipients of list ORACLE-L


Select * from all_users

Volker Schoen
INPLAN RUHR
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de



-Ursprüngliche Nachricht-
Von: Nguyen, David M [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 24. April 2002 18:24
An: Multiple recipients of list ORACLE-L
Betreff: SQL question


How do I list all user accounts created in a database?  And how do I list
all user table indexes?

Thanks,
David
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nguyen, David M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Daniel W. Fink
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-04-24 Thread Ron Rogers

David,
 Basic sqlplus as the dba.
Select username from dba_users;
select owner,index_name from dba_indexes there owner not in
('SYS',SYSTEM');
Brush up on your reading skills.
ROR mô¿ôm

>>> [EMAIL PROTECTED] 04/24/02 12:23PM >>>
How do I list all user accounts created in a database?  And how do I
list
all user table indexes?

Thanks,
David
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Nguyen, David M
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
--
Author: Ron Rogers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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

2002-04-24 Thread Farnsworth, Dave

>-How do I list all user accounts created in a database?

SELECT * FROM DBA_USERS

>-And how do I list all user table indexes?

SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'MY_LUSER'

Dave

-Original Message-
Sent: Wednesday, April 24, 2002 11:24 AM
To: Multiple recipients of list ORACLE-L


How do I list all user accounts created in a database?  And how do I list
all user table indexes?

Thanks,
David
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nguyen, David M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
--
Author: Farnsworth, Dave
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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



  1   2   >