SELECT cfo1.customvalue "Parent Option",
cfo2.customvalue "Child Option"
FROM customfield cf, customfieldoption cfo1, customfieldoption cfo2
WHERE cfname = 'Instrument'
AND cf.id = cfo1.customfield AND cf.id = cfo2.customfield
AND cfo1.parentoptionid is null
AND cfo2.parentoptionid = cfo1.id

SELECT p.pkey||'-'||j.issuenum as Key,summary,cfv.stringvalue AS "Instrument Name"
FROM jiraissue j
join issuetype i on j.issuetype = i.id
join customfieldvalue cfv on cfv.issue = j.id
join customfield c on cfv.customfield = c.id
join project p on ij.project = p.id
where p.pkey||'-'||I.issuenum in ('TEST-106')i.pname = 'Instrument'
and c.cfname = 'Instrument Name'

UPDATE changeitem 
SET OldString = 'Set Text Here'
where changeitem.id = 1819534

SELECT 	a.id, 
		a.filtername, 
		dbms_lob.substr(a.reqcontent, 4000, 1 ) as JQL, 
		a.authorname
FROM searchrequest a
WHERE dbms_lob.substr(a.reqcontent, 4000, 1 ) like '%In Post LC%'

SELECT 	a.name AS				as Swimlane_Name, 
		r.name 				as Board_Name, 
		r.owner_user_name 	as Owner, 
		a.query
FROM ao_60db71_swimlane a
JOIN ao_60db71_rapidview r on a.rapid_view_id = r.id 
WHERE a.query LIKE '%Nexome%'

SELECT 	f.username, 
		p.pagename as Dashboard, 
		p.id
FROM favouriteassociations f
JOIN portalpage p ON f.entityid = p.id 
WHERE f.entitytype = 'PortalPage' 
andAND p.pagename = 'QTP Overview'
--andAND f.entityid = 12272

SELECT 	f.username, 
		sr.filtername, 
		sr.id
FROM favouriteassociations f
JOIN searchrequest sr ON f.entityid = sr.id 
WHERE f.entitytype = 'SearchRequest' 
andAND sr.filtername = 'MyFilter'
--andAND f.entityid = 10001

selectSELECT * from
FROM searchrequest
whereWHERE id not in 
    (selectSELECT entityid 
    fromFROM sharepermissions 
    whereWHERE entitytype = 'SearchRequest') 

selectSELECT * from
FROM portalpage
whereWHERE id not in 
    (selectSELECT entityid 
    fromFROM sharepermissions 
    whereWHERE entitytype='PortalPage') 

selectSELECT distinctDISTINCT p.pkey || '-' || i.issuenum
fromFROM propertyentry pe
innerINNER joinJOIN fileattachment fa   	on pe.entity_id = fa.id
innerINNER joinJOIN jiraissue i
  			on fa.issueid = i.id
innerINNER joinJOIN project p   			on i.project = p.id
whereWHERE   pe.property_key = 'lucidchart.attachment.id';

SELECT 
  	workflowscheme.name 		AS "Workflow Scheme",
  		workflowschemeentity.scheme,
  		issuetype.pname 			AS "Associated Issue Type",
  		workflowschemeentity.issuetype
FROM 
  	issuetype,
  		workflowschemeentity,
  		workflowscheme
WHERE 
issuetype.id = workflowschemeentity.issuetype and
workflowscheme.id = workflowschemeentity.scheme and
  workflowschemeentity.workflow in
    (SELECT 
        jiraworkflows.workflowname
    FROM 
        jiraworkflows
    WHERE 
        jiraworkflows.descriptor like '%org.swift.jira.cot%');

SELECT 		CF.cfname, count(CV.ISSUE) 
FROM 		customfield CF 
joinJOIN 		customfieldvalue CV on CF.ID = CV.CUSTOMFIELD
groupGROUP byBY 	CF.cfname

SELECT p.pkey||'-'||j.issuenum as Key, a.title, a.url,
       a.iconurl, a.icontitle, a.relationship, a.applicationtype, a.applicationname
  FROM remotelink a
  JOIN jiraissue j on a.issueid=j.id
  JOIN project p on j.project = p.id

SELECT
    screen.id,
    screen.name,    
    layoutitem.fieldidentifier
    
FROM
    fieldscreenlayoutitem   layoutitem
    INNER JOIN fieldscreentab          tab ON layoutitem.fieldscreentab = tab.id
  inner  INNER joinJOIN fieldscreen             screen onON tab.fieldscreen = screen.id
  where fieldidentifier = 'priority
WHERE
    layoutitem.fieldidentifier IN ('customfield_13978','customfield_13979')

SELECT SR.filtername, CLI.fieldidentifier 
FROM columnlayoutitem    CLI
INNER JOIN columnlayout  CL  ON CLI.columnlayout = CL.id
INNER JOIN searchrequest SR  ON CL.searchrequest = SR.id
WHERE CLI.fieldidentifier IN ('summary','customfield_16165'); --'supervisor' custom field

SELECT
    *
FROM
    customfield
WHERE
    customfield.customfieldtypekey LIKE 'org.broadinstitute%'
ORDER BY
    customfield.customfieldtypekey

select p.pkey||'-'||j.issuenum as key,a.actionbody,j.summary,j.description
from jiraaction a
join jiraissue j on a.issueid = j.id
join project p on j.project = p.id
where 
a.actionbody like '%Tableau-sap%' 
OR j.summary like '%Tableau-sap%' 
OR j.description like '%Tableau-sap%'