You can have multiple OrderBy columns.Just in the wizard where you select the order by column,manually type the name of other columns using comma(,) as separator in the orderby text box.
Hope this helps.
thank you, it is a good hint but seems to be not working. We try it with comma(,) and space before and generated SQL set OrderBy columns in quotes("). Without space first it seems to work, but after deployment and startup of composite I got the error: Caused by: java.sql.SQLSyntaxErrorException: ORA-00960: ambiguous column naming in select list
I check the generated SQL and see that the "new" OrderBy column attached in SELECT statement. See the screenshot in my Dropbox https://www.dropbox.com/s/n99pimlflqeh2gm/DBAdapterWizardOrderBy.png
Why it is not possible to edit the SQL in a Polling Function? The field is disabled. See the screenshot here: https://www.dropbox.com/s/obkfk8iqg855qtv/DBAdapterWizardSQLNoEdit.png In a "normal" SELECT I'm able to edit the pre-generated SQL.
Are you not able to Edit the generated SQL by clicking on the Edit button in the last screenshot you uploaded.I can edit it and also order by clause is working for me with 2 columns.
Can you please retry once.
I have already test it, but the same behavior as if I using the Wizard. May be the Exception will help:
<BEA-000000> <Database Adapter TriggerServiceRequestFilenetDBAdapterV1 <oracle.tip.adapter.db.InboundWork handleException> Encountered a fatal exception while polling. Will continue polling but with minimal logging. Please investigate the fault and manually stop polling from the console if in development and this appears to be a modeling mistake. BINDING.JCA-11624
DBActivationSpec Polling Exception.
Query name: [ECMActivityTriggerDBAdapterSelect], Descriptor name: [ECMActivityTriggerDBAdapter.EcmInterface]. Polling the database for events failed on this iteration.
Caused by java.sql.SQLSyntaxErrorException: ORA-00960: ambiguous column naming in select list
In case of a simple SELECT Adapter the gernerate Query in *or-mappings.xml looks different. In case of SELECT there is the SQL statement in "plain" text:
<querying> <queries> <query name="SelectTESTSelect" xsi:type="read-all-query"> <call xsi:type="sql-call"> <sql>SELECT EVTKEY, ROW_CREATED, CUST_SOURCECHANNEL FROM ECM_INTERFACE ORDER BY ROW_CREATED, CUST_SOURCECHANNEL</sql> </call> <reference-class>SelectTEST.EcmInterface</reference-class> <container xsi:type="list-container-policy"> <collection-type>java.util.Vector</collection-type> </container> </query> </queries> </querying>
In case of TRIGGER there is no SQL:
<querying> <queries> <query name="ECMActivityTriggerDBAdapterSelect" xsi:type="read-all-query"> <reference-class>ECMActivityTriggerDBAdapter.EcmInterface</reference-class> <lock-mode>none</lock-mode> <container xsi:type="list-container-policy"> <collection-type>java.util.Vector</collection-type> </container> </query> </queries> </querying>
It seems the SOA Suite will generate the SQL during runtime.