Purpose

To identify the Request Type of a specific issue using SQL query. 

Solution

1. Note the project key, issue ID and the ID of 'Customer Request Type' custom field. To retrieve those info refer to the following KBs: 
   1. How to get issue id from the Jira User Interface
   2. How to find id for custom field(s)?

2. Run the following query in the database: 

   SELECT "NAME" 
   FROM "AO_54307E_VIEWPORTFORM" 
   WHERE 
     "KEY" LIKE (
       SELECT TRIM('<lowercase_pkey>/' FROM stringvalue) 
       FROM customfieldvalue 
       WHERE customfield = <cf_ID> AND issue = <issue_ID>);

   Replace <lowercase_pkey> , <cf_ID>, and <issue_ID> respectively
   
   For example: 

   SELECT "NAME" 
   FROM "AO_54307E_VIEWPORTFORM" 
   WHERE 
     "KEY" LIKE (
       SELECT TRIM('itsm/' FROM stringvalue) 
       FROM customfieldvalue 
       WHERE customfield = 10115 AND issue = 10041);

   This query is tested on PostgreSQL. If you have a different database, please adapt the syntax accordingly.

3. Below query can also be used to get all Projects and their associated Request Types along with the requesttypekey

SELECT p.pname, r."NAME" as request_type_name , (po."KEY" || '/' || r."KEY") AS request_type_key 
FROM "AO_54307E_VIEWPORT" po, "AO_54307E_VIEWPORTFORM" r, project p 
WHERE po."ID"=r."VIEWPORT_ID" AND po."PROJECT_ID"=p.id