0

I'm trying to filter out records that have an invoice number that is similar but have some characters added to it. i don't want records where there is a credit invoice present. invoice numbers can look like 6245GE134 whereas credit invoice often have 'CR' added to them ,this varies however.

I've tried to make various lines with a replace statement: 

 where invoiceNr not in (select replace (invoiceNr, 'cr' ,'') from 
 tblinvoices where invoicenr like '%cr' and strType = 'credit'

the problem is, some fields have 'cr' added to them while others use 'credit' or even something completely different. ideally i want to be able to use wildcards to cover all posibilities, see underneath

 where invoiceNr not like '%' + (select invoiceNr from tblinvoices where 
 strtype = 'credit' ) + '%'

expected: it filters out all records where invoicenr appears in invoicenr that is credit type

actual:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Timothyb99
  • 3
  • 2
  • You can't use wildcards in an `IN` statement, no. A wildcard will only be seen as a wildcard in when SQL Server is expecting a *pattern* expression, like when using `LIKE`. So you'll have to use `(Column LIKE '{pattern1}' OR Column LIKE '{pattern2}' OR... )` – Thom A Jul 17 '19 at 08:57

1 Answers1

0

If I understand your point. You should be able to use "not exists" for your issue.

Like Julian did it here: https://stackoverflow.com/a/13376052/9272910

It's a bit tricky to write a complete SQL without your complete Statements.

jannowitz
  • 84
  • 7