Prev
Next
Forums Performance Management Smart Notification

Syntax for combining Data Fields

 3 Replies
 3 Subscribed to this topic
 14 Subscribed to this forum
Sort:
Author
Messages
thummel1
Veteran Member
Posts: 121
Veteran Member
3/9/2015 5:48 PM
    Hi,

    I'm looking for the right syntax to combine 2 data fields with a semi-colon as the separator. It's 2 email address fields.

    I would expect the data field to look like this:
    bjones@company.org; speterson@company.org

    I was going to see if I could burst my smart note to these two email addresses from one data field. Before I can try it, I built my SQL in SQL Developer for Oracle and it looks like this:

    EMPLOYEE_1.EMAIL_ADDRESS ||';'|| EMPLOYEE.EMAIL_ADDRESS as EmailAddresses,

    This executes just fine in SQL Developer for Oracle, but when I paste the entire SQL into my Smart Note, it fails with tis error message:

    Error: ORA-01756: quoted string not properly terminated

    I've isolated the issue to just this line of code. My first question is, will this idea even work and second, if it will, how do I fix the code to work in my Smart Note? Thanks.
    The.Sam.Groves
    Veteran Member
    Posts: 89
    Veteran Member
    3/9/2015 6:23 PM
      Are you married to the idea of using a ;?

      The problem isn't with your SQL, it's with the fact that Smart Notes is doing some stupid parsing business in the background and not passing over the SQL correctly. Try replacing the ; with a | and it should work without issue.

      If you really need a ; in there, then replace it with & # 59 (without spaces) which is the HTML code for ;

      The.Sam.Groves
      Veteran Member
      Posts: 89
      Veteran Member
      3/9/2015 6:39 PM
        And to cover the first question, I tried it and while the system sent the email, I've received neither a bounce back indicating the email address was 'bad' or the actual email in either of the addresses I used.
        Dave Curtis
        Veteran Member
        Posts: 136
        Veteran Member
        3/9/2015 11:03 PM
          I've never been able to get multiple email addresses to work. I've tried multiple times and multiple ways and never successfully.
          I now just use a union and basically build the multiple addresses that way.

          depending on the type of query, I also utilize employee groups to manage multiple deliveries of one note to many individuals.