SQL query of custom fields with a single select list

[Bryan McMillan]

I have a number of custom fields defined but 4 of them are single select lists. I am using external tools to pull data from our Oracle 11g instance (our dbase used for JIRA) and am having an issue with getting data from 2 of the 4. All 4 are single selects where one of the four have an option for either YES or NO the other 3 all have an identical thirteen item to choose from. For purposes of explaining lets say this is custom fields called A, B, C, and D. A has the YES/NO configuration. B, C, and D all have the thirteen items to pick from. I can use the same subquery to get a single row returned for B and D consistently with no problems as follows:

select

J.Pkey, J. ID, J.Duedate,

(select CFO.CustomValue from jiraaudit.CustomField CF, jiraaudit.CustomFieldValue CFV, jiraaudit.CustomFieldOption CFO where CF.CFName = 'B' And CF.Id = CFV.CustomField And CFV.Issue = J.Id And CFO.CustomField = CF.Id And CFV.StringValue = To_Char(CFO.Id)) B_Field,

(select IStatus.Pname from jiraaudit.IssueStatus IStatus where IStatus.Id = J.IssueStatus) Issue_Status

From jiraaudit.JiraIssue J, JiraAudit.Project P

Where J.Project = P.Id And P.Pname = 'Project 1'

Order by J.Pkey

But if I change to this:

(select CFO.CustomValue from jiraaudit.CustomField CF, jiraaudit.CustomFieldValue CFV, jiraaudit.CustomFieldOption CFO where CF.CFName = 'C' And CF.Id = CFV.CustomField And CFV.Issue = J.Id And CFO.CustomField = CF.Id And CFV.StringValue = To_Char(CFO.Id)) B_Field,

(select IStatus.Pname from jiraaudit.IssueStatus IStatus where IStatus.Id = J.IssueStatus) Issue_Status

From jiraaudit.JiraIssue J, JiraAudit.Project P

Where J.Project = P.Id And P.Pname = 'Project 1'

Order by J.Pkey

I will get

ORA-10427: Single-row subquery returns more than one row error.

The really strange part of this is that the definitions for fields B, C, and D are identical other than they have different names defined in the CustomField table. Now, for the disclaimer I am not a DBA by trade so this one is just eluding me. The documentation for the database schema for v5.9 or v5. anything is not to be found. If anyone can shed some light on this for me it would be most appreciated.

Thanks, Bryan

[Udo Brand · Accepted solution]

Hi Bryan,

as for the database-schema please see here.

First you should not use single-row subqueries in your select part (that's pretty ugly). Since you got two of them both could be (in case of your customfield C) the reason for ORA-10427. But I guess it is only the B_Field part.

So you need to find out what's causing this error.

As for your analysis this select should help you to identify the root cause (maybe add some more fields to the select part).

select cfv.issue, cfo.customvalue, cf.cfname , issue.pkey
from jira.CustomFieldValue cfv 
join jira.CustomField CF on CF.Id = CFV.CustomField
join jira.CustomFieldOption CFO on CF.Id  = CFO.CustomField
join jiraissue issue on issue.id = cfv.issue
where cf.CFName = 'C' 
And To_Char(CFO.Id) =CFV.StringValue;

I also have rewritten you select-statement without using single-row subqueries.

select J.Pkey, J. ID, J.Duedate, cust.CustomValue, IStatus.Pname issue_status
from  jira.JiraIssue J 
join jira.Project P on  J.Project = P.Id 
join jira.IssueStatus IStatus on J.IssueStatus = IStatus.Id   
left join (select cfv.issue, cfo.customvalue, cf.cfname 
from jira.CustomFieldValue cfv 
join jira.CustomField CF on CF.Id = CFV.CustomField
join jira.CustomFieldOption CFO on CF.Id  = CFO.CustomField
where cf.CFName = 'C' 
And To_Char(CFO.Id) =CFV.StringValue) Cust
on cust.issue = j.id
where P.Pname = 'Project 1'
Order by J.Pkey;

I hope I could help,

Cheers, Udo

[Saurabh Unercat]

Thanks so much! This helped. Using something like your query to perform analytics on custom fields

Saurabh

[Bryan McMillan]

Udo, thank you for response. As it turns out I wasn't using joins vice using nested selects.

Thanks much,

Bryan