用case语句搜索 [英] Search with case statement

问题描述

我遇到了i.here IN @activeInd的问题，我真的需要这个来从StatusOf中提取Y / N，如果这是有道理的，我相信这很简单。但我对如何实现这一目标感到困惑。

I am having an issue with the i.here IN @activeInd, I really need this to pull the Y/N from the "StatusOf" if that makes sense, I am sure this is something simple. But I am stumped on how to accomplish this.

DECLARE @activeInd char(1)
SET @activeInd = ''

SELECT  m.number as NAMENO, m.first as FIRSTNAME, m.last as LASTNAME, m.middle as MIDNAME, m.birthd as PERAGE, m.sex as SEX, b.intdate as BOOKDT, 

   case
        when i.curbook  > '0' then 'N'
        when i.curbook  < '0' then 'Y'
        else 'UNDEFINED'
    end AS StausOf

FROM DB.dbo.nmmain m, DB.dbo.jlinmate i, DB.dbo.jlbook b


WHERE b.intdate = (SELECT MAX(b2.intdate)
			   FROM DB.dbo.jlbook b2
			   WHERE m.number = i.namenum)  
			   
AND m.first LIKE @firstName + '%'
AND m.last LIKE @lastName + '%'
AND i.here IN ('Y', @activeInd)
AND (@fromDate IS NULL
OR b.intdate BETWEEN @fromDate AND @toDate)
ORDER BY m.last, m.first, m.middle, m.birthd, i.curbook

我有什么尝试过：


我遇到了i.here IN @activeInd的问题，我真的需要这个来从StatusOf中提取Y / N，如果这是有道理的，我相信这很简单。但我对如何实现这一点感到难过。

What I have tried:

I am having an issue with the i.here IN @activeInd, I really need this to pull the Y/N from the "StatusOf" if that makes sense, I am sure this is something simple. But I am stumped on how to accomplish this.

推荐答案

Quote：

AND我在哪里（'Y'，@ activeInd）

AND i.here IN ('Y', @activeInd)

如果我理解你的问题，你需要用你的案例陈述替换'Y' 。



您的案例陈述也是比较<和>用字符串而不是数字。不确定你为什么或在这里尝试做什么。

If I understand your question correctly you will need to replace 'Y' with your case statement.

Also your case statement is comparing a < and > with a string and not a number. Not sure exactly why or what you are trying to do here.

这篇关于用case语句搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！