Unable to fetch data from database

[Prayuja Patil]

I am using the below query to get all the custom field values of my Story Jira, but I see that string value column of custom field value table has numeric values like 1276, 10400, 10423, etc for "Sprint", "K Element", "RoadMap Category" fields.

Need to lookup these values to get the corresponding text values I see on GUI

Query is as below

select cf.cfname,cfv.*
from dbo.customfield cf, dbo.customfieldvalue cfv, dbo.jiraissue task, dbo.project prj
where cf.id = cfv.customfield
and cfv.issue = task.id
and task.issuenum = '1153'
and task.project = prj.id
and prj.pkey = 'HIN';

[Nic Brough -Adaptavist-]

For a lot of the fields, you are going to have to join one or many other tables in order to get to what the user sees on-screen, and in some cases, do some calculation as you're going to find it's not in the database.

SQL is the single worst possible way to do any form of reporting on Jira.

Could you explain why you are trying to do this, so we can explain a better way to do it?

[Tuncay Senturk _Snapbytes_]

Those are the options which are stored under customfieldoption table. You should join that table as well

[Prayuja Patil]

I'm getting all the options of the custom field. I want particular value which is present on a ticket.

[Tuncay Senturk _Snapbytes_]

If you join well, you should not get all values.

For instance you should

cfv.stringvalue = cfo.customvalue and csv.customfield = cfo.customfield

 But, as Nic said this is not a good way to get the value. You should use API (Jira API or REST API) to get those values.