Hi Isomorphic,
I think I found a bug related to customSelectExpression when used together with includeFrom. No way I try the customSelectExpression I give is used.
Please see the bold parts in the ds.xmls:
The Log entry for the generated SQL statement is
I also tried to have the customSelectExpression only in one of the files, I tried without renaming the included field to "CURRENT_ACCOUNT_COUNT2", but nothing did change the result. I'm pretty sure, this is a bug.
As sidenote: http://www.smartclient.com/smartgwte...lectExpression states "Note that adding "AS partialName" as shown above is required - this is how the framework identifies the expression as producing a value for this particular DataSourceField when results are received from the database.". For me, the framework does add the "AS xxx" part automatically.
Best regards,
Blama
I think I found a bug related to customSelectExpression when used together with includeFrom. No way I try the customSelectExpression I give is used.
Please see the bold parts in the ds.xmls:
- T_RESELLER.ds.xml
Code:<DataSource dbName="Oracle" tableName="T_RESELLER" ID="T_RESELLER" serverType="sql"> <fields> [B]<field primaryKey="true" hidden="true" name="ID" type="sequence" foreignKey="V_CURRENT_ACCOUNT_COUNT.RESELLER_ID" joinType="outer"[/B]></field> <field hidden="true" name="TENANT_ID" title="TENANT_ID" type="integer"></field> <field foreignKey="V_USER_CREATED_BY.ID" name="CREATED_BY" title="Erstellt von" type="integer"></field> <field name="CREATED_AT" title="Erstellt am" type="date"></field> <field foreignKey="V_USER_MODIFIED_BY.ID" name="MODIFIED_BY" title="Geändert von" type="integer"></field> <field name="MODIFIED_AT" title="Geändert am" type="date"></field> <field name="NAME" title="Firmenname" length="60" type="text" escapeHTML="true" required="true"> <validators> <validator type="isUnique" errorMessage="Der Name wird bereits verwendet!"></validator> </validators> </field> <field name="MAXACCOUNTS" title="Max. Anzahl Accounts" type="integer" required="true"> <validators> <validator type="integerRange" min="1" errorMessage="Die Anzahl der Accounts muss größer 0 sein!"></validator> </validators> </field> <field name="UMSATZ" title="geschätzter Jahresumsatz" type="integer"> <validators> <validator type="integerRange" min="0" max="100000000" errorMessage="Der geschätzte Umsatz muss zwischen 0 und 100.000.000 EUR liegen!"></validator> </validators> </field> <field name="ANZAHL_MA" title="geschätzte Anzahl Mitarbeiter" type="integer"> <validators> <validator type="integerRange" min="1" errorMessage="Die Anzahl der Mitarbeiter muss größer 0 sein!"></validator> </validators> </field> <field name="GESCHAEFTSGEGENSTAND" title="Geschäftsgegenstand" length="60" type="text" escapeHTML="true"></field> <field foreignKey="T_CATEGORY1.ID" name="CATEGORY1_ID" displayField="CATEGORY1_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY1_NAME" includeFrom="T_CATEGORY1.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY1_POSITION" includeFrom="T_CATEGORY1.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY1_AVAILABLE" includeFrom="T_CATEGORY1.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY2.ID" name="CATEGORY2_ID" displayField="CATEGORY2_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY2_NAME" includeFrom="T_CATEGORY2.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY2_POSITION" includeFrom="T_CATEGORY2.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY2_AVAILABLE" includeFrom="T_CATEGORY2.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY3.ID" name="CATEGORY3_ID" displayField="CATEGORY3_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY3_NAME" includeFrom="T_CATEGORY3.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY3_POSITION" includeFrom="T_CATEGORY3.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY3_AVAILABLE" includeFrom="T_CATEGORY3.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY4.ID" name="CATEGORY4_ID" displayField="CATEGORY4_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY4_NAME" includeFrom="T_CATEGORY4.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY4_POSITION" includeFrom="T_CATEGORY4.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY4_AVAILABLE" includeFrom="T_CATEGORY4.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY5.ID" name="CATEGORY5_ID" displayField="CATEGORY5_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY5_NAME" includeFrom="T_CATEGORY5.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY5_POSITION" includeFrom="T_CATEGORY5.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY5_AVAILABLE" includeFrom="T_CATEGORY5.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY6.ID" name="CATEGORY6_ID" displayField="CATEGORY6_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY6_NAME" includeFrom="T_CATEGORY6.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY6_POSITION" includeFrom="T_CATEGORY6.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY6_AVAILABLE" includeFrom="T_CATEGORY6.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY7.ID" name="CATEGORY7_ID" displayField="CATEGORY7_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY7_NAME" includeFrom="T_CATEGORY7.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY7_POSITION" includeFrom="T_CATEGORY7.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY7_AVAILABLE" includeFrom="T_CATEGORY7.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY8.ID" name="CATEGORY8_ID" displayField="CATEGORY8_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY8_NAME" includeFrom="T_CATEGORY8.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY8_POSITION" includeFrom="T_CATEGORY8.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY8_AVAILABLE" includeFrom="T_CATEGORY8.AVAILABLE" canSave="false" hidden="true"></field> <field foreignKey="T_CATEGORY9.ID" name="CATEGORY9_ID" displayField="CATEGORY9_NAME" title="" type="integer" canFilter="false"></field> <field name="CATEGORY9_NAME" includeFrom="T_CATEGORY9.NAME" canSave="false" hidden="true"></field> <field name="CATEGORY9_POSITION" includeFrom="T_CATEGORY9.POSITION" canSave="false" hidden="true"></field> <field name="CATEGORY9_AVAILABLE" includeFrom="T_CATEGORY9.AVAILABLE" canSave="false" hidden="true"></field> <field name="ISASSIGNEDDISTRIBUTOR" title="Distributorzugeordnet" type="integer" tableName="T_DISTRIBUTOR" hidden="true" nativeName="ID" canSave="false"></field> <field includeFrom="V_CURRENT_ACCOUNT_COUNT.CURRENT_ACCOUNT_COUNT" name="CURRENT_ACCOUNT_COUNT2" customSelectExpression="NVL(V_CURRENT_ACCOUNT_COUNT.CURRENT_ACCOUNT_COUNT, 0)"></field> </fields> <operationBindings> <operationBinding operationType="fetch" outputs="ID, TENANT_ID, MODIFIED_BY, MODIFIED_AT, ADDRESS_ID, NAME, MAXACCOUNTS, UMSATZ, ANZAHL_MA, GESCHAEFTSGEGENSTAND, CATEGORY1_ID, CATEGORY1_POSITION, CATEGORY1_NAME, CATEGORY1_AVAILABLE, CATEGORY2_ID, CATEGORY2_POSITION, CATEGORY2_NAME, CATEGORY2_AVAILABLE, CATEGORY3_ID, CATEGORY3_POSITION, CATEGORY3_NAME, CATEGORY3_AVAILABLE, CATEGORY4_ID, CATEGORY4_POSITION, CATEGORY4_NAME, CATEGORY4_AVAILABLE, CATEGORY5_ID, CATEGORY5_POSITION, CATEGORY5_NAME, CATEGORY5_AVAILABLE, CATEGORY6_ID, CATEGORY6_POSITION, CATEGORY6_NAME, CATEGORY6_AVAILABLE, CATEGORY7_ID, CATEGORY7_POSITION, CATEGORY7_NAME, CATEGORY7_AVAILABLE, CATEGORY8_ID, CATEGORY8_POSITION, CATEGORY8_NAME, CATEGORY8_AVAILABLE, CATEGORY9_ID, CATEGORY9_POSITION, CATEGORY9_NAME, CATEGORY9_AVAILABLE, CURRENT_ACCOUNT_COUNT2"> </operationBinding> <operationBinding operationType="fetch" operationId="getNotYetAssignedPartners"> <tableClause> T_RESELLER CROSS JOIN T_DISTRIBUTOR LEFT OUTER JOIN T_RESELLER_DISTRIBUTOR ON T_RESELLER.ID = T_RESELLER_DISTRIBUTOR.RESELLER_ID AND T_DISTRIBUTOR.ID = T_RESELLER_DISTRIBUTOR.DISTRIBUTOR_ID </tableClause> <whereClause>T_RESELLER_DISTRIBUTOR.RESELLER_ID IS NULL AND ($defaultWhereClause)</whereClause> </operationBinding> <operationBinding operationType="add"> <values fieldName="CREATED_BY" value="$session.authenticatedUserID" /> <values fieldName="CREATED_AT" value="$currentDate" /> <values fieldName="MODIFIED_BY" value="$session.authenticatedUserID" /> <values fieldName="MODIFIED_AT" value="$currentDate" /> <values fieldName="TENANT_ID" value="$session.authenticatedUserTenantID" /> </operationBinding> <operationBinding operationType="update"> <values fieldName="MODIFIED_BY" value="$session.authenticatedUserID" /> <values fieldName="MODIFIED_AT" value="$currentDate" /> <values fieldName="TENANT_ID" value="$session.authenticatedUserTenantID" /> </operationBinding> </operationBindings> </DataSource>
- V_CURRENT_ACCOUNT_COUNT.ds.xml
Code:<DataSource dbName="Oracle" tableName="V_CURRENT_ACCOUNT_COUNT" ID="V_CURRENT_ACCOUNT_COUNT" dataSourceVersion="1" serverType="sql"> <fields> <field name="COMPANY_ID" title="" type="integer"></field> <field name="RESELLER_ID" title="" type="integer"></field> <field name="DISTRIBUTOR_ID" title="" type="integer"></field> [B]<field name="CURRENT_ACCOUNT_COUNT" customSelectExpression="NVL(V_CURRENT_ACCOUNT_COUNT.CURRENT_ACCOUNT_COUNT, 0)" title="Aktuelle Anzahl aktiver Accounts" type="integer"></field>[/B] </fields> <operationBindings> <operationBinding operationType="update" requiresRole="readonly" /> <operationBinding operationType="add" requiresRole="readonly" /> <operationBinding operationType="delete" requiresRole="readonly" /> </operationBindings> </DataSource>
The Log entry for the generated SQL statement is
Code:
=== 2013-08-22 16:52:08,144 [ec-2] DEBUG SQLDataSource - [builtinApplication.T_RESELLER_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT T_RESELLER.ANZAHL_MA, T_RESELLER.CATEGORY1_ID, T_RESELLER.CATEGORY2_ID, T_RESELLER.CATEGORY3_ID, T_RESELLER.CATEGORY4_ID, T_RESELLER.CATEGORY5_ID, T_RESELLER.CATEGORY6_ID, T_RESELLER.CATEGORY7_ID, T_RESELLER.CATEGORY8_ID, T_RESELLER.CATEGORY9_ID, T_RESELLER.GESCHAEFTSGEGENSTAND, T_RESELLER.ID, T_RESELLER.MAXACCOUNTS, T_RESELLER.MODIFIED_AT, T_RESELLER.MODIFIED_BY, T_RESELLER.NAME, T_RESELLER.TENANT_ID, T_RESELLER.UMSATZ, T_CATEGORY1.NAME AS CATEGORY1_NAME, T_CATEGORY1.POSITION AS CATEGORY1_POSITION, T_CATEGORY1.AVAILABLE AS CATEGORY1_AVAILABLE, T_CATEGORY2.NAME AS CATEGORY2_NAME, T_CATEGORY2.POSITION AS CATEGORY2_POSITION, T_CATEGORY2.AVAILABLE AS CATEGORY2_AVAILABLE, T_CATEGORY3.NAME AS CATEGORY3_NAME, T_CATEGORY3.POSITION AS CATEGORY3_POSITION, T_CATEGORY3.AVAILABLE AS CATEGORY3_AVAILABLE, T_CATEGORY4.NAME AS CATEGORY4_NAME, T_CATEGORY4.POSITION AS CATEGORY4_POSITION, T_CATEGORY4.AVAILABLE AS CATEGORY4_AVAILABLE, T_CATEGORY5.NAME AS CATEGORY5_NAME, T_CATEGORY5.POSITION AS CATEGORY5_POSITION, T_CATEGORY5.AVAILABLE AS CATEGORY5_AVAILABLE, T_CATEGORY6.NAME AS CATEGORY6_NAME, T_CATEGORY6.POSITION AS CATEGORY6_POSITION, T_CATEGORY6.AVAILABLE AS CATEGORY6_AVAILABLE, T_CATEGORY7.NAME AS CATEGORY7_NAME, T_CATEGORY7.POSITION AS CATEGORY7_POSITION, T_CATEGORY7.AVAILABLE AS CATEGORY7_AVAILABLE, T_CATEGORY8.NAME AS CATEGORY8_NAME, T_CATEGORY8.POSITION AS CATEGORY8_POSITION, T_CATEGORY8.AVAILABLE AS CATEGORY8_AVAILABLE, T_CATEGORY9.NAME AS CATEGORY9_NAME, T_CATEGORY9.POSITION AS CATEGORY9_POSITION, T_CATEGORY9.AVAILABLE AS CATEGORY9_AVAILABLE, V_CURRENT_ACCOUNT_COUNT.CURRENT_ACCOUNT_COUNT AS CURRENT_ACCOUNT_COUNT2 FROM T_RESELLER JOIN T_CATEGORY1 ON T_RESELLER.CATEGORY1_ID = T_CATEGORY1.ID JOIN T_CATEGORY2 ON T_RESELLER.CATEGORY2_ID = T_CATEGORY2.ID JOIN T_CATEGORY3 ON T_RESELLER.CATEGORY3_ID = T_CATEGORY3.ID JOIN T_CATEGORY4 ON T_RESELLER.CATEGORY4_ID = T_CATEGORY4.ID JOIN T_CATEGORY5 ON T_RESELLER.CATEGORY5_ID = T_CATEGORY5.ID JOIN T_CATEGORY6 ON T_RESELLER.CATEGORY6_ID = T_CATEGORY6.ID JOIN T_CATEGORY7 ON T_RESELLER.CATEGORY7_ID = T_CATEGORY7.ID JOIN T_CATEGORY8 ON T_RESELLER.CATEGORY8_ID = T_CATEGORY8.ID JOIN T_CATEGORY9 ON T_RESELLER.CATEGORY9_ID = T_CATEGORY9.ID LEFT OUTER JOIN V_CURRENT_ACCOUNT_COUNT ON T_RESELLER.ID = V_CURRENT_ACCOUNT_COUNT.RESELLER_ID WHERE ('1'='1')
As sidenote: http://www.smartclient.com/smartgwte...lectExpression states "Note that adding "AS partialName" as shown above is required - this is how the framework identifies the expression as producing a value for this particular DataSourceField when results are received from the database.". For me, the framework does add the "AS xxx" part automatically.
Best regards,
Blama
Comment