...
Return changeitems (including before and after values, as well as modification date) for issue TEST-106
| Code Block |
|---|
|
selectSELECT
p.pkey||'-'||i.issuenum as "KEY",
ci.field,
cg.created as Modified,
dbms_lob.substr(ci.oldstring, 4000, 1) as OLD_String,
dbms_lob.substr(ci.newstring, 4000, 1) as NEW_String,
dbms_lob.substr(ci.oldvalue, 4000, 1) as OLD_Value,
dbms_lob.substr(ci.newvalue, 4000, 1) as NEW_Value
ci.id as Change_Item_Id
fromFROM changeitem ci
joinJOIN changegroup cg on ci.groupid = cg.id
joinJOIN jiraissue i on cg.issueid = i.id
joinJOIN project p on i.project = p.id
whereWHERE p.pkey||'-'||I.issuenum in ('TEST-106') |
...
OR who has dashboard with id 12272 as a favorite
| Code Block |
|---|
|
noformat |
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
|
Return who has 'MyFilter' or filter id of 10001 saved as a favorite:
| Code Block |
|---|
|
noformat |
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 |
Return a user's (biasella) favorite filters along with the JQL behind the filter
| No Formatcode |
|---|
|
SELECT si.filtername,
dbms_lob.substr( si.reqcontent, 4000, 1 ) as JQL
FROM favouriteassociations fa
JOIN searchrequest si ON fa.entityid = si.id
WHERE fa.username = 'biasella'; |
...
Return filter name and JQL given a filter id e.g. filter=15191
| Code Blocknoformat |
|---|
|
SELECT si.id,
si.filtername,
dbms_lob.substr( si.reqcontent, 4000, 1 ) as JQL
FROM favouriteassociations fa
JOIN searchrequest si ON fa.entityid = si.id
WHERE si.id = 15191 |
Return private filters
| No Format |
|---|
select| Code Block |
|---|
|
SELECT *
fromFROM searchrequest
where id not in
(selectSELECT entityid
fromFROM sharepermissions
whereWHERE entitytype = 'SearchRequest') |
...