Announcement

Collapse
No announcement yet.

JOIN syntax

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

  • JOIN syntax

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

    Code:
    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:

    Code:
    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:

    Code:
    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 ?


  • #2
    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
    Chris Lang

    Comment


    • #3
      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)

      Comment


      • #4
        Re: JOIN syntax

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

        Thanks,
        -Chris
        Chris Lang

        Comment

        Working...
        X