Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 12th, 2011, 11:02 AM
Registered User
 
Join Date: Jul 2004
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default

And to add to this ancient thread (that I also found with a Google search)

I have been looking for an answer to this online, and have seen a lot of "you don't need to ever do that" responses which I just looooove. Anyway...

Here is a brief outline of what I need to do:

Employees always have a hire1 date, but some employees have a hire2 date. If someone has a hire2 date, I need to use that in my select and where, but if they don't have a hire2, then I have to use hire1. The final output has to show both dates.

I have a case statement in my select clause, but I also need one in my where clause; the where clause is currently not working correctly.

Code:
SELECT  employeeNumber, hire1, hire2, CASE WHEN hire2 = '' THEN hire1 ELSE hire2 END AS year
FROM employees
WHERE CASE WHEN hire2 = '' THEN hire1 ELSE hire2 END IN (1997,1998,1999) 
ORDER BY year
Right now, I am only getting results that have a hire2 date, so something isn't right.

I know what I need in my head, but that doesn't mean I'm explaining it right, so let me know if it's not clear.

Thanks!

Michelle
 
Old September 12th, 2011, 11:37 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Are you sure that the empty dates aren't expressed as NULL rather than an empty string?
If they are NULL then you can use:
Code:
SELECT employeeNumber, hire1, hire2, COALESCE(hire2, hire1) [year]
FROM employees
WHERE COALESCE(hire2, hire1)  IN (1997,1998,1999) 
ORDER BY 4
__________________
Joe
http://joe.fawcett.name/
The Following User Says Thank You to joefawcett For This Useful Post:
mnp13 (September 12th, 2011)
 
Old September 12th, 2011, 01:43 PM
Registered User
 
Join Date: Jul 2004
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default

You hit it. The reason it wasn't functioning was because I was using '' and not NULL.

I changed it to this, and now it's working.

Code:
SELECT  employeeNumber, hire1, hire2, CASE WHEN hire2 IS NULL THEN hire1 ELSE hire2 END AS year
FROM employees
WHERE CASE WHEN hire2 IS NULL THEN hire1 ELSE hire2 END IN (1997,1998,1999) 
ORDER BY year
However, I'll try coalesce the next time around. THANKS!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Case Statement In Where Clause Logic dwj119 SQL Server 2000 4 October 27th, 2011 03:14 PM
CASE WHEN WHERE CLAUSE deontae45 SQL Server 2000 2 January 11th, 2011 10:03 AM
Case in Where Clause demiwolf SQL Server 2000 3 March 23rd, 2006 12:45 PM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 05:49 AM
case clause in Access mateenmohd Access 19 May 6th, 2004 03:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.