Is this a BUG

SQL Server 2005 SP2.
The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
2000 or 2005. The first column (cust_code) displays correct in all the
versions but the second column (related_info2 - That has all the concatenated
stuff) displays different. In 7.0, it brings all the values and if the column
has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
the rest of the columns data.
In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
NULL value.
Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
select customer.cust_code,
customer.bus_name
+ ' '
+ customer.address3
+ ' Direct: '
+ customer.phone_direct
+ ' 800: '
+ customer.phone_800
related_info2
from customer
order by customer.cust_code
and here is the DDL:
CREATE TABLE [dbo].[customer] (
[cust_code] [int] NOT NULL ,
[bus_name] [varchar] (40) NULL ,
[address3] [varchar] (30) NULL ,
[phone_800] [varchar] (30) NULL ,
[phone_direct] [varchar] (30) NULL
) ON [PRIMARY]
GO
Thanks.Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see ALTER
DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest you
adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance you
can use ISNULL for each nullable column and convert to empty string if value is NULL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
> SQL Server 2005 SP2.
> The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
> 2000 or 2005. The first column (cust_code) displays correct in all the
> versions but the second column (related_info2 - That has all the concatenated
> stuff) displays different. In 7.0, it brings all the values and if the column
> has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
> the rest of the columns data.
> In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
> NULL value.
> Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
>
> select customer.cust_code,
> customer.bus_name
> + ' '
> + customer.address3
> + ' Direct: '
> + customer.phone_direct
> + ' 800: '
> + customer.phone_800
> related_info2
> from customer
> order by customer.cust_code
> and here is the DDL:
> CREATE TABLE [dbo].[customer] (
> [cust_code] [int] NOT NULL ,
> [bus_name] [varchar] (40) NULL ,
> [address3] [varchar] (30) NULL ,
> [phone_800] [varchar] (30) NULL ,
> [phone_direct] [varchar] (30) NULL
> ) ON [PRIMARY]
> GO
>
> Thanks.|||No, this is not a bug. Take a look at the CONCAT_NULL_YIELDS_NULL option in
Books Online. Probably it is set OFF on your SQL Server 7 database, or the
client API that connects sets it to off. You can check it like this:
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')
On SQL Server 2000 & 2005 it is ON by default, and most new client libraries
will set it to ON, which overrides the database settings.
You can easily get the same result by using COALESCE or ISNULL:
SELECT customer.cust_code,
COALESCE(customer.bus_name, '')
+ ' '
+ COALESCE(customer.address3, '')
+ ' Direct: '
+ COALESCE(customer.phone_direct, '')
+ ' 800: '
+ COALESCE(customer.phone_800, '')
AS related_info2
FROM customer
ORDER BY customer.cust_code
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
"Tibor Karaszi" wrote:
> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see ALTER
> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest you
> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance you
> can use ISNULL for each nullable column and convert to empty string if value is NULL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
> > SQL Server 2005 SP2.
> >
> > The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
> > 2000 or 2005. The first column (cust_code) displays correct in all the
> > versions but the second column (related_info2 - That has all the concatenated
> > stuff) displays different. In 7.0, it brings all the values and if the column
> > has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
> > the rest of the columns data.
> >
> > In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
> > NULL value.
> >
> > Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
> >
> >
> > select customer.cust_code,
> > customer.bus_name
> > + ' '
> > + customer.address3
> > + ' Direct: '
> > + customer.phone_direct
> > + ' 800: '
> > + customer.phone_800
> > related_info2
> > from customer
> > order by customer.cust_code
> >
> > and here is the DDL:
> >
> > CREATE TABLE [dbo].[customer] (
> > [cust_code] [int] NOT NULL ,
> > [bus_name] [varchar] (40) NULL ,
> > [address3] [varchar] (30) NULL ,
> > [phone_800] [varchar] (30) NULL ,
> > [phone_direct] [varchar] (30) NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Thanks.
>|||> Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
You can't (assuming you refer to the session setting) since this isn't a sticky option (like SET
ANSI_NULLS). So, either use the database option or adjust your code to work regardless.
> COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
You lost me there. Can you explain with an example?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8D646949-DFA0-43FA-9AEA-B2D558AFC71C@.microsoft.com...
> Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
> COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
>
> "Tibor Karaszi" wrote:
>> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see
>> ALTER
>> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest
>> you
>> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance
>> you
>> can use ISNULL for each nullable column and convert to empty string if value is NULL.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
>> > SQL Server 2005 SP2.
>> >
>> > The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
>> > 2000 or 2005. The first column (cust_code) displays correct in all the
>> > versions but the second column (related_info2 - That has all the concatenated
>> > stuff) displays different. In 7.0, it brings all the values and if the column
>> > has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
>> > the rest of the columns data.
>> >
>> > In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
>> > NULL value.
>> >
>> > Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
>> >
>> >
>> > select customer.cust_code,
>> > customer.bus_name
>> > + ' '
>> > + customer.address3
>> > + ' Direct: '
>> > + customer.phone_direct
>> > + ' 800: '
>> > + customer.phone_800
>> > related_info2
>> > from customer
>> > order by customer.cust_code
>> >
>> > and here is the DDL:
>> >
>> > CREATE TABLE [dbo].[customer] (
>> > [cust_code] [int] NOT NULL ,
>> > [bus_name] [varchar] (40) NULL ,
>> > [address3] [varchar] (30) NULL ,
>> > [phone_800] [varchar] (30) NULL ,
>> > [phone_direct] [varchar] (30) NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> >
>> > Thanks.|||My bet.........Sorry..........COALESCE works. I just forgot to apply it
to whole fields in the select statement and not only the sub select.
Thanks again Tibor & Plamen.
"Tibor Karaszi" wrote:
> > Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
> You can't (assuming you refer to the session setting) since this isn't a sticky option (like SET
> ANSI_NULLS). So, either use the database option or adjust your code to work regardless.
>
> > COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
> You lost me there. Can you explain with an example?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8D646949-DFA0-43FA-9AEA-B2D558AFC71C@.microsoft.com...
> > Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
> >
> > COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see
> >> ALTER
> >> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest
> >> you
> >> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance
> >> you
> >> can use ISNULL for each nullable column and convert to empty string if value is NULL.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
> >> > SQL Server 2005 SP2.
> >> >
> >> > The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
> >> > 2000 or 2005. The first column (cust_code) displays correct in all the
> >> > versions but the second column (related_info2 - That has all the concatenated
> >> > stuff) displays different. In 7.0, it brings all the values and if the column
> >> > has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
> >> > the rest of the columns data.
> >> >
> >> > In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
> >> > NULL value.
> >> >
> >> > Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
> >> >
> >> >
> >> > select customer.cust_code,
> >> > customer.bus_name
> >> > + ' '
> >> > + customer.address3
> >> > + ' Direct: '
> >> > + customer.phone_direct
> >> > + ' 800: '
> >> > + customer.phone_800
> >> > related_info2
> >> > from customer
> >> > order by customer.cust_code
> >> >
> >> > and here is the DDL:
> >> >
> >> > CREATE TABLE [dbo].[customer] (
> >> > [cust_code] [int] NOT NULL ,
> >> > [bus_name] [varchar] (40) NULL ,
> >> > [address3] [varchar] (30) NULL ,
> >> > [phone_800] [varchar] (30) NULL ,
> >> > [phone_direct] [varchar] (30) NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> >
> >> > Thanks.
> >>
>