Query with "not null"?

Is there a way to do a query and include a field if it is *not* null?
For instance, I know I can:
Select LastName + isnull(FirstName, '') from tblClients
I want to include a field only if it isn't null, for instance, if a client
is inactive, I want to display "(inactive)" in the results:
Smith, Jane (inactive)
Smith, John
Smith, Joe
Smith, Carol (inactive)
My fields are LastName, FirstName, Inactive (bit)Hi dew
I'm not sure what the connection with NULL is - is Inactive nullable,
so that you want to show (inactive) when Inactive is NULL or 0?
To do this, you can use the CASE statement:
SELECT LastName + isnull(FirstName, '') + CASE WHEN Inactive IS NULL
THEN '(inactive)' ELSE CASE WHEN Inactive=0 THEN ('inactive') ELSE ''
END END
(two nested CASE statements - would only need one if Inactive can only
have values 0 or 1 - i.e. is not NULLable).
hope this helps
Seb|||I'm not sure what you want to do.
But, I can tell you that you results will always contain the same number of
columns for all rows. So, you can't return a different number of columns fo
r
different criteria.
You could definitely build a dynamic string based on your query.
Like
SELECT LastName + ', ' + FirstName + CASE WHEN Inactive =1 THEN '
(inactive)' ELSE '' END FROM YourTable
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||The output of the query must be in table-format; all rows returned must have
the same number of columns. You can get something similar in appearance to
your desired output with something like this
SELECT LastName + ', ' + FirstName AS "Name", "Active"=
CASE
WHEN Inactive = 1 THEN '(inactive)'
ELSE ''
END
FROM [Your Table]
-
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Not sure im understanding you properly but isn't this all you need...
Select LastName + isnull(FirstName, '') from tblClients where Inactive is NU
LL
Select LastName + isnull(FirstName, '') from tblClients where Inactive is
NOT NULL
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Thanks so much, the select with Case works great, that is just what I
needed. Currently the Inactive column can be null but I can change that to
always be 0 or 1 so either one works. Thanks!
"dew" <dew@.yahoo.com> wrote in message
news:%23yUecnhEGHA.2072@.TK2MSFTNGP10.phx.gbl...
> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>