Announcement

Collapse

Mirth Connect 3.12.0 Released!

Mirth Connect 3.12.0 is now available as an appliance update and on our GitHub page. This release includes database performance improvements, improves visual HL7 representation, message pruning, keystore handling, PDF generation, community contributions, and fixes several security vulnerabilities. This release also contains many improvements to commercial extensions. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

Database Reader to File Writer with column aliases

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database Reader to File Writer with column aliases

    I am using Mirth 1.7 and have created a channel that reads from two tables in a SQL 2000 database and writes to an HL7 file. (attached for reference)

    I'd like to use column name aliases so that I can eventually use a UNION ALL statement to connect multiple select statements. For now, I just have one SELECT statement, but I'm trying to use an alias and it doesn't work. No error, but the data is not updated in the HL7 message.

    SELECT
    p.person_id AS PersonID,
    p.last_name AS PatientLast,
    v.hib1_Date AS VaccineDate
    FROM New_vaccine_ v
    LEFT JOIN Person p ON p.Person_id = v.Person_id
    WHERE p.Last_Name like 'Smith'

    If I use the following SQL statement, it works:
    SELECT
    p.person_id,
    p.last_name,
    v.hib1_Date
    FROM New_vaccine_ v
    LEFT JOIN Person p ON p.Person_id = v.Person_id
    WHERE p.Last_Name like 'Smith'

    However, I have to map:
    msg['last_name'].toString()

    NOT
    msg['p.last_name'].toString()

    Any suggestions would be very much appreciated!
    Thanks! Test_DB_to_HL7.xml (6690 bytes)

  • #2
    Reatabase Reader to File Writer with column aliases

    Column names might be case-sensitive.

    Try:

    msg['patientlast'].toString()

    Or

    msg['PatientLast'].toString()

    Another option would be to create a VIEW in the database and change the SELECT to access the VIEW instead of the base table. Like
    Code:
    SELECT 
    PersonID,
    PatientLast,
    VaccineDate
    FROM vwNewVaccine
    WHERE LastName like 'Smith'
    and then drag-and-drop the columns in the FILTER / TRANSFORMER.

    Hope this helps!

    Post edited by: nshaik, at: 11/05/2008 06:56

    Comment


    • #3
      Reatabase Reader to File Writer with column aliases

      Thanks! I think I'll try the View!

      Comment


      • #4
        Reatabase Reader to File Writer with column aliases

        Hi

        I tried the view also tried the drag and drop.
        I am not able to get the values in the hl7 msg
        any advice. ?

        Comment


        • #5
          Reatabase Reader to File Writer with column aliases

          I know this is a long time after this message was posted, but I ran into the same problem, and I thought I'd document it here.

          In my Destinations tab in my channel, I changed the template to:

          ${message.rawData}

          Prior to that, I had ${message.encodedData}, and it didn't work.

          I also used a View, instead of the straight query, to really ensure that column names didn't overlap.

          Comment

          Working...
          X