Using CASE in Where Condition

Question

Using CASE in Where Condition

luissantos

SSCertifiable

Points: 7391
More actions
April 11, 2018 at 5:37 pm

#344602

Hello comunity
I need to use in Where condition a Case statment to show only records where 1=1 or if 1=0 then show ALL:
Where BO.LOGI1 = (CASE WHEN mylogicVariable = 1 THEN 1 END) --Show only records where mylogicVariable = 1, ( 1=1 ) --Now i need to place code to show All records if mylogicvariable = 0 : ( mylogicvariable = 0 or mylogicvariable = 1 ) )
What the best solution to solve this issue.
Best regards
Luis

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

In T-SQL variables must start with the @ character.
Maybe
WHERE BO.LOGI1 = @mylogicVariable;
It's hard to guess, try to provide more complete information.

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello Joe

Thanks for your Reply, when i write : mylogicVariable is a variable that i declare in VFP.
what i want is :
Where
BO.LOGI1 = (case when 1=1 then 1 end) -- show only record where BO.LOGI1 = 1
but now i need if BO.LOGI1 = 0 show all records , i mean where my BO.LOGI1 = 0 or BO.LOGI1 = 1Thanks
Luis

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

luissantos - Wednesday, April 11, 2018 5:57 PM
Hello Joe
Thanks for your Reply, when i write : mylogicVariable is a variable that i declare in VFP.
what i want is :
Where
BO.LOGI1 = (case when 1=1 then 1 end) -- show only record where BO.LOGI1 = 1
but now i need if BO.LOGI1 = 0 show all records , i mean where my BO.LOGI1 = 0 or BO.LOGI1 = 1Thanks
Luis

VFP = Visual Foxpro?
BO.LOGI1 is not a variable in T-SQL, as Joe has already pointed out.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

luissantos SSCertifiable Points: 7391 More actions · Answer 4

Hello,
Sorry if my question is a bit confusing.
in practice I have a form where there is a checkbox of type true / false.
In this form there is a grid containing a sql query to return records from my BO table.
What I want is when my checkbox equals True, the query returns only the records in the grid where the logi1 field of my BO table is True.
If my checkbox equals False, my query should return all records on the grid where the logi1 field in my BO table equals True or False.
Think for example of an excel sheet where I have a True / false field.
If I enable the filter I can in excel ask to return only the data where the field is equal to True, or all (true / false)
I hope you understood my question better.~
little example in SQL:

Declare @YesNo as bit set @Yesno = 0

Select name, married from mytable A where ( ( @YesNo=1 AND A.married in(1) ) OR ( @YesNo = 0 AND A.married in(0,1) ) )

in my case it works, it looks good or I can do it another way ?

Thanks,
Ls

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Yes, you can do it like that, or you could build the query dynamically, adding the WHERE clause only if the checkbox is ticked.

John

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Your suggested solution can be written more succinctly, like this:
DECLARE @YesNo BIT = 0;

SELECT A.name , A.married FROM mytable A WHERE A.married = IIF(@YesNo = 1, 1, A.married);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 7

VFP isn't going to provide any other alternative for tables under its auspices. What you have is pretty much the only easy way.....

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Why are we making it so complex?
Select name, married from mytable A where A.married = 1 OR @YesNo = 0

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

luissantos SSCertifiable Points: 7391 More actions · Answer 9

Hello,
Many thanks for all your replies.
Luis Cazares i read your example that is in fact not complex, i think that return both married and not married.
Can you tell me if I'm correct?
But if i want only married ?

Thanks
Luis

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

luissantos - Thursday, April 12, 2018 9:05 AM
Hello,
Many thanks for all your replies.
Luis Cazares i read your example that is in fact not complex, i think that return both married and not married.
Can you tell me if I'm correct?
But if i want only married ?
Thanks
Luis

It will return only married, unless the parameter is set to false and then it will ignore the condition and bring both.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

luissantos SSCertifiable Points: 7391 More actions · Answer 11

Hello,

Thansk you Luis for your explanation.

Best regards,
Luis