JOIN syntax

[sween]

I have the a sql statement that works from the prompt:

mysql> SELECT patients.id, patients.patient_name, patients.patient_middle, patients.patient_last, patients.sex, orders.procedure, orders.id FROM patients JOIN orders WHERE patients.id=orders.patient_id AND orders.sent_flag = '0';
+----+--------------+----------------+--------------+------+------------------+----+
| id | patient_name | patient_middle | patient_last | sex  | procedure        | id |
+----+--------------+----------------+--------------+------+------------------+----+
|  1 | Ron          | J              | Sweeney      | M    | CT with contrast |  1 | 
|  1 | Ron          | J              | Sweeney      | M    | MRI              |  2 | 
|  1 | Ron          | J              | Sweeney      | M    | CT with contrast |  3 | 
+----+--------------+----------------+--------------+------+------------------+----+
3 rows in set (0.00 sec)

However, when "mirthifying it" I get unknown column in my debug output...

Here is the syntax that is in my sql statement in the Database Reader:

SELECT `patients.id`, `patients.patient_name`, `patients.patient_middle`,` patients.patient_last`, `patients.sex`, `orders.procedure`, `orders.id` FROM `patients` JOIN `orders` WHERE `patients.id=orders.patient_id` AND `orders.sent_flag = '0'`;

This is what I get in my output:

ERROR org.mule.impl.DefaultExceptionStrategy: Caught exception in Exception Strategy: Unknown column 'patients.id' in 'field list' Query: SELECT `patients.id`, `patients.patient_name`, `patients.patient_middle`,` patients.patient_last`, `patients.sex`, ` Parameters: []e`, `orders.id` FROM patients JOIN orders WHERE `patients.id=orders.patient_id` AND `orders.sent_flag = '0'`;
java.sql.SQLException: Unknown column 'patients.id' in 'field list' Query: SELECT `patients.id`, `patients.patient_name`, `patients.patient_middle`,` patients.patient_last`, `patients.sex`, `orders.procedure`, `orders.id` FROM patients JOIN orders WHERE  Parameters: []ders.patient_id` AND `orders.sent_flag = '0'`;
        at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330)
        at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:213)
        at org.mule.providers.jdbc.JdbcMessageReceiver.getMessages(JdbcMessageReceiver.java:116)
        at org.mule.providers.TransactedPollingMessageReceiver.poll(TransactedPollingMessageReceiver.java:108)
        at org.mule.providers.PollingMessageReceiver.run(PollingMessageReceiver.java:73)
        at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290)
        at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
        at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
        at java.lang.Thread.run

I am so close here, any ideas on what is preventing this statement within MIRTH ?

[chrisl]

Re: JOIN syntax

This should work as expected - try removing the back-quotes on your column names in Mirth. It should support your previous SQL syntax.

Also, what version are you using?

Thanks,
-Chris

[sween]

Re: JOIN syntax

Im Using 1.1.0...

I tried that, it gives me this exception...

ERROR org.mule.impl.DefaultExceptionStrategy: Caught exception in Exception Strategy: Can not retrieve argument orders.id
java.lang.IllegalArgumentException: Can not retrieve argument orders.id
at org.mule.providers.jdbc.JdbcUtils.getParams(JdbcUt ils.java:154)
at org.mule.providers.jdbc.JdbcMessageReceiver.proces sMessage(JdbcMessageReceiver.java:90)
at org.mule.providers.TransactedPollingMessageReceive r$MessageProcessorWorker.doInTransaction(Transacte dPollingMessageReceiver.java:159)
at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:72)

[chrisl]

Re: JOIN syntax

Can you create an issue for this in the JIRA: www.mirthproject.org/jira?

Thanks,
-Chris