question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked Bone12-2270 commented

WHERE clause still returning non selected data

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?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Bone12-2270

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Bone12-2270 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have changed the substring, thank you for pointing that out.

0 Votes 0 ·