SQL的CASE+WHERE

ahmeng

各位大哥，这个sql query,最后一行是会出error的，
但如果我真的要找类似的资料，就是自己设的column name里面的资料，
就是这个例子里面，我自己设的新column name(ProcessType)，
但搜寻的时候，要找出所有ProcessType ='request'的，
要怎么写才能没有error呢？请指教~

SELECT

Description AS Process,

CASE
WHEN Type = 1 THEN 'Request'
WHEN Type = 2 THEN 'Contract agreement data'
END AS ProcessType

FROM HRProcess

WHERE ProcessType ='Request'

goatstudio

为什么最后一行会有 error? 不如把你的 table 结构放上来看看.

基本上, 你的 sql 很奇怪, 既然只想找出 "Request" 的, 为什么不直接用

SELECT Description AS Process, 'Request' AS ProcessType
FROM HRProcess
WHERE Type = 1

ahmeng

其实我简写的例子错了，paiseh...
把比较完整的放上来。。。

SELECT Description AS Process,
Startdate AS Enddate,
Enddate AS Enddate,
CASE WHEN blocked = 1 THEN 'Blocked'
WHEN Active = 1 AND (startdate <= GETDATE() AND (enddate IS NULL OR enddate >= GETDATE())) THEN 'Active'
WHEN Active = 1 AND enddate IS NULL OR enddate >= GETDATE() THEN 'Future'
ELSE 'Inactive'
END AS Status

FROM HRProcess

WHERE Status = 'Active'

status table是不存在的。。。

DRAGONISM

这个是你要的吗?

SELECT Description AS Process,
       Startdate AS Enddate,
       Enddate AS Enddate,
       'Active'AS Status
FROM HRProcess
WHERE Active = 1 AND (startdate <= GETDATE() AND
     (enddate IS NULL OR enddate >= GETDATE()))

[ Last edited by DRAGONISM on 30-4-2005 at 12:14 AM ]

SELECT Description AS Process,
(CASE
WHEN Type = 1 THEN 'Request'
WHEN Type = 2 THEN 'Contract agreement data'
END ) AS ProcessType
FROM HRProcess
WHERE ProcessType ='Request'

试这个.

DRAGONISM

RossChang 于 29-4-2005 09:31 PM  说 :
SELECT Description AS Process,
(CASE
WHEN Type = 1 THEN 'Request'
WHEN Type = 2 THEN 'Contract agreement data'
END ) AS ProcessType
FROM HRProcess
WHERE ProcessType ='Request'

试这个.

其实我觉得很疑惑,

第一, 为什么知道了只是要ProcessType='Request',还要 WHEN Type = 2 THEN 'Contract agreement data' 这个部分呢???

SELECT Description AS Process,
(CASE
WHEN Type = 1 THEN 'Request'
WHEN Type = 2 THEN 'Contract agreement data'
END ) AS ProcessType
FROM HRProcess
WHERE ProcessType ='Request'

第二,ProcessType并不是HRProcess里的field,可以用在WHERE CLAUSE里吗?

1.不知道...

2.可以.

DRAGONISM

RossChang 于 30-4-2005 01:29 AM  说 :
1.不知道...

2.可以.

我做了简单的测试,

tblLog 的fields
--------------------------------
sUserID char(8)
dDate smalldatetime
sMsg varchar(200)

在SQL Query Analyzer执行下面的SQL :
select sUserID, 'Active' as strStatus
from tblLog
where strStatus = 'Active'

得到的error是:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'strStatus'.

请问我错在哪儿呢?谢谢。

Woops... 记错了,那个是给 GROUP BY CLAUSE 的...