SelectClause

Select columns and constants can be set in the Select clause. It is also possible to give it a different name.

Select

There are multiple ways to specify the Select clause, but the most frequent pattern is column selection.

In addition to column selection, it supports constants, calculation formulas, and inline queries.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("table_a").As("a");
        sq.Select(a, "a_id");
        sq.Select(a, "value").As("val");
        sq.Select(10).As("val_10");
        Console.WriteLine(sq.ToText());
    }
}

Column(ColumnValue)

If you want to select a column, set the From clause and get the SelectableTable in advance.

Then use the Select method to select the table and column names. You can specify an alias name by using the chain method As.

Alias name is optional.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("table_a").As("a");
        sq.Select(a, "a_id");
        sq.Select(a, "value").As("val");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    a.a_id,
    a.value AS val
FROM
    table_a AS a

NOTE: The above description is an abbreviation. To be verbose, you need to use the ColumnValue class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
		var (f, a) = sq.From("tabla_a").As("a");
        sq.Select(new ColumnValue(a, "a_id")).As("a_id");
        sq.Select(new ColumnValue(a, "value")).As("val");
        Console.WriteLine(sq.ToText());
    }
}

Constant

SelectableTable is not used when selecting constants.

Write the value you want to select in the Select method as is, and also specify an alias name using method chaining.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(1).As("v1");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    1 AS v1

NOTE: The above description is an abbreviation. To be verbose, you need to use the LiteralValue class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(new LiteralValue("1")).As("v1");
        Console.WriteLine(sq.ToText());
    }
}

Parameter

You need to register parameters using the AddParameter method in advance. The return is the parameter name and the type is string. Please note that Carbunql does not manage placeholder identifiers, so you need to specify an identifier that matches the execution environment.

You can then select by passing the parameter name to the Select method.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var prm = sq.AddParameter(":prm", 1);
        sq.Select(prm).As("val");
        Console.WriteLine(sq.ToText());
    }
}

/*
  :prm = 1
*/
SELECT
    :prm AS val

Inline query

Select queries can be used as inline queries by using the ToValue method.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var x = new SelectQuery("select x.value from table_x"); 
		
        var sq = new SelectQuery();
        sq.Select(x.ToValue()).As("val");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    (
        SELECT
            x.value
        FROM
            table_x
    ) AS val

Formula

You can add operators and operands using the AddOperatableValue method of the ValueBase class. Note that added expressions are always added at the end.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("tabla_a").As("a");
        sq.Select(() => {
            var v = new LiteralValue("1");
            v.AddOperatableValue("+", new ColumnValue(f, "value1"));
            v.AddOperatableValue("-", new ColumnValue(f, "value2"));
            return v;
        }).As("val");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    1 + a.value1 - a.value2 AS val
FROM
    tabla_a AS a

Grouping

If you want to use parentheses, use the ToGroup method.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("tabla_a").As("a");
        sq.Select(() => {
            var v1 = new LiteralValue("1");
            
            var v2 = new ColumnValue(f, "value1");
            v2.AddOperatableValue("-", new ColumnValue(f, "value2"));
            
            v1.AddOperatableValue("*", v2.ToGroup());
      
            return v1;
        }).As("val");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    1 * (a.value1 - a.value2) AS val
FROM
    tabla_a AS a

Function

Express it using the FunctionValue class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(new FunctionValue("now")).As("dt");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    NOW() AS dt

Arguments

Specify the arguments as follows.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(new FunctionValue("concat", new ValueCollection { "'a'", "'b'", "'c'" })).As("txt");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    CONCAT('a', 'b', 'c') AS txt

Window function

It can be defined using the OverClause class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
using Carbunql.Clauses;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("table_a").As("a");
        sq.Select(new FunctionValue("row_number",
            () =>
            {
                var ov = new OverClause();
                ov.AddPartition(new ColumnValue(a, "name"));
                ov.AddOrder(new ColumnValue(a, "a_id").ToSortable());
                return ov;
            }
        )).As("row_num");
        Console.WriteLine(sq.ToText());
    }
}

SELECT
    ROW_NUMBER() OVER(
        PARTITION BY
            a.name
        ORDER BY
            a.a_id
    ) AS row_num
FROM
    table_a AS a

Window clause

It is possible to write, but it is on hold because it is very redundant and the specifications are likely to change.