Datasources SQL Join

[l.bourgeois]

I use SmartClient v9.0p_2014-02-11/EVAL Deployment

I have 2 tables : FACT_CONTRACTS referencing CONTRACTS.
Here are my 2 datasources definitions :

<DataSource
schema="CALMS_BUSINESS"
dbName="calms_business"
tableName="FACT_CONTRACTS"
ID="FACT_CONTRACTS"
dataSourceVersion="1"
generatedBy="v9.0p_2014-02-11/EVAL Deployment 2014-02-11"
serverType="sql"
useAnsiJoins="true"
>
<fields>
<field primaryKey="true" name="ID" type="sequence"></field>
<field name="ARCHIVE_NUMBER" type="integer" foreignKey="CONTRACTS.ARCHIVE_NUMBER"></field>
<field name="CURRENT_DATE" type="date"></field>
</fields>

</DataSource>

<DataSource
schema="CALMS_BUSINESS"
dbName="calms_business"
tableName="CONTRACTS"
ID="CONTRACTS"
dataSourceVersion="1"
generatedBy="v9.0p_2014-02-11/EVAL Deployment 2014-02-11"
serverType="sql"
>
<fields>
<field primaryKey="true" name="ID" type="sequence"></field>
<field name="OLY_CREDIT_PORTFOLIO_ID" type="integer"></field>
<field name="ARCHIVE_NUMBER" type="integer"></field>
<field name="FACILITY_TYPE" length="200" type="text"></field>
<field name="FACILITY_TYPE_ID" type="integer"></field>
<field name="OLY_RUBRIC_ID" type="integer"></field>
<field name="START_DATE" type="date"></field>
<field name="SIGNING_DATE" type="date"></field>
<field name="CURRENCY_ID" type="integer"></field>
<field name="CURRENCY" length="12" type="text"></field>
<field name="FIX_FLOATING" length="1" type="text"></field>
<field name="CURRENT_INDEX" length="40" type="text"></field>
<field name="COUPON_FREQUENCY" length="12" type="text"></field>
<field name="MARGIN" type="float"></field>
<field name="INTEREST_RATE" type="float"></field>
<field name="UPFRONT_FEES" length="200" type="text"></field>
<field name="LIMIT" type="float"></field>
<field nativeFK="STATUS.ID" name="FK_STATUS" type="integer"></field>
</fields>
</DataSource>

Here is my jsp :

<SCRIPT>

<isomorphic:loadDS ID="FACT_CONTRACTS" />
grid = isc.ListGrid.create({
id: "listGridTest",
position: "relative",
width: $("#main").width(), height: $("#main").height(),
alternateRecordStyles:true,
canResizeFields: true,
autoFitFieldsFillViewport:false,
resizeFieldsInRealTime:false,
autoFitData:false,
dataFetchMode:"paged",
dataPageSize: 3,
fields:[
// Contracts table
{name:"ARCHIVE_NUMBER", title:"Archive number", width:100},
{name:"", title:"Next Credit review date", width:150},
{name:"OLY_CREDIT_PORTFOLIO_ID", title:"Oly Number – Credit Account", width:50},
{name:"FACILITY_TYPE_ID", title:"Facility code type", width:50},
{name:"FACILITY_TYPE", title:"Facility type", width:50},
{name:"SIGNING_DATE", title:"Signing date of new real estate credits", width:50},
{name:"START_DATE", title:"Start date", width:50},
{name:"FACILITY_AMOUNT_CCY", title:"Facility amount (ccy)", width:50},
{name:"CURRENCY", title:"Currency", width:50},
{name:"COUPON_FREQUENCY", title:"Frequency of roll over", width:50},
{name:"NUMBER_OF_ROLL", title:"Number of rolls", width:50},
{name:"INTEREST_RATE", title:"Interest rate", width:50},
{name:"MARGIN", title:"Margin (bp ou %)", width:50},
{name:"UPFRONT_FEES", title:"Fees", width:50},
// H_comments table
{name:"REMARKS", title:"Remarks", width:50}, // See how to manager One to many relationship
// Mandates table
{name:"CLIENT_ADVISOR", title:"Client advisor", width:50},
{name:"CLIENT_NAME", title:"Client name", width:50},
{name:"OLY_MANDATE_ID", title:"Mandate", width:50},
{name:"", title:"Market Segment", width:50},
// Status table
{name:"FACILITY_STATUS", title:"Status of the facility", width:50},
// Maturities table
{name:"", title:"Next Maturity", width:50},
{name:"ULTIMATE_MATURITY_COMMENT", title:"Comment on final maturity", width:50},
{name:"ULTIMATE_MATURITY", title:"Ultimate Final Maturity", width:50},
// Collaterals table
{name:"PLEDGED_ASSETS", title:"Pledged assets", width:50},
{name:"OLY_ACCOUNT_ID", title:"Olympic accounts", width:50},
{name:"NON_STANDARD_COLLATERAL", title:"Non Standard Collateral", width:50},
// Leverages table
{name:"LIC_NAME", title:"Name of the LIC", width:50}
],
autoFetchData: "true",
dataSource: "FACT_CONTRACTS",
showFilterEditor: true
});
</SCRIPT>

My Problem is that the generated SQL has no join on table CONTRACTS, here is the log :

