Boolean to Bit?

IT seems to me that I can't write something like this:
DECLARE @.Exists bit
SELECT @.Exists=(UserId Is NOT NULL)
FROM User
WHERE FirstName="Some name" And LastNAme="Other"
I tries this and got an error. I am using Case to get around it. Just wanted
to know that I am correct. Is there other way doing the same thing?
Thanks,
Shimon.There is no boolean datatype in SQL Server. An alternative to a CASE
expression is an IF statement:
DECLARE @.Exists bit
IF EXISTS
(
SELECT *
FROM User
WHERE FirstName="Some name" AND
LastName="Other" AND
UserId IS NOT NULL
)
SET @.Exists = 1
ELSE
SET @.Exists = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Boolean expressions are valid in a WHERE clause, in CASE expressions
and in other places but can't be assigned directly to a variable
because there is no explicit boolean datatype in SQL Server. Use CASE
to convert a boolean expression to a valid datatype.
David Portas
SQL Server MVP
--|||You need a good SQL book.
1) The double quotes in Standard SQL are used to mark an identifier;
strings have always used single quotes.
2) There are no BOOLEAN or BIT data types in SQL. They would be a
total screw up. Why? Because machine level things like a BIT or BYTE
datatype have no place in a high level language like SQL. SQL is a
high level language; it is abstract and defined without regard to
PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
You have a major learning curve in front of you.
I usually tell peopel it takes at least a year of full time programming
to UN-learn their origianl language.|||
--CELKO-- wrote:

>You need a good SQL book.
>1) The double quotes in Standard SQL are used to mark an identifier;
>strings have always used single quotes.
>2) There are no BOOLEAN or BIT data types in SQL. They would be a
>total screw up. Why? Because machine level things like a BIT or BYTE
>datatype have no place in a high level language like SQL. SQL is a
>high level language; it is abstract and defined without regard to
>PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
You are mistaken. Both types appear in the ISO/IEC SQL standards.
BOOLEANs were introduced in SQL-99 (they correspond to PL/I's BIT(1) type)
and there was a BIT type in SQL-92 and early drafts of SQL-99, but it's gone
now, I believe.

>SQL is a high level language; it is abstract and defined without regard
>to PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
Well-said!

>Bits and Bytes are the <i>lowest<i> units of hardware-specific,
>physical implementation you can get. Are you on a high-end or low-end
>machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
>complement or ones complement math? Hey, the standards allow decimal
>machines, so bits do not exist at all! What about NULLs? To be a SQL
>datatype, you have to have NULLs, so what is a NULL bit? By definition
>a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
>how are the bit-wise operations defined? Oh what a tangled web we
>weave when first we mix logical and physical :)
>
There is no confusion in SQL Server. The confusion is yours, in
confounding the
abstract type [bit] of SQL Server with the use of the word bit in
describing hardware.
They aren't the same thing at all. SQL Server [bit] behavior is
consistent, documented and
fully hardware-independent. [bit] values are 0 and 1, and it's possible for
a bit column or variable to be NULL. All the operations valid for [bit]
values
are well-defined and documented. If you think the documentation gets
any of them
wrong, please let us know.

>What does the implementation of the host languages do with bits? Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
>VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
>proprietary languages from the same vendor. That means <i>all<i> the
>host languages -- present, future and not-yet-defined -- can be
>different. Surely, no good programmer would ever write non-portable
>code by getting to such a low level as bit fiddling!!
>
There is no BOOLEAN type in SQL Server, and there are libraries to help
anyone using T-SQL with other languages bridge the gulf between different
type systems, which are expected with any pair of languages.

>There are usually two situations in practice. Either the bits are
>individual attributes or they are used as a vector to represent a
>single attribute. In the case of a single attribute, the encoding is
>limited to two values, which do not port to host languages or other
>SQLs, cannot be easily understood by an end user, and which cannot be
>expanded.
>
The bit type is generally non-portable, although like many other
common features not part of the standard, like SIGN, bit is not hard
to port when needed. It might be good to avoid one too-proprietary quirk,
in my opinion, that CAST(x as bit) evaluates to 1 when x is a non-zero
value of any numeric type. If that is avoided, it should port well to a
system
without a matching type if it is converted to the smallest available
integer type.

