Query with "not null"?

5 Jan 2006 5:03 PM

dew

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)

5 Jan 2006 5:12 PM

sebthirlway

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

5 Jan 2006 5:22 PM

Ryan Powers

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

Show quote

"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)
>
>
>

5 Jan 2006 5:25 PM

Mark Williams

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]

-

Show quote

"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)
>
>
>

5 Jan 2006 5:26 PM

marcmc

Not sure im understanding you properly but isn't this all you need...
Select LastName + isnull(FirstName, '') from tblClients where Inactive is NULL
Select LastName + isnull(FirstName, '') from tblClients where Inactive is
NOT NULL

Show quote

"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)
>
>
>

5 Jan 2006 7:36 PM

dew

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!
Show quote

"dew" <d**@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)
>