SQL View does not accept Subclause

Question

I need to get a boolean field in a view from a string field in the table. The boolean field should be True if the string is filled:

SELECT (MasterId IS NOT NULL) AS HasMaster FROM entries

However, this causes the error:

incorrect syntax near the keyword 'IS'

Why is that and how to fix?

SQL SERVER doesn't have a boolean type. Try `CASE WHEN blah THEN 1 ELSE 0 END` — MatBailie, Jan 16 '18 at 08:20
T-SQL has boolean expressions, but those can never be used where a value is required, only in conditional clauses (`IF`, `WHERE`, `CASE`, etcetera). Yes, this is a defect in the language. — Jeroen Mostert, Jan 16 '18 at 08:21

sanatsathyan · Answer 1 · 2018-01-16T08:23:08.637

5

Try the following code:

SELECT CAST(CASE WHEN MasterId IS NULL THEN 0 ELSE 1 END AS BIT) [HasMaster]
FROM entries

edited Jan 16 '18 at 08:23

answered Jan 16 '18 at 08:21

sanatsathyan

Abdul Rasheed · Accepted Answer · 2018-01-16T08:28:04.937

1

Try this,

SELECT (CASE WHEN MasterId IS NOT NULL THEN 1 ELSE 0 END) AS HasMaster 
FROM  entries

There is no Boolean values in SQL Server, so you can use '0' and '1' in CASE expression here.

edited Jan 16 '18 at 08:28

answered Jan 16 '18 at 08:20

Abdul Rasheed

score -1 · Answer 3 · edited Jan 16 '18 at 08:37

-1

Question: I need to get a boolean field in a view from a string field in the table. The boolean field should be True if the string is filled

Answer: use case

select case when MasterId is null then 0 else 1 end
from table_name

edited Jan 16 '18 at 08:37

Martin Smith

answered Jan 16 '18 at 08:21

Wiki Panwar

Strictly speaking `0`and `1` are **not** boolean values - at least not in the SQL standard. But as SQL Server does not support boolean values this is a workaround. – Jan 16 '18 at 08:25
A tip, do not repeat the Question in the Answer, just the answer is enough. – Peter B Jan 16 '18 at 08:28
What relevance does the youtube link have? I assume this is yours and you are trying to spam it? – Martin Smith Jan 16 '18 at 08:36

3 Answers3