Problem with FilterBuilder and fetchData?

Technical Q&A
1

I have an sql datasource connected to Oracle that has a table join, when called with listGrid.filterData(advancedFilter.getCriteria()); it works as expected. When I use the same filter but call it with isc.DataSource.get(“waferTrend”).fetchData(advancedFilter.getCriteria(), function(dsResponse, data){})
The the join is left out of the query.

I am using the SmartClient 8.0 Power edition with Analytics.
DS:

<DataSource ID="waferTrend" serverType="sql" dbName="Data-corp" tableName="probe_headers">
    <fields>
        <field name="id" type="sequence" title="ID" primaryKey="true" hidden="true">
            <validators>
                <Validator>
                    <type>isInteger</type>
                    <stopIfFalse>true</stopIfFalse>
                    <typeCastValidator>true</typeCastValidator>
                    <defaultErrorMessage>Must be a whole number.</defaultErrorMessage>
                    <resultingValue></resultingValue>
                </Validator>
            </validators>
        </field>
        <field name="name" type="text" tableName="lots" />
        <field name="device" type="text" length="16" title="Device">
            <validators>
                <Validator>
                    <type>isString</type>
                    <stopIfFalse>true</stopIfFalse>
                    <typeCastValidator>true</typeCastValidator>
                </Validator>
            </validators>
        </field>
        <field name="end_date" type="date" length="10" title="Test Date">
            <validators>
                <Validator>
                    <type>isString</type>
                    <stopIfFalse>true</stopIfFalse>
                    <typeCastValidator>true</typeCastValidator>
                </Validator>
            </validators>
        </field>
      </fields>
    
    <operationBindings>
        <operationBinding operationType="fetch">
            <tableClause>probe_headers, lots</tableClause>
            <whereClause>probe_headers.lot_id = lots.id AND ($defaultWhereClause)</whereClause>
        </operationBinding>
    </operationBindings>
    
    <orderClause>name, scribe ASC</orderClause>
</DataSource>

The Filter:

isc.FilterBuilder.create({
    ID:"advancedFilter",
    dataSource:"waferTrend",
    criteria: { _constructor: "AdvancedCriteria",
        operator: "and", criteria: [
            {fieldName: "end_date", operator: "greaterThan", value: {
                "_constructor":"RelativeDate", 
                "value":"$yesterday"
            }}, 
            {fieldName:"device", operator:"iContains", value:"C5AE2"
            }            
        ]
    }
});

When called from:

isc.ListGrid.create({
    ID: "waferList",
    height:200, width:600,
    autoDraw:false,
    dataSource:"waferTrend",
    alternateRecordStyles:true, 
    fields:[
        {name:"name", title:"Lotname", width:80, align:"left"},
        {name:"end_date", title:"Test Date", width:110}
    ],
    showFilterEditor:false,
    autoFetchData:false,
    bodyProperties: {
        canSelectText: true
    }
});

isc.IButton.create({
    ID:"filterButton",
    title:"Filter",
    click : function () {
        waferList.filterData(advancedFilter.getCriteria());
    }
});

I get this as the Oracle sql:

SELECT probe_headers.id, probe_headers.scribe_nbr, probe_headers.end_date, probe_headers.passed, round(100*passed/tested,2) AS yield, 
 lots.name,
probe_headers.device, probe_headers.tested, probe_headers.test_type 
FROM probe_headers, lots 
WHERE probe_headers.lot_id = lots.id AND (((probe_headers.end_date > TO_DATE('2011-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND probe_headers.end_date IS NOT NULL) 
AND (LOWER(probe_headers.device) LIKE LOWER('%c5ae2%') {ESCAPE '\'} AND probe_headers.device IS NOT NULL)))

When I call it from:

isc.IButton.create({ ID: "chartButton", autoDraw: true, title:"Fetch Data",
		   click:function(viewer, record, recordNum, field, fieldNum, value, rawValue){		
			isc.DataSource.get("waferTrend").fetchData(advancedFilter.getCriteria(),
			function(dsResponse, data){
         //do something with the returned data here
			}
			,{operationId:"jfetch"})
		   }
});

I get the following Oracle query:

SELECT probe_headers.id, probe_headers.scribe_nbr, probe_headers.end_date, probe_headers.passed, round(100*passed/tested,2) AS yield, probe_headers.device, probe_headers.tested, probe_headers.test_type 
FROM probe_headers 
WHERE ((probe_headers.end_date > TO_DATE('2011-02-04 00:00:00','YYYY-MM-DD HH24:MI:SS') 
AND probe_headers.end_date IS NOT NULL) 
AND (LOWER(probe_headers.device) LIKE LOWER('%c5ae2%') {ESCAPE '\'} AND probe_headers.device IS NOT NULL))

The second use of advancedFilter leaves off the lots table from the query and the column from the lots table.

2

As a work around I can create a view in Oracle to do the table join. That will solve the issue for my use case.

3

It looks as though you may have edited the code before posting? Your direct call of DataSource.fetchData() specifies an operationBinding that does not exist in your DataSource .ds.xml file - this would not work at all but you’re reporting it does, but just incorrectly?

4

I did edit out a lot of the fields to make the post shorter. I’ll make a new simpler datasource and page from scratch later and post the entirety of the code. For now the Oracle view is working around the issue .