It is often useful to use the SQL IN clause to apply operations. The {Where.IdList} Sql Pattern provides a build-in pattern for building such clauses when applying an operation to multiple rows. For example:
However, the {Where.IdList} pattern only applies to IDs for the subject table. The Valuation Hub must represent the contractual relationship between 'Service Providers' (BPO companies) and their 'Clients'. The new Contract and ContractProduct classes handle this mapping. For example:
When designing the New Contract page, it is convenient to present the user with the basic Contract fields, as well as a list of Process Templates, allowing the user to select the products being covered under the contract from the same screen. The SQL statement in the Contract.config for this needs to be something like: <Statement Name="New" Query=" INSERT INTO {Table} ({Select.Insert}) VALUES ({From.Insert}) INSERT INTO ContractProduct (ContractProduct, ContractID, ProcessTemplateID, CreatedDate, CreatedPersonID) SELECT ProcessTemplate, SCOPE_IDENTITY(), ProcessTemplateID, @CreatedDate, @CreatedPersonID) FROM ProcessTemplate WHERE ProcessTemplateID IN ({A list of Process Template IDs here would be nice}) "> The {Where.IdList} pattern does not work in this case, because it's engineered to expect Contract IDs, not ProcessTemplateIDs. For this situation, we can use the {Param.*} pattern, to wit: <Statement Name="New" Query=" INSERT INTO {Table} ({Select.Insert}) VALUES ({From.Insert}) INSERT INTO ContractProduct (ContractProduct, ContractID, ProcessTemplateID, CreatedDate, CreatedPersonID) SELECT ProcessTemplate, SCOPE_IDENTITY(), ProcessTemplateID, @CreatedDate, @CreatedPersonID) FROM ProcessTemplate WHERE ProcessTemplateID IN ({Param.ProcessTemplateID}) "> <Parameters> <Parameter Name="ProcessTemplateID" DbType="String" Value="0"/> </Parameters> </Statement> The {Param.ProcessTemplateID} will be replaced by a ProcessTemplateID parameter. Assuming the New Contract page has a bunch of checkboxes named 'ProcessTemplateID', the data being send to the server will look like: Contract.ashx/New?Contract=My New Contract&...&ProcessTemplateID=1&ProcessTemplateID=6&... translating to a ProcessTemplateID parameter like this: ProcessTemplateID: '1,6' translating to a SQL snippet like this: WHERE ProcessTemplateID IN (1, 6) Note that the Statement in the configuration file has a default parameter value of 0. If the user checks no checkboxes, the SQL IN clause will still be syntactically valid, and simply result in no ContractProduct rows being added since there are no ProcessTemplate rows with an ID of 0. |
Quandis Business Objects 3 > QBO 3 Blog >