=== 2014-03-10 18:35:17,539 [81-2] INFO IDACall - Performing 1 operation(s)
=== 2014-03-10 18:35:17,539 [81-2] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2014-03-10 18:35:17,539 [81-2] DEBUG DeclarativeSecurity - DataSource FACT_CONTRACTS is not in the pre-checked list, processing...
=== 2014-03-10 18:35:17,539 [81-2] DEBUG AppBase - [builtinApplication.FACT_CONTRACTS_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2014-03-10 18:35:17,539 [81-2] DEBUG AppBase - [builtinApplication.FACT_CONTRACTS_fetch] No public zero-argument method named '_FACT_CONTRACTS_fetch' found, performing generic datasource operation
=== 2014-03-10 18:35:17,540 [81-2] INFO SQLDataSource - [builtinApplication.FACT_CONTRACTS_fetch] Performing fetch operation with
criteria: {} values: {}
=== 2014-03-10 18:35:17,540 [81-2] INFO SQLWhereClause - [builtinApplication.FACT_CONTRACTS_fetch] empty condition
=== 2014-03-10 18:35:17,540 [81-2] INFO SQLDataSource - [builtinApplication.FACT_CONTRACTS_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2014-03-10 18:35:17,540 [81-2] DEBUG SQLDataSource - [builtinApplication.FACT_CONTRACTS_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2014-03-10 18:35:17,541 [81-2] DEBUG SQLDataSource - [builtinApplication.FACT_CONTRACTS_fetch] Eval'd row count query: SELECT COUNT(*) FROM CALMS_BUSINESS.FACT_CONTRACTS WHERE ('1'='1')
=== 2014-03-10 18:35:17,541 [81-2] DEBUG PoolableSQLConnectionFactory - [builtinApplication.FACT_CONTRACTS_fetch] makeObject() created an unpooled Connection '594213922'
=== 2014-03-10 18:35:17,541 [81-2] DEBUG SQLConnectionManager - [builtinApplication.FACT_CONTRACTS_fetch] Borrowed connection '594213922'
=== 2014-03-10 18:35:17,541 [81-2] DEBUG SQLDriver - [builtinApplication.FACT_CONTRACTS_fetch] About to execute SQL query in 'calms_business' using connection '594213922'
=== 2014-03-10 18:35:17,541 [81-2] INFO SQLDriver - [builtinApplication.FACT_CONTRACTS_fetch] Executing SQL query on 'calms_business': SELECT COUNT(*) FROM CALMS_BUSINESS.FACT_CONTRACTS WHERE ('1'='1')
=== 2014-03-10 18:35:17,541 [81-3] INFO Download - Returning 304: Not modified on conditional get of: C:\WORKBENCH\JAVA\.metadata\.plugins\org.eclipse.wst.server.core\tmp1\wtpwebapps\lending-upgrade-web\isomorphic\skins\Enterprise\images\Scrollbar\hscroll_sprite.png
=== 2014-03-10 18:35:17,545 [81-2] DEBUG SQLDataSource - [builtinApplication.FACT_CONTRACTS_fetch] JDBC driver windowed select rows 0->56, result size 56. Query: SELECT FACT_CONTRACTS.ARCHIVE_NUMBER, FACT_CONTRACTS.CURRENT_DATE, FACT_CONTRACTS.ID FROM CALMS_BUSINESS.FACT_CONTRACTS WHERE ('1'='1')
=== 2014-03-10 18:35:17,549 [81-2] DEBUG SQLConnectionManager - [builtinApplication.FACT_CONTRACTS_fetch] About to close PoolGuardConnectionWrapper with hashcode "594213922"
=== 2014-03-10 18:35:17,549 [81-2] DEBUG PoolableSQLConnectionFactory - [builtinApplication.FACT_CONTRACTS_fetch] makeObject() created an unpooled Connection '594213922'
=== 2014-03-10 18:35:17,554 [81-2] WARN RequestContext - dsRequest.execute() failed:
java.sql.SQLException: ORA-00904: "FACT_CONTRACTS"."ARCHIVE_NUMBER" : identificateur non valide

What did I miss ?

Thanks in advance

[davidj6]

Joins don't happen by magic and you don't include anything in your dataSources to cause a join to occur. I suggest you read the QuickStart Guide for further information.

[l.bourgeois]

Hello,

Based on the model of the supplyItem -> supplyCategory in docs examples (wich is not based on an SQL connection by the way) I put foreignKey="CONTRACTS.ARCHIVE_NUMBER" to make the glue.

Seems that something is missing.

Back to reading the user guide.

[l.bourgeois]

I finally found the solution by changing FACT_CONTRACTS this way :

<DataSource
schema="CALMS_BUSINESS"
dbName="calms_business"
tableName="FACT_CONTRACTS"
ID="FACT_CONTRACTS"
dataSourceVersion="1"
generatedBy="v9.0p_2014-02-11/EVAL Deployment 2014-02-11"
serverType="sql"
useAnsiJoins="true"
>
<fields>
<field primaryKey="true" name="ID" type="sequence"></field>
<field includeFrom="CONTRACTS.ARCHIVE_NUMBER"/>
<field name="CURRENT_DATE" type="date"></field>
<field name="FK_CONTRACT" type="integer" foreignKey="CONTRACTS.ID"></field>
</fields>
</DataSource>