September 12, 2010 at 8:40 am
Hmm. If anyone is wondering why 'Lennie' mentioned CASE before - it's because (s)he asked the same question on another site and has become confused as to who said what where.
It seems Lennie is actually asking how to use the IIF statement in SQL Server to 'decode' a bit column. The answer given on the other site was to use CASE. No example was provided there, so Lennie got a grump on.
Lennie, instead of:
Select SupplierID, SupplierName,
iif (Status = 1, 'Active', 'NotActive') as [Status]
from TblSupplier
Use:
SELECT SupplierID,
SupplierName,
[Status] =
CASE
WHEN [Status] = 1 THEN 'Active'
WHEN [Status] = 0 THEN 'NotActive'
ELSE NULL
END
FROM dbo.tblSupplier;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 12, 2010 at 8:44 am
Nice work Paul. That could certainly explain some of it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 12, 2010 at 9:09 am
just my 2ct:
Bit is context driven !
If your column name doesn't declare it by nature, you should document it well !
e.g. a column named "AreLightsOn" would by nature have a bit value 0 for NO (lights are not ON) and the other value (-1 or 1, depending on you sqlserver version) would be YES (lights are on)
A column named "Gender" would have to be documented which gender code would be used for male and which code for female.
Keep in mind a bit column can be NULL unless declared otherwise.
Up to 7 bit columns can be stored in a byte, but off course the first bit column will add a byte to your row length.
btw: There is no reason to shout at any forum !
In many cases if you don't get the answer you aimed for, or if the answers aren't what you would expect, chances are you malformed the question.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2010 at 9:49 am
GilaMonster (9/12/2010)
Lennie (9/11/2010)
GilaMonster (9/11/2010)
A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.😎
I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and
Does value 1 (one) means NO or FALSE ?
Up to you.
Intrinsically in SQL 0 means just 0 and 1 means just 1. It's a bit column, not a boolean. You can assign true/false meanings to the values as you like. It's not MS-Access where the boolean column has such meanings (0 true and 1 false)
Typically, if people do assign such logical meanings to the 1 and 0, 1 is true and 0 false, but it's totally up to you.
Your point about Access is why it is so important to understand that while, as Paul White says
You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0. (2005 and later only)
By convention, a bit value of 1 is associated with a boolean 'true', and 0 with a boolean 'false'. As Gail points out, this isn't enforced by SQL Server (except by implication as noted in the string assignments above), but it is extremely common.
Most people would see a bit value of 1 as implying 'true', 'on', 'yes' or some other equally 'positive' interpretation. A bit value of 0 is seen as implying 'false', 'off', or 'no'.
this won't always be the case. If you're dealing with a table that someone else designed and they were primarily an Access developer before creating this database or if the database was "upsized" to SQL Server from an Access database, then 0 could easily be "true" and 1 would be "false".
This could also be the case if the specs for the database comes from a picky programmer or a business person with no tech experience.
You also have to remember that a bit field can be declared as accepting NULL values. This can complicate matters if the original designer considers NULL a default position and instead of meaning "unknown" it could mean either "true" OR "false".
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 12, 2010 at 9:55 am
Lennie (9/11/2010)
GilaMonster (9/11/2010)
A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.😎
I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and
Does value 1 (one) means NO or FALSE ?
These are my question that I asked in my earlier posting. 😛
What do 0 and 1 mean? They mean whatever the database designer wanted them to represent. If the answers provided are not sufficient for you, then you should be asking the database designer, not us.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 12, 2010 at 3:14 pm
GilaMonster (9/12/2010)
I don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.
By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.
I am not the one tha created the SQL SERVER 2000 Table and DataType of BIT. It was created by the Database Administrator (DBA). If you have read the initial posting that I have posted then you will understand what I need as well as the other 5 co-worker programmers of the office.
September 12, 2010 at 3:20 pm
Lennie (9/12/2010)
GilaMonster (9/12/2010)
I don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.
By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.
I am not the one tha created the SQL SERVER 2000 Table and DataType of BIT. It was created by the Database Administrator (DBA). If you have read the initial posting that I have posted then you will understand what I need as well as the other 5 co-worker programmers of the office.
You need to ask the DBA what they represent! Only that DBA can answer!!!
What's your problem? Are you scared to ask the DBA?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 12, 2010 at 3:21 pm
Hi Paul
thank you very much for sharing the information with me. YOu are awesome and generous in sharing information.
In a normal SQL String (non stored procedures or Trans.SQL) how to use the IIF statement to determine.
Example:
strSql = "Select SupplierID, SupplierName "
strSql &= "IIF(ActiveStatus = 1, 'YES' , 'NO') as ActiveStatus "
strSqk &= " From TblSupplier "
September 12, 2010 at 3:25 pm
Alvin Ramard (9/12/2010)
Lennie (9/11/2010)
GilaMonster (9/11/2010)
A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.😎
I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and
Does value 1 (one) means NO or FALSE ?
These are my question that I asked in my earlier posting. 😛
What do 0 and 1 mean? They mean whatever the database designer wanted them to represent. If the answers provided are not sufficient for you, then you should be asking the database designer, not us.
I am posting my problem here to seek the expert help because the DataBase Administrator he himself is very confused and coulld not explain logically. That's why the problem is posted here for help. Once I got it working I will post the solution here for others like you who doesn't know about it.
September 12, 2010 at 3:54 pm
Lennie (9/12/2010)
strSql = "Select SupplierID, SupplierName "strSql &= "IIF(ActiveStatus = 1, 'YES' , 'NO') as ActiveStatus "
strSqk &= " From TblSupplier "
SQL Server does not have the IIF function, so you would need to write:
strSQL = "SELECT SupplierID, SupplierName "
strSQL &= "CASE WHEN ActiveStatus = 1 THEN 'YES' ELSE 'NO' END AS ActiveStatus "
strSQL &= "FROM TblSupplier "
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 12, 2010 at 4:25 pm
Hi Paul,
Yaaa...Hooooo...............you are awesome and wonderful in sharing information.
That's what I wanted......................Yeee...Haaa..........
I am so glad to meet you here. I really appreciate your help very much and this FORUM is wonderful to have you here..........Yaaa...Hooo
Thank you again,
Have a Good Day.
Cheers,
Lennie
September 12, 2010 at 6:11 pm
Hi Good Guys, :w00t:
I have found the solution to my non Stored Procedure or TRansactSQL SQL String. It working now on the VBNET2008 Form and it retrieve and display the information as requested by the Business Analyst. 😛
Here are the working coding that I want to share with Newbies who may have similar problems and also to Contributors of this AWESOME AND WONDERFUL FORM 😀
HERE ARE THE SQL CODING (SQL SERVER 2000)
Select SupplierID, SupplierName,
Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier
September 13, 2010 at 4:47 am
Lennie (9/12/2010)
GilaMonster (9/12/2010)
I don't understand what you want me to give you.When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.
By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.
I am not the one tha created the SQL SERVER 2000 Table and DataType of BIT. It was created by the Database Administrator (DBA). If you have read the initial posting that I have posted then you will understand what I need as well as the other 5 co-worker programmers of the office.
And you need to stop being so rude to someone who's trying to assist you. You didn't explain to Gail what you were looking for, nor did you post a link to your previous thread where you got confused. In fact, some other poster had to read both threads and point out the issue to everyone. Therefore, you don't have a right to scream at her for giving you the answer to the question you did ask.
We are not mind readers. We can only see the text you point on the screen. And your little "Thank you" shout does not in any way absolve you of how you treated one of the most respected members of this committee. If you can't remember a little thing called "manners," then you should perhaps consider not using forums at all.
And you really should apologize to Gail for treating her so harshly when she did actually answer your question, whether you agree with the answer or not.
September 13, 2010 at 11:43 am
Lennie (9/12/2010)
Hi Good Guys, :w00t:I have found the solution to my non Stored Procedure or TRansactSQL SQL String. It working now on the VBNET2008 Form and it retrieve and display the information as requested by the Business Analyst. 😛
Here are the working coding that I want to share with Newbies who may have similar problems and also to Contributors of this AWESOME AND WONDERFUL FORM 😀
HERE ARE THE SQL CODING (SQL SERVER 2000)
Select SupplierID, SupplierName,
Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier
BWAA-HAAA!!!! That won't work if Status = 1 is defined as "IsNotActive". 😛 And wait until you run into the 3rd value a BIT can have (or not). Please read about the BIT datatype in BOL (ie: Books On Line ... the free help software that comes with SQL Server.. You and your 5 team mates will understand so much more.
Like the others have been trying to tell you, the meaning of 0 or 1 is up to you. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2010 at 3:11 pm
Jeff,
I am surprise that you said the coding list below is not working.
Based on it the data export to Excel Spreadsheet display exactly what the Business Analyst Requested of 1 = Active and 0 = Inactive and the same data display on the VBNET2008 ListView is working very well too.
This is the working coding that I posted to share with others and you said it's not working and was surprised that you did not show sample coding to prove that it's not working. 😎
Select SupplierID, SupplierName,
Case Status when 1 Then 'Active' Else 'NotActive' as [Status]From TblSupplier
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply