View column that checks if table's int column is null, enters a boolean value in the view column instead of the int value

Question

Post reply

View column that checks if table's int column is null, enters a boolean value in the view column instead of the int value

php_guy

SSC-Addicted

Points: 475
More actions
October 29, 2014 at 6:29 am

#397578

Have a view that I need to add a column to which checks the value of another table's column which is an int value. If that value is null and not an int I need the view's column to be a boolean and not the int value of the column it is checking. how can I do this? In Access it was a query which contained a switch function, which does not seem to transfer over to SQL

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ISNULL (IntegerColumnName, ReplacementValue)

The data type returned will be the 'larger' of INT and whatever the replacement value is.

btw, SQL doesn't have booleans. It has the bit data type which is a numeric data type that stores 1, 0 or NULL. Interpretation of those values is up to the front end app

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

php_guy SSC-Addicted Points: 475 More actions · Answer 2

I have been trying that ISNULL logic, which is what I had in my Access DB Query which basically checks if there is a null value and places a zero in the field, and if not then it places a 1. I tried to use the same ISNULL statement I had and put it in the "filter" section of the SQL view, and then put a 1 in the "or" section. but it didnt work. Am I putting this logic in the correct place?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

php_guy (10/29/2014)
Have a view that I need to add a column to which checks the value of another table's column which is an int value. If that value is null and not an int I need the view's column to be a boolean and not the int value of the column it is checking. how can I do this? In Access it was a query which contained a switch function, which does not seem to transfer over to SQL

A column can only have 1 data type. If you need this Boolean value, it would have to be a separate column in the view, otherwise you won't be able to determine between the int value 1 and the bit value 1 representing TRUE.

The calculation for this new column would be something like:

CAST(CASE WHEN OtherColumn IS NULL THEN 1 ELSE 0 END AS bit) AS IsOtherColumnNull

ScottPletcher SSC Guru Points: 101192 More actions · Answer 4

GilaMonster (10/29/2014)
ISNULL (IntegerColumnName, ReplacementValue)
The data type returned will be the 'larger' of INT and whatever the replacement value is.
btw, SQL doesn't have booleans. It has the bit data type which is a numeric data type that stores 1, 0 or NULL. Interpretation of those values is up to the front end app

Actually, ISNULL() will return the first data type. COALESCE() will pick the "largest" data type. For example, try this statement:

SELECT ISNULL(CAST(NULL AS bit), CAST(3 AS int))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

php_guy SSC-Addicted Points: 475 More actions · Answer 5

I like the idea about using a separate column to use for computing the bit value, but still I am not sure how to implement this. Main question is if I put this logic in the "Filter" section when in design mode in SQL server management studio? I could alter the SQL statement but I would need a bit moe of a hint as how to edit my current sql statement to include this new idea.

here is what I currently have.

SELECT table1.column1, table2.column1 as SomeRandomColumnName, BitValueColumnName

FROM table2 INNER JOIN

table1 ON table2.column1 = table2.column2

now where can I put the logic for the "BitValueColumnName" so that it can look at table2.column1 and check if it is null and enter a 0, and if it is not null it will enter a 1?

php_guy SSC-Addicted Points: 475 More actions · Answer 6

Here is my revised SQL statement. Still not working but I thought I would post it and maybe someone can spot what I am doing wrong

SELECT table1.column1, table2.column1 as SomeRandomColumnName, CAST(CASE WHEN table2.column1 IS NULL THEN 1 ELSE 0 END AS bit)

AS BitValueColumnName

FROM table2 INNER JOIN

table1 ON table2.column1 = table2.column2

David Burrows SSC Guru Points: 65144 More actions · Answer 7

Just a guess, try using a LEFT JOIN?

SELECT table1.column1, table2.column1 as SomeRandomColumnName, CAST(CASE WHEN table2.column1 IS NULL THEN 1 ELSE 0 END AS bit)

AS BitValueColumnName

FROM table2 LEFT JOIN

table1 ON table2.column1 = table2.column2

Far away is close at hand in the images of elsewhere.
Anon.

php_guy SSC-Addicted Points: 475 More actions · Answer 8

Thank you, that worked. thanks everyone for your help on this.

View column that checks if table's int column is null, enters a boolean value in the view column instead of the int value

Cookies on SQLServerCentral