How can i fix this problem with OR/AND clause in SQL

Question

I have a set of patents that I have titles and abstracts on and would like to search in such a way that their final search algorithm requires the keyword “software” to be present, and none of the keywords “chip”, “semiconductor”, “bus”, “circuit” or “circuit” to be present. i did This:

SELECT distinct 
  tls201_appln.docdb_family_id
, tls201_appln.appln_id
, [appln_auth]
, [appln_nr]
, [appln_kind]
, [appln_filing_date]
, [receiving_office]
, [earliest_publn_date]
, [granted]
, [nb_citing_docdb_fam]
, [nb_applicants]
, [nb_inventors]
, tls202_appln_title.appln_title

FROM tls201_appln
  INNER JOIN tls202_appln_title ON tls201_appln.appln_id = tls202_appln_title.appln_id 
  INNER JOIN tls203_appln_abstr ON tls201_appln.appln_id = tls203_appln_abstr.appln_id 

WHERE (appln_title like '%software%'
    or appln_abstract like '%software%')
  AND appln_title not like '%chip%'
                       or  '%semiconductor%'
                       or  '%circuity%'
                       or  '%circuitry%'
                       or  '%bus'%'
   or appln_abstract  not like  '%chip%'
                            or  '%semiconductor%'
                            or  '%circuity%'
                            or  '%circuitry%'
                            or  '%bus'%' 
    AND appln_filing_year between 2003 and 2008

but im getting this error An expression of non-boolean type specified in a context where a condition is expected, near 'or'. What should i do?

Instead of this pattern: `a like "b" or "c"`... do this: `a like "b" or a like "c"`, ...etc — trincot, Nov 29 '22 at 19:25
Checking many like conditions will have a bad readability and can lead to poor performance. Do you really need this? Better check for exact strings using IN ('string1','string2',...) or NOT IN ('string3','string4',...) — Jonas Metzler, Nov 29 '22 at 19:28
format your code to make it easier for someone to help you. Please review my edits to your question. — dougp, Nov 29 '22 at 19:31
Some dialects support [LIKE ANY](https://stackoverflow.com/a/52264937/5070879) syntax — Lukasz Szozda, Nov 29 '22 at 19:34

score 0 · Answer 1 · answered Nov 29 '22 at 19:31

0

This is wrong:

and appln_title not like '%chip%' or  '%semiconductor%' or '%circuity%'

This is right:

and appln_title not like '%chip%' 
and appln_title not like '%semiconductor%' 
and appln_title not like '%circuity%'

answered Nov 29 '22 at 19:31

Kurt

1,708
1
3
11

score 0 · Answer 2 · answered Nov 29 '22 at 19:53

Sample data would be helpful so I could test my code.

To help whoever maintains this code in the future, you'd do well to qualify your column names. It's difficult to know what's going on when I can't tell which table each column comes from.

This is considerably more verbose, but possibly easier to understand and maintain. Will this work?

with main as (
SELECT distinct 
  tls201_appln.docdb_family_id
, tls201_appln.appln_id
, [appln_auth]
, [appln_nr]
, [appln_kind]
, [appln_filing_date]
, [receiving_office]
, [earliest_publn_date]
, [granted]
, [nb_citing_docdb_fam]
, [nb_applicants]
, [nb_inventors]
, tls202_appln_title.appln_title
, appln_abstract

FROM         tls201_appln
  INNER JOIN tls202_appln_title ON tls201_appln.appln_id = tls202_appln_title.appln_id 
  INNER JOIN tls203_appln_abstr ON tls201_appln.appln_id = tls203_appln_abstr.appln_id 

WHERE appln_title + ' ' + appln_abstract like '%software%'
  AND appln_filing_year between 2003 and 2008
)

select docdb_family_id
, appln_id
, [appln_auth]
, [appln_nr]
, [appln_kind]
, [appln_filing_date]
, [receiving_office]
, [earliest_publn_date]
, [granted]
, [nb_citing_docdb_fam]
, [nb_applicants]
, [nb_inventors]
, appln_title
from main

except
select docdb_family_id
, appln_id
, [appln_auth]
, [appln_nr]
, [appln_kind]
, [appln_filing_date]
, [receiving_office]
, [earliest_publn_date]
, [granted]
, [nb_citing_docdb_fam]
, [nb_applicants]
, [nb_inventors]
, appln_title
from main
where exists (
    select 1
    from (
                  select '%chip%' as cond
            union select '%semiconductor%'
            union select '%circuity%'
            union select '%circuitry%'
            union select '%bus%'
         ) c
    where appln_title like c.cond
      or  appln_abstract like c.cond
  )

based on https://stackoverflow.com/a/1127100/9937026

How can i fix this problem with OR/AND clause in SQL

2 Answers2