WHERE clause still returning non selected data

Question

Hi,

I have the following WHERE clause which isn't working and I'm not too sure why.

I have explicitly stated to return select data, but if I use the 'e.cust_field' as an example, it's returning values not specified and I don't know why example '2'.

where a.comp = '0'
and a.mort_code like '%1/%'
and c.start_date >= '2020-01-01' and c.start_date <= '2020-12-31'
and substring(a.cust_no,3,7) <> 'TMU787'
and e.cust_table = '12'
and e.cust_field in ('1','3','7','17','21','29','32','35')
and e.cust_text <> '1258 testing'

Any idea why please?

Answer 1

Hi @Bone_12 ，

The unselected data is returned because those data actually satisfy the conditions behind WHERE.

Maybe this is what you want:

and (e.cust_field='1' or e.cust_field='3' or e.cust_field='7'or  
e.cust_field='17' or e.cust_field='21'or e.cust_field='29'or  
e.cust_field='32' or e.cust_field='35')  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

and substring(a.cust_no,3,7) <> 'TMU787'

Your SUBSTRING returns 7 characters and you compare it with 6 characters on unequal?

Please post table design as DDL, some sample data as DML statement and the expected result.