Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

LOOP in LEAD statement?

2923313Jul 21 2015 — edited Jul 22 2015

Hi All,

I need some help with this one, perhaps I am looking at it the wrong way around - any suggestions would be appreciated.

Below is what my data looks like:

CAL_DATEINTERACTION_IDINTERACTION_RESOURCE_IDQUEUE_NAME
2015-07-14 09:51:104358118169758170NULL
2015-07-14 09:51:114358118NULLWB_TFR_HMB_SITEL_CCO_WB
2015-07-14 11:43:174358118169813426HMB_SitelCCO_WB
2015-07-14 11:56:064358118169818626desktop_in_progress
2015-07-14 12:14:074358118169826178desktop_in_progress
2015-07-14 12:44:044358118169838050desktop_in_progress
2015-07-14 19:01:324358118170005574desktop_in_progress
2015-07-14 19:04:574358118170005830desktop_in_progress
2015-07-14 19:04:574358118NULLWB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB
2015-07-15 15:27:044358118170256386HMB_SitelCCO_Escalations_WB
2015-07-15 15:27:054358118NULLWB_TFR_HMB_SITEL_CCO_WB
2015-07-15 19:24:544358118170329642HMB_SitelCCO_WB
2015-07-15 19:31:544358118170330926desktop_in_progress
2015-07-15 19:31:544358118NULLWB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB

When the queue name is in a predefined list, I need to find the most recent queue.

So I have a statement that establishes if it is in that predefined list or not:

