Error Message

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

dgcol
Posts: 2
Joined: Tue Nov 17, 2015 10:55 pm

Error Message

Post by dgcol » Tue Nov 17, 2015 10:58 pm

Dear all,

I would like to ask for your help with an error message I`m getting on PATSTAT Online.

I`m trying to generate a list of number of patent applications per country of origin of the applicant and year. In the case of more than one applicant, I divide the (1) application by the total number of applicants, and each one gets an equal share (i.e., if there are two applicants from different countries, the count for this application is 0.5 for each).

I`m using the following code to generate the table:

SELECT person_ctry_code, appln_filing_year, COUNT(*), SUM(nb_applicants), SUM(1/(nb_applicants*1.00))
FROM tls201_appln a
JOIN tls207_pers_appln b ON a.appln_id = b.appln_id
JOIN tls206_person c ON b.person_id=c.person_id
WHERE appln_filing_year>1998
AND applt_seq_nr>0
AND person_ctry_code NOT IN ('', '--','..','04','75')
GROUP BY a.appln_id, person_ctry_code, appln_filing_year
ORDER BY person_ctry_code, appln_filing_year

I tested the code with a small number of applications (introducing a WHERE condition such as `AND a.appln_id BETWEEN 1 and 1000`) and it works just fine.

However, when I try to run for the complete database (excluding such condition), PATSTAT online returns the following error message: “4:45:14 PM [PATSTAT Online error] The statement did not return a result set.”

Does anyone know what should be the problem, and how could I circumvent it?

Thanks in advance,
Daniel Colombo


mkracker
Posts: 114
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Error Message

Post by mkracker » Thu Nov 19, 2015 9:00 am

Daniel,

This is the classical division-by-zero error, because you compute "SUM(1/(nb_applicants*1.00))".
It will help if you include in your statement "AND nb_applicants > 0"; then your query will run fine.

Still, I do not fully understand the situation. In principle, there exist applications in PATSTAT which have no applicants (i.e. NB_APPLICANTS is 0), e. g. when offices did not provide this information for old applications, or when applications have been replenished by PATSTAT for keep the database consist. However, because of your JOINS and the WHERE conditions your query does retrieve only rows with NB_APPLICANTS > 0.

I run the query directly in the MS SQL Server client and get this error message: "Divide by zero error encountered.". So the behaviour is the same as in PATSTAT Online, so this is obviously no PATSTAT Online issue. I suspect the MS SQL engine executes the query in a way in which it encounters some division-by-zero.
For the DB experts: One can retrieve the estimated query plan also in PATSTAT Online when pressing <Ctrl>+ <lower-case L>. But I am not expert enough to understand what MS SQL really has been doing. Does anybody know?

A side remark: In your GROUP BY clause you included the APPLN_ID attribute. I am not sure that this will give you the result you said you wanted. You may need to remove it from the grouping.
-------------------------------------------
Martin Kracker / EPO


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: Error Message

Post by nico.rasters » Fri Nov 20, 2015 2:42 pm

Just pondering, in case the nb_applicants variable is bugged... MAX(applt_seq_nr) would probably also give you the number of applicants.
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


dgcol
Posts: 2
Joined: Tue Nov 17, 2015 10:55 pm

Re: Error Message

Post by dgcol » Mon Nov 30, 2015 5:39 pm

Dear Martin and Nico,

Thank you very much for your insights.

Martin was correct. Once I inserted the command "nb_applicants>0", Patstat ran the code without any problems.

As per the side remark, he was once again correct. It is meaningless to GROUP BY appln_id.

I truly appreciate both of you taking the time to help me. It`s nice to know that I can count with your expertise.

I apologize for the delay in replying. I was working on other parts of the code.

Best regards,
Daniel Colombo


Post Reply