Why my issue select run endless

s.mallmann

Hi @ all

I have a problem with my SQL Query.
If i insert the underlined row the statement run endless.
What i want is that i get a list with all issues and the value of selected custom field.

SELECT 
ji.id,
ji.issuenum AS "Incident ID",
issuestatus.pname AS "Incident Status",
ji.priority AS Priorität,
--ji.component AS "Kategorie betroffenes Produkt", --NV
--ji.component AS "Kategorie verursachendes Produkt", --NV
--(select customfieldvalue.textvalue from customfieldvalue where customfieldvalue.issue = ji.id and customfieldvalue.customfield = 10139)as Umgebung,
--(select customfieldvalue.textvalue from customfieldvalue  where customfieldvalue.issue = ji.id and customfieldvalue.customfield = 10133)as Sicherheitsrelevant,
--(select customfieldvalue.textvalue from customfieldvalue  where customfieldvalue.issue = ji.id and customfieldvalue.customfield = 10135)as Datenschutzrelevant,
(select cfv.stringvalue from customfieldvalue cfv, customfieldoption cfo where cfv.customfield = 10132 and cfv.customfield = cfo.customfield and cfv.stringvalue = cfo.id and customfieldvalue.issue = jiraissue.id) AS "TI-Notfall",
ji.reporter AS "Incident Melder",
ji.assignee AS "Incident Bearbeiter",
ji.summary AS "Incident Beschreibung",
DATE_FORMAT(ji.duedate, '%Y-%m-%dT%T%+02' )  AS "Zieltermin", --NV
resolution.pname  AS "Indicent Lösung"
FROM jiraissue ji join project on ji.project=project.ID 
join issuetype on ji.issuetype=issuetype.ID
join issuestatus on ji.issuestatus=issuestatus.ID
left outer join resolution on ji.resolution=resolution.ID

 

Please, can anybody help me.

 

Best regards,

Stephan

Andy Heinzer

I tried to run just that underlined select statement against my own Jira database. But I ran into some problems that might be helpful to cover here.  I started trying this in Postgressql, but then finished in mysql to confirm this:

1. First error was in regards to different field types.  This was in postgres, so maybe not exactly the same, but  It looks like you're trying to compare a string value to a numeric field.  Some flavors of SQL get upset with that:
   

   ERROR:  operator does not exist: character varying = numeric
   LINE 1: ...customfield = cfo.customfield and cfv.stringvalue = cfo.id a...
                                                                ^
   HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
   
   ********** Error **********
   
   ERROR: operator does not exist: character varying = numeric
   SQL state: 42883
   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
   Character: 161

2. I was able to get around that with using a CAST operator
3. Next I found a few inconsistencies in regards to which table/column are being referred to here.  Maybe that's a postgres difference, but either way, I tweaked this a bit and was able to get that subquery to work when it looks like this:
   
   

select cfv.stringvalue from customfieldvalue cfv, customfieldoption cfo, jiraissue ji 
where cfv.customfield = 10100 and 
cfv.customfield = cfo.customfield and 
CAST(cfv.stringvalue as SIGNED) = cfo.id and 
cfv.issue = ji.id

That is the specifically MySQL syntax.   I did change the customfield value in my test to 10100 just because that was a valid value in my system, so you'll have to change that back.  But I did test this syntax and it appears to work correctly with mysql.  I made sure that the jiraissues table is referred to as 'ji' and I also CAST that string value as a signed integer, which should be sufficient to then make the comparison of the other numeric field accurately.

I hope this helps.