Zip Code Column Empty
Posted: Fri May 21, 2021 2:10 pm
Hi,
I'm trying to extract the zip codes for a number of patents from PATSTAT and it seems everything is empty. My code is this:
SELECT tls209_appln_ipc.appln_id, ipc_class_symbol,
tls201_appln.appln_auth, tls201_appln.appln_id, appln_filing_year,
tls206_person.person_ctry_code, tls206_person.psn_sector, tls206_person.psn_name, tls206_person.psn_level,
tls226_person_orig.zip_code, city, state
FROM tls209_appln_ipc
LEFT JOIN tls201_appln ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
LEFT JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
LEFT JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
LEFT JOIN tls226_person_orig ON tls206_person.person_id = tls226_person_orig.person_id
WHERE tls206_person.person_ctry_code = 'GB'
AND (ipc_class_symbol LIKE 'H04M 3%'
OR ipc_class_symbol LIKE 'H04M 4%'
OR ipc_class_symbol LIKE 'H04M 5%')
When I view the results, the zip codefield is completely empty despite there being thousands of patents listed. I'm wondering a) if potentially this data is stored somewhere else in the system, so I just need to change my code to reflect that, if b) this is a unique GB issue as we call them post codes, or c) if zip codes just genuinely arent stored in the system despite the variable being there
The zip code is completely fundamental to the analysis I'm doing (they will be my nodes for a patent network analysis), so I'm needing to find out where they are in the system and if they're not anywhere, what to try next
Thanks!
I'm trying to extract the zip codes for a number of patents from PATSTAT and it seems everything is empty. My code is this:
SELECT tls209_appln_ipc.appln_id, ipc_class_symbol,
tls201_appln.appln_auth, tls201_appln.appln_id, appln_filing_year,
tls206_person.person_ctry_code, tls206_person.psn_sector, tls206_person.psn_name, tls206_person.psn_level,
tls226_person_orig.zip_code, city, state
FROM tls209_appln_ipc
LEFT JOIN tls201_appln ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
LEFT JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
LEFT JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
LEFT JOIN tls226_person_orig ON tls206_person.person_id = tls226_person_orig.person_id
WHERE tls206_person.person_ctry_code = 'GB'
AND (ipc_class_symbol LIKE 'H04M 3%'
OR ipc_class_symbol LIKE 'H04M 4%'
OR ipc_class_symbol LIKE 'H04M 5%')
When I view the results, the zip codefield is completely empty despite there being thousands of patents listed. I'm wondering a) if potentially this data is stored somewhere else in the system, so I just need to change my code to reflect that, if b) this is a unique GB issue as we call them post codes, or c) if zip codes just genuinely arent stored in the system despite the variable being there
The zip code is completely fundamental to the analysis I'm doing (they will be my nodes for a patent network analysis), so I'm needing to find out where they are in the system and if they're not anywhere, what to try next
Thanks!