>In the second case what some Newbies, who are still thinking in terms
>of second and third generation programming languages or even punch
>cards, do is build a vector for a series of "yes/no" status codes,
>failing to see the status vector as a single attribute. Did you ever
>play the children's game "20 Questions" when you were young?
>Imagine you have six components for a loan approval, so you allocate
>bits in your second generation model of the world. You have 64 possible
>vectors, but only 5 of them are valid (i.e. you cannot be rejected for
>bankruptcy and still have good credit). For your data integrity, you
>can:
>
This is an interesting problem that has nothing to do with the original
post in this thread. It also does not have a single out-of-context answer.

>1) Ignore the problem. This is actually what <i>most<i> newbies do.
>2) Write elaborate CHECK() constraints with user defined functions or
>proprietary bit level library functions that cannot port and that run
>like cold glue.
>Now we add a 7-th condition to the vector -- which end does it go on?
>Why? How did you get it in the right place on all the possible
>hardware that it will ever use? Did all the code that references a bit
>in a word by its position do it right after the change?
>
Fortunately, [bit], like all SQL Server types, are hardware-independent.
There is no need to worry about this. Columns are referred to by their
names, and the underlying implementation is not exposed to the user.

>You need to sit down and think about how to design an encoding of the
>data that is high level, general enough to expand, abstract and
>portable. For example, is that loan approval a hierarchical code?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!
>
>
It's preposterous and presumptions, in my opinion, to be telling the poster
how to design something irrelevant to the question asked.

>You have a major learning curve in front of you.
>I usually tell peopel it takes at least a year of full time programming
>to UN-learn their origianl language.
>
And it takes just a little bit of listening and research to divest
oneself of erroneous beliefs, like yours, that the SQL Server
[bit] type exposes hardware details and depends on
endianness and other nonsense. You have to be interested
in learning and growing up, that's all.
Steve Kass
Drew University|||Well, I don't know the answer to your specific question (and I'm sorry for
that), but I'm glad you posted it and I'm sure you will get an answer soon
as there are plenty of competent dbas in this group. In the mean time, I'd
like to take a moment to affirm you as an aspiring dba. Given the fact that
you are asking for help assures me that you are striving to become a better
person in at least one small way. And for that, you are in inspiration to
all of us and a huge asset to your employer. God bless, and have a great
day!"
Celko-2
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Joe --
I read through your customary belittling comments about Shimon's total lack
of
intelligence/skill/knowledge/etc. and something just jumped out at me --
You completely forgot the derogatory comment about the poster's command of t
he English
language!
From a previous Celko post --
Rows and records are TOTALLY different concepts. Since you did not use
plurals correctly in your English, can I assume you are Asian and
speak/write a language without them?
Now, because there were relatively few errors in Shimon's question (it was a
short post),
that may have kept the message below the acceptable threshhold for being com
mented on. If
not, this was a serious oversight on your part; one I feel needs to be recti
fied as soon
as possible (i.e., your next posting).
Thanks in advance,
Carl

Blank values Replicating to Oracle

Replicating from SQL Server 2005 to Oracle 9i Release 2. The source table has NOT NULL specified for all columns. The table contains multiple rows with blank values in one of the NOT NULL columns. When I attempt to replicate this table to Oracle, I'm unable to replicate the rows containing the blank values.

I've followed the directions in the Oracle Subscribers page in Books Online: http://msdn2.microsoft.com/en-us/library/ms151738.aspx

Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @.creation_script parameter of sp_addarticle

Here is the segment of the Publication Script referencing sp_addarticle:

exec sp_addarticle
@.publication = N'mike_hayes_test_replication2',
@.article = N'mike_hayes_test_replication',
@.source_owner = N'bmssa',
@.source_object = N'mike_hayes_test_replication',
@.type = N'logbased',
@.description = N'',
@.creation_script = 'CREATE TABLE [bmssa].[mike_hayes_test_replication2]( [EXCELCOLUMN] [int] NULL, [TABLENAME] [varchar](40) NULL, [FIELDNUM] [int] NULL , [REFTABLEID] [int] NULL , [MODULETYPE] [int] NULL , [DATAAREAID] [varchar](3) NULL, [RECVERSION] [int] NULL, [RECID] [int] NULL, [BRAND] [varchar](20) NULL )',
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00,
@.identityrangemanagementoption = N'manual',
@.destination_table = N'mike_hayes_test_replication2',
@.destination_owner = N'bmssa',
@.vertical_partition = N'false'
GO

