I have a column in my database I want to be a computed flag based on an active date.. the Flag column is called active..
basicall I want if the ActiveDate <= getdate() (as in today is after the item was active) then the active flag is a bit 1, else its a bit 0... I tried this as a flat out <= statement, got an error by SQL Managment studio... so how would this be done? thanks!
>I have a column in my database I want to be a computed flag based on an >active date.. the Flag column is called active..
> basicall I want if the ActiveDate <= getdate() (as in today is after the > item was active) then the active flag is a bit 1, else its a bit 0... I > tried this as a flat out <= statement, got an error by SQL Managment > studio... so how would this be done? thanks!
>> I have a column in my database I want to be a computed flag based on an active date.. the Flag column is called active.. basically I want if the ActiveDate <= getdate() (as in today is after the
item was active) then the active flag is a bit 1, else its a bit 0... I tried this as a flat out <= statement, got an error by SQL Management studio... so how would this be done? <<
This would be done by forgetting all the basic rules for programming in SQL :)
1) We do not use the proprietary BIT data type that does not exist in Standard SQL
2) Flags are for assembly language programming and have no place in RDBMS; that is why SQL has no BOOLEAN data types
3) We do not store computed columns in a table. Write a VIEW and learn to use CURRENT_TIMESTAMP instead of the old proprietary getdate() function call to get today's active data.
A lot of stuff depends on flags true or false, why would I want to spend 32bits of bandwidth to return a 1 for true if I can do it in 1 bit... as for other SQL why would I care about that right now either if I am targeting my program at SQL server only?
"--CELKO--" <jcelko...@earthlink.net> wrote in message
>>> I have a column in my database I want to be a computed flag based on an >>> active date.. the Flag column is called active.. basically I want if >>> the ActiveDate <= getdate() (as in today is after the > item was active) then the active flag is a bit 1, else its a bit 0... > I tried this as a flat out <= statement, got an error by SQL > Management studio... so how would this be done? <<
> This would be done by forgetting all the basic rules for programming > in SQL :)
> 1) We do not use the proprietary BIT data type that does not exist in > Standard SQL
> 2) Flags are for assembly language programming and have no place in > RDBMS; that is why SQL has no BOOLEAN data types
> 3) We do not store computed columns in a table. Write a VIEW and > learn to use CURRENT_TIMESTAMP instead of the old proprietary > getdate() function call to get today's active data.
>> A lot of stuff depends on flags true or false, why would I want to spend 32 bits of bandwidth to return a 1 for true if I can do it in 1 bit...<<
Why a +1 and not a -1? Have you compared VB and C# on this issue? Or any of the X3J languages? Since SQL uses 3VL, where is your UNKNOWN and how do you move it to an application.
In the RDBMS world, true and false are at a "higher level" than the data -- they refer to predicates about that data and are not part of it.
An RDBMS person designs data, not just queries. You will quickly find that you need a status code that can expand as "yes/no" becomes "yes/ no/not answered/ impossible because of other answers/ etc." Or you have one attribute split into "20-questions" with a ton of constraints to try and co-ordinate them.
>> as for other SQL why would I care about that right now either if I am targeting my program at SQL server only? <<
Professionalism? Maintainable and readable code? "Future Proofing"? That last one is from Ken Henderson; it means we known that SQL Server will always be moving toward Standards and not away from them, so let's avoid problems before they hit us. For example, the bad programmers who used BIT got caught when the data type went from (0/1) to (0/1/NULL) as an exact numeric data type.
What is a bit data type in SQL Server to be used for then? It has to be there for some reason... Even MS uses the bit type to store true and false and even encourages it in the .NET SDK with ADO.NET...
"--CELKO--" <jcelko...@earthlink.net> wrote in message
>>> A lot of stuff depends on flags true or false, why would I want to spend >>> 32 bits of bandwidth to return a 1 for true if I can do it in 1 bit...<<
> Why a +1 and not a -1? Have you compared VB and C# on this issue? Or > any of the X3J languages? Since SQL uses 3VL, where is your UNKNOWN > and how do you move it to an application.
> In the RDBMS world, true and false are at a "higher level" than the > data -- they refer to predicates about that data and are not part of > it.
> An RDBMS person designs data, not just queries. You will quickly find > that you need a status code that can expand as "yes/no" becomes "yes/ > no/not answered/ impossible because of other answers/ etc." Or you > have one attribute split into "20-questions" with a ton of constraints > to try and co-ordinate them.
>>> as for other SQL why would I care about that right now either if I am >>> targeting my program at SQL server only? <<
> Professionalism? Maintainable and readable code? "Future Proofing"? > That last one is from Ken Henderson; it means we known that SQL Server > will always be moving toward Standards and not away from them, so > let's avoid problems before they hit us. For example, the bad > programmers who used BIT got caught when the data type went from (0/1) > to (0/1/NULL) as an exact numeric data type.
>>> A lot of stuff depends on flags true or false, why would I want to spend >>> 32 bits of bandwidth to return a 1 for true if I can do it in 1 bit...<<
> Why a +1 and not a -1? Have you compared VB and C# on this issue? Or > any of the X3J languages? Since SQL uses 3VL, where is your UNKNOWN > and how do you move it to an application.
> In the RDBMS world, true and false are at a "higher level" than the > data -- they refer to predicates about that data and are not part of > it.
> An RDBMS person designs data, not just queries. You will quickly find > that you need a status code that can expand as "yes/no" becomes "yes/ > no/not answered/ impossible because of other answers/ etc." Or you > have one attribute split into "20-questions" with a ton of constraints > to try and co-ordinate them.
>>> as for other SQL why would I care about that right now either if I am >>> targeting my program at SQL server only? <<
> Professionalism? Maintainable and readable code? "Future Proofing"? > That last one is from Ken Henderson; it means we known that SQL Server > will always be moving toward Standards and not away from them, so > let's avoid problems before they hit us. For example, the bad > programmers who used BIT got caught when the data type went from (0/1) > to (0/1/NULL) as an exact numeric data type.
>> What is a bit data type in SQL Server to be used for then? It has to be there for some reason... Even MS uses the bit type to store true and false and even encourages it in the .NET SDK with ADO.NET... <<
It is there for historial reasons. Sybase/SQL Server grew up before the Standards and was written for 16 bit hardware on a UNIX platform decades ago.
The main purpose today, now that we understand RDBMS better, is to lock you into their products.
I do not care about .NET and ADO, or other proprietary front ends for applications. I am the database guy and I write an RDBMS that can work with any front end.
On Jul 13, 4:39 am, --CELKO-- <jcelko...@earthlink.net> wrote: [snip]
> I do not care about .NET and ADO, or other proprietary front ends for > applications. I am the database guy and I write an RDBMS that can > work with any front end.
So why do you continually harp on about the (no longer existent) differences in interpretation of bit/boolean by VB and C#? Surely you shouldn't be worrying about such issues?
|
