CASE statement not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CASE statement not working

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-07-19 : 11:33:00

I'm writing script for a DTS, but I'm having trouble with a CASE statement:

It has to extract data between a daterange (this is in the WHERE clause). The problem is that the datefield could be two different fields, so I added a CASE statement in the SELECT section, however when I try to run the query, it comes back with "Server: Msg 207, Level 16, State 3, Line 5
Invalid column name 'PRECLOSING'."

Or can/should I have the CASE in the WHERE section.

I've included the script below:

--*****************************************************************--
-- RECEIPT TO COMMITMENT - NLC REFI --
--*****************************************************************--

SELECT
L.CASENO,
L.BORROWERLASTNAME,
L.UAC,

L.DATESENTTOPROCESSING,
L.DATESENTTOPRECLOSING,
CASE
WHEN RE008.STATUS = 'clr' THEN RE008.DATECOMPLETED
ELSE L.DATESENTTOPRECLOSING
END AS [PRECLOSING],
L.SOC,
L.LOANPURPOSE,
L.LOANPLAN,
RE008.TRACKINGCONDITION,
RE008.TRACKINGSEQUENCE,
RE008.STATUS,
RE008.DATECOMPLETED,
L.BUILDERNAME,
L2.PROPERTYSTATUS,
L.PARCODE
FROM
EAR2001.DBO.TBLCLOSERLOANS L (NOLOCK)
LEFT JOIN
EAR2001.DBO.TBLCLOSERLOANS2 L2 (NOLOCK)
ON
L.CASENO = L2.CASENO
LEFT JOIN
(SELECT CASENO, TRACKINGCONDITION, TRACKINGSEQUENCE, STATUS, DATECOMPLETED
FROM EAR2001.DBO.ITEMTRAK RE008 (NOLOCK)
WHERE TRACKINGCONDITION = 'RE'
AND TRACKINGSEQUENCE = '008') RE008
ON L.CASENO = RE008.CASENO

WHERE
(l.borrowerlastname not like 'test' and
l.borrowerlastname not like 'sample%' and
l.borrowerlastname not like 'public%' and
l.borrowerlastname not like 'fistimer%' and
l.borrowerlastname not like 'customer%' and
l.borrowerlastname not like 'credco' and
l.borrowerlastname not like 'test' and
l.borrowerlastname not like 'none' and
l.borrowerlastname not like 'case' and
l.borrowerlastname not like 'america' and
l.borrowerlastname not like 'peoples')
AND L.UAC NOT LIKE 'r%' AND L.UAC NOT LIKE 'c%' AND L.UAC NOT LIKE 'p%'
AND L.UAC NOT LIKE 't%' AND L.UAC NOT LIKE 'w%'
AND L.SOC BETWEEN 4 AND 9
AND L.LOANPURPOSE IN ('r','c')
AND L.PARCODE NOT LIKE '3%'
AND L.BUILDERNAME = ''
AND L2.PROPERTYSTATUS <> 'c'
AND PRECLOSING BETWEEN CONVERT(SMALLDATETIME,GETDATE()-10,101)
AND CONVERT(SMALLDATETIME,GETDATE()-4,101)

ORDER BY L.CASENO ASC

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 11:41:06

You need to duplicate the case statement in the where clause. It would be nice if it did work how you've tried, but sadly it doesn't.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-07-19 : 11:49:45

I added the case statement like you suggested, but now I'm getting another error:

Server: Msg 156, Level 15, State 1, Line 61
Incorrect syntax near the keyword 'BETWEEN'.

see blow example:

--*****************************************************************--
-- RECEIPT TO COMMITMENT - NLC REFI --
--*****************************************************************--

SELECT
L.CASENO,
L.BORROWERLASTNAME,
L.UAC,

