Why my issue select run endless

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

1 answer

0 votes

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.

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

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

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,932 views 12 18
Join discussion

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot