adding two view columns together

I have created a new view where I joined two other views I want to use in
reporting services. I want to add two columns one from each view. The join
allows all records from both views. This creates a lot of null values in one
of the columns I want to add and therefore I get a null value as the answer
instead of the desired result of adding the two columns. Is there a way to
put zeros in place of the nulls within the select statement so that all
records will add ?
Ex: Col1 + Col2 = Col3
2 3 5
null 2 null (desired answer is 2)
I have been able to do this by using a temporary table but that makes things
more complicated.
Thank you for your help.Use the SQL statement IsNull. From books online:
USE AdventureWorks;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max
Quantity'
FROM Sales.SpecialOffer;
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JD" <jim@.microsoft.com> wrote in message
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>|||You can also use in reporting services in your Col3 the following expression
=cint(Fields!Col1) + cint(Fields!Col2)
and of course, you can use cint function in your Col1 and Col2 columns.
Hope this helps,
Mónica
"JD" <jim@.microsoft.com> escribió en el mensaje
news:emMfrtvKIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have created a new view where I joined two other views I want to use in
>reporting services. I want to add two columns one from each view. The join
>allows all records from both views. This creates a lot of null values in
>one of the columns I want to add and therefore I get a null value as the
>answer instead of the desired result of adding the two columns. Is there a
>way to put zeros in place of the nulls within the select statement so that
>all records will add ?
> Ex: Col1 + Col2 = Col3
> 2 3 5
> null 2 null (desired answer is 2)
> I have been able to do this by using a temporary table but that makes
> things more complicated.
> Thank you for your help.
>

Adding Read Uncommited to Views

Hi,
Wanted to throw out a question to you SQL experts. I have 3149 views i need
to add READ UNCOMMITED to.
They are currently scripted out waiting to be run on my replicated database.
My question is where do i put this statement?
I tried adding it after the AS like in a stored procedure, but SQL doesn't
like it. I haven't seen any documentation on this at all, even on this board..
..
HELP PLEASE...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VW_AR_PostingExtractValidate
You can't specify the transaction isolation level in the view definition.
You need to specify the WITH (NOLOCK) hint instead.
I suggest you do analysis before making wholesale changes to force
uncommitted reads. This is especially true for financial applications.
Data integrity is not guaranteed when you read uncommitted data.
Hope this helps.
Dan Guzman
SQL Server MVP
"sqlclubber" <u19392@.uwe> wrote in message news:5cdfe94eec857@.uwe...
> Hi,
> Wanted to throw out a question to you SQL experts. I have 3149 views i
> need
> to add READ UNCOMMITED to.
> They are currently scripted out waiting to be run on my replicated
> database.
> My question is where do i put this statement?
> I tried adding it after the AS like in a stored procedure, but SQL doesn't
> like it. I haven't seen any documentation on this at all, even on this
> board..
> .
> HELP PLEASE...
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VW_AR_PostingExtractValidate
|||Thanks for your reply, Now i got to do a ctrl V 10 thousand times.
: (
Dan Guzman wrote:[vbcol=seagreen]
>You can't specify the transaction isolation level in the view definition.
>You need to specify the WITH (NOLOCK) hint instead.
>I suggest you do analysis before making wholesale changes to force
>uncommitted reads. This is especially true for financial applications.
>Data integrity is not guaranteed when you read uncommitted data.
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1

Adding Read Uncommited to Views

Hi,
Wanted to throw out a question to you SQL experts. I have 3149 views i need
to add READ UNCOMMITED to.
They are currently scripted out waiting to be run on my replicated database.
My question is where do i put this statement?
I tried adding it after the AS like in a stored procedure, but SQL doesn't
like it. I haven't seen any documentation on this at all, even on this board..
.
HELP PLEASE...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VW_AR_PostingExtractValidateYou can't specify the transaction isolation level in the view definition.
You need to specify the WITH (NOLOCK) hint instead.
I suggest you do analysis before making wholesale changes to force
uncommitted reads. This is especially true for financial applications.
Data integrity is not guaranteed when you read uncommitted data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sqlclubber" <u19392@.uwe> wrote in message news:5cdfe94eec857@.uwe...
> Hi,
> Wanted to throw out a question to you SQL experts. I have 3149 views i
> need
> to add READ UNCOMMITED to.
> They are currently scripted out waiting to be run on my replicated
> database.
> My question is where do i put this statement?
> I tried adding it after the AS like in a stored procedure, but SQL doesn't
> like it. I haven't seen any documentation on this at all, even on this
> board..
> .
> HELP PLEASE...
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VW_AR_PostingExtractValidate|||Thanks for your reply, Now i got to do a ctrl V 10 thousand times.
: (
Dan Guzman wrote:
>You can't specify the transaction isolation level in the view definition.
>You need to specify the WITH (NOLOCK) hint instead.
>I suggest you do analysis before making wholesale changes to force
>uncommitted reads. This is especially true for financial applications.
>Data integrity is not guaranteed when you read uncommitted data.
>> Hi,
>> Wanted to throw out a question to you SQL experts. I have 3149 views i
>[quoted text clipped - 17 lines]
>> CREATE VIEW dbo.VW_AR_PostingExtractValidate
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1

Adding Read Uncommited to Views

Hi,
Wanted to throw out a question to you SQL experts. I have 3149 views i need
to add READ UNCOMMITED to.
They are currently scripted out waiting to be run on my replicated database.
My question is where do i put this statement?
I tried adding it after the AS like in a stored procedure, but SQL doesn't
like it. I haven't seen any documentation on this at all, even on this board
.
.
HELP PLEASE...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VW_AR_PostingExtractValidateYou can't specify the transaction isolation level in the view definition.
You need to specify the WITH (NOLOCK) hint instead.
I suggest you do analysis before making wholesale changes to force
uncommitted reads. This is especially true for financial applications.
Data integrity is not guaranteed when you read uncommitted data.
Hope this helps.
Dan Guzman
SQL Server MVP
"sqlclubber" <u19392@.uwe> wrote in message news:5cdfe94eec857@.uwe...
> Hi,
> Wanted to throw out a question to you SQL experts. I have 3149 views i
> need
> to add READ UNCOMMITED to.
> They are currently scripted out waiting to be run on my replicated
> database.
> My question is where do i put this statement?
> I tried adding it after the AS like in a stored procedure, but SQL doesn't
> like it. I haven't seen any documentation on this at all, even on this
> board..
> .
> HELP PLEASE...
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VW_AR_PostingExtractValidate|||Thanks for your reply, Now i got to do a ctrl V 10 thousand times.
: (
Dan Guzman wrote:[vbcol=seagreen]
>You can't specify the transaction isolation level in the view definition.
>You need to specify the WITH (NOLOCK) hint instead.
>I suggest you do analysis before making wholesale changes to force
>uncommitted reads. This is especially true for financial applications.
>Data integrity is not guaranteed when you read uncommitted data.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1