conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditional update

I want to update a varbinary(max). If the column is null, then I will just
set it, but if not null, then append with a .Write. Something like:
row = select...
if (row.Document = null)
Update myTable
Set Document = 0xFF
where FileName = 'Text99.txt';
else
UPDATE myTable
SET Document .WRITE(0xFF, null, 0)
WHERE FileName = 'Text99.txt';
What is the pattern to do this sort of thing? TIA
William Stacey [MVP]William Stacey [MVP] wrote:
> I want to update a varbinary(max). If the column is null, then I
> will just set it, but if not null, then append with a .Write.
> Something like:
> row = select...
> if (row.Document = null)
> Update myTable
> Set Document = 0xFF
> where FileName = 'Text99.txt';
> else
> UPDATE myTable
> SET Document .WRITE(0xFF, null, 0)
> WHERE FileName = 'Text99.txt';
> What is the pattern to do this sort of thing? TIA
Update MyTable
Set Document =
CASE ISNULL(Document, -99)
WHEN -99 THEN SOMETHING
WHEN Document THEN SOMETHING_ELSE
END
Where FileName = 'Text99.txt'
Even easier would be to write a stored procedure and set the new column
value accordingly using a local variable.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
William Stacey [MVP]

Conditional Split - Expression Evaluates to Null

Hi everyone!

I'm using a conditional split to discriminate modified records. My expression looks like this:

col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

Use the ISNULL() function to test your columns for NULLs.

For instance:

ISNULL(colX_source) && !ISNULL(colX_dest)

|||

Simply add to your expression:

(IsNull(colX_Source) && IsNull(colX_dest)) || ((IsNull(colX_source) == false) && (IsNull(colX_dest)))

etc...

Or, use a derived column before the conditional split to eliminate the nulls:

Output1 / add as new column / IsNull(colX_Source) ? "" : colX_Source

|||

Thank you both for a quick response.

I understood the derived column idea but not the isnull idea.

If I want to cover all the cases in which I want the update to execute, my expression (if I haven’t missunderstood sth) should look like this:

isNull(col1_source) && !IsNull(col1_dest) || !isNull(col1_source) && IsNull(col1_dest) || col1_source != col1_dest

Isn’t it true that again, the last part of the expression col1_source != col1_dest evaluates to null if one of the columns is null? And that the only solution to the problem is a derived column which transforms null values in “”?

I’m new to SSIS and slowly learning that programming logic is not always the SSIS logic.

|||

The way it is written, it will only get to the last branch if both columns are NULL, which would render the last branch a moot point anyway because they would have to be "equal."

Use the derived column idea to change the NULLs to empty strings and then your conditional split will be easier to write/maintain. Plus, if you're not supposed to have NULL data, then it would be the proper thing to do to clean it up.

|||

I think my initial post is a little misguiding because I omitted a condition I have to test (which in fact is the last branch (a moot point as you say )).

If both colX_source and colX_dest are not null and different --> also Update!

+ the one I mentioned in the 1st post:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

|||( !ISNULL(colX_source) && !ISNULL(colX_dest) ) && (colX_source != colX_dest)|||

Wow, it works! Thank you for your patience!

Conditional Send Mail Task

Hi,

I want send email if certain conditions are met (by send mail task)... if compnay records does not exists in some table (returns null)... not task failure.... how do I achieve this without using Script task?

does any one have an idea about it,

regards

You need conditional workflow: http://www.sqlis.com/default.aspx?306

You can base your expression on a boolean variable that can be set from various places, including a script task.

-Jamie

|||

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

|||

Zadoras wrote:

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

Hmmm interesting one. probably the easiest way is to push that data into a flat file destination and then email that file as an attachment.

-Jamie

|||

that's what i was thinking... but i m searching for other way (may be the easiest way)

BTW... thanx for your support

if ne one come across to ne better idea than that please let me know

Conditional AND statement in stored procedure Select query?

This should be simple but I can't figure it out. So I have a select query an
d
I want to run a conditional AND if a value is not NULL. Here is the type of
statment I want to run.
-- Declared values not shown.
SELECT * FROM MyTABLE
WHERE city = @.city
AND department = @.department
IF @.statusCode is NOT NULL
AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
END
-- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
and splits them out for the IN clause. It works fine if a string is actually
passed but I simply want to execute the IN clause if the @.statusCode value
passed to the procedure is not NULL.
I looked at the WHEN THEN clause but that only seems to work for equalities
AND status = WHEN @.statusCode IS NULL THEN ...
Thanks folks.SQL will short-circuit when the first part of an OR condition fails, so
this should work (performance may suffer though)
...
and (@.statusCode is null
or status in (select item from dbo.tsqlSplit(@.statusCode))
another option is to default the @.statusCode variable to the list of
possible status codes. then you won't have to check if @.statusCode is null.
Ramez wrote:
> This should be simple but I can't figure it out. So I have a select query
and
> I want to run a conditional AND if a value is not NULL. Here is the type o
f
> statment I want to run.
> -- Declared values not shown.
> SELECT * FROM MyTABLE
> WHERE city = @.city
> AND department = @.department
> IF @.statusCode is NOT NULL
> AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
> END
> -- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
> and splits them out for the IN clause. It works fine if a string is actual
ly
> passed but I simply want to execute the IN clause if the @.statusCode value
> passed to the procedure is not NULL.
> I looked at the WHEN THEN clause but that only seems to work for equalitie
s
> AND status = WHEN @.statusCode IS NULL THEN ...
> Thanks folks.

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
>
>
>
>
>
>
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

Concatenating null fields in an SQL server query

I am concatenating 2 fields in a query. When one of the fields is a null
value the resulting string is also null wether the other string is null or
not. I have tried setting the concat_null_yields_null to off and on without
any change to the resulting string. Does anyone have any ideas about what I
might try next?
Thanks!
On Wed, 9 Mar 2005 14:23:01 -0800, Bob Boles wrote:

>I am concatenating 2 fields in a query. When one of the fields is a null
>value the resulting string is also null wether the other string is null or
>not. I have tried setting the concat_null_yields_null to off and on without
>any change to the resulting string. Does anyone have any ideas about what I
>might try next?
>Thanks!
Hi Bob,
Use
SELECT COALESCE(Column1, '') + COALESCE(Column2, '')
FROM YourTable
WHERE ...
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Concatenating Fields With NULL Values

Hey everyone,

This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.

Here is my current SELECT statement:

SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.

The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.

Can someone tell me what I am missing? Is there a function that I can use for this?

Thanks,

Use ISNULL ( check_expression , replacement_value ) function

SELECT ISNULL(m.FName,'') + SPACE(1) + ISNULL(m.MName,'') + SPACE(1) + ISNULL(m.LName,'') + ', ' + ISNULL(m.Credentials,'') AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

|||

Hmm, I had never seen the SPACE function :) Only thing to suggest to your code is that you probably want to include the SPACE function calls in the ISNULL to get rid of extra useless spaces:

