I'm sending a link, "on ticket close", via email. I am populating the link with bits of info from the ticket. I need to use full name (versus user name) in the link but, the space between first name and last name breaks it. We use the email format firstname.lastname@xxx.blah, so using the submitter's email address after trimming everything including the atmark from it would work but alas, quoting the full name or stripping characters from a returned string is far beyond my feeble SQL capabilities.

 

Any suggestions would be most appreciated.

Thank you

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

How are you constructing the link? Can you post the code you're using?

Answered 03/29/2013 by: chucksteel
Red Belt

  • Hi Chuck,

    It's a canned query for setting up an email notification from the kbox. In this rule I'm using it to populate this link - http://masweb.mascorp.com/applications/SelectSurveyNET3/TakeSurvey.aspx?SurveyID=76M08pl&WO=$ticknum&Agent=$owner_uname&Submitter=$submitter_uname&DateClosed=$closed

    select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, DATE_FORMAT(HD_TICKET.TIME_CLOSED,'%b-%d-%Y') as CLOSED, -- $closed
    HD_STATUS.NAME AS STATUS_NAME,
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    HD_TICKET.RESOLUTION, -- $resolution
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
    HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
    HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
    STATE,
    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, -- $owner_fullname
    U1.USER_NAME AS OWNER_UNAME, -- $owner_uname
    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, -- $submitter_fullname
    U2.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    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
    where HD_PRIORITY.ID = HD_PRIORITY_ID
    and HD_STATUS.ID = HD_STATUS_ID
    and HD_IMPACT.ID = HD_IMPACT_ID
    and HD_CATEGORY.ID = HD_CATEGORY_ID and ((HD_STATUS.STATE = 'closed')
    and HD_TICKET.RESOLUTION not like '%Closed Email Sent')
    and HD_TICKET.HD_QUEUE_ID = 16
    • You can use the SQL replace command to replace the spaces with %20 which is used in URLs for a space character. Add the following line:

      replace(U2.FULL_NAME, " ", "%20") as SUBMITTER_FULLNAME_FORURL

      beneath:
      U2.FULL_NAME as SUBMITTER_FULLNAME, -- $submitter_fullname

      Then use $submitter_fullname_forurl in the URL you're printing.
  • Thank you very much Chuck, that did the trick. I did end up changing the %20 to an underscore (_) because the receiving application wasn't converting the %20 back to a space it was displaying it.
Please log in to comment
Answer this question or Comment on this question for clarity