How to query STRINGVALUE of customfield in Jira ver 4.4.4

[Erwin Manuel]

Hi, we just upgraded to Jira 4.4.4 and we noticed changes in DB structure.

In Jira 4.2.3, when we run the script:

select STRINGVALUE from customfieldvalue 

We get real string values, however after upgrading to 4.4.4, query returned numeric ID's.

Where do we retrieve the sting/text values for this? Thanks in advance.

[Jobin Kuruvilla [Adaptavist] · Accepted solution]

If the customfield is a Select or Mutli Select field, it is now storing the option ids instead of option name. This was changed to introduce option renaming.

Have a look at the customfieldoption table to find the String value for that option using the id you got from customfieldvalue table.

[Erwin Manuel]

Thanks! Although there is a need to add sql CONVERT and CASE to achive this.

[Mehmet Kazgan]

Erwin,

How did you get the stringvalue as the text instead of the numeric value?

[Erwin Manuel]

Hi Mehmet,

I think you should be able to retrieve stringvalue when you look into customfieldoption table. I could not recall the exact script I wrote but like I mentioned in the comment I used CONVERT and CASE.

[Naren]

Hi Erwin,

Can you check for which CUSTOMFIELD are your trying to retrieve the STRINGVALUE from CUSTOMFIELDVALUE table.

Please refer https://developer.atlassian.com/display/JIRADEV/Database+Schema