HomeActive Query BuilderMSSQL 2005 Common Tables Expression, modifying a WITH statement

MSSQL 2005 Common Tables Expression, modifying a WITH statement


Hi!

I am trying to modify a query using a WITH statement to add an extra output column to the WITH statement, but I am not able to change it.

I can get hold of the DataSource object by calling
unionSubQuery.FromClause.FindTablesByDBName(tableQN, tables);
and it allows me to add the column like this
foreach (DataSource dsObj in tables)
{
MetadataField mdf = dsObj.Fields.AddField(columnQN);
}
but this is not affected in the output SQL property

To add the extra column to all SELECT statements works fine.

The query looks like this:
WITH OverdueCustomerDebtsCTE(CustomerNo, VoucherNo, Overdue1, Overdue2, Overdue3, Overdue4, Overdue5, Overdue6, NotDue)
AS
(
SELECT T.CustNo AS CustomerNo, T.VoNo AS VoucherNo, ...
FROM CustTr T
WHERE ...
UNION ALL
SELECT T.CustNo AS CustomerNo, T.VoNo AS VoucherNo, ...
FROM CustTr T
WHERE ...
)
SELECT O.CustomerNo AS CustomerNo, O.VoucherNo AS VoucherNo, ...
FROM OverdueCustomerDebtsCTE
Thomas Schjerpen | email
March 31 2009, 07:13 AM
Dear Thomas,

Use the following sample:


private void button1_Click(object sender, EventArgs e)
{
SQLWithClauseItem with;
SQLAliasExpression alias;
SubQuery subQuery;
UnionSubQuery unionSubQuery;
CriteriaItem ci;

// set SQL to process
queryBuilder1.SyncSQL = "with o(OrderId, OrderDate) as (select OrderId, OrderDate from Orders union all select OrderId, OrderDate from Orders) select * from o";

queryBuilder1.BeginUpdate();

try
{
if (queryBuilder1.Query.SubQueryAST.WithClause.Count > 0)
{
// get WITH clause item
with = (SQLWithClauseItem) queryBuilder1.Query.SubQueryAST.WithClause.Items[0];

// add new entry into WITH clause item columns list
alias = new SQLAliasExpression(queryBuilder1.SQLContext);
alias.Alias = new AstTokenIdentifier(queryBuilder1.SQLContext, "my additional field");
with.ColumnNamesList.Add(alias);

// get subquery from WITH item
subQuery = ((SubSelectStatementProxy) with.SubQuery).SubQuery;

// process each union subquery
for (int i = 0; i < subQuery.Count; i++)
{
// get reference to "union subquery"
unionSubQuery = (UnionSubQuery) subQuery[i];

// add index as new expression
ci = unionSubQuery.CriteriaList.AddExpression(i.ToString() + " as [my additional expression]");
ci.Select = true;
}
}
}
finally
{
queryBuilder1.EndUpdate();
}
}
Eugene Starkov | email
1 day, 22 hours since original post
Thank you!
This worked perfect.

Is there some kind of guideline on when to look in the UnionSubQuery elements or when to use the AST elements.
Thomas Schjerpen | email
6 days, 1 hour since original post
Dear Thomas,

Most of the possibilities of working with UnionSubQuery elements are demonstrated in the Non-Visual and Query Modification demo projects. The rest of operation should be made using AST elements.
Sergey Smagin | email
6 days, 19 hours since original post
This topic is closed