RE: Need SQL Example
Ethan, Is it possible for you to use your function instead of decode? I guess that way you can evaluate situations wherein decode would not work. That way you could have it work on older versions of Oracle. Shailesh -Original Message- Sent: Tuesday, July 24, 2001 1:29 PM To: Multiple recipients of list ORACLE-L Anyone got a good example of flipping a range of values into columner buckets. I have done this in the past but my solutions always seem so convoluted, it seems I have seen more elegant examples in the past. I want to use decode so it will run on older versions of Oracle. Pseudo Example: select sum(decode(if value between 0 and 64 then return 1 else 0)) count_of_this_bucket, sum(decode(if value between 65 and 128 then return 1 else 0)) count_of_this_bucket,... from table Thanks, Ethan -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yadav, Shailesh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need SQL Example
Thanks for the SQL all. Here is the result. You can change and use this with DBA_FREE_SPACE also if your not using uniform extent sizes to help determine level of fragmentation. select tablespace_name, count(*) f_count, sum(decode(sign(round(bytes/1024) - 64), 1, 0, 1)) f_below_64kb, sum(decode(sign(round(bytes/1024) - 129) * sign(round(bytes/1024) - 64), -1, 1, 0)) f_65k_128kb, sum(decode(sign(round(bytes/1024) - 501) * sign(round(bytes/1024) - 128), -1, 1, 0)) f_129k_500kb, sum(decode(sign(round(bytes/1024) - 1025) * sign(round(bytes/1024) - 500), -1, 1, 0)) f_501kb_1mb, sum(decode(sign(round(bytes/1024) - 10241) * sign(round(bytes/1024) - 1024), -1, 1, 0)) f_1025kb_10mb, sum(decode(sign(round(bytes/1024) - ) * sign(round(bytes/1024) - 10240), -1, 1, 0)) f_above_10mb, round(max(bytes/1024)) f_max_kb, round(avg(bytes/1024)) f_avg_kb from dba_segments where segment_type = 'TABLE' group by tablespace_name; -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need SQL Example
If you are on 8, then you can use case. Here is a simple example. SUM( CASE WHEN TRUNC(SYSDATE)-APS.due_date < 1 THEN DECODE(AI.exchange_rate, NULL, APS.amount_remaining, ROUND(AI.exchange_rate*APS.amount_remaining,2)) ELSE 0 END )payment_due_current, SUM( CASE WHEN TRUNC(SYSDATE)-APS.due_date BETWEEN 1 AND 30 THEN DECODE(AI.exchange_rate, NULL, APS.amount_remaining, ROUND(AI.exchange_rate*APS.amount_remaining,2)) ELSE 0 END ) payment_due_1_30, SUM( CASE WHEN TRUNC(SYSDATE)-APS.due_date BETWEEN 31 AND 60 THEN DECODE(AI.exchange_rate, NULL, APS.amount_remaining, ROUND(AI.exchange_rate*APS.amount_remaining,2)) ELSE 0 END ) payment_due_31_60, SUM( CASE WHEN TRUNC(SYSDATE)-APS.due_date > 60 THEN DECODE(AI.exchange_rate, NULL, APS.amount_remaining, ROUND(AI.exchange_rate*APS.amount_remaining,2)) ELSE 0 END )payment_due_60, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] "I'm too sexy for my code." - Awk Sed Fred [EMAIL PROTECTED] Sent by: To: [EMAIL PROTECTED] root@fatcity.cc: com Subject: RE: Need SQL Example 07/24/01 12:20 PM Please respond to ORACLE-L Yeah that one is great, thanks. - Ethan -Original Message- Sent: Tuesday, July 24, 2001 11:28 AM To: Multiple recipients of list ORACLE-L "Post, Ethan" wrote: > > Anyone got a good example of flipping a range of values into columner > buckets. I have done this in the past but my solutions always seem so > convoluted, it seems I have seen more elegant examples in the past. I want > to use decode so it will run on older versions of Oracle. > > Pseudo Example: > > select >sum(decode(if value between 0 and 64 then return 1 else 0)) > count_of_this_bucket, >sum(decode(if value between 65 and 128 then return 1 else 0)) > count_of_this_bucket,... > from >table > > Thanks, > Ethan select sum(decode(sign(value - 64), 1, 0, 1)) count_of_this_bucket, sum(decode(sign(value - 129) * sign(value - 64), -1, 1, 0)) count_of_this_bucket,... Not certain that it is as elegant as you wish, but easy to decline. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please in
RE: Need SQL Example
Yeah that one is great, thanks. - Ethan -Original Message- Sent: Tuesday, July 24, 2001 11:28 AM To: Multiple recipients of list ORACLE-L "Post, Ethan" wrote: > > Anyone got a good example of flipping a range of values into columner > buckets. I have done this in the past but my solutions always seem so > convoluted, it seems I have seen more elegant examples in the past. I want > to use decode so it will run on older versions of Oracle. > > Pseudo Example: > > select >sum(decode(if value between 0 and 64 then return 1 else 0)) > count_of_this_bucket, >sum(decode(if value between 65 and 128 then return 1 else 0)) > count_of_this_bucket,... > from >table > > Thanks, > Ethan select sum(decode(sign(value - 64), 1, 0, 1)) count_of_this_bucket, sum(decode(sign(value - 129) * sign(value - 64), -1, 1, 0)) count_of_this_bucket,... Not certain that it is as elegant as you wish, but easy to decline. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Need SQL Example
"Post, Ethan" wrote: > > Anyone got a good example of flipping a range of values into columner > buckets. I have done this in the past but my solutions always seem so > convoluted, it seems I have seen more elegant examples in the past. I want > to use decode so it will run on older versions of Oracle. > > Pseudo Example: > > select >sum(decode(if value between 0 and 64 then return 1 else 0)) > count_of_this_bucket, >sum(decode(if value between 65 and 128 then return 1 else 0)) > count_of_this_bucket,... > from >table > > Thanks, > Ethan select sum(decode(sign(value - 64), 1, 0, 1)) count_of_this_bucket, sum(decode(sign(value - 129) * sign(value - 64), -1, 1, 0)) count_of_this_bucket,... Not certain that it is as elegant as you wish, but easy to decline. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Need SQL Example
Ethan, Try this: -- LeftMost bin: SUM(DECODE(SIGN(lim_val0 - column_value),1,1,0,1,0)) -- less then lim_val0 -- Middle bins: SUM(DECODE(DECODE(SIGN(lim_val1 - column_value),-1,0,1), 1 , DECODE(SIGN(lim_val0 - column_value),-1,1,0), 0)) -- for (lim_val1-lim_val0) bin SUM(DECODE(DECODE(SIGN(lim_val2 - column_value),-1,0,1), 1 , DECODE(SIGN(lim_val1 - column_value),-1,1,0), 0)) -- for (lim_val2-lim_val1) bin SUM(DECODE(DECODE(SIGN(lim_val3 - column_value),-1,0,1), 1 , DECODE(SIGN(lim_val2 - column_value),-1,1,0), 0)) -- for (lim_val3-lim_val2) bin ... . -- RightMost bin: SUM(DECODE(SIGN(lim_valN - column_value),1,0,0,0,1)) -- greater then lim_valN Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 24, 2001 1:28 PM > Anyone got a good example of flipping a range of values into columner > buckets. I have done this in the past but my solutions always seem so > convoluted, it seems I have seen more elegant examples in the past. I want > to use decode so it will run on older versions of Oracle. > > Pseudo Example: > > select >sum(decode(if value between 0 and 64 then return 1 else 0)) > count_of_this_bucket, >sum(decode(if value between 65 and 128 then return 1 else 0)) > count_of_this_bucket,... > from >table > > Thanks, > Ethan > > -- > This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. > > == > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Post, Ethan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need SQL Example
I think you use sign eg the above will work for integers at least and just requires the the lower and upper values of the ranges plugged in. I'm sure it could be adapted for real numbers but hopefully this will do select sum(decode(sign(:value - (0-1)),1,decode(sign(:value - (64+1)),-1,1,0), 0)), sum(decode(sign(:value - (65-1)),1,decode(sign(:value - (128+1)),-1,1,0), 0)), sum(decode(sign(:value - (129-1)),1,decode(sign(:value - (192+1)),-1,1,0), 0)) from dual Cheers Iain Nicoll -Original Message- Sent: 24 July 2001 18:29 To: Multiple recipients of list ORACLE-L Anyone got a good example of flipping a range of values into columner buckets. I have done this in the past but my solutions always seem so convoluted, it seems I have seen more elegant examples in the past. I want to use decode so it will run on older versions of Oracle. Pseudo Example: select sum(decode(if value between 0 and 64 then return 1 else 0)) count_of_this_bucket, sum(decode(if value between 65 and 128 then return 1 else 0)) count_of_this_bucket,... from table Thanks, Ethan -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Need SQL Example
Anyone got a good example of flipping a range of values into columner buckets. I have done this in the past but my solutions always seem so convoluted, it seems I have seen more elegant examples in the past. I want to use decode so it will run on older versions of Oracle. Pseudo Example: select sum(decode(if value between 0 and 64 then return 1 else 0)) count_of_this_bucket, sum(decode(if value between 65 and 128 then return 1 else 0)) count_of_this_bucket,... from table Thanks, Ethan -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
