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).
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);
}