the number of forward citation

[Darwin]

Hello all,

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,

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

but the result shows a syntax error. Could you please give me some suggestion about this. thanks in advance.