SQL [Conversion to bool]
25,032
Solution 1
Cast to bit?
SELECT Name,
CAST(
COALESCE(
(
SELECT TOP 1 0
FROM TbUserParam
WHERE TbUserParam.ID_User = @ID_User
AND TbUserParam.ID_Param = CfgListParIzm.ID_ListParIzm
), 1) as bit) Visi
FROM CfgListParIzm
WHERE CfgListParIzm.ID_ListGroupParIzm = @ID_ListGroupParIzm
But can you not rewrite the query thus to avoid the subquery:
SELECT
C.Name,
CAST(CASE WHEN T2.ID_Param IS NULL THEN 0 ELSE 1 END as bit) AS Visi
FROM
CfgListParIzm C
LEFT JOIN
(
SELECT
T.ID_Param
FROM
TbUserParam T
WHERE
T.ID_User = @ID_User
) T2 On T2.ID_Param = C.ID_ListParIzm
WHERE
C.ID_ListGroupParIzm = @ID_ListGroupParIzm
Solution 2
Maybe a CAST to BIT?
SELECT CAST(COALESCE(NULL,1) AS BIT)
UPDATE
You could use is ISNULL() as well.
SELECT CAST(ISNULL(NULL,1) AS BIT)
Author by
cnd
Updated on January 24, 2020Comments
-
cnd over 4 years
C++Builder ADOQuery SQLServer
I'm using a stored procedure with this select
SELECT Name, COALESCE( ( SELECT TOP 1 0 FROM TbUserParam WHERE TbUserParam.ID_User = @ID_User AND TbUserParam.ID_Param = CfgListParIzm.ID_ListParIzm ), 1) Visi FROM CfgListParIzm WHERE CfgListParIzm.ID_ListGroupParIzm = @ID_ListGroupParIzm
in my query with SQL string :
FlowClientHardQ :ID_User, :ID_ListGroupParIzm
then DataSource and DBGrid with CheckBox fields. Source
So I need a bool (or bit) field there from my ADO Query. How / Where can I convert it ?
Thank you.