I’ve seen examples on using a BAQDataView where it subscribes to a publisher and filters a column.
Is this not inefficient? I mean aren’t we getting back ALL BAQ results, and then filtering them? Is there a way to do this with a parameter in the BAQ? If so, how do I change the value of that parameter in my BAQDataView?
It is not inneficient as far as I can tell the filter is applied as an additional condition (on the fly)
and you can’t use a parameter for that you don’t need to since you are filtering based on the data on the baq.
-Jose
When I am subscribed to a publisher (say part) - does a change cause only a filter or does it requery the baq data?
Sure I could trace it, but you probably know off the top of your head 
Either way, I’ll take you word for it and give it a try. Thanks.
The example I gave you the other day uses a BAQView trace that one LoL
So for sh*ts and giggles I decided to do the trace wrote that small customization I shared with you before Quick Lot Pick where it has a Part dataView and a BAQView which subscribes to it.
Upon loading the customization it makes a call to Query.GetByID
<tracePacket>
<businessObject>Ice.Proxy.BO.DynamicQueryImpl</businessObject>
<methodName>GetByID</methodName>
<appServerUri>https://e101.sixspartners.us/IGGESUND_HTTPS/</appServerUri>
<returnType>Ice.Tablesets.DynamicQueryTableset</returnType>
<localTime>10/25/2017 20:59:50:5748504 PM</localTime>
<threadID>1</threadID>
<executionTime total="341" roundTrip="225" channel="0" bpm="0" other="116" />
<retries>0</retries>
<parameters>
<parameter name="queryID" type="System.String"><![CDATA[GetLotBinsByPartCopy]]></parameter>
</parameters>
</tracePacket>
Then it makes a call to a Method I didn’t even know existed but it is uber cool called GetEmptyResultSet, which as the name states just gives you back the “schema” of what the BAQ Result will look like. This allows your Grids and DataViews to initialize and get columns and such
<businessObject>Ice.Proxy.BO.DynamicQueryImpl</businessObject>
<methodName>GetQueryEmptyResultSet</methodName>
<appServerUri>https://e101.sixspartners.us/IGGESUND_HTTPS/</appServerUri>
<returnType>System.Data.DataSet</returnType>
<localTime>10/25/2017 20:59:51:0905305 PM</localTime>
<threadID>1</threadID>
<executionTime total="116" roundTrip="32" channel="0" bpm="0" other="84" />
<retries>0</retries>
Upon Changing the PartNum to (“1/12/001/610/8011”) in the “Parent” publishing view there is a call to BAQ.Execute… (as expected) but upon further inspection there is an additional on the fly injected “Where” item as shown
<businessObject>Ice.Proxy.BO.DynamicQueryImpl</businessObject>
<methodName>Execute</methodName>
<appServerUri>https://e101.sixspartners.us/IGGESUND_HTTPS/</appServerUri>
<returnType>System.Data.DataSet</returnType>
<localTime>10/25/2017 21:02:31:6314485 PM</localTime>
<threadID>1</threadID>
<executionTime total="147" roundTrip="123" channel="0" bpm="0" other="24" />
<retries>0</retries>
<parameter name="executionParams" type="Ice.BO.QueryExecutionDataSet">
<QueryExecutionDataSet xmlns="http://www.epicor.com/Ice/300/BO/DynamicQuery/QueryExecution">
<ExecutionSetting>
<Name>Where</Name>
<Value>(PartBin_PartNum = N'1/12/001/610/8011')</Value>
<SysRowID>00000000-0000-0000-0000-000000000000</SysRowID>
</ExecutionSetting>
</QueryExecutionDataSet>
</parameter>
As you can see, it on-the fly added a new Where clause to the BAQ and passed in my Part number, so the BAQ never executed without the filter!
Awesome sauce, no inefficiency!
#TheMoreYouKnow
Great work! I’m still a bit curious as to what the actual final query would look like if we got a SQL trace of that.
Ask and you shall receive, looks like it creates a Proper SQL query with an actual whereClause and it uses CTE which is pretty awesome, so it can add the whereClause to the entire BAQ set… pretty SWANKY
with [SubQuery1] as
(select [PartBin].[PartNum] [PartBin_PartNum],[PartBin].[OnhandQty]-[PartBin].[AllocatedQty] [Calculated_Actual],[PartBin].[WarehouseCode] [PartBin_WarehouseCode],[PartBin].[BinNum] [PartBin_BinNum],[PartBin].[LotNum]
[PartBin_LotNum],[PartBin].[DimCode] [PartBin_DimCode],[Part_UD].[BaseName_c] [Part_BaseName_c],[Part_UD].[Calliper_RecID_c] [Part_Calliper_RecID_c],[Part_UD].[CoreDiameter_c]
[Part_CoreDiameter_c],[Part_UD].[CoreDiameterMetric_c] [Part_CoreDiameterMetric_c],[Part_UD].[Density_c] [Part_Density_c],[Part_UD].[Diameter_c] [Part_Diameter_c],[Part_UD].[DiameterMetric_c]
[Part_DiameterMetric_c],[Part_UD].[DimensionUoM_c] [Part_DimensionUoM_c],[Part_UD].[Format_c] [Part_Format_c],[Part_UD].[FormatDesc_c] [Part_FormatDesc_c],[Part_UD].[Grammage_c] [Part_Grammage_c],[Part_UD].[GrammageUoM_c] [Part_GrammageUoM_c],[Part_UD].[Length_c] [Part_Length_c],[Part_UD].[LengthMetric_c] [Part_LengthMetric_c],[Part_UD].[LinearFootFactor_c] [Part_LinearFootFactor_c],[Part_UD].[LinearFootUoM_c] [Part_LinearFootUoM_c],[Part_UD].[MillCode_c] [Part_MillCode_c],[Part_UD].[MillCodeDesc_c] [Part_MillCodeDesc_c],[Part_UD].[MWeight_c] [Part_MWeight_c],[Part_UD].[MWeightMetric_c] [Part_MWeightMetric_c],[Part_UD].[MWeightMetricUoM_c] [Part_MWeightMetricUoM_c],[Part_UD].[MWeightUoM_c] [Part_MWeightUoM_c],[Part_UD].[NetWeightAdder_c] [Part_NetWeightAdder_c],[Part_UD].[Packaging_c] [Part_Packaging_c],[Part_UD].[PackagingDesc_c] [Part_PackagingDesc_c],[Part_UD].[PoundFactor_c] [Part_PoundFactor_c],[Part_UD].[PoundUoM_c] [Part_PoundUoM_c],[Part_UD].[PrintingSideUp_c] [Part_PrintingSideUp_c],[Part_UD].[Product_RecID_c] [Part_Product_RecID_c],[Part_UD].[ProductMktDesc_c] [Part_ProductMktDesc_c],[Part_UD].[ProductName_c] [Part_ProductName_c],[Part_UD].[ProductNameEx_c] [Part_ProductNameEx_c],[Part_UD].[PurchasingFactor_c] [Part_PurchasingFactor_c],[Part_UD].[PurchasingFactor8_c] [Part_PurchasingFactor8_c],[Part_UD].[PurchasingUoM_c] [Part_PurchasingUoM_c],[Part_UD].[SalesGrade_c]
[Part_SalesGrade_c],[Part_UD].[SellingFactor_c] [Part_SellingFactor_c],[Part_UD].[SellingUoM_c] [Part_SellingUoM_c],[Part_UD].[SheetsPerPackingUnit_c] [Part_SheetsPerPackingUnit_c],[Part_UD].[SheetsPerPallet_c] [Part_SheetsPerPallet_c],[Part_UD].[SquareFootFactor_c] [Part_SquareFootFactor_c],[Part_UD].[SquareFootUoM_c] [Part_SquareFootUoM_c],[Part_UD].[StacksPerPallet_c] [Part_StacksPerPallet_c],[Part_UD].[Tabbed_c] [Part_Tabbed_c],[Part_UD].[ThicknessPoints_c] [Part_ThicknessPoints_c],[Part_UD].[Width_c] [Part_Width_c],[Part_UD].[WidthMetric_c] [Part_WidthMetric_c]
from ( [Erp].[PartBin] inner join @AvailCompLst [AvailCLst_PartBin] on [Erp].[PartBin].[Company] is null Or [Erp].[PartBin].[Company] = [AvailCLst_PartBin].[Company])
inner join ( [Erp].[Part] inner join [Erp].[Part_UD] on [Erp].[Part].[SysRowID] = [Erp].[Part_UD].[ForeignSysRowID]) on [Erp].[PartBin].[Company] = [Erp].[Part].[Company] And [Erp].[PartBin].[PartNum] = [Erp].[Part].[PartNum]
where [PartBin].[OnhandQty]-[PartBin].[AllocatedQty] > 0
)
select *
from [SubQuery1] [SubQuery1]
where (PartBin_PartNum = N'1/12/001/610/8011')
That is not how I envisioned that to work behind the scenes. Very nice.
Thanks Jose, you’re a gun!
That’s actually really good. Dynamically generated SQL like this can be difficult to work out indexes for, but if the rules are straingt forward, I mightn’t be too bad.