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 vote

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 Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Apr 17, 2018 in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

767 views 2 19
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