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

Query Visual FoxPro .dbf files from SQL Server 2005 (64-bit)

I need to query a foxpro database from a new SQL Server 2005 (64-bit) instance Running on a 64-bit Windows 2003 Server. I can't find an ODBC driver, only the OLE DB one.
I install the OLE DB and it creates the appropriate registry settings, but it doesn't show up.
Please help, I'm probably not on the right track, but I don't know where to go from here.
Any and all attempts at helping are greatly appreciated.
Thanks,
jim

Hi Jim,

The latest (and last) VFP ODBC driver is downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc and the latest OLE DB data provider is downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates.

However, the OLE DB data provider isn't compatible with a 64-bit OS (and I assume the ODBC driver isn't either though you can check) which is why it doesn't show up in your list.

|||

The ODBC driver is 32 bit. The OLE DB provider is supposed to be 64-bit compatible, it installed and I can see the registry entries, but I can't seem to figure out how to use it.

Is it not possible to query .dbf files from SQL Server 2005 64-bit?

I know I'm not the only person trying to query a Microsoft Visual FoxPro database from a 64-bit SQL server, am I?

|||

Hi Jim,

What makes you think that the OLE DB data provider is 64-bit compatible? The following was posted elsewhere by Lee Mitchell. Although he speaks to the case of a Visual Studio app being compiled as 64-bit I assume the same goes for the 64-bit version of SQL Server:

The 64-bit application will not see a 32 bit OLE DB Provider. You need to compile the application as a 32-bit app, and it can then use the 32-bit OLE DB Provider.

There is no 64-bit VFP OLE DB Provider at this time. ......

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

|||

Cindy, I appreciate the information you provided, although I can't seem to understand why Microsoft would roll out a product that is handicapped the way the 64-bit SQL Server is.

I assumed that since it created the correct registry keys, that the OLE DB provider was 64 bit. I'm running Windows 2003 Server, which is one of the OS's listed on the download page.

So, I should compile SQL Server as a 32 bit application? If so, how do I do this?

I also assumed that Microsoft would make it possible to query one Microsoft product from another. Is Microsoft no longer supporting Visual FoxPro nor SQL server, new versions have recently been released for both? What gives?

Is 64 bit computing with Microsoft not an actuality?

I guess I'll just have to call stored procedures on a 32 bit SQL Server 2000 from the SQL Server 2005 64-bit box. I'll tell ya, this is a great way to start out with a new product, it really gives me great confidence in this product handling my companies most critical data! :( ...NOT

Microsoft: "We have a great new product, a 64 bit SQL Server!"

Jim: "What drivers exist for it to connect to other databases?"

Microsoft: "Well, you can connect to another SQL Server, Access, and...and, you can connect to Oracle."

Jim: "What about FoxPro?"

Microsoft: "Duh....we can't do that. You'll have to buy another SQL Server, a 32-bit version and call stored procedures on that instance from you 64 bit SQL Server."

|||

Hi Jim,

Technology is always difficult when you're playing on both sides of a fence. An example of this is the move from Visual Studio 6 to VS 7+ and the .NET framework. Although there are converters, all but the simplest code won't run as-is on VS.NET. By the way, FoxPro went through a similar upgrade between versions 2.x and 3.

I'm not much of a server person but I don't see 64-bit SQL as handicapped since I consider it to be the OLE DB data providers' "responsibility" to be compatible with the 64-bit SQL Server software.

In these days when 64-bit machines are still not all that prevalent one should assume that the "Windows Server 2003" listed on the OLE DB downloads page would refer to the 32-bit version only and that if the 64-bit version were supported then "64" would be specifically mentioned.

How would you compile 64-bit SQL Server as a 32-bit application? Well, since you don't have the source you can't. What you would need to do is install the 32-bit version, as you suggested.

Visual Studio .NET is Microsoft's flagship development platform; FoxPro isn't for either data or development. The resources devoted to it at Microsoft are in keeping with it's value to the company.