×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Deviding a Grupping

Deviding a Grupping

Deviding a Grupping

(OP)
Hi

I am using this piece of code to deliver a status regarding a system

CODE

select decode(area,
              'B','BATCH',
              'G','RESEND',
              'Y','CR_FIX',
              'N','IT_FIX',
              'Q','ANALYSIS_IN_PROGRESS',
              null,'UNKNOWN',
              'T','Cannot FIX') "DISTRIBUTION",
              count(error_log_id)
from (select ERROR_LOG_ID,
       decode(decode(CR_TO_CORRECT,'B','Batch',null)||decode(VAL,'Batch','Batch',null),'Batch','B',cr_to_correct) "AREA"       
    from (
    select el.error_log_id, ol.order_line_id, oli.Initiator_desc, em.cr_to_correct,
       substr(decode(oli.Initiator_desc,null,'Batch','Script - Change Username', 'Batch',oli.Initiator_desc),1,5) "VAL"
     from delta_owner.ext_error_log el, bc.order_line ol, BC.ORDER_LINE_INITIATOR oli,
       batch_owner.errorlog_message em
    where nvl(el.comments,0) not like 'Resolved%'
       and el.orderline_id=ol.order_line_id(+)
       and ol.initiator = OLI.INITIATOR(+)
       and el.workaround_id=em.errorlog_message_id(+)   
    )
)  
group by rollup(decode(area,'B','BATCH','G','RESEND','Y','CR_FIX','N','IT_FIX','Q','ANALYSIS_IN_PROGRESS',null,'UNKNOWN','T','Cannot FIX'))



And i works fine, but I would like to modify it.
the 'G','RESEND'
Is should accutaly be devided into 2 portions.
If there in the delta_owner.ext_error_log under the same ERROR_LOG_ID is 2 of the same workaround_id then it should be put into
RESEND FAILED

The reason is that resend is a funtionalaty, and if it is resend and then goes back into the system, then a resend is probally not the solution.

Hope some one can help.

This is on Oracle 10

Thanks
LHG

RE: Deviding a Grupping

perhaps you could post your question in one of the oracle forums?

there is no such thing as DECODE in ANSI SQL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Deviding a Grupping

You might also want to familiarize yourself with CASE - it allows you much more flexibility than DECODE.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close