SQL problem with where clause and AND operator-CodePudding

I have a problem because I would like to select some A11, C11, ZH11 with where and and. However, when I do and I only choose the first condition A11

proc sql;
create table zabcheck_hip as
select 
zab_data_spr
,sum(zab_hip) as ZAB_HIP
from _mart.zab_kred 
where zab_data_spr=&gv_date_dly. and zab_hip = 1 and ZAB_KOD = 'A11' AND 'C11' and 'ZH1'
group by zab_data_spr
;
quit;

when i try do like a below. I got empty table, but i know that for example for C11 should be many result

proc sql;
create table zabcheck_hip as
select 
zab_data_spr
,sum(zab_hip) as ZAB_HIP
from _mart.zab_kred 
where zab_data_spr=&gv_date_dly. and zab_hip = 1 and ZAB_KOD = 'A11' AND ZAB_KOD = 'C11' and ZAB_KOD = 'ZH1'
group by zab_data_spr
;
quit;

if I choose one by one it shows me the results where I am making a mistake

CodePudding user response：

ZAB_KOD = 'A11' AND 'C11' and 'ZH1'

This woudn't make sense, to a computer - this is something that humans do when they speak:

"Go through this box, getting out all the red and green balls"

A ball cannot be simultaneously red and green; it's red OR it's green. As a human we know how to understand it, but reality the person is requesting "go through this box of balls; if the ball is red or if the ball is green, get it out"

The truth of the where clause is evaluated for every row in the results and rows do not mix. Because a cell cannot have two different values at the same time, a single row cannot have 'A11' and 'Cll' so saying ZAB_KOD = 'A11' AND ZAB_KOD = 'C11' instantly, automatically means no rows will return. Sure you might have two different rows in your DB, with different values:

ID, ZAB_KOD 
1,  A11        --ZAB_KOD = 'A11' TRUE,   ZAB_KOD = 'C11' FALSE
2,  C11        --ZAB_KOD = 'A11' FALSE,  ZAB_KOD = 'C11' TRUE

But when you put AND, then a TRUE AND FALSE is FALSE; it needs to be TRUE OR FALSE, because that results in TRUE

(ZAB_KOD = 'A11' OR ZAB_KOD = 'C11' OR ZAB_KOD = 'ZH1')   and zab_hip = 1

You can also use the IN operator, which works like a series of OR operations

ZAB_KOD IN ('A11', 'C11', 'ZH1')                          and zab_hip = 1

Footnote:

When you mix OR and AND in a WHERE clause, always use parentheses to make it clear which things go together. These two things are very different:

(name = 'John' and age = 20) or city = 'Chicago'  --all 20 year olds called John who live anywhere, plus everyone in Chicago regardless their name or age
name = 'John' and (age = 20 or city = 'Chicago')  --only 20 year old people called John who live anywhere, plus only people called John of any age who live in Chicago

--which of the two above, is this:
name = 'John' and age = 20 or city = 'Chicago'

If you don't put parentheses, you have to know the rules of precedence to determine which data will return; in practise it's less confusing for all concerned (you right now, you in 6 months, the person who takes over your job etc) if you parenthesize clearly