Sets the WHERE clause of an SQL statement before processing, overwriting any SQL from the initial WHERE clause to the end of the SQL statement.
This is a useful method when you want to create a query which references temporary tables and you need to write the SQL WHERE clause to derive effective database indices.
The WHERE clause identifies which rows to use in a table based on selected criteria. The CustomSQLWhere (Method), the CustomSQLFrom (Method) , and the ResetCustomSQL (Method) correspond to the edit SQL functionality in the Custom SQL dialog box.
To use the Custom SQL feature, the query data model must have at least one table. If no table exists, then the Console Window displays: “Script(x):uncaught exception:Invalid String”. |
Expression.CustomSQLWhere(CustomSQLStr As String)
An expression that returns a query object
This example sets the FROM clause and the WHERE clause, processes the query, and then restores the original SQL statement:
//Set the FROM clause, Set the WHERE clause, PROCESS, and then RESET SQLActiveDocument.Sections["Query"].CustomSQLFrom("FROM From.Sales_Fact, From.Periods, From.Products") ActiveDocument.Sections["Query"].CustomSQLWhere("WHERE (Periods.Day_Id=Sales_Fact.Day_Id AND
Products.Product_Id=Sales_Fact.Product_Id) AND (Periods.Quarter='Q1')")
ActiveDocument.Sections["Query"].ResetCustomSQL()
ActiveDocument.Sections["Query"].Process();