Active Query Builder support area

Programmatically remove tables from a query

Avatar
  • updated
  • Completed

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

Avatar
Andrey Zavyalov, PM

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.

Avatar
Colin

Excellent, thanks for the fast support!