Find issues by "Epic Name" = empty not working

Solution 1:

The following query will show you all Epic-type issues in the database, along with their summaries and the associated epic name (if any):

SELECT p.pkey || '-' || ji.issuenum AS issueid, ji.summary AS issuesummary,
cfv.stringvalue AS epicname
FROM jiraissue ji
LEFT JOIN project p ON p.id=ji.project
LEFT JOIN customfieldvalue cfv ON cfv.customfield=(SELECT id FROM customfield
    WHERE cfname LIKE 'Epic Name') AND cfv.issue=ji.id
WHERE ji.issuetype=(SELECT id FROM issuetype WHERE pname LIKE 'Epic')
ORDER BY p.pkey, ji.issuenum;

If you're using JIRA 6.0 or earlier, you'll have to replace the first "p.pkey || ... as issueid" with "ji.pkey as issueid" and adjust the ORDER BY to match. If using JIRA 6.1+ but using MySQL, the first argument should instead become "concat(p.pkey,'-',ji.issuenum)".

I don't have any no-name epics in my system, so I have no way to test a query that returns only the nameless epics (as opposed to all of them)...but adding this to the WHERE clause should do it:

AND (CFV.stringvalue like '' OR CFV.stringvalue is null)