2 tables in query don't make the transformer work

[shifting_18]

hi!
I used this simple query in Mirth Select portion for my Oracle Db Reader:

select PAT.patient_no, PAT.roomtype, PAT.roomno, MR.medicalrecord_no, MR.lastname, MR.firstname, MR.middlename,from csmchis.medicalrecord MR, csmchis.patient_info PAT where MR.medicalrecord_no ='48374' and PAT.medicalrecord_no = '48374' and PAT.patient_no= '82793'

However, I noticed that my transformer with simple message builder and mappers did not work. Mirth cannot extract the data from database even though an xml like this is seen in incoming message:

<?xml version="1.0" encoding="UTF-8"?>
<result>
<pat.patient_no>value</pat.patient_no>
<pat.roomtype>value</pat.roomtype>
<pat.roomno>value</pat.roomno>
<mr.medicalrecord_no>value</mr.medicalrecord_no>
<mr.lastname>value</mr.lastname>
<mr.firstname>value</mr.firstname>
<mr.middlename>value</mr.middlename>
<mr.birthdate>value</mr.birthdate>
</result>


Is ther something wrong with the query I made? It's working on my Toad. Thanks!!

Post edited by: shifting_18, at: 05/21/2007 19:32

[djroger_nc]

Re:2 tables in query don't make the transformer wo

I am running into the same thing. i am not sure how to assign the variables that are a result of a postgres query. Any help is appreciated.

[brendanh]

Re:2 tables in query don't make the transformer wo

That is the XML data that will be returned from the query. Drag the values from the tree like you would for any other mapper.

Post edited by: brendanh, at: 05/29/2007 13:12

[shifting_18]

Re:2 tables in query don't make the transformer wo

I tried removing the table alias on the msg['p.patient_no'].toString() and instead use msg['patient_no'].toString(). Mirth cannot find the value because table alias is workable on the query itself. Try this and it'll work.
To make it easy, you can copy the raw data(xml) and it'll show you the tags without the alias. I hope that helps!

[brendanh]

Re:2 tables in query don't make the transformer wo

Are you using 1.5?