Search with case statement

Question

2.00/5 (1 vote)

See more:

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.

SQL

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

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.

Posted 10-Mar-19 10:54am

Member 12678237

Updated 10-Mar-19 23:45pm

Herman<T>.Instance

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2019-03-10T14:17:00

Solution 1

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

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.

Posted 10-Mar-19 14:17pm

RossMW

Comments

Member 12678237 11-Mar-19 8:36am

RossMW,

I am going to try and explain it a little better. This is for a website, and basically the current "AND i.here IN ('Y', @activeInd)" is what I am wanting to replace with something like the below AND StatusOf IN ('Y', @activeInd)"

I want to be able to use the StatusOf which is not a field in the database, so I can search active and inactive items. The only database we had I had a field for StatusOf so I could search easily with Y/N vs this software vendor I no longer have that option, so I am hoping I can use the case above somehow, that is the part I am not sure how to do if that makes better sense?