case when INSTR(UPPER(queue_name), UPPER('desktop_in_progress')) = 0

     and INSTR(UPPER(queue_name), UPPER('Forward e-mails')) = 0

     and INSTR(UPPER(queue_name), UPPER('desktop_draft/PrivateQueue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('NONE')) = 0

     and INSTR(UPPER(queue_name), UPPER('Termination failure')) = 0          

     and INSTR(UPPER(queue_name), UPPER('internetsales_q')) = 0

     and INSTR(UPPER(queue_name), UPPER('desktop_in_progress/PrivateQueue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('inbound_email_q')) = 0

     and INSTR(UPPER(queue_name), UPPER('staticip-workbin')) = 0 

     and INSTR(UPPER(queue_name), UPPER('staticip-workbin/PrivateQueue')) = 0

     and INSTR(UPPER(queue_name), UPPER('Workbin for Original Agent')) = 0          

     and INSTR(UPPER(queue_name), UPPER('desktop_draft')) = 0

     and INSTR(UPPER(queue_name), UPPER('specialdeals_q')) = 0             

     and INSTR(UPPER(queue_name), UPPER('Inbound queue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('desktop-draft-workbin')) = 0

     and INSTR(UPPER(queue_name), UPPER('staticip_q')) = 0        

     and INSTR(UPPER(queue_name), UPPER('Outbound e-mails1')) = 0         

     and INSTR(UPPER(queue_name), UPPER('Outbound_email_q')) = 0   

     then ROW_NUMBER() over (partition by interaction_id order by cal_date desc)

The output of this is then:

CAL_DATEINTERACTION_IDINTERACTION_RESOURCE_IDQUEUE_NAMEROWNUM_
2015-07-14 09:51:104358118169758170NULLNULL
2015-07-14 09:51:114358118NULLWB_TFR_HMB_SITEL_CCO_WB16
2015-07-14 11:43:174358118169813426HMB_SitelCCO_WB15
2015-07-14 11:56:064358118169818626desktop_in_progressNULL
2015-07-14 12:14:074358118169826178desktop_in_progressNULL
2015-07-14 12:44:044358118169838050desktop_in_progressNULL
2015-07-14 19:01:324358118170005574desktop_in_progressNULL
2015-07-14 19:04:574358118170005830desktop_in_progressNULL
2015-07-14 19:04:574358118NULLWB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB10
2015-07-15 15:27:044358118170256386HMB_SitelCCO_Escalations_WB8
2015-07-15 15:27:054358118NULLWB_TFR_HMB_SITEL_CCO_WB7
2015-07-15 19:24:544358118170329642HMB_SitelCCO_WB6
2015-07-15 19:31:544358118170330926desktop_in_progressNULL
2015-07-15 19:31:544358118NULLWB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB4

So what essentially what I am trying to do is find the LEAD QUEUE_NAME where the ROWNUM_ is not null, but I am having issues where there are multiple instances in a row of queue names in my predefined list - see the multiple instances of desktop_in_progress above.

Below is the query I wrote:

  select cal_date,

          interaction_id,

          interaction_resource_id,

          queue_name,

     case when INSTR(UPPER(queue_name), UPPER('desktop_in_progress')) = 0

     and INSTR(UPPER(queue_name), UPPER('Forward e-mails')) = 0

     and INSTR(UPPER(queue_name), UPPER('desktop_draft/PrivateQueue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('NONE')) = 0

     and INSTR(UPPER(queue_name), UPPER('Termination failure')) = 0          

     and INSTR(UPPER(queue_name), UPPER('internetsales_q')) = 0

     and INSTR(UPPER(queue_name), UPPER('desktop_in_progress/PrivateQueue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('inbound_email_q')) = 0

     and INSTR(UPPER(queue_name), UPPER('staticip-workbin')) = 0 

     and INSTR(UPPER(queue_name), UPPER('staticip-workbin/PrivateQueue')) = 0

     and INSTR(UPPER(queue_name), UPPER('Workbin for Original Agent')) = 0          

     and INSTR(UPPER(queue_name), UPPER('desktop_draft')) = 0

     and INSTR(UPPER(queue_name), UPPER('specialdeals_q')) = 0             

     and INSTR(UPPER(queue_name), UPPER('Inbound queue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('desktop-draft-workbin')) = 0

     and INSTR(UPPER(queue_name), UPPER('staticip_q')) = 0        

     and INSTR(UPPER(queue_name), UPPER('Outbound e-mails1')) = 0         

     and INSTR(UPPER(queue_name), UPPER('Outbound_email_q')) = 0   

     then ROW_NUMBER() over (partition by interaction_id order by cal_date desc) end as rownum_,

     case when(

     case when INSTR(UPPER(queue_name), UPPER('desktop_in_progress')) = 0

     and INSTR(UPPER(queue_name), UPPER('Forward e-mails')) = 0

     and INSTR(UPPER(queue_name), UPPER('desktop_draft/PrivateQueue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('NONE')) = 0

     and INSTR(UPPER(queue_name), UPPER('Termination failure')) = 0          

     and INSTR(UPPER(queue_name), UPPER('internetsales_q')) = 0

     and INSTR(UPPER(queue_name), UPPER('desktop_in_progress/PrivateQueue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('inbound_email_q')) = 0

     and INSTR(UPPER(queue_name), UPPER('staticip-workbin')) = 0 

     and INSTR(UPPER(queue_name), UPPER('staticip-workbin/PrivateQueue')) = 0

     and INSTR(UPPER(queue_name), UPPER('Workbin for Original Agent')) = 0          

     and INSTR(UPPER(queue_name), UPPER('desktop_draft')) = 0

     and INSTR(UPPER(queue_name), UPPER('specialdeals_q')) = 0             

     and INSTR(UPPER(queue_name), UPPER('Inbound queue')) = 0          

     and INSTR(UPPER(queue_name), UPPER('desktop-draft-workbin')) = 0

     and INSTR(UPPER(queue_name), UPPER('staticip_q')) = 0        

     and INSTR(UPPER(queue_name), UPPER('Outbound e-mails1')) = 0         

     and INSTR(UPPER(queue_name), UPPER('Outbound_email_q')) = 0   

     then ROW_NUMBER() over (partition by interaction_id order by cal_date desc)

     end) is null then  LEAD(queue_name) over (partition by interaction_id  order by cal_date desc) else queue_name end as lead_queue 

  from s_inf_voice_interactions_key

  where interaction_id = 4358118

  order by cal_date

Which gives the following output

CAL_DATEINTERACTION_IDINTERACTION_RESOURCE_IDQUEUE_NAMEROWNUM_LEAD_QUEUE
2015-07-14 09:51:104358118169758170NULLNULLNULL
2015-07-14 09:51:114358118NULLWB_TFR_HMB_SITEL_CCO_WB16WB_TFR_HMB_SITEL_CCO_WB
2015-07-14 11:43:174358118169813426HMB_SitelCCO_WB15HMB_SitelCCO_WB
2015-07-14 11:56:064358118169818626desktop_in_progressNULLHMB_SitelCCO_WB
2015-07-14 12:14:074358118169826178desktop_in_progressNULLdesktop_in_progress
2015-07-14 12:44:044358118169838050desktop_in_progressNULLdesktop_in_progress
2015-07-14 19:01:324358118170005574desktop_in_progressNULLdesktop_in_progress
2015-07-14 19:04:574358118170005830desktop_in_progressNULLdesktop_in_progress
2015-07-14 19:04:574358118NULLWB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB10WB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB
2015-07-15 15:27:044358118170256386HMB_SitelCCO_Escalations_WB8HMB_SitelCCO_Escalations_WB
2015-07-15 15:27:054358118NULLWB_TFR_HMB_SITEL_CCO_WB7WB_TFR_HMB_SITEL_CCO_WB
2015-07-15 19:24:544358118170329642HMB_SitelCCO_WB6HMB_SitelCCO_WB
2015-07-15 19:31:544358118170330926desktop_in_progressNULLHMB_SitelCCO_WB
2015-07-15 19:31:544358118NULLWB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB4WB_TFR_HMB_SITEL_CCO_ESCALATIONS_WB

So it works for the leading value, but not in examples where I have the predefined values in a list, obviously as that's how LEAD works.

So what I think I need to do is some sort of LEAD(LOOP QUEUE_NAME where ROWNUM_ IS NOT NULL) ??

Not sure..

Any help appreciated.


Thanks!
This post has been answered by odie_63 on Jul 22 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 19 2015
Added on Jul 21 2015
3 comments
236 views