For research work, I would like to count the number of forward citation of companies' patent per company per year. And at current stage, I would like to show all distinct PAT_PUBLN_ID based on same EARLIST_APPLN_ID by using following codes,
but the result shows a syntax error. Could you please give me some suggestion about this. thanks in advance.
Code: Select all
SELECT tls201_appln.earliest_filing_id, distinct (case when earliest_filing_id = earliest_filing_id then pat_publn_id end) from tls201_appln join tls212_citation on tls201_appln.appln_id = tls212_citation .cited_appln_id join tls211_pat_publn on tls201_appln.appln_id = tls211_pat_publn.appln_id where applt_seq_nr > 0 and publn_date = 2000 group by earliest_filing_id order by earliest_filing_id