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
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:
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
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.
Hi, thank you very much for your great help and sorry for my late response.
If I run the statement alone everything is fine after changing the
select cfv.stringvalue
to
select cfo.customvalue
but if i run this
SELECT
ji.issuenum AS "Incident ID",
issuestatus.pname AS "Incident Status",
ji.priority AS Priorität,
ji.component AS "Kategorie betroffenes Produkt",
ji.component AS "Kategorie verursachendes Produkt",
(select cfo.customvalue from customfieldvalue cfv, customfieldoption cfo, jiraissue ji where cfv.customfield = 10133 and cfv.customfield = cfo.customfield and CAST(cfv.stringvalue as SIGNED) = cfo.id and cfv.issue = ji.id) AS "TI-Notfall",
DATE_FORMAT(ji.created, '%Y-%m-%dT%T%+02' ) AS "Zieltermin",
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",
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
I get a error like this.
[Code: 1054, SQL State: 42S22] Unknown column 'customfieldvalue.issue' in 'where clause'
I think the error is a little weird because customfieldvalue.issue or cfv.issue is a valid column.
Best regards,
Stephan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I fix the problem above by my self. But now i have the endless running statement as before.
...
I can't find a way out.
Please.. need help.
Thanks
Stephan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.