SQL Deviding a Grupping

[lhg1]

Hi

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

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

 

[SantaMufasa]

LHG,

If I understand your inquiry correctly, then the following code may be what you want:

select decode(area,
              'B','BATCH',[B][I]
              'G',decode(el.ERROR_LOG_ID
                        ,2,'RESEND FAILED'
                        ,'RESEND'),[/I][/B]
              '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'
                    [B][I]   ,'G',decode(el.ERROR_LOG_ID
                                  ,2,'RESEND FAILED'
                                  ,'RESEND')[/I][/B]
                       ,'Y','CR_FIX'
                       ,'N','IT_FIX'
                       ,'Q','ANALYSIS_IN_PROGRESS'
                       ,null,'UNKNOWN'
                       ,'T','Cannot FIX'
                       )
                )

Let us know if this is what you needed.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”