anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:
> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I have a query that contains a sub query. The problem is that the sub query
> >returns more than 1 value so it fails with the error (Subquery returned more
> >than 1 value). Here is a simplified version of how it is setup.
> >
> >Select * from table1 where column2 = (select col2 from table2 where column 3
> >=4)
> >So for the example below it should return rows 1 and 3 from table 1 since a
> >and c were returned in the subquery.
> >
> >table 1
> >col1 col2
> >1 a
> >2 b
> >3 c
> >
> >table 2
> >col1 col2 col3
> >1 a 4
> >2 b 2
> >3 c 4
> >thanks.
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

Any SQL equivalent of InStrRev()?

In VB, there's a string function called InStrRev() which returns the
position of an occurrence of one string within another, from the end of
string.
I have a column containing data such as "/uploads/encrypted/design1.doc". I
want to be able to extract the pure filename from this column (e.g. returns
"design1.doc" from the above example).
In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
and then extract the rest of the string from that position.
Is there a similar way to do this in SQL script?
Thank you.DId you try something using CHARINDEX(REVERSE())?
SELECT CHARINDEX('x', 'foobarxgoo')
SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
"Bob" <noreply@.nospam.com> wrote in message
news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
> In VB, there's a string function called InStrRev() which returns the
> position of an occurrence of one string within another, from the end of
> string.
> I have a column containing data such as "/uploads/encrypted/design1.doc".
> I want to be able to extract the pure filename from this column (e.g.
> returns "design1.doc" from the above example).
> In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
> and then extract the rest of the string from that position.
> Is there a similar way to do this in SQL script?
> Thank you.
>|||Hi Aaron,

> SELECT CHARINDEX('x', 'foobarxgoo')
returns 7

> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
returns 4
The only way I can find this useful is if I REVERSE the string (i.e.
"/uploads/encrypted/design1.doc" to "cod.1ngised/detpyrcne/sdaolpu/"), then
find the first instance of a slash "/". Truncate the rest of the string.
That would get me "cod.1ngised". And finally, REVERSE again to get
"design1.doc".
But that sounds like a lot of calls and could slow down performance!
What do you think?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23CZHh883FHA.2196@.tk2msftngp13.phx.gbl...
> DId you try something using CHARINDEX(REVERSE())?
> SELECT CHARINDEX('x', 'foobarxgoo')
> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
>
> "Bob" <noreply@.nospam.com> wrote in message
> news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
>|||You can directly use the expression:
RIGHT( @.s, CHARINDEX( '/', REVERSE( @.s ) ) - 1 )
Obviously, any nested string manipulation functions done over a large
dataset is going to impact performance ( VB is no different either ). In
many cases it might be negligible. If it is a crucial issue, rather than
putting the entire path in a single column, consider using a separate column
for the file name.
Anith

Any function?

Hi, any function wich returns me if contains some string inside another
string for using on SP?
InStr?
ThanksCHARINDEX ,PATINDEX'
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi, any function wich returns me if contains some string inside another
> string for using on SP?
> InStr?
> Thanks
>|||if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
Should work?
Thanks man!
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
> CHARINDEX ,PATINDEX'
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi, any function wich returns me if contains some string inside another
>> string for using on SP?
>> InStr?
>> Thanks
>>
>|||Paulo
CREATE TABLE #tmp (c VARCHAR(1000))
GO
INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
FROM #tmp
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
> if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
> if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
> if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
> Should work?
> Thanks man!
> "Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
> news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> CHARINDEX ,PATINDEX'
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi, any function wich returns me if contains some string inside another
>> string for using on SP?
>> InStr?
>> Thanks
>>
>>
>|||Thanks man... very good !
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uqwDEZUeIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Paulo
> CREATE TABLE #tmp (c VARCHAR(1000))
> GO
> INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
> SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
> WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
> WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
> FROM #tmp
>
>
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
>> if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
>> if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
>> if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
>> Should work?
>> Thanks man!
>> "Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
>> news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> CHARINDEX ,PATINDEX'
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi, any function wich returns me if contains some string inside another
>> string for using on SP?
>> InStr?
>> Thanks
>>
>>
>>
>

Any function?

Hi, any function wich returns me if contains some string inside another
string for using on SP?
InStr?
Thanks
CHARINDEX ,PATINDEX?
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi, any function wich returns me if contains some string inside another
> string for using on SP?
> InStr?
> Thanks
>
|||if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
Should work?
Thanks man!
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
> CHARINDEX ,PATINDEX?
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>
|||Paulo
CREATE TABLE #tmp (c VARCHAR(1000))
GO
INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
FROM #tmp
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
> if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
> if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
> if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
> Should work?
> Thanks man!
> "Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
> news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks man... very good !
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uqwDEZUeIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Paulo
> CREATE TABLE #tmp (c VARCHAR(1000))
> GO
> INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
> SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
> WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
> WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
> FROM #tmp
>
>
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
>

