Re: sql question
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)
<<>>
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
<<>>
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
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
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
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
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
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
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
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
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 ???
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 ???
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
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
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
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
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
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
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
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
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
>>[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
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
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
. 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
> 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
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
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
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
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
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
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
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
[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
> 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
<> 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
"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
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
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
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
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
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
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
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
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
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
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
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
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
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
>-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).
