CDR Tickets
| Issue Number | 4981 |
|---|---|
| Summary | Ad Hoc Query request - External Related Information Links |
| Created | 2021-06-04 12:46:26 |
| Issue Type | Task |
| Submitted By | Osei-Poku, William (NIH/NCI) [C] |
| Assigned To | Englisch, Volker (NIH/NCI) [C] |
| Status | Closed |
| Resolved | 2021-06-08 11:15:45 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.291681 |
Please create an ad-hoc query for all Glossary Terms with External Related Links (which are contained in the Glossary Term Concept documents). One for English Terms and another for Spanish Terms. We already have a simple ad hoc query but need additional information in this new report.
The display columns should be GTN ID, Term Type, Term Name, GTC ID, and Related Link(or URL).
Are these the queries you're referring to that already exist?
Related External Ref URL - English
Related External Ref URL - Spanish
Would you like those to be replaced or create new queries for the new requests?
They are similar but this one requires more columns. They should not replace the existing ones.
Yes, that's why I was wondering if you wanted the existing queries extended or create new ones.
What titles would you like for the new queries and are you looking for a specific sorting order?
Please name them.
External Related Links - additional columns - English
External Related Links - additional columns- Spanish
Sort by GTN name.
These queries have been added on DEV. Please take a look.
They looks good. Could you please exclude blocked documents?
The blocked documents have been excluded.
Looks good. Thanks! I have one more request. Several links are duplicated for the same term because of multiple term types. Would you be able to just display only one URL even if a term has multiple term types?
Are you saying you don't need to see the term type information? If we remove the term type column you won't see the URL multiple times.
If you want to keep the term type column I don't know if it's possible to only pick one of multiple rows. Which type would you want to have displayed?
I need to see the term type information. However, the URL is repeated for each term when the term type is different. It would be nice if only the first URL is displayed.
Please see attached example.
As far as I know - with the help from the Internet - what you want cannot be done in a simple, single SQL query. The only way I can think of to suppress duplicated URLs is by using the approach we've applied in the past and combining the TermType in a single cell:
I've replaced the query on DEV to use this approach.
(Jira had problems including the screenshot. Please see the attached file instead.)
Looks good. Thank you! I will copy the queries to PROD. I assume it is a manual process for you as well.
That's correct! Applying the C/P technique (copy/paste). 🙂
Looks good. Thank you ~volker
| File Name | Posted | User |
|---|---|---|
| Duplicate URL.PNG | 2021-06-08 20:38:30 | Osei-Poku, William (NIH/NCI) [C] |
| Screen Shot 2021-06-10 at 18.37.41.png | 2021-06-10 18:44:12 | Englisch, Volker (NIH/NCI) [C] |
Elapsed: 0:00:00.002135