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

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?

CodePudding user response：

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%'

CodePudding user response：

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