+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Imply bit with constant 1 or 0 in SQL Server (/Thread-Imply-bit-with-constant-1-or-0-in-SQL-Server)
Imply bit with constant 1 or 0 in SQL Server - itis684 - 07-31-2023
Is it possible to express 1 or 0 as a bit when used as a field value in a select statement?
e.g.
In this case statement (which is part of a select statement) ICourseBased is of type int.
case
when FC.CourseId is not null then 1
else 0
end
as IsCoursedBased
To get it to be a bit type I have to cast both values.
case
when FC.CourseId is not null then cast(1 as bit)
else cast(0 as bit)
end
as IsCoursedBased
Is there a short hand way of expressing the values as bit type without having to cast every time?
(I'm using MS SQL Server 2005)
RE: Imply bit with constant 1 or 0 in SQL Server - collettegkxeik - 07-31-2023
No, but you could cast the whole expression rather than the sub-components of that expression. Actually, that probably makes it *less* readable in this case.
RE: Imply bit with constant 1 or 0 in SQL Server - hazing574 - 07-31-2023
Unfortunately, no. You will have to cast each value individually.
RE: Imply bit with constant 1 or 0 in SQL Server - metricism877284 - 07-31-2023
You might add the second snippet as a field definition for ICourseBased in a view.
DECLARE VIEW MyView
AS
SELECT
case
when FC.CourseId is not null then cast(1 as bit)
else cast(0 as bit)
end
as IsCoursedBased
...
SELECT ICourseBased FROM MyView
RE: Imply bit with constant 1 or 0 in SQL Server - suvaneetwftdlyp - 07-31-2023
cast (
case
when FC.CourseId is not null then 1 else 0
end
as bit)
The CAST spec is "CAST (expression AS type)". The CASE is an *expression* in this context.
If you have multiple such expressions, I'd declare bit vars @true and @false and use them. Or use UDFs if you really wanted...
DECLARE @True bit, @False bit;
SELECT @True = 1, @False = 0; --can be combined with declare in SQL 2008
SELECT
case when FC.CourseId is not null then @True ELSE @False END AS ...
RE: Imply bit with constant 1 or 0 in SQL Server - Drbolter5 - 07-31-2023
Slightly more condensed than gbn's:
Assuming `CourseId` is non-zero
CAST (COALESCE(FC.CourseId, 0) AS Bit)
`COALESCE` is like an `ISNULL()`, but returns the first non-Null.
A Non-Zero `CourseId` will get type-cast to a 1, while a null `CourseId` will cause COALESCE to return the next value, 0
RE: Imply bit with constant 1 or 0 in SQL Server - scaler70 - 07-31-2023
If you want the column is BIT and NOT NULL, you should put ISNULL before the CAST.
ISNULL(
CAST (
CASE
WHEN FC.CourseId IS NOT NULL THEN 1 ELSE 0
END
AS BIT)
,0) AS IsCoursedBased
RE: Imply bit with constant 1 or 0 in SQL Server - treponemiasis118394 - 07-31-2023
The expression to use inside SELECT could be
CAST(IIF(FC.CourseId IS NOT NULL, 1, 0) AS BIT)
RE: Imply bit with constant 1 or 0 in SQL Server - wokas885348 - 07-31-2023
Tested and functional
SELECT fc.CourseId, IsCoursedBased = CAST(CASE WHEN fc.CourseId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM fc