Subquery in Select statement

[Marc]

This is a great project to make dynamic SQL. I wanted to add some functionality: Subquery within one a selectcolumn e.g. select clientid, Total = (select sum(amount) from client where id = client.id) from client.
I know there are different ways to add such kind off functionality but this can be handy if you want to check for some existing record and not want to return the inner join.
I hope you understand my question

[Alon Catz]

Thanks for the feedback,
SubQueries in select columns is going to be included in the next version.

[Alon Catz]

Version 0.5 supports sub queries in select statement.
Example:
query.Columns.Add(new SelectColumn(SqlExpression.SubQuery("select count(*) from customers"), "cnt"));

[Marc]

I have a question about that: isn't it better to use SelectQuery object, so your SQL statement goes through the provider factory?
Here is some example code:
            public SelectColumn(string columnName, FromTerm table, string columnAlias, SelectQueryAggregationFunction function, SelectQuery subQuery)

            {

                  this.columnName = columnName;

                  this.table = table;

                  this.alias = columnAlias;

                  this.function = function;

                  this.selectstatement = subQuery;

            }

And to render:

            public void SelectColumn(StringBuilder builder, SelectColumn col, ISqlOmRenderer RenderObject)

            {

                  if (col.Function != SelectQueryAggregationFunction.None)

                        builder.AppendFormat("{0}(", col.Function.ToString());

                  QualifiedIdentifier(builder, col.TableName, col.ColumnName);

                 

                  if (col.Function != SelectQueryAggregationFunction.None)

                  {     builder.Append(")");}

                  if (col.SelectStatement != null)

                  {

                        if (col.SelectStatement.Columns.Count >= 1)

                        {builder.Append(" = (");

                        builder.Append(RenderObject.RenderSelect(col.SelectStatement));

                             builder.Append(")");}

                  }

                  if (col.ColumnAlias != null)

                  {

                        builder.Append(" ");

                        Identifier(builder, col.ColumnAlias);

                  }

            }

[Alon Catz]

Thanks, forgot about that.

In the recently released version 5.1 you can use SelectQuery as a parameter to SqlExpression.SubQuery