/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Where statement to NOT rlike

01/16/2018 506 views

I am trying to exclude by using the NOT rlike and it does not seem to exclude as I want it. Am I doing something wrong?

and HD_TICKET.CUSTOM_FIELD_VALUE0 NOT RLIKE ('HP Records Manager (HP RM)|Bar Code Reader TRIM|Intranet|Internet Site|Project Development Reg (PDR)')
and HD_CATEGORY.id rlike ('43|44|46|47|60|69') and HD_TICKET.HD_QUEUE_ID = 2  

2 Comments   [ + ] Show comments

Comments

  • Can you please post the entire statement?
  • Thank you Chuck.
    The following statement seems to ignore the exclusion and it still send email notification to the email address. Here's the statement:

    select HD_TICKET.ID as TICKNUM,
    HD_TICKET.TITLE as TITLE,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_TICKET.CUSTOM_FIELD_VALUE0 as FPA_APPLICATION,
    HD_TICKET.CUSTOM_FIELD_VALUE2 as COMMENTS,
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    HD_IMPACT.NAME as IMPACT_NAME,
    HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
    HD_CATEGORY.NAME as CATEGORY_NAME,
    HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
    HD_PRIORITY.NAME as PRIORITY_NAME,
    HD_PRIORITY.ORDINAL as PRIORITY_ORDINAL,
    'Support_Systems_Development@ourdomain' as EMAIL,
    STATE,
    if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
    if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
    if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
    if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
    if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
    if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
    if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
    if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
    if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
    case upper(STATE)
    when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
    when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
    else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
    if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
    U1.FULL_NAME as OWNER_FULLNAME,
    U1.EMAIL as OWNER_EMAIL,
    if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
    if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
    U2.FULL_NAME as SUBMITTER_FULLNAME,
    U2.EMAIL as SUBMITTER_EMAIL,
    if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
    if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
    if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
    Q.NAME as QUEUE_NAME
    from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
    LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
    LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
    LEFT JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
    and HD_TICKET_CHANGE.ID=<CHANGE_ID>
    where HD_PRIORITY.ID = HD_PRIORITY_ID
    and HD_STATUS.ID = HD_STATUS_ID
    and HD_IMPACT.ID = HD_IMPACT_ID
    and HD_TICKET.OWNER_ID = 0
    and HD_CATEGORY.ID = HD_CATEGORY_ID
    and HD_STATUS.NAME !='Closed'
    and HD_TICKET_CHANGE.DESCRIPTION !=''
    and HD_TICKET.CUSTOM_FIELD_VALUE0 NOT RLIKE ('HP Records Manager (HP RM)|Bar Code Reader TRIM|Intranet|Internet Site|Project Development Reg (PDR)')
    and HD_CATEGORY.id rlike ('43|44|46|47|60|69') and HD_TICKET.HD_QUEUE_ID = 2

All Answers

This content is currently hidden from public view.
Reason: Spam For more information, visit our FAQ's.
0
Do you need to deal with partial matches? If not, I would use "NOT IN" instead of "NOT RLIKE." So the comparison would become:
NOT IN ('HP Records Manager (HP RM)','Bar Code Reader TRIM','Intranet','Internet Site','Project Development Reg (PDR)')
Answered 01/16/2018 by: JasonEgg
Red Belt

  • Thank you Jason, much appreciated. I think it's working, I need further testing to confirm. Thank you.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