SQL query of custom fields with a single select list

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

2 answers

1 accepted

Accepted Answer
2 votes

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

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

Saurabh

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

Thanks much,

Bryan

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Featured Groups

Tuesday tips & tricks: What is the Atlassian Community?

It's officially Tuesday, which means it's officially time for another tip to help you better navigate this space we call the Atlassian Community. 😄 I got a great question from community member, Sa...

128 views 6 8
View post

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you