a quick pl/sql question
Hi: In pl/sql, I want to add chr(10) into a string in every 70th position. The string can be up to 2000 characters long. The follwoing code works. But is there an even FASTER way to do this? Thanks. Guang --- declare pos number := 1; len number; buf varchar2(2000); x varchar2(2100); begin buf := '012345678901234567890123456789012345678901234567890123456789012345678901234 5678901234567890123456789012345678901234567890123456789012345678901234567890 12345678901234567890123456789012345678901234567890123456789A'; len := length(buf); while pos=len loop x := x || substr(buf, pos, 70) || chr(10); pos := pos+70; end loop; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql question
Bear in mind though that the original query will only count rows where b.award_number is not null whereas this new query will count all rows in the result set. Regards, Dave [EMAIL PROTECTED] wrote: Can you change it to this query: SELECT count(1) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) eric - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 12, 2004 3:04 PM David - Can you post the EXPLAIN PLAN for both? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 12, 2004 1:14 PM To: Multiple recipients of list ORACLE-L Hi List, I have following sql that runs in 1 sec: SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) However, when I try to count above query as following, it hangs. Does someone have any ideas? SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) _ High-speed users-be more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sql question
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 usersbe more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql question
David - Can you post the EXPLAIN PLAN for both? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 12, 2004 1:14 PM To: Multiple recipients of list ORACLE-L Hi List, I have following sql that runs in 1 sec: SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) However, when I try to count above query as following, it hangs. Does someone have any ideas? SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) _ High-speed users-be more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql question
Can you change it to this query: SELECT count(1) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) eric - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 12, 2004 3:04 PM David - Can you post the EXPLAIN PLAN for both? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 12, 2004 1:14 PM To: Multiple recipients of list ORACLE-L Hi List, I have following sql that runs in 1 sec: SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) However, when I try to count above query as following, it hangs. Does someone have any ideas? SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) _ High-speed users-be more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: eric king INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql question
That's fairly typical behavior. Try the following SELECT /*+ NO_MERGE(x) */ COUNT(*) FROM (your 1 second query) x Kevin -Original Message- Sent: Monday, January 12, 2004 2:14 PM To: Multiple recipients of list ORACLE-L Hi List, I have following sql that runs in 1 sec: SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) However, when I try to count above query as following, it hangs. Does someone have any ideas? SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s WHERE (s.award_number = 'ALL') OR (b.award_number = s.award_number AND s.project_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND s.task_number = 'ALL') OR (b.award_number = s.award_number AND b.project_number= s.project_number AND b.task_number = s.task_number) _ High-speed users-be more efficient online with the new MSN Premium Internet Software. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Toepke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql question
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. --{56703FBA-6707-4823-B54F-C1F79FFDC9D6} Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: base64 DQoNCg0KDQpIaSBEYXZpZCwNCg0KRG9lcyB0aGUgZmlyc3QgcXVlcnkgInJ1biB0byBjb21wbGV0 aW9uIiBpbiAxIHNlY29uZCwgb3IgImJlZ2lucyByZXR1cm5pbmcNCnJlc3VsdHMiIGluIDEgc2Vj b25kPz8/DQoNCklFOiBIYXZlIHlvdSBydW4gdGhlIHF1ZXJ5IGluIFNRTCpQbHVzIGFuZCB3YWl0 ZWQgZm9yIHRoZSBwcm9tcHQgdG8gcmV0dXJuPw0KSSBzdXNwZWN0IGl0IHdpbGwgc3Bvb2wgZm9y IGEgd2hpbGUuDQoNClNvbWUgcHJvZ3JhbXMgKGxpa2UgVE9BRCBmb3IgZXhhbXBsZSkgYXBwZWFy IHRvIGhhdmUgZmluaXNoZWQgdGhlIHF1ZXJ5IGFzDQpzb29uIGFzIHRoZSBmaXJzdCBwYWdlIG9m IHJlc3VsdHMgYXJlIGluLCBidXQgd2hlbiB5b3Ugc2Nyb2xsIGRvd24gaXQNCnJlcXVlc3RzIG1v cmUgcmVjb3JkcyBmcm9tIHRoZSBkYXRhYmFzZS4gIFNlbGVjdGluZyBDT1VOVCgqKSBjYW5ub3Qg cmV0dXJuDQp1bnRpbCB0aGUgZW50aXJlIHNlYXJjaCBpcyBjb21wbGV0ZSBiZWNhdXNlIGl0IG5l ZWRzIHRoZSB0b3RhbCBudW1iZXIuDQoNCklmIHRoZSBmaXJzdCBxdWVyeSBnZW51aW5lbHkgcnVu cyB0byBjb21wbGV0aW9uIGluIDEgc2Vjb25kIHRob3VnaCwgdGhlbg0KcG9zdCB0aGUgZXhwbGFp biBwbGFucyBhcyBzdWdnZXN0ZWQgYnkgRGVubmlzLiAgSSBzdXNwZWN0IHRob3VnaCB0aGF0IHlv dQ0KYXJlIHNpbXBseSBtaXN1bmRlcnN0YW5kaW5nIHRoZSAxIHNlY29uZCByZXN1bHQuDQoNCklN UE9SVEFOVCBTSURFIElTU1VFOg0KQWxzbywgbG9va2luZyBhdCB5b3VyIHF1ZXJ5IEkgc3VzcGVj dCB5b3Ugd2lsbCBnZXQgYSBjYXJ0ZXNpYW4gam9pbiBpbiB0aGUNCmZpcnN0IHBhcnQgb2YgeW91 ciB3aGVyZSBjbGF1c2UgLSBGb3IgZWFjaCByZWNvcmQgaW4gInMiIHdoZXJlIGF3YXJkX251bWJl cg0KPSAnQUxMJyB5b3Ugd2lsbCBnZXQgZXZlcnkgcm93IGZyb20gImIiIC0gYSBtYXNzaXZlIHJl c3VsdCBzZXQgdGhhdA0KcHJvYmFibHkgZXhwbGFpbnMgeW91ciBsb25nIHJ1bm5pbmcgcXVlcnku ICBTb21lb25lIGVsc2UgbWlnaHQgd2FudCB0bw0KY29uZmlybSBteSBzdXNwaWNpb24/ICBUaGUg ZXhwbGFpbiBwbGFuIG1pZ2h0IGV2ZW4gaW5kaWNhdGUgdGhhdCBhDQpjYXJ0ZXNpYW4gam9pbiBp cyBvY2N1cmluZywgYnV0IEknbSBub3Qgc3VyZSBiZWNhdXNlIG9mIHRoZSBvdGhlciBjbGF1c2Vz Lg0KDQpSZWdhcmRzLA0KICAgICAgTWFyay4NCg0KDQoNCg0KICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAN CiAgICAgICAgICAgICAgICAgICAgICAiRGF2aWQgQm95ZCIgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAgICAgICAgICAgPGRhdmlkYjE1OEBo b3RtYWkgICAgICAgIFRvOiAgICAgICBNdWx0aXBsZSByZWNpcGllbnRzIG9mIGxpc3QgT1JBQ0xF LUwgPE9SQUNMRS1MQGZhdGNpdHkuY29tPiAgICAgICAgICAgICAgICAgIA0KICAgICAgICAgICAg ICAgICAgICAgIGwuY29tPiAgICAgICAgICAgICAgICAgICBjYzogICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBTZW50IGJ5OiAgICAgICAgICAgICAgICAg U3ViamVjdDogIHNxbCBxdWVzdGlvbiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAgICAgICAgICAgbWwt ZXJyb3JzQGZhdGNpdHkgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0KICAg ICAgICAgICAgICAgICAgICAgIC5jb20gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIA0KICAgICAgICAgICAgICAgICAgICAgIDEzLzAxLzIwMDQgMDY6MTQgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBQbGVhc2Ug cmVzcG9uZCB0byAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAgICAg ICAgICAgICAgICAgICAgT1JBQ0xFLUwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAg
Re: sql question (RESEND)
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. --{E9F68FD4-42A7-47CE-A4DD-84FB99CBBCFE} Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: base64 DQoNCg0KDQpTb3JyeSBpZiB0aGlzIGFycml2ZXMgdHdpY2UgLSBJIHNlbnQgaXQgc2V2ZXJhbCBo b3VycyBhZ28gYW5kIG5ldmVyIHNhdyBpdA0KYXBwZWFyIGV2ZW4gdGhvdWdoIG1lc3NhZ2VzIEkg cG9zdGVkIGxhdGVyIGFwcGVhcmVkIHZlcnkgZmFzdC4NCg0KDQoNCiAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgDQogICAgICAgICAgICAgICAgICAgICAgTWFyayBSaWNoYXJkICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICBUbzogICAgICBPUkFDTEUtTEBmYXRjaXR5LmNvbSAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAg ICAgICAgICAgICAgICAxMy8wMS8yMDA0IDA4OjMwICAgICAgICAgY2M6ICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIFN1YmplY3Q6IFJlOiBzcWwgcXVlc3Rpb24oRG9jdW1lbnQgbGluazogTWFyayBSaWNoYXJk KSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAN Cg0KDQoNCkhpIERhdmlkLA0KDQpEb2VzIHRoZSBmaXJzdCBxdWVyeSAicnVuIHRvIGNvbXBsZXRp b24iIGluIDEgc2Vjb25kLCBvciAiYmVnaW5zIHJldHVybmluZw0KcmVzdWx0cyIgaW4gMSBzZWNv bmQ/Pz8NCg0KSUU6IEhhdmUgeW91IHJ1biB0aGUgcXVlcnkgaW4gU1FMKlBsdXMgYW5kIHdhaXRl ZCBmb3IgdGhlIHByb21wdCB0byByZXR1cm4/DQpJIHN1c3BlY3QgaXQgd2lsbCBzcG9vbCBmb3Ig YSB3aGlsZS4NCg0KU29tZSBwcm9ncmFtcyAobGlrZSBUT0FEIGZvciBleGFtcGxlKSBhcHBlYXIg dG8gaGF2ZSBmaW5pc2hlZCB0aGUgcXVlcnkgYXMNCnNvb24gYXMgdGhlIGZpcnN0IHBhZ2Ugb2Yg cmVzdWx0cyBhcmUgaW4sIGJ1dCB3aGVuIHlvdSBzY3JvbGwgZG93biBpdA0KcmVxdWVzdHMgbW9y ZSByZWNvcmRzIGZyb20gdGhlIGRhdGFiYXNlLiAgU2VsZWN0aW5nIENPVU5UKCopIGNhbm5vdCBy ZXR1cm4NCnVudGlsIHRoZSBlbnRpcmUgc2VhcmNoIGlzIGNvbXBsZXRlIGJlY2F1c2UgaXQgbmVl ZHMgdGhlIHRvdGFsIG51bWJlci4NCg0KSWYgdGhlIGZpcnN0IHF1ZXJ5IGdlbnVpbmVseSBydW5z IHRvIGNvbXBsZXRpb24gaW4gMSBzZWNvbmQgdGhvdWdoLCB0aGVuDQpwb3N0IHRoZSBleHBsYWlu IHBsYW5zIGFzIHN1Z2dlc3RlZCBieSBEZW5uaXMuICBJIHN1c3BlY3QgdGhvdWdoIHRoYXQgeW91 DQphcmUgc2ltcGx5IG1pc3VuZGVyc3RhbmRpbmcgdGhlIDEgc2Vjb25kIHJlc3VsdC4NCg0KSU1Q T1JUQU5UIFNJREUgSVNTVUU6DQpBbHNvLCBsb29raW5nIGF0IHlvdXIgcXVlcnkgSSBzdXNwZWN0 IHlvdSB3aWxsIGdldCBhIGNhcnRlc2lhbiBqb2luIGluIHRoZQ0KZmlyc3QgcGFydCBvZiB5b3Vy IHdoZXJlIGNsYXVzZSAtIEZvciBlYWNoIHJlY29yZCBpbiAicyIgd2hlcmUgYXdhcmRfbnVtYmVy DQo9ICdBTEwnIHlvdSB3aWxsIGdldCBldmVyeSByb3cgZnJvbSAiYiIgLSBhIG1hc3NpdmUgcmVz dWx0IHNldCB0aGF0DQpwcm9iYWJseSBleHBsYWlucyB5b3VyIGxvbmcgcnVubmluZyBxdWVyeS4g IFNvbWVvbmUgZWxzZSBtaWdodCB3YW50IHRvDQpjb25maXJtIG15IHN1c3BpY2lvbj8gIFRoZSBl eHBsYWluIHBsYW4gbWlnaHQgZXZlbiBpbmRpY2F0ZSB0aGF0IGENCmNhcnRlc2lhbiBqb2luIGlz IG9jY3VyaW5nLCBidXQgSSdtIG5vdCBzdXJlIGJlY2F1c2Ugb2YgdGhlIG90aGVyIGNsYXVzZXMu DQoNClJlZ2FyZHMsDQogICAgICBNYXJrLg0KDQoNCg0KDQogICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0K ICAgICAgICAgICAgICAgICAgICAgICJEYXZpZCBCb3lkIiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICA8ZGF2aWRiMTU4QGhv dG1haSAgICAgICAgVG86ICAgICAgIE11bHRpcGxlIHJlY2lwaWVudHMgb2YgbGlzdCBPUkFDTEUt TCA8T1JBQ0xFLUxAZmF0Y2l0eS5jb20+ICAgICAgICAgICAgICAgICAgDQogICAgICAgICAgICAg ICAgICAgICAgbC5jb20+ICAgICAgICAgICAgICAgICAgIGNjOiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgIA0KICAgICAgICAgICAgICAgICAgICAgIFNlbnQgYnk6ICAgICAgICAgICAgICAgICBT dWJqZWN0OiAgc3FsIHF1ZXN0aW9uICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICBtbC1l cnJvcnNAZmF0Y2l0eSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQogICAg
Sql question
Hallo, I have this sql query. I would like to change it so I could pick out the three forst three charachters in the between statements. How can I do that in the most simply way? and vgr in (between 101 and 101) and vgr in (between 104 and 104) order by Avdnr ,Datum1 in the statement above I woul dlike to do the changing, select substr(Avdnr,2) Avdnr , Dato Datum1, Dato datum2 , lpad(vgr,3) VGRP from kunlin A where transfil_id in (select transfil_id from transfil_dag where avdnr between 20 and 29 and dato between to_date ('2003-12-07','-mm-dd') and to_date ('2003-12-07','-mm-dd')) and vgr in (between 101 and 101) and vgr in (between 104 and 104) order by Avdnr ,Datum1 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Simple SQL Question
Hello: I'm trying to figure out the new 9i outer joins. I can get a single table outer join working without any issues. But seem to keep getting errors when trying to do a two table outer join. I know it is just something with my syntax. Could anyone provide a quick sample, thanks in advance. A:= Base Table B:= Child Table 1 B:= Child Table 2 select a.emp, b.ValueA c.ValueB From base_table A outer join child_Table1 B on A.emp=B.Emp... I know the old way of select a.emp, b.ValueA c.ValueB From base_table A, child_Table1 B, child_Table2 C where A.emp=B.Emp(+) and A.emp=C.Emp(+) _ Grab our best dial-up Internet access offer: 6 months @$9.95/month. http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Simple SQL Question
select a.emp, b.ValueA c.ValueB From base_table A RIGHT OUTER JOIN child_Table1 B ON b.emp = a.emp RIGHT OUTER JOIN child_Table2 C ON c.emp = a.emp -Original Message- Sent: Thursday, December 18, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Hello: I'm trying to figure out the new 9i outer joins. I can get a single table outer join working without any issues. But seem to keep getting errors when trying to do a two table outer join. I know it is just something with my syntax. Could anyone provide a quick sample, thanks in advance. A:= Base Table B:= Child Table 1 B:= Child Table 2 select a.emp, b.ValueA c.ValueB From base_table A outer join child_Table1 B on A.emp=B.Emp... I know the old way of select a.emp, b.ValueA c.ValueB From base_table A, child_Table1 B, child_Table2 C where A.emp=B.Emp(+) and A.emp=C.Emp(+) _ Grab our best dial-up Internet access offer: 6 months @$9.95/month. http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kevin Toepke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i pl/sql question
Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i pl/sql question
The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i pl/sql question
what does myfunction1() do? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 16, 2003 10:24 AM To: Multiple recipients of list ORACLE-L The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i pl/sql question
Below is the code for myfunction1 in the package, It calls a bunch of other functions, such as getBlastMatches, escapeGene, genes.gene2protein and addItem. Guang --- type blastMatch is record ( geneName gene.name%type, percent number ); function myfunction1 (seqid in number, sid in number, secondsid in number default -1) return varchar2 is tbl blastMatches; cnt number; item varchar2(256); str varchar2(256); begin cnt := getBlastMatches(seqid, sid, tbl, secondsid); if cnt1 then return null; end if; for i in 1..cnt loop item := escapeGene(sid, tbl(i).geneName, genes.gene2protein(tbl(i).geneName, sid)) || ' (' || tbl(i).percent || '%)'; if not addItem(str, item, '; ', 80) then exit; end if; end loop; return str; exception when others then return null; end myfunction1; -- function getBlastMatches (seqid in number, spid in number, matchTable out blastMatches, secondspid in number default -1) return number is cursor bcur is select queryid, subjid, 100.0*identity/matchlen pct from blastresults where ((subjspid in (spid,secondspid) and queryid = seqid) or (queryspid in (spid,secondspid) and subjid = seqid)) and (identity/matchlen = .200 or positive/matchlen = .400) order by blast.pvalToNumber(pval) asc, score desc; match number; cnt number := 0; gname gene.name%type; begin for bmatch in bcur loop if bmatch.queryid=seqid then match := bmatch.subjid; else match := bmatch.queryid; end if; BEGIN select name into gname from gene,seqtable where geneid=gene.id and aaseqid = match and gene.use = 'Y' and seqtable.use='Y'; EXCEPTION when no_data_Found then gname := NULL; END; if gname is not null then cnt := cnt + 1; matchTable(cnt).geneName := gname; matchTable(cnt).percent := round(bmatch.pct, 0); if cnt = maxMatches then return cnt; end if; end if; end loop; return cnt; exception when others then return 0; end getBlastMatches; -Original Message- Jamadagni, Rajendra Sent: Tuesday, December 16, 2003 10:45 AM To: Multiple recipients of list ORACLE-L what does myfunction1() do? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 16, 2003 10:24 AM To: Multiple recipients of list ORACLE-L The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn,
8i pl/sql question
Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i pl/sql question
can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pl/sql question and owa_pattern question
Guang, I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters. I would suggest something such as : - First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it). - Start with initializing your string to LTRIM(string, '#') - Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa). This will be probably much faster than a character-by-character loop and calls to an owa package. HTH, Stephane Faroult - --- Original Message --- - From: Guang Mei [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 19:39:55 Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W'); -- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pl/sql question and owa_pattern question
Hi Stephane: Thanks for your good suggestion. I compared the method you suggested and the orginal one and it indeed boosted the performance (in my simple test). However the ONLY problem I am having is that by doing TRANSLATE, I lost the original delimits. The new method (you suggested) correctly extract the words (and sent for processing), But after processing I need to put processed-words back to the orginal string with orginal demilters un-changed. I tried to track to position of delimit from the orginal string by doing global_pos := global_pos + pos ; in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos wrong when ltrim trims '#'. Any work-around? TIA. Guang -Original Message- Stephane Faroult Sent: Friday, November 21, 2003 4:19 AM To: Multiple recipients of list ORACLE-L Guang, I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters. I would suggest something such as : - First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it). - Start with initializing your string to LTRIM(string, '#') - Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa). This will be probably much faster than a character-by-character loop and calls to an owa package. HTH, Stephane Faroult - --- Original Message --- - From: Guang Mei [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 19:39:55 Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W'); -- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei
RE: pl/sql question and owa_pattern question
Perl is a good tool for text processing. But our program is already written in pl/sql long time ago and there are intensive db calls in this pl/sql program. (text processing is only part of it). So I can not change that. BTW I did a comparison study a while ago for some of our pl/sql packages (specifically for our application). When there are lots of db calls (select, insert, update and delete), pl/sql package is faster than correponding perl program (I made sure sqls are prepared once and used bind variables in perl. All code were executed on the unix server, no other programs were running, etc). That's why we stick to pl/sql because our app need the performance. Others may have different results, it all depends on what the code does. Guang -Original Message- Mladen Gogala Sent: Thursday, November 20, 2003 11:14 PM To: Multiple recipients of list ORACLE-L I don't know about PL/SQL but here is how I would get separate words from a big string: #!/usr/bin/perl -w use strict; my (@ARR); while () { chomp; @ARR = split(/[^0-9a-zA-Z_\.,]/); foreach (@ARR) { print $_\n; } } There is something called DBI and it can be used to insert separated words into the database, instead of printing them. The bottom line is that perl is an excellent tool for parsing strings and all sorts of string manipulation. On 2003.11.20 22:39, Guang Mei wrote: Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an
RE: pl/sql question and owa_pattern question
Guang, Well you are almost there ... you need fifo structure namely a pl/sql array 1. create a local pl/sql array to store the delimiter (store the ascii value of the delimiter to be safe) my_array (varchar2(5)) 2. as you find a delimiter insert into the first position in the array and replace the delimiting character with # 3. lather.rinse.repeat. when it is time to put it back use a loop nIndex := 0; nPos := 0; loop npos := instr(my_str,'#',1); exit when npos := 0; nIndex := nindex + 1; my_str := substr(my_str,1,nPos-1) || chr(my_array(nIndex)) || sybstr(my_str, nPos+1); end loop; something like this should help, proof-read though ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, November 21, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Hi Stephane: Thanks for your good suggestion. I compared the method you suggested and the orginal one and it indeed boosted the performance (in my simple test). However the ONLY problem I am having is that by doing TRANSLATE, I lost the original delimits. The new method (you suggested) correctly extract the words (and sent for processing), But after processing I need to put processed-words back to the orginal string with orginal demilters un-changed. I tried to track to position of delimit from the orginal string by doing global_pos := global_pos + pos ; in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos wrong when ltrim trims '#'. Any work-around? TIA. Guang -Original Message- Stephane Faroult Sent: Friday, November 21, 2003 4:19 AM To: Multiple recipients of list ORACLE-L Guang, I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters. I would suggest something such as : - First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it). - Start with initializing your string to LTRIM(string, '#') - Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa). This will be probably much faster than a character-by-character loop and calls to an owa package. HTH, Stephane Faroult - --- Original Message --- - From: Guang Mei [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 20 Nov 2003 19:39:55 Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W'); -- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0
Re: pl/sql question and owa_pattern question
PL/SQL is the fastest thing of them all when it comes to executing SQL commands, but there are things which simply aren't practical in 9.2 PL/SQL. Regular expression processing is one of those things. Fortunately, you can mix the two. Without DBI, perl scripts simply woudn't be very useful. Of course, there are things that are faster then even the fastest perl script. Lexer written in C is one of them and you don't need much work to write one, either, but using OCI is not easy. OCI is a library written to confuse the enemy, not to help developer. Using plain and simple regex or PCRE within a C program is the same thing as above, but slightly more complicated then a lexer. For the specific task of manipulating patterns and resolving regular expressions, I use perl almost exclusively because I find it an optimal tradeoff between ease of use and performance. If performance is a paramount, as in real time application processing, then you'll have to resort to C and, possibly, write an external procedure and, thus, enabling oracle to use C regex calls or even pcre. I was toying with the idea of enabling oracle to use PCRE but I gave up when I read that 10g will have that included. On 11/21/2003 11:59:31 AM, Guang Mei wrote: Perl is a good tool for text processing. But our program is already written in pl/sql long time ago and there are intensive db calls in this pl/sql program. (text processing is only part of it). So I can not change that. BTW I did a comparison study a while ago for some of our pl/sql packages (specifically for our application). When there are lots of db calls (select, insert, update and delete), pl/sql package is faster than correponding perl program (I made sure sqls are prepared once and used bind variables in perl. All code were executed on the unix server, no other programs were running, etc). That's why we stick to pl/sql because our app need the performance. Others may have different results, it all depends on what the code does. Guang -Original Message- Mladen Gogala Sent: Thursday, November 20, 2003 11:14 PM To: Multiple recipients of list ORACLE-L I don't know about PL/SQL but here is how I would get separate words from a big string: #!/usr/bin/perl -w use strict; my (@ARR); while () { chomp; @ARR = split(/[^0-9a-zA-Z_\.,]/); foreach (@ARR) { print $_\n; } } There is something called DBI and it can be used to insert separated words into the database, instead of printing them. The bottom line is that perl is an excellent tool for parsing strings and all sorts of string manipulation. On 2003.11.20 22:39, Guang Mei wrote: Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this
Re: pl/sql question and owa_pattern question
Would extproc_perl fit well enough, though, until 10g is here? On Fri, 21 Nov 2003, Mladen Gogala wrote: PL/SQL is the fastest thing of them all when it comes to executing SQL commands, but there are things which simply aren't practical in 9.2 PL/SQL. Regular expression processing is one of those things. Fortunately, you can mix the two. Without DBI, perl scripts simply woudn't be very useful. Of course, there are things that are faster then even the fastest perl script. Lexer written in C is one of them and you don't need much work to write one, either, but using OCI is not easy. OCI is a library written to confuse the enemy, not to help developer. Using plain and simple regex or PCRE within a C program is the same thing as above, but slightly more complicated then a lexer. For the specific task of manipulating patterns and resolving regular expressions, I use perl almost exclusively because I find it an optimal tradeoff between ease of use and performance. If performance is a paramount, as in real time application processing, then you'll have to resort to C and, possibly, write an external procedure and, thus, enabling oracle to use C regex calls or even pcre. I was toying with the idea of enabling oracle to use PCRE but I gave up when I read that 10g will have that included. On 11/21/2003 11:59:31 AM, Guang Mei wrote: Perl is a good tool for text processing. But our program is already written in pl/sql long time ago and there are intensive db calls in this pl/sql program. (text processing is only part of it). So I can not change that. BTW I did a comparison study a while ago for some of our pl/sql packages (specifically for our application). When there are lots of db calls (select, insert, update and delete), pl/sql package is faster than correponding perl program (I made sure sqls are prepared once and used bind variables in perl. All code were executed on the unix server, no other programs were running, etc). That's why we stick to pl/sql because our app need the performance. Others may have different results, it all depends on what the code does. Guang -Original Message- Mladen Gogala Sent: Thursday, November 20, 2003 11:14 PM To: Multiple recipients of list ORACLE-L I don't know about PL/SQL but here is how I would get separate words from a big string: #!/usr/bin/perl -w use strict; my (@ARR); while () { chomp; @ARR = split(/[^0-9a-zA-Z_\.,]/); foreach (@ARR) { print $_\n; } } There is something called DBI and it can be used to insert separated words into the database, instead of printing them. The bottom line is that perl is an excellent tool for parsing strings and all sorts of string manipulation. On 2003.11.20 22:39, Guang Mei wrote: Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City
pl/sql question and owa_pattern question
Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql question and owa_pattern question
I don't know about PL/SQL but here is how I would get separate words from a big string: #!/usr/bin/perl -w use strict; my (@ARR); while () { chomp; @ARR = split(/[^0-9a-zA-Z_\.,]/); foreach (@ARR) { print $_\n; } } There is something called DBI and it can be used to insert separated words into the database, instead of printing them. The bottom line is that perl is an excellent tool for parsing strings and all sorts of string manipulation. On 2003.11.20 22:39, Guang Mei wrote: Hi: In my pl/sql program, I want to process each word in a string. The string is selected from a varchar2(300) column. The delimit that separates the words is not necessary space character. The definition of the delimit in this program is set as 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z) and 2. the character is not one of these: '-.,/*_' Now my program is basically checking each character, find the delimit, and rebuild each word. After that I process each word. The code looks like this: --- str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; len := length(str)+1; for i in 1..len loop ch := substr(str,i,1); if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then if word is not null then -- do some processing to variable word ! word := null;-- reset it end if; else word := word || ch; -- concat ch to word end if; end loop; --- I think It's taking too long because it loops through each characters. I hope I could find a way to speed it up. I don't have experiience in owa_pattern, but I thought there might be a way to do it here: str := This will be a long string with length upto 300 characters, it may contain some invisible characters'; newstr := str; pos := 1; while pos != 0 loop pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out these '-.,/*_' ??? word := substr(newstr, 1, pos-1); -- do some processing to variable word ! if pos != 0 then newstr := substr(newstr, pos+1); end if; end loop; -- My simple tests showed that owa_pattern call is much slower than direct string manupilation. But I would like to try it in this case if I could easily get the wrods from the string. Any suggestions? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Jacques, I checked your example, I think there are some issues here: 1. Original queries provided below do use merge join. 2. We could have missing indexes which can exist on real system. 3. Timings below is not a criteria -- after gathering statistics and creation an index on val this both queries take about 1,3 seconds. So it means on your system you checked the *speed of sort operation* only -- because, most probably, merge was used. Even w/o index but with hash join it works much more faster -- 11.87 vs 1.25 (figures are not precise). 4. It'a all for nothing -- life is cruel and real-life examples are much more complex :) If you do not mind I would not continue this discussion. Thank you. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jacques Kilchoer wrote: Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there! In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise. With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice) If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Jacques -- Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. HTH, Bambi. = create table gab (usr char(3),val number); insert into gab values ('GAP',1); insert into gab values ('GAP',5); insert into gab values ('GAP',5); insert into gab values ('GAP',7); insert into gab values ('PAG',1); insert into gab values ('PAG',7); insert into gab values ('PAG',2); insert into gab values ('JKL',1); insert into gab values ('JKL',5); insert into gab values ('JKL',5); insert into gab values ('GPA',1); insert into gab values ('GPA',5); insert into gab values ('GPA',7); insert into gab values ('GPA',8); select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; USR --- GAP GPA -Original Message- Sent: Friday, November 14, 2003 7:24 PM To: Multiple recipients of list ORACLE-L -Original Message- Bellow, Bambi Why not do it like this... select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; Because that way you would get the wrong answer. With the sample data as kindly provided by Mr. Begun the correct query would return one row, but your query returns two rows. SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP5 GAP7 PAG1 PAG7 PAG2 JKL1 JKL5 JKL5 GPA1 GPA5 GPA7 GPA8 14 ligne(s) sélectionnée(s). SQL select usr from gab 2 where val=1 3 intersect 4 select usr from gab 5 where val=5 6 intersect 7 select usr from gab 8 where val=7; USR -- GAP GPA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Just a guess: select distinct usr from xxx where (select count(*) from xxx group by Usr) = (select count(*) from xxx group by Usr, val) -Original Message- Sent: Thursday, November 13, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Gabriel Aragon wrote: I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Using Bambi's table and values. Try this query, it's ugly but it works (kind of like the contributor...) Daniel 1 select o.usr, count(o.usr) 2 from (select distinct usr, val 3from gab 4where val in (1,5,7) 5 and usr not in (select usr 6 from gab 7 where val not in (1,5,7))) o 8 group by o.usr 9* having count(o.usr) = 3 Jacques Kilchoer wrote: The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Gabriel, How about this untested code? Alan. select usr from ( select usr, sum(decode(val,1,1,0)) look1, -- flag for 1 sum(decode(val,5,5,0)) look2, -- flag for 5 sum(decode(val,7,7,0)) look3, -- flag for 7 sum(decode(val,1,0,5,0,7,0,1)) look4 -- flag for others from the_table group by usr ) where look1 = 1 AND look2 = 1 AND look3 = 1 AND look4 = 0 / -Original Message- Sent: Thursday, November 13, 2003 2:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Alan Gano INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Ok, guys I have to apologize twice, First: the delay to answer your very kind emails, (you know the urgent problems dont let you time for the important ones) Second: maybe my question was not clear enough, ciertanly what Bambi says is what I need give me all the usrs where there exists a record containing 1 AND 5 AND 7, the criteria was the list, not the records, so it does not matter if the user has many more records, but if he/she has those records that are in the list, that is what I want, the solution is as simple as Bambi's query. I really really appreciate all the solutions provided for you guys, I swear I tested every one. Below I have a copy of my original email. Thank you very much! Gabriel Aragon +++ I have a table like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. Gabriel +++ --- Bellow, Bambi [EMAIL PROTECTED] wrote: Jacques -- Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. HTH, Bambi. = create table gab (usr char(3),val number); insert into gab values ('GAP',1); insert into gab values ('GAP',5); insert into gab values ('GAP',5); insert into gab values ('GAP',7); insert into gab values ('PAG',1); insert into gab values ('PAG',7); insert into gab values ('PAG',2); insert into gab values ('JKL',1); insert into gab values ('JKL',5); insert into gab values ('JKL',5); insert into gab values ('GPA',1); insert into gab values ('GPA',5); insert into gab values ('GPA',7); insert into gab values ('GPA',8); select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; USR --- GAP GPA -Original Message- Sent: Friday, November 14, 2003 7:24 PM To: Multiple recipients of list ORACLE-L -Original Message- Bellow, Bambi Why not do it like this... select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; Because that way you would get the wrong answer. With the sample data as kindly provided by Mr. Begun the correct query would return one row, but your query returns two rows. SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP5 GAP7 PAG1 PAG7 PAG2 JKL1 JKL5 JKL5 GPA1 GPA5 GPA7 GPA8 14 ligne(s) sélectionnée(s). SQL select usr from gab 2 where val=1 3 intersect 4 select usr from gab 5 where val=5 6 intersect 7 select usr from gab 8 where val=7; USR -- GAP GPA -- = Any dream worth having is a dream worth fighting for(Cualquier sueño que valga la pena tener, es un sueño por el que vale la pena luchar)Charles Xavier __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Jacques Kilchoer wrote: Mr. Begun: I'm not convinced that your answer is quite the right one. I've provided two solutions but I'm still confused :). Jacques, does that mean that I understand English and the original query was Ok? :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Gabriel Aragon wrote: Ok, guys I have to apologize twice, ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there! In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise. With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice) If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32 VB query: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (SELECT DISTINCT element FROM ( SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element FROM ( SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p , INSTR(:list, ',', 1, ROWNUM) c FROM gab WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) ) ) ) AND cnt = 4 -- it's for nothing, because count can be give by caller GROUP BY usr , cnt HAVING COUNT(*) = 4 ; JRK query: select a.usr from (select distinct b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt from gab b ) a where val in (select * from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) and cnt = 4 group by usr, cnt having count(*) = cnt ; Test data creation: drop table gab; create table gab (usr varchar2(10) not null, val number not null) ; declare insert_cnt constant pls_integer := 20 ; commit_cnt constant pls_integer := 2000 ; i pls_integer ; j pls_integer ; k pls_integer ; l pls_integer ; n pls_integer ; usr gab.usr%type ; type usrt is table of gab.usr%type index by binary_integer ; usra usrt ; type valt is table of gab.val%type index by binary_integer ; vala valt ; begin dbms_random.initialize (dbms_utility.get_time) ; i := 1 ; while i = insert_cnt loop usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26)) || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ; n := mod (abs (dbms_random.random), 5) + 1 ; j := mod (i - 1, commit_cnt) + 1 ; k := least (commit_cnt, j + n - 1) ; for l in j..k loop usra (l) := usr ; vala (l) := mod (abs (dbms_random.random), 9) + 1 ; end loop ; i := i + k - j + 1 ; if k = commit_cnt or i = insert_cnt then forall m in 1..k insert into gab (usr, val) values (usra (m), vala (m)) ; commit ; end if ; end loop ; commit ; end ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Jacques, you can use my first name -- Mr. is too official for this list :). You have modified the query, however I would suggest you to check execution plan (and present it here) and remove LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) by replacing it using number of elements in the list i.e., in your case, 4. As I already said, it was just an example, in real life I would think is it Ok or not Ok to use it. Timing is not everything you can check, consider statisticts. Did you consider indexing val? -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jacques Kilchoer wrote: Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there! In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise. With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice) If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32 VB query: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (SELECT DISTINCT element FROM ( SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element FROM ( SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p , INSTR(:list, ',', 1, ROWNUM) c FROM gab WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) ) ) ) AND cnt = 4 -- it's for nothing, because count can be give by caller GROUP BY usr , cnt HAVING COUNT(*) = 4 ; JRK query: select a.usr from (select distinct b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt from gab b ) a where val in (select * from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) and cnt = 4 group by usr, cnt having count(*) = cnt ; Test data creation: drop table gab; create table gab (usr varchar2(10) not null, val number not null) ; declare insert_cnt constant pls_integer := 20 ; commit_cnt constant pls_integer := 2000 ; i pls_integer ; j pls_integer ; k pls_integer ; l pls_integer ; n pls_integer ; usr gab.usr%type ; type usrt is table of gab.usr%type index by binary_integer ; usra usrt ; type valt is table of gab.val%type index by binary_integer ; vala valt ; begin dbms_random.initialize (dbms_utility.get_time) ; i := 1 ; while i = insert_cnt loop usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26)) || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ; n := mod (abs (dbms_random.random), 5) + 1 ; j := mod (i - 1, commit_cnt) + 1 ; k := least (commit_cnt, j + n - 1) ; for l in j..k loop usra (l) := usr ; vala (l) := mod (abs (dbms_random.random), 9) + 1 ; end loop ; i := i + k - j + 1 ; if k = commit_cnt or i = insert_cnt then forall m in 1..k insert into gab (usr, val) values (usra (m), vala (m)) ; commit ; end if ; end loop ; commit ; end ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Why not do it like this... select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; -Original Message- Sent: Thursday, November 13, 2003 7:35 PM To: Multiple recipients of list ORACLE-L Mr. Begun: I'm not convinced that your answer is quite the right one. I tried INSERT INTO gab VALUES ('GAP', 9) ; and then this query SELECT usr FROM ( SELECT DISTINCT usr, val FROM gab ) WHERE val IN (1, 5, 7) GROUP BY usr HAVING COUNT(*) = 3 -- number of elements in the list / returned the value 'GAP' even though 'GAP' has 4 vals in the table. The HAVING COUNT (*) = should also match the number of distinct rows for usr. Inspired by Tom Kyte's answer varying elements in IN list http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:110612348061, I propose this solution, using a str_to_tbl function (see function definition after the proof of concept.) SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP7 GAP9 JKL8 JKL5 XXX1 XXX5 8 ligne(s) sélectionnée(s). SQL variable num_list varchar2 (4000) SQL select b.usr 2 from 3 (select distinct a.usr, a.val from gab a) b, 4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d 5 where 6 b.usr = d.usr and 7 b.val in (select * 8from 9 the (select 10 cast (str_to_tbl (:num_list) as my_number_table) 11 from dual 12 ) 13 ) 14 group by b.usr, d.num_usr_val 15 having 16 count(*) = d.num_usr_val 17 and count (*) = (select count (*) 18 from 19 the (select 20 cast (str_to_tbl (:num_list) as my_number_table) 21 from dual 22 ) 23 ) 24 SQL execute :num_list := '1,5' Procédure PL/SQL terminée avec succès. SQL / USR -- XXX SQL execute :num_list := ' 8 , 5 ' Procédure PL/SQL terminée avec succès. SQL / USR -- JKL SQL execute :num_list := '1,5,7' Procédure PL/SQL terminée avec succès. SQL / aucune ligne sélectionnée SQL execute :num_list := '1,5,7,8' Procédure PL/SQL terminée avec succès. SQL / aucune ligne sélectionnée SQL execute :num_list := '1,5,7,9' Procédure PL/SQL terminée avec succès. SQL / USR -- GAP SQL execute :num_list := '1,5,7,8,9' Procédure PL/SQL terminée avec succès. SQL / aucune ligne sélectionnée script: drop table gab; create table gab (usr varchar2(10) not null, val number not null) ; insert into gab (usr, val) values ('GAP', 1) ; insert into gab (usr, val) values ('GAP', 5) ; insert into gab (usr, val) values ('GAP', 7) ; insert into gab (usr, val) values ('GAP', 9) ; insert into gab (usr, val) values ('JKL', 8) ; insert into gab (usr, val) values ('JKL', 5) ; insert into gab (usr, val) values ('XXX', 1) ; insert into gab (usr, val) values ('XXX', 5) ; commit ; create or replace type my_number_table as table of number ; / create or replace function str_to_tbl (p_str in varchar2) return my_number_table as l_str varchar2 (32760) default p_str || ',' ; l_nnumber ; l_pos pls_integer default 1 ; l_data my_number_table := my_number_table () ; begin loop l_n := instr (l_str, ',', l_pos) ; exit when (nvl (l_n, 0) = 0) ; l_data.extend ; l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n - l_pos))) ; l_pos := l_n + 1 ; end loop; return l_data ; end; / variable num_list varchar2 (4000) select b.usr from (select distinct a.usr, a.val from gab a) b, (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d where b.usr = d.usr and b.val in (select * from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) group by b.usr, d.num_usr_val having count(*) = d.num_usr_val and count (*) = (select count (*) from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) execute :num_list := '1,5' / execute :num_list := ' 8 , 5 ' / execute :num_list := '1,5,7' / execute :num_list := '1,5,7,8' / execute :num_list := '1,5,7,9' / execute :num_list := '1,5,7,8,9' / -Original Message- Vladimir Begun DROP TABLE gab; CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL --, CONSTRAINT gab$uq UNIQUE (usr, val) ); INSERT INTO gab VALUES('GAP', 1); INSERT INTO gab VALUES('GAP', 5); INSERT INTO gab
RE: Silly SQL Question
Yes, your query was much better. I keep on forgetting about those analytic functions. Shame on me. I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) -Original Message- Vladimir Begun ... PL/SQL is not needed to solve this task as SQL task. There reason when it would be wise to rewrite it is out of scope of this topic (but the reason is obvious). I'm just thinking that the query proposed by you is a bit expensive. So, I've re-scribbled mine: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (1, 5, 7) AND cnt = 3 GROUP BY usr , cnt HAVING COUNT(*) = cnt / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
-Original Message- Bellow, Bambi Why not do it like this... select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; Because that way you would get the wrong answer. With the sample data as kindly provided by Mr. Begun the correct query would return one row, but your query returns two rows. SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP5 GAP7 PAG1 PAG7 PAG2 JKL1 JKL5 JKL5 GPA1 GPA5 GPA7 GPA8 14 ligne(s) sélectionnée(s). SQL select usr from gab 2 where val=1 3 intersect 4 select usr from gab 5 where val=5 6 intersect 7 select usr from gab 8 where val=7; USR -- GAP GPA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Jacques Jacques Kilchoer wrote: I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. If the given list is created properly, which I think it's a must in this case, one would not need to use PL/SQL, the task can be solved in SQL only. Below is just *an example*, not a generic solution. VAR list VARCHAR2(30); -- number could be counted as well, not a big deal EXEC :list := '1,7,5,'; WITH numbers AS ( SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element FROM ( SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p , INSTR(:list, ',', 1, ROWNUM) c FROM gab WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) ) ) SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (SELECT DISTINCT element FROM numbers) AND cnt = (SELECT COUNT(DISTINCT element) FROM numbers) -- it's for nothing, because count can be give by caller GROUP BY usr , cnt HAVING COUNT(*) = (SELECT COUNT(DISTINCT element) FROM numbers) -- same / Again, it's not a generic solution but it's Ok to use it for this particular task -- the number of elements is limited anyway. One could add yet one condition to avoid troubles with TO_NUMBER conversion, it's easy but I'm leaving it as is. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) That's obviously nice :) but I think it's not a reason to use PL/SQL to solve this task. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Silly SQL Question
I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Gabriel Aragon wrote: I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Here's one solution. SELECT usr FROM xxx GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 HTH Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aponte, Tony INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Sorry I missed one. Try this. SELECT usr FROM bogus GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 AND SUM(DECODE(val,1,0,5,0,7,0,val)) = 0 Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Here's one solution. SELECT usr FROM xxx GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 HTH Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:05 PM To: Multiple recipients of list ORACLE-L I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aponte, Tony INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aponte, Tony INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Silly SQL Question
Gabriel DROP TABLE gab; CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL --, CONSTRAINT gab$uq UNIQUE (usr, val) ); INSERT INTO gab VALUES('GAP', 1); INSERT INTO gab VALUES('GAP', 5); INSERT INTO gab VALUES('GAP', 7); INSERT INTO gab VALUES('JKL', 8); INSERT INTO gab VALUES('JKL', 5); COMMIT; SELECT usr FROM ( SELECT DISTINCT usr, val FROM gab ) WHERE val IN (1, 5, 7) GROUP BY usr HAVING COUNT(*) = 3 -- number of elements in the list / Depending on the existence of the constraint, here gab$uq, you can either use inline view of run it against original table. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Gabriel Aragon wrote: I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. TIA Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Mr. Begun: I'm not convinced that your answer is quite the right one. I tried INSERT INTO gab VALUES ('GAP', 9) ; and then this query SELECT usr FROM ( SELECT DISTINCT usr, val FROM gab ) WHERE val IN (1, 5, 7) GROUP BY usr HAVING COUNT(*) = 3 -- number of elements in the list / returned the value 'GAP' even though 'GAP' has 4 vals in the table. The HAVING COUNT (*) = should also match the number of distinct rows for usr. Inspired by Tom Kyte's answer varying elements in IN list http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061, I propose this solution, using a str_to_tbl function (see function definition after the proof of concept.) SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP7 GAP9 JKL8 JKL5 XXX1 XXX5 8 ligne(s) sélectionnée(s). SQL variable num_list varchar2 (4000) SQL select b.usr 2 from 3 (select distinct a.usr, a.val from gab a) b, 4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d 5 where 6 b.usr = d.usr and 7 b.val in (select * 8from 9 the (select 10 cast (str_to_tbl (:num_list) as my_number_table) 11 from dual 12 ) 13 ) 14 group by b.usr, d.num_usr_val 15 having 16 count(*) = d.num_usr_val 17 and count (*) = (select count (*) 18 from 19 the (select 20 cast (str_to_tbl (:num_list) as my_number_table) 21 from dual 22 ) 23 ) 24 SQL execute :num_list := '1,5' Procédure PL/SQL terminée avec succès. SQL / USR -- XXX SQL execute :num_list := ' 8 , 5 ' Procédure PL/SQL terminée avec succès. SQL / USR -- JKL SQL execute :num_list := '1,5,7' Procédure PL/SQL terminée avec succès. SQL / aucune ligne sélectionnée SQL execute :num_list := '1,5,7,8' Procédure PL/SQL terminée avec succès. SQL / aucune ligne sélectionnée SQL execute :num_list := '1,5,7,9' Procédure PL/SQL terminée avec succès. SQL / USR -- GAP SQL execute :num_list := '1,5,7,8,9' Procédure PL/SQL terminée avec succès. SQL / aucune ligne sélectionnée script: drop table gab; create table gab (usr varchar2(10) not null, val number not null) ; insert into gab (usr, val) values ('GAP', 1) ; insert into gab (usr, val) values ('GAP', 5) ; insert into gab (usr, val) values ('GAP', 7) ; insert into gab (usr, val) values ('GAP', 9) ; insert into gab (usr, val) values ('JKL', 8) ; insert into gab (usr, val) values ('JKL', 5) ; insert into gab (usr, val) values ('XXX', 1) ; insert into gab (usr, val) values ('XXX', 5) ; commit ; create or replace type my_number_table as table of number ; / create or replace function str_to_tbl (p_str in varchar2) return my_number_table as l_str varchar2 (32760) default p_str || ',' ; l_nnumber ; l_pos pls_integer default 1 ; l_data my_number_table := my_number_table () ; begin loop l_n := instr (l_str, ',', l_pos) ; exit when (nvl (l_n, 0) = 0) ; l_data.extend ; l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n - l_pos))) ; l_pos := l_n + 1 ; end loop; return l_data ; end; / variable num_list varchar2 (4000) select b.usr from (select distinct a.usr, a.val from gab a) b, (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d where b.usr = d.usr and b.val in (select * from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) group by b.usr, d.num_usr_val having count(*) = d.num_usr_val and count (*) = (select count (*) from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) execute :num_list := '1,5' / execute :num_list := ' 8 , 5 ' / execute :num_list := '1,5,7' / execute :num_list := '1,5,7,8' / execute :num_list := '1,5,7,9' / execute :num_list := '1,5,7,8,9' / -Original Message- Vladimir Begun DROP TABLE gab; CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL --, CONSTRAINT gab$uq UNIQUE (usr, val) ); INSERT INTO gab VALUES('GAP', 1); INSERT INTO gab VALUES('GAP', 5); INSERT INTO gab VALUES('GAP', 7); INSERT INTO gab VALUES('JKL', 8); INSERT INTO gab VALUES('JKL', 5); COMMIT; SELECT usr FROM ( SELECT DISTINCT usr, val FROM gab ) WHERE val IN (1, 5, 7) GROUP BY usr HAVING COUNT(*) = 3 -- number of
Re: Silly SQL Question
Jacques Yes, probably, you are right. I've overlooked example section, given by Gabriel. DROP TABLE gab; CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL); INSERT INTO gab VALUES('GAP', 1); INSERT INTO gab VALUES('GAP', 5); INSERT INTO gab VALUES('GAP', 5); INSERT INTO gab VALUES('GAP', 7); INSERT INTO gab VALUES('PAG', 1); INSERT INTO gab VALUES('PAG', 7); INSERT INTO gab VALUES('PAG', 2); INSERT INTO gab VALUES('JKL', 1); INSERT INTO gab VALUES('JKL', 5); INSERT INTO gab VALUES('JKL', 5); INSERT INTO gab VALUES('GPA', 1); INSERT INTO gab VALUES('GPA', 5); INSERT INTO gab VALUES('GPA', 7); INSERT INTO gab VALUES('GPA', 8); COMMIT; PL/SQL is not needed to solve this task as SQL task. There reason when it would be wise to rewrite it is out of scope of this topic (but the reason is obvious). I'm just thinking that the query proposed by you is a bit expensive. So, I've re-scribbled mine: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (1, 5, 7) AND cnt = 3 GROUP BY usr , cnt HAVING COUNT(*) = cnt / HTH, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jacques Kilchoer wrote: Mr. Begun: I'm not convinced that your answer is quite the right one. I tried INSERT INTO gab VALUES ('GAP', 9) ; and then this query SELECT usr FROM ( SELECT DISTINCT usr, val FROM gab ) WHERE val IN (1, 5, 7) GROUP BY usr HAVING COUNT(*) = 3 -- number of elements in the list / returned the value 'GAP' even though 'GAP' has 4 vals in the table. The HAVING COUNT (*) = should also match the number of distinct rows for usr. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
interesting dynamic pl/sql question
Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: interesting dynamic pl/sql question
You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: interesting dynamic pl/sql question
Title: RE: interesting dynamic pl/sql question Ryan, what errors are you getting? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L Subject: RE: interesting dynamic pl/sql question You should be getting errors, because PL/SQL inside execute immediate knows nothing about mystorageArray (or i for that matter) declared in your stored procedure. Probably, you could get by using package variables (and referring to them properly: package_name.var_name, specifically inside your dynamic sql). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**5
Re: interesting dynamic pl/sql question
I think ' begin mystorageArray.field_''i'' := 1; end; '; will not recongize mystoragearray as a variable . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:49 AM Im on 8.1.7. Is it possible to do something like this? Im getting errors: create or replace procedure myproc is TYPE myRecord is RECORD ( field_1 number, field_2 number); TYPE storageArray IS TABLE OF myRecord INDEX BY BINARY_INTEGER; myStorageArray storageArray; i number; begin i := 1; execute immediate ' begin mystorageArray.field_''i'' := 1; end; '; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
interesting sql question
Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: interesting sql question
Ralph, Assuming that there is no history in the BIDS table (meaning that there are no old records indicating a bid recorded last year), I think the following would work just fine. select name from person, (select distinct sid, count(*) bid_count from bids group by sid) bids where person.sid = bids.sid and bid_count = 3 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: interesting sql question
- --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 29 Sep 2003 05:19:39 Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. I would run an uncorrelated subquery on BOATS to count how many of them we have (mot likely to be a multimillion row table, and it's just a PK scan), which you can feed into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and supposing (which is often the case) that your FK is indexed it doesn't require anything but another index scan. Which can of course take *some* time if BIDS is really big but I don't see how to escape a group by here (or anything worse). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: interesting sql question
select pn.name from (select /*+ no_merge */ count(*) boat_cnt from boat) bt, bid bd, person pn where bd.sid = pn.sid group by pn.name, boat_cnt having count(bd.boat_id) = boat_cnt Waleed -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: interesting sql question
From: Stephane Faroult [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 09:59:39 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: interesting sql question - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Mon, 29 Sep 2003 05:19:39 Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. I would run an uncorrelated subquery on BOATS to count how many of them we have (mot likely to be a multimillion row table, and it's just a PK scan), which you can feed into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and supposing (which is often the case) that your FK is indexed it doesn't require anything but another index scan. Which can of course take *some* time if BIDS is really big but I don't see how to escape a group by here (or anything worse). Bitmap scan would be the fastest. Ive noticed that counts on those are incredibly fast. So your saying something like: how would you write the query? I dont quite see it. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: interesting sql question
Title: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: interesting sql question
This would eliminate duplicate bids on the same boat by the same person SELECT p.* FROMPERSON p, ( SELECT COUNT(*) boat_count FROMBOAT ) c, ( SELECT sid, COUNT(DISTINCT boat_id) bid_count FROMBIDS GROUP BY sid ) b WHERE p.sid = b.sid AND b.bid_count = c.boat_count; -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chelur, Jayadas {PBSG} INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: interesting sql question
a user may request the same boat more than once. not sure that work. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Title: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: RE: interesting sql question
Title: RE: RE: interesting sql question Hey ... the question wasn't complete ... give us the full statement of the question ... g Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: interesting sql question a user may request the same boat more than once. not sure that work. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: RE: interesting sql question
you could do this, but i would have concerns over the indexing strategy. select name from person, (select distinct sid, count(*) bid_count from bids group by sid HAVING count(*) = (SELECT COUNT(BOAT_ID FROM BOATS)) bids where person.sid = bids.sid; Now yours bids table is an intersect table and would have the most records of all three tables. I would create an extra field that never gets update and just put a default value in it. Then I would put a bitmap index on it. since they aer VERY faster on counts. my problem is with the group by. SID could be huge. That could lead to a massive slow down and alot of LIOs dont think there is a faster a solution though. No correlated sub-queries which are LIO intensive. From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 09:34:38 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: interesting sql question Ralph, Assuming that there is no history in the BIDS table (meaning that there are no old records indicating a bid recorded last year), I think the following would work just fine. select name from person, (select distinct sid, count(*) bid_count from bids group by sid) bids where person.sid = bids.sid and bid_count = 3 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: interesting sql question
no there are examples in the book using where 'not exists'. the query was horrible. Ill post it later if you want to see how bad it is. no its not homework. Id get the answer wrong if i did it this way, since Id have to follow the model in the book. Which is terrible. From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/09/29 Mon PM 12:29:40 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question yeah! I think it *is* homework :) Tom -Original Message- Sent: Monday, September 29, 2003 12:10 PM To: Multiple recipients of list ORACLE-L Hey ... the question wasn't complete ... give us the full statement of the question ... g Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L a user may request the same boat more than once. not sure that work. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Title: RE: RE: interesting sql question yeah! I think it *is* homework :) Tom -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 12:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: interesting sql question Hey ... the question wasn't complete ... give us the full statement of the question ... g Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: interesting sql question a user may request the same boat more than once. not sure that work. From: "Jamadagni, Rajendra" [EMAIL PROTECTED] Date: 2003/09/29 Mon AM 10:34:53 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
is this a good practice...pl/sql question
Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: is this a good practice...pl/sql question
Steve, It may be old fashion code, but if it works within the time frame it needs to run in, why spend time recoding? At any rate, I don't see a problem. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, September 17, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: is this a good practice...pl/sql question
Title: RE: is this a good practice...pl/sql question Steve, Nothing wrong with setting xrec.state to null ... your developer is avoiding hard coding of NULL in the insert statement. In fact if this is working fine then only thing I'd try to change is bulk inserts instead of one by one ... Don't worry it is normal. Raj -Original Message- From: Steve McClure [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: is this a good practice...pl/sql question Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: is this a good practice...pl/sql question
Steve McClure wrote: Allright I am making some changes to some pl/sql code that handles batch inserts into the database. I am making changes to correct an error where our clients are sending us data with invalid state information in their address fields. A constraint prohibits the insert with records with invalid states, nulls are however allowed. The decision was made to insert the rest of the address information, leaving the state column null. OK that is enough background. Here is an example of how I am handling this. for x_rec in driving_cur loop if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation x_rec.state:=NULL; end if; insert into address(other_columns,..state) values(x_rec.other_cols,...x_rec.state); end loop; I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements. My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say..that was obviously not an intended feature, that usage no longer allowed. I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record. Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think. Steve McClure Steve, Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: is this a good practice...pl/sql question
Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. I guess I am really wondering if it is a good practice to modify the attributes of a record previously selected via a cursor. It is not just a typical variable that is defined and used in a manner that suits the algorithm. I am just wondering if it would be a better idea to treat this type of a record as a constant. To elevate it conceptually. The reason being that it does(should?)represent what was actually selected from the database. If another developer, or myself for that matter, were to come along at a later date, and use that attribute in another section of code not knowing, or forgetting, it had been altered above. I have actually used this technique extensively in a couple of routines more complex than the one I have described above. It was just as I made this little code change, I felt a pang of conscience, and wanted to ask this philosophical question. I am not worried about mucking up the database, or any such thing. I am aware that variable is just like any other attribute in any record I might have explicitly created myself. It is just that since the database instantiated this record to represent the results of my query, I am wondering if it should grant it an elevated postition. Sorry for rambling on and on etc. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: is this a good practice...pl/sql question
Title: RE: is this a good practice...pl/sql question Yes it is a good practice when required. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Steve McClure [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: is this a good practice...pl/sql question Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. I guess I am really wondering if it is a good practice to modify the attributes of a record previously selected via a cursor. It is not just a typical variable that is defined and used in a manner that suits the algorithm. I am just wondering if it would be a better idea to treat this type of a record as a constant. To elevate it conceptually. The reason being that it does(should?)represent what was actually selected from the database. If another developer, or myself for that matter, were to come along at a later date, and use that attribute in another section of code not knowing, or forgetting, it had been altered above. I have actually used this technique extensively in a couple of routines more complex than the one I have described above. It was just as I made this little code change, I felt a pang of conscience, and wanted to ask this philosophical question. I am not worried about mucking up the database, or any such thing. I am aware that variable is just like any other attribute in any record I might have explicitly created myself. It is just that since the database instantiated this record to represent the results of my query, I am wondering if it should grant it an elevated postition. Sorry for rambling on and on etc. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
SQL question : How to retrieve the File_name without Directorie P
Title: SQL question : How to retrieve the File_name without Directorie Path ? Hi Gurus! a very simple problem for You :I just want to retrieve the .dbf name from file_name column in dba_data_files. eg :'/oracle/d0/data/user.dbf' -- user.dbf Maybe using translate function ? Thank in advance ! Philippe
Antw: SQL question : How to retrieve the File_name without
Hi Philippe, eat this: select substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',-1)+1) from dual; hth, Guido [EMAIL PROTECTED] 23.07.2003 09.59 Uhr Hi Gurus! a very simple problem for You :I just want to retrieve the .dbf name from file_name column in dba_data_files. eg :'/oracle/d0/data/user.dbf' -- user.dbf Maybe using translate function ? Thank in advance ! Philippe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Antw: SQL question : How to retrieve the File_name without
eat this: Is it chewable? -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Guido Konsolke Sent: Wednesday, July 23, 2003 3:14 AM To: Multiple recipients of list ORACLE-L Hi Philippe, eat this: select substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',- 1)+1) from dual; hth, Guido [EMAIL PROTECTED] 23.07.2003 09.59 Uhr Hi Gurus! a very simple problem for You :I just want to retrieve the .dbf name from file_name column in dba_data_files. eg :'/oracle/d0/data/user.dbf' -- user.dbf Maybe using translate function ? Thank in advance ! Philippe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Antw: SQL question : How to retrieve the File_name
Hi Igor, yes, it is. But it would be better if we all swallow the use of the builtin functions. My weaknesses are with analytic functions. I would give my colleague's right arm for getting into them ;-)) Greetings, Guido [EMAIL PROTECTED] 23.07.2003 15.54 Uhr eat this: Is it chewable? -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Guido Konsolke Sent: Wednesday, July 23, 2003 3:14 AM To: Multiple recipients of list ORACLE-L Hi Philippe, eat this: select substr('/oracle/d0/data/user.dbf',instr('/oracle/d0/data/user.dbf','/',- 1)+1) from dual; hth, Guido [EMAIL PROTECTED] 23.07.2003 09.59 Uhr Hi Gurus! a very simple problem for You :I just want to retrieve the .dbf name from file_name column in dba_data_files. eg :'/oracle/d0/data/user.dbf' -- user.dbf Maybe using translate function ? Thank in advance ! Philippe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question : How to retrieve the File_name without Directorie P
Phillipe, Look at using INSTR and SUBSTR to calculate the position of the last / and work from there. Daniel NGUYEN Philippe (Cetelem) wrote: Hi Gurus! a very simple problem for You :I just want to retrieve the .dbf name from file_name column in dba_data_files. eg :'/oracle/d0/data/user.dbf' -- user.dbf Maybe using translate function ? Thank in advance ! Philippebegin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
SQL question - crosstab in oracle
Hi I have a table with the following structure. CHECK_DATE DATE PHYRDS NUMBER(20) PHYWRTSNUMBER(20) PHYBLKRD NUMBER(20) PHYBLKWRT NUMBER(20) FILE# NUMBER FILE_NAME VARCHAR2(120) This records the IO on the different datafiles every 3 minutes. I want to run a report on this table but I want the output to display on file# as a heading.Similar to iostat on UNIX. So my output might be: Check DATE FIle1 File2 File3 etc... PHYRDS PHYWRTS PHYRDS PHYWRTS 11:14:341270820218 14 11:18:261303821118 14 11:33:441429024318 14 11:40:451500824518 14 11:45:371545924918 14 11:48:381577625018 14 11:51:371586425118 14 11:54:371602925418 14 11:57:391621025418 14 12:00:401631125418 14 12:03:401643925618 14 I could do this as a bit of plsq but Is it possible to generate this report with one bit of sql. (views,inline views, decodes. etc) thanks stephen ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question - crosstab in oracle
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 of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
SQL Question
Hi, Sorry for reposting. Just wanted to put in a subject... I have been grappling with this for sometime and thought it will be best for others to take a look at it. I have a table a_user_groups USER_ID SECURITY_GROUP_ID GROUP_ID -- - -- 1005 1012 1010 1005 1012 1011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 1011 The security_group_id currently is uniquely generated every time a user is added and a group_id is associated with the user_id. For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008 has the same combination but the security_group_id is generated differently. The generation happens for C code and there is an option to correct the problem in the C code but I am trying to see if I can prevent that The requirement is that user_id 1008 and any other users with the same group_id combination should have the same security_group_id 1012, basically the first occurrence for the combination. In the case of user_id 1006 the value for security_group_id is 1013 and the group_id the user_id belongs to is 1016. So all the user_ids with a group_id association of 1016 (1016 in a combination does not count) down the line will have to be updated to 1013. I found a solution for the case where I associate a group to a user_id in this existing table by creating another table that converted the above table into a hierarchy Table b_hier_user_groups USER_ID CGID PARENT_VALUE CHILD_VALUE -- -- --- 1005 1012 1010 1005 1012 10101011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 10101011 Then using a PL/SQL script I generated the tree using sys_connect_by_path I determined if the user had a path that already. For eg: in the above case if the user 1006 was being associated with group_id 1011, then I would check the exsiting trees to see if there was a path already as in 1010,1011. In this case it does exist and the cgid (equivalent to security_group_id in the above table) is 1012 and update the user_id 1006 to cgid 1012. But I am not for some reason able to apply this solution to the existing rows. I have a feeling that I am missing something simple The requirement that I am grappling with is to update the values in the existing table. I can get the table b_hier_user_groups created from the a_user_groups. Please let me know if you need more information The table structures are as below a_user_groups Name Null?Type - USER_ID NOT NULL NUMBER SECURITY_GROUP_ID NOT NULL NUMBER GROUP_ID NOT NULL NUMBER Table b_hier_user_groups Name Null?Type - USER_IDNUMBER CGID --- same as security_Group_id from above) NUMBER PARENT_VALUE NUMBER CHILD_VALUENUMBER Thanks for your time and help in advance. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - I mean, what is it about a decent email service? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Question
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
Hi Jaydas, Thanks for the reply. It gives me a good starting point to go with. The query handles cases where there are multiple rows. For eg: U=2006 has G=1010 and S=1013. If there were another U=2010 with same G=1010, then a rum through the query would generate a S=1012 for this combination also as the min checks for group_id in () and that will evaluate any U belonging to a single group that is part of the multiple groups that a U belongs to. But I will take this query as a starting point and will work on getting that resolved. Thanks for your time and appreciate your help Regards, Madhavan http://www.dpapps.com On Thu, 03 Apr 2003 12:28:42 -0800, Chelur, Jayadas {PBSG} [EMAIL PROTECTED] said: Madhavan, I have created a similiar table and inserted the data as follows :- = CREATE TABLE UT ( U NUMBER(4), S NUMBER(4), G NUMBER(4) ); INSERT INTO UT VALUES(2005,1012,1010); INSERT INTO UT VALUES(2005,1012,1011); INSERT INTO UT VALUES(2006,1013,1010); INSERT INTO UT VALUES(2007,1017,1016); INSERT INTO UT VALUES(2008,1018,1010); INSERT INTO UT VALUES(2008,1018,1011); INSERT INTO UT VALUES(2009,1019,1016); INSERT INTO UT VALUES(2001,1020,1010); INSERT INTO UT VALUES(2001,1020,1011); COMMIT; === this query will identify all the security groups and the minimum security group id of the identical one ... SELECT DISTINCT S2.SORIGINAL_SG,/* original security group */ S3.SEQUIV_SG/* equivalent security group*/ FROM( SELECT S, COUNT(*) RECS FROMUT GROUP BY S ) S1, /* security groups and their group counts - table1 */ ( SELECT S, COUNT(*) RECS FROMUT GROUP BY S ) S2, /* security groups and their group counts - table2 */ ( SELECT DISTINCT S FROMUT ) S3/* just the unique security groups */ WHERE S1.RECS = S2.RECS /* match the sec. groups with the same record counts */ AND S1.S S2.S /* make sure they are NOT the same security group*/ AND NOT EXISTS /* make sure they include identical group ids */ ( SELECT G FROM UT WHERE S = S1.S MINUS SELECT G FROM UT WHERE S = S2.S ) AND S3.S = ( /* see note */ SELECT MIN(S) FROM UT WHERE G IN ( SELECT G FROMUT WHERE S = S1.S ) ) /* note : this is to find the minimum value of the security id which has the same group id records as that any of the matching security groups. this minimum value can be used to update the security group ids of all other identical security groups at a later point of time */ you can either change the query to update all the eligible security id to their corresponding minimum values or generate equivalent update statements using this query and run them as a batch ... HTH ... -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: a DIFFERENT sql question
Title: RE: a DIFFERENT sql question SELECT DISTINCT t1.category CAT ,t2.type TYP ,SUM(DISTINCT t1.amount1) OVER(PARTITION BY t1.category) Sum1 ,SUM(t2.amount2) OVER(PARTITION BY t1.category, t2.type) Sum2 FROM t1 ,t2 WHERE t1.mykey1 = t2.mykey1 / CAT TYP SUM1 SUM2 AA x 8 27 AA y 8 6 AA z 8 10 BB y 50 27 HTH Tony APonte Home Shopping Network -Original Message- From: STEVE OLLIG [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: a DIFFERENT sql question ok - i came up with a solution. but in real life i have a lot of amount1's in t1 so it becomes an ugly brute force looking query. anybody have a more elegant solution? 1 select a.category 2 , (select sum(s.amount1) from t1 s where a.category = s.category) as amount1sum 3 , b.type 4 , sum(b.amount2) 5 from t1 a 6 , t2 b 7 where a.mykey1 = b.mykey1 8 group by 9 a.category 10* , b.type SQL / CATEG AMOUNT1SUM TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB 50 y 27 4 rows selected. SQL -Original Message- Sent: Thursday, March 13, 2003 10:49 AM To: Multiple recipients of list ORACLE-L since we're having fun with SQL today - here's one that's hurting my brain at the moment. I need to sum columns at 2 different groupings in my resultset. The first select is perfect; the 2nd is where i have trouble; but i know i can do stuff like the 3rd example. How do I get the 2nd one to work? SQL SCRIPT: drop table t1; drop table t2; create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5)); create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5), amount2 number(5)); insert into t1 values (1, 'AA', 5); insert into t1 values (2, 'AA', 3); insert into t1 values (3, 'BB', 50); insert into t2 values (1, 1, 'x', 1); insert into t2 values (2, 1, 'x', 2); insert into t2 values (3, 1, 'y', 6); insert into t2 values (4, 2, 'x', 4); insert into t2 values (5, 2, 'z', 10); insert into t2 values (6, 2, 'x', 20); insert into t2 values (7, 3, 'y', 12); insert into t2 values (8, 3, 'y', 15); select a.category , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category , a.mykey1 , a.amount1 , b.type / select a.category -- , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category -- , a.mykey1 -- , a.amount1 , b.type / select decode(grouping(a.category), 1, 'All', a.category) as category , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job , decode(grouping(b.type), 1, 'All', b.type) as type , count(*) , sum(distinct a.amount1) , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by rollup ( a.category , a.mykey1 , b.type ) order by a.category , a.mykey1 , b.type / here's what i get: SQL select a.category 2 , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6 from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 , a.mykey1 12 , a.amount1 13 , b.type 14 / CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- -- - -- AA 1 5 x 3 AA 1 5 y 6 AA 2 3 x 24 AA 2 3 z 10 BB 3 50 y 27 5 rows selected. perfect. but this is the problem query: SQL select a.category 2 -- , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6 from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 -- , a.mykey1 12 -- , a.amount1 13 , b.type 14 / CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 5 y 6 AA 3 z 10 BB 50 y 27 4 rows selected. wrong. i want the resultset to look like this: CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB 50 y 27 then this is cool, but not what i want: SQL select decode(grouping(a.category), 1, 'All', a.category) as category 2 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job 3 , decode(grouping(b.type), 1, 'All', b.type) as type 4 , count(*) 5 , sum(distinct a.amount1) 6 , sum(b.amount2) 7 from t1 a 8 , t2 b 9 where a.mykey1 = b.mykey1 10 group by rollup 11 ( a.category 12 , a.mykey1 13 , b.type 14 ) 15 order by 16 a.category 17 , a.mykey1 18 , b.type 19 / CATEG JOB TYPE COUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2) - - - -- -- -- AA 1 x 2 5 3 AA 1 y 1 5 6 AA 1 All 3 5 9 AA 2 x 2 3 24 AA 2 z 1 3 10 AA 2 All 3 3 34 AA All All 6 8 43 BB 3 y 2 50 27 BB 3 All 2 50 27 BB All All 2 50 27 All All All 8 58 70 11 rows selected. Steve Ollig [EMAIL PROTECTED] (952)826-4241
A SQL Question
Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Kirti, I think, you have typo (duplicate rows), when describing data inserted into table, considering PK on (col1, col2). Shouldn't it be: SQLWKS create table test( 2 col1 varchar2(10), 3 col2 varchar2(10), 4 constraint PK_TEST primary key (col1, col2)); Statement processed. SQLWKS SQLWKS insert into test (col1, col2) values ('A', 'B'); 1 row processed. SQLWKS insert into test (col1, col2) values ('C', 'D'); 1 row processed. SQLWKS insert into test (col1, col2) values ('E', 'F'); 1 row processed. SQLWKS insert into test (col1, col2) values ('G', 'H'); 1 row processed. SQLWKS insert into test (col1, col2) values ('B', 'A'); 1 row processed. SQLWKS insert into test (col1, col2) values ('F', 'E'); 1 row processed. SQLWKS insert into test (col1, col2) values ('D', 'C'); 1 row processed. SQLWKS insert into test (col1, col2) values ('H', 'G'); 1 row processed. SQLWKS commit; Statement processed. SQLWKS SQLWKS select * from test; COL1 COL2 -- -- A B C D E F G H B A F E D C H G 8 rows selected. SQLWKS select * from test order by col1; COL1 COL2 -- -- A B B A C D D C E F F E G H H G 8 rows selected. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:23 AM Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Kirti - I haven't had enough coffee this morning, so it seems to me the obvious solution is an order by clause. What am I missing here? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] SQL select * from test; C C - - A B C D E F G H B A F E D C H G 8 rows selected. SQL select * from test order by col1; C C - - A B B A C D D C E F F E G H H G 8 rows selected. -Original Message- Sent: Thursday, March 13, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Title: RE: A SQL Question SELECT table.Col1, table.Col2 FROM table UNION SELECT table.Col2, table.Col1 FROM table ORDER BY table.Col1; Actually you might not even need the ORDER BY Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED] Hi SQL Developers, I have a table as follows: Col1 Col2 A B C D E F G H B A E F C D H G With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1 Col2 A B B A C D D C E F F E G H H G Thanks for your help. - Kirti
Re: A SQL Question
Hi Kirti, Just a clarification: PK on col1, col2 but you have duplicates C,D and E,F. If the dups are removed, is the porblem still valid? mohammed --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti Kirti, On your example 'ORDER BY COL1' should be enough :-). I have a solution which is not excellent (I dislike the way I prevent the query from returning too many rows), but seems to be working even when there is no transitivity. May at least give you an idea on which to start work : select * from (select * from T connect by col1 = prior col2 and col1 col2) x where rownum = (select count(*) from T) / Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Title: RE: A SQL Question Kirti, It's impossible to have a primary key as you have duplicate values. C-D and E-F both have dupes. If there should be D-C and F-E, a simple Order By Col1 would do the trick. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED] Hi SQL Developers, I have a table as follows: Col1 Col2 A B C D E F G H B A E F C D H G With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1 Col2 A B B A C D D C E F F E G H H G Thanks for your help. - Kirti
RE: A SQL Question
I messed up typing the data for the table. It has no dups. The second occurrence of C, D and E, F should actually be D, C and F, E. Sorry about that... Need more hot tea to wake me up !! - Kirti -Original Message- From: Deshpande, Kirti Sent: Thursday, March 13, 2003 7:25 AM To: oracle list (E-mail) Subject: A SQL Question Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Hi Kirti, This isn't possible. The primary key won't allow for the duplicate values. There are 2 records of C,D and 2 records of E,F. Darrell [EMAIL PROTECTED] 03/13/03 07:23AM Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Assuming dups can be deleted, here's my humble attempt: select col1, col2 from t order by col1, col2; Col1 Col2 -- AB BA CD EF GH HG 6 rows selected. select col1, col2 from t union select col2, col1 from t ; Col1 Col2 -- AB BA CD DC EF FE GH HG 8 rows selected. mohammed --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Kirti, I tried to reply to your direct e-mail, but your mail-server is very strict and considered my message to be Unsolicited Bulk Email. What I was trying to say is: Oracle-l list behaves very strangely (sometimes), I'm still waiting to see corrected version of your question. And actually I suspected, that the question isn't that simple -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:23 AM Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Kirti, Would not and order by col1,col2 give the resulting set you want? Is the data shown correct? you have C,D twice. I think you ment C,D and D,C. Ron [EMAIL PROTECTED] 03/13/03 08:23AM Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Kirti, is this a trick question, or am I missing something? select col1, col2 from table order by col1 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, March 13, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Will 'ORDER BY col1' not do?? ;-) Atleast in this example it does. What are exact requirements? Regards Naveen -Original Message- Sent: Thursday, March 13, 2003 6:54 PM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Igor (and all): Yes, our SPAM Cops and their filters are very strict with the wording in the e-mail footers. Unfortunately, FatCity.com uses the footer that gets caught by these filters. When replying to me directly, using list message, you need to remove the old footers from the e-mail. Sorry about this little problem. I will post my Corrected SQL Question again... Thanks. - Kirti -Original Message- Sent: Thursday, March 13, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Kirti, I tried to reply to your direct e-mail, but your mail-server is very strict and considered my message to be Unsolicited Bulk Email. What I was trying to say is: Oracle-l list behaves very strangely (sometimes), I'm still waiting to see corrected version of your question. And actually I suspected, that the question isn't that simple -:) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 8:23 AM Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: A SQL Question
Select * from my_table order by col1; -Original Message- Sent: Thursday, March 13, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Hi SQL Developers, I have a table as follows: Col1 Col2 AB CD EF GH BA EF CD HG With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1Col2 AB BA CD DC EF FE G H H G Thanks for your help. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Corrected SQL Question...
Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: A SQL Question
Title: Re: A SQL Question SQL select A.c1, B.c2 2 from (select col1 c1, rownum r from tbl order by col1) A 3 , (select col2 c2, rownum r from tbl order by col2) b 4 where a.r = b.r 5 union all 6 select B.c2, A.c1 7 from (select col1 c1, rownum r from tbl order by col1) A 8 , (select col2 c2, rownum r from tbl order by col2) b 9 where a.r = b.r 10 order by 1 11 / C C - - A B B A C D D C E F F E G H H G At 05:23 AM 3/13/2003 -0800, you wrote: Hi SQL Developers, I have a table as follows: Col1 Col2 A B C D E F G H B A E F C D H G With a PK on (Col1, Col2). How do I write a SQL script to get following result? Col1 Col2 A B B A C D D C E F F E G H H G Thanks for your help. - Kirti Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications. Cette communication par courrier lectronique est une communication prive l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.
a DIFFERENT sql question
since we're having fun with SQL today - here's one that's hurting my brain at the moment. I need to sum columns at 2 different groupings in my resultset. The first select is perfect; the 2nd is where i have trouble; but i know i can do stuff like the 3rd example. How do I get the 2nd one to work? SQL SCRIPT: drop table t1; drop table t2; create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5)); create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5), amount2 number(5)); insert into t1 values (1, 'AA', 5); insert into t1 values (2, 'AA', 3); insert into t1 values (3, 'BB', 50); insert into t2 values (1, 1, 'x', 1); insert into t2 values (2, 1, 'x', 2); insert into t2 values (3, 1, 'y', 6); insert into t2 values (4, 2, 'x', 4); insert into t2 values (5, 2, 'z', 10); insert into t2 values (6, 2, 'x', 20); insert into t2 values (7, 3, 'y', 12); insert into t2 values (8, 3, 'y', 15); select a.category , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category , a.mykey1 , a.amount1 , b.type / select a.category -- , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category -- , a.mykey1 -- , a.amount1 , b.type / select decode(grouping(a.category), 1, 'All', a.category) as category , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job , decode(grouping(b.type), 1, 'All', b.type) as type , count(*) , sum(distinct a.amount1) , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by rollup ( a.category , a.mykey1 , b.type ) order by a.category , a.mykey1 , b.type / here's what i get: SQL select a.category 2 , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 , a.mykey1 12 , a.amount1 13 , b.type 14 / CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- -- - -- AA 1 5 x 3 AA 1 5 y 6 AA 2 3 x 24 AA 2 3 z 10 BB 3 50 y 27 5 rows selected. perfect. but this is the problem query: SQL select a.category 2 -- , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 -- , a.mykey1 12 -- , a.amount1 13 , b.type 14 / CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 5 y 6 AA 3 z 10 BB50 y 27 4 rows selected. wrong. i want the resultset to look like this: CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB50 y 27 then this is cool, but not what i want: SQL select decode(grouping(a.category), 1, 'All', a.category) as category 2 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job 3 , decode(grouping(b.type), 1, 'All', b.type) as type 4 , count(*) 5 , sum(distinct a.amount1) 6 , sum(b.amount2) 7from t1 a 8 , t2 b 9 where a.mykey1 = b.mykey1 10 group by rollup 11 ( a.category 12 , a.mykey1 13 , b.type 14 ) 15 order by 16 a.category 17 , a.mykey1 18 , b.type 19 / CATEG JOB TYPECOUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2) - - - -- -- -- AA1 x 2 5 3 AA1 y 1 5 6 AA1 All3 5 9 AA2 x 2 3 24 AA2 z 1 3 10 AA2 All3 3 34 AAAll All6 8 43 BB3 y 2 50 27 BB3 All2 50 27 BBAll All2 50 27 All All
Corrected SQL Question...
Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Corrected SQL Question...
I think its easier if you do it cross-tab AUS DAL DAL AUS Is that acceptable? Or just select AUS DAL If it also has a DAL AUS Are either of those metods acceptable? If so, pick one and Ill show you how to do it. From: Deshpande, Kirti [EMAIL PROTECTED] Date: 2003/03/13 Thu AM 11:19:15 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Corrected SQL Question... Okay, let me do this right this time,... (Now that I have my hot tea going;) Here is the test data: SQL select c1,c2 from cp; C1 C2 --- --- AUS DAL AUS HOU DAL AUS DAL HOU DAL LIT DAL XYZ HOU AUS HOU DAL HOU LIT HOU XYZ LIT DAL C1 C2 --- --- LIT HOU XYZ DAL XYZ HOU 14 rows selected. SQL Here is what is required: C1 C2 --- --- AUS DAL DAL AUS AUS HOU HOU AUS DAL HOU HOU DAL DAL LIT LIT DAL DAL XYZ XYZ DAL HOU LIT LIT HOU HOU XYZ XYZ HOU I think I am clear now... Sorry about the wrong test data earlier... Thanks, - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).