I added the @.creation_script value, and changed the schema_option to 0x00.

The problem is that when replication occurs, the table that is created has NOT NULL specified for all columns. I expected that the @.creation_script would be executed, (as per the instructions), but that doesn't appear to be the case.

Any ideas?

Thanks for your help.

Mike Hayes

Hi Mike, you should specify the path to the file containing the create table statement for the @.creation_script parameter instead of just the "create table" statement.

Hope that helps,

-Raymond

|||Thanks for the reply. So this should be an Oracle script? Should I refer to it on the server (i.e. c:\scripts, where c:\ refers to the SQL Server drive)?

Thanks,

Mike Hayes|||

The syntax can arguably be anything compatible with Oracle although the safer thing to do is to just modify what the snapshot agent would have generated otherwise. The path should be something that is accessible(and\or relative) to the snapshot agent process since your file will then be copied by the snapshot agent to the snapshot folder. I am actually a bit surprised that the snapshot agent didn't die a horrible death trying to copy the "weird path" so something may be amiss here.

Come to think of it, would it be easier to just modify the script generated by the snapshot agent?

-Raymond

blank textbox is NOT null ..

This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
@.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:

> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
> @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>

blank textbox is NOT null ..

This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
@.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />
Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:

> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
> @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>

blank textbox is NOT null ..

This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode = @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode => @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>

blank parameter

what's the diff between 'allow null value' and 'allow blank value'?
if I have a parameter with 'allow null value' and 'allow blank value'
selected, does that mean that I can run the report without having to
enter a value for that parameter?
I have a report that has 3 fields:
1) region
2) store
3) choice
A ) If a user selects a region from region paratmeter drop down then
selects 'by region' in the choice parameter and leaves the store
parameter blank...it's supposed to pull a report that lists all trans
grouped by region
B) If a user selects a store location from store paratmeter drop down
then selects 'by store' in the choice parameter and leaves the region
parameter blank...it's supposed to pull a report that lists all trans
grouped by the stor
I am able to do this when I run the query in the data tab. But when I
try to do the same using the report parameters...the report will only
run if I have something selected in both the region and store
parameters. I have selected both 'allow null value' and 'allow blank
value' for all the parameters.
But I also noticed that even though I have to select both region and
store parameters, the report will display the data by region if I
select 'by region' in the choice parameter or by store if I select 'by
store' in the choice parameter.
So, the data is displayed properly but I keep to have the select a
value for all fields.
I hope someone can help me out.Hi,
Allow Null and Blank depends on your query to handle. yes reporting server
should not allow, "Allow blank" to get checked when the data type is string
and the values are coming from drop down.
To work around this problem.
you can possibily give a union in your data tab something like this.
select ' ' as abc from table1
union
select distinct abc from table2
so the blank wont be visible instead a (null) is inserted for you to select.
This can be a possible solution.
Amarnath
"bevarg" wrote:
> what's the diff between 'allow null value' and 'allow blank value'?
> if I have a parameter with 'allow null value' and 'allow blank value'
> selected, does that mean that I can run the report without having to
> enter a value for that parameter?
> I have a report that has 3 fields:
> 1) region
> 2) store
> 3) choice
>
> A ) If a user selects a region from region paratmeter drop down then
> selects 'by region' in the choice parameter and leaves the store
> parameter blank...it's supposed to pull a report that lists all trans
> grouped by region
>
> B) If a user selects a store location from store paratmeter drop down
> then selects 'by store' in the choice parameter and leaves the region
> parameter blank...it's supposed to pull a report that lists all trans
> grouped by the stor
>
> I am able to do this when I run the query in the data tab. But when I
> try to do the same using the report parameters...the report will only
> run if I have something selected in both the region and store
> parameters. I have selected both 'allow null value' and 'allow blank
> value' for all the parameters.
>
> But I also noticed that even though I have to select both region and
> store parameters, the report will display the data by region if I
> select 'by region' in the choice parameter or by store if I select 'by
> store' in the choice parameter.
>
> So, the data is displayed properly but I keep to have the select a
> value for all fields.
>
> I hope someone can help me out.
>

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''