L.DATESENTTOPROCESSING,
L.DATESENTTOPRECLOSING,
CASE
WHEN RE008.STATUS = 'clr' THEN RE008.DATECOMPLETED
ELSE L.DATESENTTOPRECLOSING
END AS [PRECLOSING],
L.SOC,
L.LOANPURPOSE,
L.LOANPLAN,
RE008.TRACKINGCONDITION,
RE008.TRACKINGSEQUENCE,
RE008.STATUS,
RE008.DATECOMPLETED,
L.BUILDERNAME,
L2.PROPERTYSTATUS,
L.PARCODE
FROM
EAR2001.DBO.TBLCLOSERLOANS L (NOLOCK)
LEFT JOIN
EAR2001.DBO.TBLCLOSERLOANS2 L2 (NOLOCK)
ON
L.CASENO = L2.CASENO
LEFT JOIN
(SELECT CASENO, TRACKINGCONDITION, TRACKINGSEQUENCE, STATUS, DATECOMPLETED
FROM EAR2001.DBO.ITEMTRAK RE008 (NOLOCK)
WHERE TRACKINGCONDITION = 'RE'
AND TRACKINGSEQUENCE = '008') RE008
ON L.CASENO = RE008.CASENO

WHERE
(l.borrowerlastname not like 'test' and
l.borrowerlastname not like 'sample%' and
l.borrowerlastname not like 'public%' and
l.borrowerlastname not like 'fistimer%' and
l.borrowerlastname not like 'customer%' and
l.borrowerlastname not like 'credco' and
l.borrowerlastname not like 'test' and
l.borrowerlastname not like 'none' and
l.borrowerlastname not like 'case' and
l.borrowerlastname not like 'america' and
l.borrowerlastname not like 'peoples')
AND L.UAC NOT LIKE 'r%' AND L.UAC NOT LIKE 'c%' AND L.UAC NOT LIKE 'p%'
AND L.UAC NOT LIKE 't%' AND L.UAC NOT LIKE 'w%'
AND L.SOC BETWEEN 4 AND 9
AND L.LOANPURPOSE IN ('r','c')
AND L.PARCODE NOT LIKE '3%'
AND L.BUILDERNAME = ''
AND L2.PROPERTYSTATUS <> 'c'
AND CASE
WHEN RE008.STATUS = 'clr' THEN RE008.DATECOMPLETED
BETWEEN CONVERT(SMALLDATETIME,GETDATE()-10,101)
AND CONVERT(SMALLDATETIME,GETDATE()-4,101)
ELSE L.DATESENTTOPRECLOSING
BETWEEN CONVERT(SMALLDATETIME,GETDATE()-10,101)
AND CONVERT(SMALLDATETIME,GETDATE()-4,101)
END --AS [PRECLOSING]
ORDER BY L.CASENO ASC

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 12:05:57

More something like this (literally replacing 'PRECLOSING' with the whole case statement)...

...
AND L2.PROPERTYSTATUS <> 'c'
AND CASE
WHEN RE008.STATUS = 'clr' THEN RE008.DATECOMPLETED
ELSE L.DATESENTTOPRECLOSING
END BETWEEN CONVERT(SMALLDATETIME,GETDATE()-10,101) 
AND CONVERT(SMALLDATETIME,GETDATE()-4,101)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 13:14:34

By the way, there are a few ways your code can (probably) be improved.

Here's one suggestion to simplify all those 'not like' statements (by way of an example).

--data
declare @t table (borrowerlastname varchar(20))
insert @t
          select 'test'
union all select 'sample1'
union all select 'sample2'
union all select 'something else'
union all select 'test1'
union all select 'hello'
union all select 'world'
union all select 'public convenience'
union all select 'public record'

declare @u table (borrowerlastnamelike varchar(20))
insert @u
          select 'test'
union all select 'sample%'
union all select 'public%'

--calculation
select * from @t where not exists (select * from @u where borrowerlastname like borrowerlastnamelike)

/*results
borrowerlastname     
-------------------- 
something else
test1
hello
world
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-07-19 : 14:05:59

It worked!! I'll also keep in mind your other suggestion.

thanks alot!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-19 : 15:12:15

To avoid repeating long expressions in WHERE clauses, just use a derived table:


select 
  tmp.*
from
  ( select .... as LongExpression .. from ....) tmp
where
  LongExpression = ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources