Question
I have a rather quirky question. In a SELECT statement can I set a default value?
In the following query I want boolItem to always be false (not retrieved from the database). Crazy I know but bear with me as I explain why.
SELECT id, boolItem = False
FROM MyTable;
I am working with a large existing SQL database and project. I am querying data and returning them as Action C# objects. An action can be custom made by the user or a standard one. This is signified by a property IsCustom.
public class Action
{
public int Id { get; set; }
public bool IsCustom { get; set; }
.....
}
In the SQL database custom actions are stored in the table custom_actions and standard actions are in the table actions.
I retrieve and store Action objects using the below code. I want to make the query of the actions table always set the property IsCustom to false. And the query of the custom_actions table always set the property IsCustom to true. I am using the query SELECT a.id AS Id, a.is_custom = false AS IsCustom which is not valid code because the table doesn't have a is_custom column but its to demonstrate what I am trying to do.
public async Task<IEnumerable<Models.Action>> ExecuteAsync (IDbConnection conn, IDbTransaction transition, long userId)
{
string sql = @"SELECT a.id AS Id, a.is_custom = false AS IsCustom
FROM actions a
INNER JOIN members_actions ma ON a.id = ma.action_id AND is_custom = false
WHERE ma.member_id = :userId
UNION
SELECT a.id AS Id, a.is_custom = true AS IsCustom
FROM custom_actions a
INNER JOIN members_actions ma ON a.id = ma.action_id AND is_custom = true
WHERE ma.member_id = :userId;";
return await conn.QueryAsync<Models.Action> (sql, new {userId = userId}, transition);
}
Table 'Actions' Columns = id || description || name
Table 'Custom_actions' Columns = id || description || name || parameters
Is this possible? Its better than structurally changing the database (merging the 2 tables into 1 and adding an is_custom column).
Accepted Answer
You can just select the value true or false and use an alias to specify the the column name IsCustom
For example, I modified your example below to show how to do it (and also removed AND is_custom = false/true from the JOIN conditions because it didn't appear that there is an is_custom column in either table).
public async Task<IEnumerable<Models.Action>> ExecuteAsync (IDbConnection conn, IDbTransaction transition, long userId)
{
string sql = @"SELECT a.id AS Id, false AS IsCustom
FROM actions a
INNER JOIN members_actions ma ON a.id = ma.action_id
WHERE ma.member_id = :userId
UNION
SELECT a.id AS Id, true AS IsCustom
FROM custom_actions a
INNER JOIN members_actions ma ON a.id = ma.action_id
WHERE ma.member_id = :userId;";
return await conn.QueryAsync<Models.Action> (sql, new {userId = userId}, transition);
}