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:
>