Blank field in column

I am using the following query:
DELETE FROM ORDER_HEADER
WHERE (INVOICE_NUMBER IS NULL) OR (INVOICE_NUMBER = '0')
This works fine but doesn't remove the records where the
INVOICE_NUMBER field is blank.
Is there a way to identify a blank or empty field?
Hi,
Use the below query to delete invoice number with NULLS, 0's and Blanks:-
DELETE FROM ORDER_HEADER
WHERE (INVOICE_NUMBER IS NULL) OR (INVOICE_NUMBER = '0')
OR (DATALENGTH(LTRIM(RTRIM(INVOICE_NUMBER)))=0)
Thanks
Hari
MCDBA
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:238c201c45ef6$c0af5ba0$a601280a@.phx.gbl...
> I am using the following query:
> DELETE FROM ORDER_HEADER
> WHERE (INVOICE_NUMBER IS NULL) OR (INVOICE_NUMBER = '0')
> This works fine but doesn't remove the records where the
> INVOICE_NUMBER field is blank.
> Is there a way to identify a blank or empty field?

Bizarre case behavior.

I'm doing a select which includes the following:

case
when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
then ''
else
convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
',''),10))
end as QuarterStartDate

The COMMENT_4 field is a char(51), and contains values like: ' QTR:
03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
the convert to datetime within the case, data returns as expected,
retaining NULLs and blanks. If I leave the convert in, it converts
every row to datetime and seems to disregard the case expression.

I've attempted converting COMMENT_4 to a varchar first, and that didn't
help either. Also this does not seem to be affected by removal of the
LTRIM and RTRIM. Googling for this turns up no similar results. Have
I simply nested too far within the case, or what?

Any and all help and advice would be greatly appreciated.

Thanks!<twbanks@.gmail.com> wrote in message
news:1109020542.481788.195910@.c13g2000cwb.googlegr oups.com...
> I'm doing a select which includes the following:
> case
> when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
> then ''
> else
> convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
> ',''),10))
> end as QuarterStartDate
> The COMMENT_4 field is a char(51), and contains values like: ' QTR:
> 03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
> the convert to datetime within the case, data returns as expected,
> retaining NULLs and blanks. If I leave the convert in, it converts
> every row to datetime and seems to disregard the case expression.
> I've attempted converting COMMENT_4 to a varchar first, and that didn't
> help either. Also this does not seem to be affected by removal of the
> LTRIM and RTRIM. Googling for this turns up no similar results. Have
> I simply nested too far within the case, or what?
> Any and all help and advice would be greatly appreciated.
> Thanks!

A CASE expression can only return a single data type, which may be part of
your problem, but rather than guessing at exactly what your data looks like,
I suggest you provide a sample - if someone else can quickly copy and paste
some code into Query Analyzer, you are much more likely to get a useful
reply:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Thanks, Simon.

I wasn't thinking about that. Adding another convert to turn it into a
varchar did the trick.

Bit datatypes physical storage

Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).

Is there any good documentation about the physical storage layout for a data page?

Thanks -From SQL BOL: Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.

In general, avoid Nulls as much as you possibly can for two reasons: Data Integrity
Reduced Page Splits|||i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged

are you suggesting 6th normal form? because i have a real problem with that

and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity

or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)|||Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?

If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.|||Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.

How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.

I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.

But, I'll try for posterity.

Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).

For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.

Which is it? An offset table or an indicator? Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.

Hope this helps. I gotta Split.|||My point is if a value is expected, then don't allow Nulls.well, sure, if one knows what one's doing (although i would change the word "expected" to "required")

but that's a lot different than "avoid Nulls as much as you possibly can"

the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly

as far as all columns but PK being nullable, i myself like that idea :)

it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row

would save writing a trigger

and don't say you haven't seen an application where this is a valid business rule ;)|||I was gonna leave this alone, but then I'm a pedant.

The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.

If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.

Where ya gonna put 'em and how ya gonna get them if you just ignored them?

I have not seen a valid business for which this type of an application would rule. :p|||Avoid nulls as much as you can?

Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.

I'd classify that as bad data in a heartbeat.

Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."

There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?

Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.|||Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."Who's being anal-retentive?

Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.

I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.

I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.

I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.

