web stats
JOIN syntax - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 09-26-2006, 08:48 AM
sween sween is offline
OBX.2 Kenobi
 
Join Date: Sep 2006
Location: Michigan, USA
Posts: 52
sween
Default 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 ?

Reply With Quote
  #2  
Old 09-26-2006, 12:37 PM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default 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
Reply With Quote
  #3  
Old 09-26-2006, 12:51 PM
sween sween is offline
OBX.2 Kenobi
 
Join Date: Sep 2006
Location: Michigan, USA
Posts: 52
sween
Default 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)
Reply With Quote
  #4  
Old 09-26-2006, 04:21 PM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default Re: JOIN syntax

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

Thanks,
-Chris
__________________
Chris Lang
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
email sender HELO syntax error dgilperez Support 2 03-23-2009 03:44 AM
Proper syntax to check if a segment exists? Tireseus Support 8 03-13-2009 06:56 AM
Has anybody successfully used a JOIN? sween Support 1 10-01-2006 09:44 PM


All times are GMT -8. The time now is 02:25 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Mirth Corporation