Any alternative way to retreive data

Hi: Guys
Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.

CREATE TABLE [Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
RegionID] [int],
[AccountRepID] [int],
[SalesAmount] [money]
)

If the data were
salesid,regionid,accountrepid,salesamount
1,101,31,$50
2,101,32,$25
3,102,31,$25
4,102,32,$25
5,102,31,$15

The query should return
regionid,accountrepid
101,31
102,31 or 102,32

Is there another way to get the data other than the following query:

select regionID,accountrepid FROM Sales
where salesamount in
(select max(salesamount) FROM Sales group by regionid)

ThanksI don't think that will actually get you what you need. What if one region has the exact same salesamount as another region, but it's not the max for that region. You've then returned duplicate rows for that region.

Try this:

SELECT sa1.regionID, MAX(sa1.accountrepid)
FROM
Sales sa1
INNER JOIN (
SELECT regionID, MAX(salesamount) AS salesamount
FROM Sales) sa2 ON sa1.regionID = sa2.regionID
AND sa1.salesamount = sa2.salesamount|||select a.RegionId,a.AccountRepId,a.SalesAmount From Sales a
Inner join
(select RegionId,Max(salesAmount) as SalesAmount from Sales group by RegionId) b
on a.RegionId = b.RegionId and a.SalesAmount = b.SalesAmount

ANSI_NULL setting and null comparisons