I didn't say mislead others with default values. I said avoid Nulls as much as you can.

If a value is expected, then don't allow Nulls. That is reality.

That's my story and I'm sticking to it.

Avoid Nulls as much as you can.|||Man, I love this. MaxA, - you rule!!!!

But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me ;)

EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.|||Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.

How about this statement: "Avoid making a field required as much as you can."

I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.

I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.

I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.

If a value is not required, then allow Non-Nulls. That is reality.

That's my story and I'm sticking to it.

Avoid Non-Nulls as much as you can.|||Oh that's just plagiarism, Mr. Lindman, come up with your own style! ;)|||Plagiarism IS a style. Just look at Hollywood or the Grammy Awards! ;)

"Plagiarism is the sincerest form of flattery".
I forget who originally said that...|||Apparently Charles Caleb Colton:

http://www.quotationspage.com/quote/27484.html

I just don't think he has ever gotten enough credit for his work, do you?

Bit column with NULL

I have this table called Location. There is a column called IsInactive. The
value can contain a NULL,0, or 1.
Why does this statement work:
SELECT * FROM Location WHERE IsInactive <> 1
I have 3 location records where IsInactive = NULL and it doesn't return
them.
Just curious.
DavidNULL is not <> 1
This evaluates to Unknown.
Try:
SELECT * FROM Location WHERE IsInactive = 0 or IsInactive is null
Or:
SELECT * FROM Location WHERE isnull(IsInactive, 0) = 0
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:uUDKvbvAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> I have this table called Location. There is a column called IsInactive.
The
> value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't return
> them.
> Just curious.
> David
>|||Hello David,
This depends on your ANSI_NULL settings.
If you have ANSI_NULL on, anytime you compare something to a NULL it will
fail. This means that if the value is NULL and you want all rows that <>
1, then it won't be returned.
For more information read up on SET ANSI_NULLS in BOL.
http://msdn.microsoft.com/library/d... />
t_7g32.asp
Aaron Weiker
http://aaronweiker.com/

> I have this table called Location. There is a column called
> IsInactive. The value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't
> return them.
> Just curious.
> David
>|||NULL is basically unknown or not yet filled in...
So the systems response to anything >, =, < or != is never going to respond
with the TRUE.
to get your statement to work try...
SELECT * FROM Location WHERE isnull(IsInactive, 0) <> 1
look up isnull in books on-line (BOL) for more specific information.
Ryan
"David Pope" wrote:

> I have this table called Location. There is a column called IsInactive. Th
e
> value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't return
> them.
> Just curious.
> David
>
>|||1) NULLs do not compare to anything. This is absolute foundations, not
fancy stuff. You should know the rules for NULL before you ever wrote
any code.
2) A proper data model does not use flags; that is assembly language
programming. There are no BOOLEAN types in SQL-92 for a good reason,
having to do with NULLs and 3VL.
For example, your data might want to store the date range that
something a location was active.
Oh, unless you have only one of them, the table name ought to be
"Locations" -- it is set and not a scalar.|||
--CELKO-- wrote:

>1) NULLs do not compare to anything. This is absolute foundations, not
>fancy stuff. You should know the rules for NULL before you ever wrote
>any code.
>2) A proper data model does not use flags; that is assembly language
>programming. There are no BOOLEAN types in SQL-92 for a good reason,
>having to do with NULLs and 3VL.
>
There is a BOOLEAN type in SQL-99. That's not to say we'd ever get
used to a type where NOT(Unknown) is Unknown.
Steve Kass
Drew University

>For example, your data might want to store the date range that
>something a location was active.
>Oh, unless you have only one of them, the table name ought to be
>"Locations" -- it is set and not a scalar.
>
>|||I will tell the developers at Olero Software. =0}
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1106751641.483267.271940@.c13g2000cwb.googlegroups.com...
> 1) NULLs do not compare to anything. This is absolute foundations, not
> fancy stuff. You should know the rules for NULL before you ever wrote
> any code.
> 2) A proper data model does not use flags; that is assembly language
> programming. There are no BOOLEAN types in SQL-92 for a good reason,
> having to do with NULLs and 3VL.
> For example, your data might want to store the date range that
> something a location was active.
> Oh, unless you have only one of them, the table name ought to be
> "Locations" -- it is set and not a scalar.
>