ISNULL(m.FName + SPACE(1),'')

|||

Thanks to both of you. I have even used ISNULL in the past. I feel pretty stupid now.

The reason I am using SPACE(1) is because I have seen SQL treat ' ' as an empty string. I think it has to do with sp_dbcmptlevel.

|||

Here's what I actually ended up doing:

SELECT REPLACE(RTRIM(m.FName + SPACE(1) + ISNULL(m.MName, '')) + SPACE(1) + m.LName + ', ' + ISNULL(m.Credentials, '*'), ', *', '') AS Name
FROM tblMembers m
WHERE m.DRGInclude = 1

The RTRIM gets rid of the extra space when MName is NULL, and to get rid of the ', ' when Credentials is null, I used the REPLACE function.

|||

Check out the property 'CONCAT_NULL_YIELDS_NULL'

you can turn it OFF by the following command

SET CONCAT_NULL_YIELDS_NULL OFF
GO

Thanks,
Loonysan

Concatenating Fields - Null Problem

I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
one of the fields is null, the whole value comes out at null. Can anyone
give me a hint on how to still show fld1 and fld2 if fld3 is null?
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemPut coalesce or ISNULL around your fields
select coalesce(fld1,'') + coalesce(fld2,'') + cpalesce(fld3,'') as BigField
from table
this is for character data
for ints use this
select coalesce(fld1,0) + coalesce(fld2,0) + cpalesce(fld3,0) as BigField
from table
http://sqlservercode.blogspot.com/
"chuckdfoster" wrote:

> I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when an
y
> one of the fields is null, the whole value comes out at null. Can anyone
> give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||SELECT COALESCE(col1,'')+COALESCE(col2,'')+COAL
ESCE(col3,'') FROM whatever
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||The IsNull() function returns an alternate value when the supplied value is
NULL.
isnull(fld1,'') + isnull(fld2,'') + isnull(fld3,'')
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Hi,
Try using IsNull function.
Example: IsNull(fld1,'Null')
If fld1 is null, it will be replaced with string 'Null'.
--
*** Sent via Developersdex http://www.examnotes.net ***|||hi "chuckdfoster",
hope this helps
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All
expressions must be of the same type or must be implicitly convertible to th
e
same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
..
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
Examples
In this example, the wages table is shown to include three columns with
information about an employee's yearly wage: hourly_wage, salary, and
commission. However, an employee receives only one type of pay. To determine
the total amount paid to all employees, use the COALESCE function to receive
only the nonnull value found in hourly_wage, salary, and commission.
SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO
Here is the result set:
Total Salary
--
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000
(12 row(s) affected)
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"chuckdfoster" wrote:

> I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when an
y
> one of the fields is null, the whole value comes out at null. Can anyone
> give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Try
SELECT
ISNULL(fld1,'') + ISNULL(fld2,'')+ISNULL(fld3,'')
FROM YourTable
You can also use the COALESCE function instead of ISNULL.
If you are interested in the differences, have a look at
http://toponewithties.blogspot.com/...es.blogspot.com
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:%23vJdkl$zFHA.2884@.TK2MSFTNGP09.phx.gbl...
>I am trying concatenate 3 fields (fld1, fld2, fld3) in a view, but when any
>one of the fields is null, the whole value comes out at null. Can anyone
>give me a hint on how to still show fld1 and fld2 if fld3 is null?
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Thanks,
That worked perfect. I knew there had to be an easy way.
Thanks,
Chuck Foster
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:C2F6C12E-56AC-4C5C-9E4D-2AF873081BBD@.microsoft.com...
> Put coalesce or ISNULL around your fields
> select coalesce(fld1,'') + coalesce(fld2,'') + cpalesce(fld3,'') as
> BigField
> from table
> this is for character data
> for ints use this
> select coalesce(fld1,0) + coalesce(fld2,0) + cpalesce(fld3,0) as BigField
> from table
>
> http://sqlservercode.blogspot.com/
> "chuckdfoster" wrote:
>