I'm trying to use the new server summaries feature on a datasource that also uses customSelectExpressions on a few fields. But those fields are not wrapped in their summaryFunction so the SQL SELECT fails.
For example, the originalPlanSales field is just a normal field with no customSelectExpression. It has a summaryFunction of "sum" specified. But the weekEnd field has a subselect as it's customSelectExpression and "min" as it's summaryFunction. The resulting SELECT looks like this for those fields.
When it should look like this.
Also, there is no GROUP BY clause specified on the select, even though I used setGroupBy on the request.
Here is the complete datasource definition.
And here is the resulting SELECT after using setGroupBy("fiscalYear", "fiscalWeek") on the request.
For example, the originalPlanSales field is just a normal field with no customSelectExpression. It has a summaryFunction of "sum" specified. But the weekEnd field has a subselect as it's customSelectExpression and "min" as it's summaryFunction. The resulting SELECT looks like this for those fields.
Code:
SUM(DCATFWK.DCFOPA) AS originalPlanSales, (SELECT FCACDT FROM FCALNDR WHERE DCATFWK.FCAFYY=FCALNDR.FCAFYY AND DCATFWK.FCAFWW=FCALNDR.FCAFWW AND FCALNDR.FCAFDW=7) AS weekEnd
Code:
SUM(DCATFWK.DCFOPA) AS originalPlanSales, MIN((SELECT FCACDT FROM FCALNDR WHERE DCATFWK.FCAFYY=FCALNDR.FCAFYY AND DCATFWK.FCAFWW=FCALNDR.FCAFWW AND FCALNDR.FCAFDW=7) AS weekEnd
Here is the complete datasource definition.
Code:
<DataSource ID="CatalogWeek" dataFormat="iscServer"
webServiceOperations="fetch" serverType="sql" dbName="as400" title="Catalog Week"
pluralTitle="Catalog Results" sparseUpdates="true" tableName="DCATFWK"
canUpload="true" serverConstructor="com.islandpacific.gui.server.customDataSource.IpDataSource">
<!-- IP Direct catalog/week file with planned and actual demand. -->
<fields>
<field name="catalogId" nativeName="DCACAT" title="Catalog" type="text" length="6" primaryKey="true" detail="true" canEdit="false"/>
<field name="fiscalYear" nativeName="FCAFYY" title="Fiscal Year" type="integer" length="4" primaryKey="true" detail="true" canEdit="false"/>
<field name="fiscalWeek" nativeName="FCAFWW" title="Fiscal Week" type="integer" length="2" primaryKey="true" detail="true" canEdit="false"/>
<field name="weekStart" title="Week Start" type="date" sqlStorageStrategy="number" sqlDateFormat="yyyyMMdd" canEdit="false"
customSelectExpression="(SELECT FCACDT FROM FCALNDR WHERE DCATFWK.FCAFYY=FCALNDR.FCAFYY AND DCATFWK.FCAFWW=FCALNDR.FCAFWW AND FCALNDR.FCAFDW=1)"/>
<field name="weekEnd" title="Week End" type="date" sqlStorageStrategy="number" sqlDateFormat="yyyyMMdd" detail="true" canEdit="false"
customSelectExpression="(SELECT FCACDT FROM FCALNDR WHERE DCATFWK.FCAFYY=FCALNDR.FCAFYY AND DCATFWK.FCAFWW=FCALNDR.FCAFWW AND FCALNDR.FCAFDW=7)"/>
<field name="actualOrders" nativeName="DCFACO" title="Actual Orders" type="integer" length="7" canEdit="false"/>
<field name="actualSales" nativeName="DCFACA" title="Actual Sales" type="currency" canEdit="false"/>
<field name="actualAOV" title="Actual AOV" type="currency" canEdit="false" customSQL="true">
<userFormula text="S/O">
<formulaVars>
<S>actualSales</S>
<O>actualOrders</O>
</formulaVars>
</userFormula>
</field>
<field name="lostOrders" nativeName="DCFLOR" title="Lost Orders" type="integer" length="7" canEdit="false"/>
<field name="lostSales" nativeName="DCFLSL" title="Lost Sales" type="currency" canEdit="false"/>
<field name="lostAOV" title="Lost AOV" type="currency" canEdit="false" customSQL="true">
<userFormula text="S/O">
<formulaVars>
<S>lostSales</S>
<O>lostOrders</O>
</formulaVars>
</userFormula>
</field>
<field name="currentPlanOrders" nativeName="DCFCPO" title="Current Plan Orders" type="integer" length="7"/>
<field name="currentPlanSales" nativeName="DCFCPA" title="Current Plan Sales" type="integer" length="7"/>
<field name="currentPlanAOV" title="Current Plan AOV" type="currency" canEdit="false" customSQL="true">
<userFormula text="S/O">
<formulaVars>
<S>currentPlanSales</S>
<O>currentPlanOrders</O>
</formulaVars>
</userFormula>
</field>
<field name="originalPlanOrders" nativeName="DCFOPO" title="Original Plan Orders" type="integer" length="7" detail="true" />
<field name="originalPlanSales" nativeName="DCFOPA" title="Original Plan Sales" type="integer" length="7" detail="true" />
<field name="originalPlanAOV" title="Original AOV" type="currency" detail="true" canEdit="false" customSQL="true">
<userFormula text="S/O">
<formulaVars>
<S>originalPlanSales</S>
<O>originalPlanOrders</O>
</formulaVars>
</userFormula>
</field>
<field name="fieldUpdateAccessIdentifier" nativeName="@@UPID" title="Field Update / Access Identifier" type="integer" length="7" hidden="true" />
</fields>
<operationBindings>
<operationBinding operationType="fetch">
<summaryFunctions>
<catalogId>min</catalogId>
<fiscalYear>min</fiscalYear>
<fiscalWeek>min</fiscalWeek>
<weekStart>min</weekStart>
<weekEnd>min</weekEnd>
<actualOrders>sum</actualOrders>
<actualSales>sum</actualSales>
<lostOrders>sum</lostOrders>
<lostSales>sum</lostSales>
<currentPlanOrders>sum</currentPlanOrders>
<currentPlanSales>sum</currentPlanSales>
<originalPlanOrders>sum</originalPlanOrders>
<originalPlanSales>sum</originalPlanSales>
</summaryFunctions>
</operationBinding>
</operationBindings>
</DataSource>
Code:
SELECT SUM(DCATFWK.DCFACO) AS actualOrders, SUM(DCATFWK.DCFACA) AS actualSales, MIN(DCATFWK.DCACAT) AS catalogId, SUM(DCATFWK.DCFCPO) AS currentPlanOrders, SUM(DCATFWK.DCFCPA) AS currentPlanSales, MIN(DCATFWK.FCAFWW) AS fiscalWeek, MIN(DCATFWK.FCAFYY) AS fiscalYear, SUM(DCATFWK.DCFLOR) AS lostOrders, SUM(DCATFWK.DCFLSL) AS lostSales, SUM(DCATFWK.DCFOPO) AS originalPlanOrders, SUM(DCATFWK.DCFOPA) AS originalPlanSales, (SELECT FCACDT FROM FCALNDR WHERE DCATFWK.FCAFYY=FCALNDR.FCAFYY AND DCATFWK.FCAFWW=FCALNDR.FCAFWW AND FCALNDR.FCAFDW=7) AS weekEnd, (SELECT FCACDT FROM FCALNDR WHERE DCATFWK.FCAFYY=FCALNDR.FCAFYY AND DCATFWK.FCAFWW=FCALNDR.FCAFWW AND FCALNDR.FCAFDW=1) AS weekStart FROM DCATFWK WHERE ((DCATFWK.DCACAT IN ('B2B')) AND DCATFWK.DCACAT IS NOT NULL) ORDER BY min(DCATFWK.FCAFYY), min(DCATFWK.FCAFWW)
Comment