Programmatically remove tables from a query
Hi all,
I'd like to programmatically remove tables from my query. If I have a query with several joins I can click the "X" on a table in the UI and it will:
Remove the table
Remove any joins
Remove any columns from the Select clause.
E.g, In the following query I had 4 tables, and removed FactInternetSales and DimCustomer, and was left with the second query.
I couldn't find the API to do this despite some searching, and would appreciate being pointed in the right direction.
Thanks
Colin
-------------------------------------------------
Example queries, started with
Select DimProduct.ProductKey, DimProduct.EnglishProductName, FactInternetSales.SalesOrderNumber, FactInternetSales.SalesAmount, DimCustomer.CustomerKey, DimCustomer.FirstName, DimProductSubcategory.ProductSubcategoryKey, DimProductSubcategory.EnglishProductSubcategoryName From DimProduct Inner Join FactInternetSales On DimProduct.ProductKey = FactInternetSales.ProductKey Inner Join DimCustomer On DimCustomer.CustomerKey = FactInternetSales.CustomerKey Inner Join DimProductSubcategory On DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
After removing tables
Select DimProduct.ProductKey, DimProduct.EnglishProductName, DimProductSubcategory.ProductSubcategoryKey, DimProductSubcategory.EnglishProductSubcategoryName From DimProduct Inner Join DimProductSubcategory On DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
Hi,
You can use the following code:
queryBuilder1.BeginUpdate(); try { UnionSubQuery firstSelect = queryBuilder1.Query.FirstSelect(); System.Collections.ArrayList dataSourcesArrayList = new System.Collections.ArrayList(); firstSelect.FromClause.GetDatasources(dataSourcesArrayList); foreach (DataSourceObject dataSourceObject in dataSourcesArrayList) { SQLDatabaseObject databaseObject = dataSourceObject.DatabaseObject; if (databaseObject.QualifiedNameWithoutQuotes == 'dbo.Orders') { dataSourceObject.Dispose; } } queryBuilder1.Query.NotifySQLUpdatedRecursive(); } finally { queryBuilder1.EndUpdate(); }Joins and columns related to this table will be removed automatically.