PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/09/2015 7:03 PM by  Dave Curtis
Syntax for combining Data Fields
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
thummel1
Data Analyst II
Fairview Health Services
Veteran Member
(183 points)
Veteran Member
Posts:101


Send Message:

--
03/09/2015 1: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
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    03/09/2015 2: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
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    03/09/2015 2: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
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (292 points)
    Veteran Member
    Posts:102


    Send Message:

    --
    03/09/2015 7: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.
    You are not authorized to post a reply.