I tried this experiment:
The database setting for ANSI_NULL is OFF. I set up two columns in a table
with null values. The following code returns my table:
SET ANSI_NULLS OFF
SELECT * FROM Exam WHERE TestNull1 = NULL
And this code does not:
SET ANSI_NULLS ON
SELECT * FROM Exam WHERE TestNull1 = NULL
But this codes won't return my table no matter what the ANSI_NULLS setting:
SELECT * FROM Exam WHERE TestNull1 = TestNull2
First question: Why doesn't the database setting determine the behavior in
the first two samples, instead of the setting I'm making in the query window?
Second question: Since both the fields are null, why aren't they considered
equal in the third sample? The documentation says:
"When OFF is specified, comparisons of non-UNICODE values to a null value
evaluate to TRUE if both values are NULL."
I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
letters, numbers, and symbols that SQL Server recognizes in the nchar,
nvarchar, and ntext data types." And I thought I got around that my defining
the two columns as char(10).Bev Kaufman,
I would suggest to move on and forget about setting it to OFF, and keep it
always set to ON. You should use IS [NOT] NULL instead, if you want you are
script to work as intended.
AMB
"Bev Kaufman" wrote:
> I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query window?
> Second question: Since both the fields are null, why aren't they considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my defining
> the two columns as char(10).
>|||In real life situations, I would always use IS [NOT] NULL. I'm just trying
to understand the database option settings, and I am puzzled when the setting
in the database properties has no effect on the behavior.
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||According to BOL, SET ANSI_NULLS OFF is deprecated and should be avoided.
The SET ANSI_NULLS OFF option is more or less a backwards-compatibility
feature going way back, and was probably originally designed for programmers
coming from other languages who couldn't understand the concept that NULL is
not equal to NULL.
NULLs are not equal to any other value, including other NULLs. Don't try to
use them in equality or comparison expressions; instead use IS NULL and IS
NOT NULL as Alejandro suggested. This will make your code that much easier
to upgrade to later versions of SQL Server, and easier for other developers
to maintain since they'll be able to reference the standard behavior of
NULLs in your code.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C1B0E1B0-5D1E-4D5F-BA61-5A57BFCA0528@.microsoft.com...
>I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a
> table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS
> setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query
> window?
> Second question: Since both the fields are null, why aren't they
> considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my
> defining
> the two columns as char(10).
>|||Many of the SET options are set on a connection-wide basis, so if your
connection specifies a different value for the setting or you set the value
differently during your connection, you will not override the default
database setting for that connection. BOL tells you which settings are
server-wide, database-wide or connection-wide.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Ooops, should read "you will override the default database setting for that
connection."
"Mike C#" <xyz@.xyz.com> wrote in message
news:eTHgtnmHIHA.280@.TK2MSFTNGP03.phx.gbl...
> Many of the SET options are set on a connection-wide basis, so if your
> connection specifies a different value for the setting or you set the
> value differently during your connection, you will not override the
> default database setting for that connection. BOL tells you which
> settings are server-wide, database-wide or connection-wide.
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
>> In real life situations, I would always use IS [NOT] NULL. I'm just
>> trying
>> to understand the database option settings, and I am puzzled when the
>> setting
>> in the database properties has no effect on the behavior.
>> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the
>> > behavior in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >
>|||Bev,
For the most part you should ignore the database settings for query options.
SET options for a connection override the database options, and almost all
client interfaces set values for most of the query options including
ANSI_NULLS. So it doesn't matter what you set it to at the db level, as soon
as you open a connection with Query Analyzer or Management Studio, your
connection will set its own setting. I wrote an article about this several
years ago for SQL Server Magazine. It's very confusing to someone just
reading about database options.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Well, I think there is some inconsistency in the T-SQL behavior as Bev
described regardless what the best practices may be. Note that the ANSI_NULLS
seeting was not set by Query Analyzer or its driver, but set in the script
immediately before each SELECT.
I don't know whether this inconsistency is a feature by design or not. But
it appears to be a bug to me.
Linchi
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||From BOL:
"SET ANSI_NULLS ON affects a comparison only if one of the operands of the
comparison is either a variable that is NULL or a literal NULL. If both
sides of the comparison are columns or compound expressions, the setting
does not affect the comparison."
http://msdn2.microsoft.com/en-us/library/ms188048.aspx
Whether or not SET ANSI_NULLS OFF is inconsistent in its behavior is
probably a moot point, since BOL also says this:
"This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
Don't use SET ANSI_NULLS OFF, and inconsistent (though documented) behavior
won't be an issue.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:08DB5BC1-4051-4B12-84B4-0A5D3F6A9634@.microsoft.com...
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the
> ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Linchi Shea,
As Mike stated, the comparison should be against the literal NULL. Setting
ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
about it, instead trying to understand it. Microsoft is thinking in
deprecating most of those settings, and I guess the future behavior will be
like having them ON.
NULL puzzle by Steve Kass
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
AMB
"Linchi Shea" wrote:
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
> > Bev Kaufman,
> >
> > I would suggest to move on and forget about setting it to OFF, and keep it
> > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > script to work as intended.
> >
> >
> > AMB
> >
> > "Bev Kaufman" wrote:
> >
> > > I tried this experiment:
> > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > with null values. The following code returns my table:
> > > SET ANSI_NULLS OFF
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > And this code does not:
> > > SET ANSI_NULLS ON
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > >
> > > First question: Why doesn't the database setting determine the behavior in
> > > the first two samples, instead of the setting I'm making in the query window?
> > >
> > > Second question: Since both the fields are null, why aren't they considered
> > > equal in the third sample? The documentation says:
> > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > evaluate to TRUE if both values are NULL."
> > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > the two columns as char(10).
> > >|||It's good that at least it's documented in BOL. I missed it. Any clue why
comparing two columns is treated differently?
Linchi
"Alejandro Mesa" wrote:
> Linchi Shea,
> As Mike stated, the comparison should be against the literal NULL. Setting
> ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
> about it, instead trying to understand it. Microsoft is thinking in
> deprecating most of those settings, and I guess the future behavior will be
> like having them ON.
> NULL puzzle by Steve Kass
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
> AMB
> "Linchi Shea" wrote:
> > Well, I think there is some inconsistency in the T-SQL behavior as Bev
> > described regardless what the best practices may be. Note that the ANSI_NULLS
> > seeting was not set by Query Analyzer or its driver, but set in the script
> > immediately before each SELECT.
> >
> > I don't know whether this inconsistency is a feature by design or not. But
> > it appears to be a bug to me.
> >
> > Linchi
> >
> > "Alejandro Mesa" wrote:
> >
> > > Bev Kaufman,
> > >
> > > I would suggest to move on and forget about setting it to OFF, and keep it
> > > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > > script to work as intended.
> > >
> > >
> > > AMB
> > >
> > > "Bev Kaufman" wrote:
> > >
> > > > I tried this experiment:
> > > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > > with null values. The following code returns my table:
> > > > SET ANSI_NULLS OFF
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > And this code does not:
> > > > SET ANSI_NULLS ON
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > > >
> > > > First question: Why doesn't the database setting determine the behavior in
> > > > the first two samples, instead of the setting I'm making in the query window?
> > > >
> > > > Second question: Since both the fields are null, why aren't they considered
> > > > equal in the third sample? The documentation says:
> > > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > > evaluate to TRUE if both values are NULL."
> > > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > > the two columns as char(10).
> > > >