Query database values based on custom field values by issue

[f***@atlassian.com]

I would like to use the database values plugin to query values from another database, returning a single value for each issue in a project. We are storing the primary key of the DB table in a custom field, but I am unsure as to how to write the query to select only the record that corresponds to the custom field value for each given issue.

Can someone tell me how I can capture the custom field value in the query below?

sql.query=select id, order_date from orders where id = [value from another custom field for this issue]

Any advice you can provide would be greatly appreciated -- thanks!
--
Post by m_t - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

"value from another custom field for this issue" will be

{code}
select stringvalue/textvalue/numbervalue from customfieldvalue where customfield='xxxx' and issue='xxxx'
{code}

Replace xxxx with the ids for your custom field and issue! Whether it is stringvalue,textvalue or numbervalue depends on the type of the custom field.
--
Post by jobinkk - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

Thank you Jobin,
Given that I want to perform this query for each issue within the project, I can't hard-code the issue id; how can I dynamically select the applicable issue id? By now it should be painfully obvious that I'm a novice :)

Thanks,
Marshall
--
Post by m_t - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

select stringvalue/textvalue/numbervalue from customfieldvalue where customfield='xxxx' and issue= (select distinct ID from jiraissue) should work if it's just normal SQL you're after. that will select for every issue in the database.
--
Post by MHarper - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

Thanks Matt,
I'm sorry, I should clarify what I meant by "applicable issue"; let me provide some additional context, which I'm hoping will help:

The custom field we've set up is the primary key of the "order" table in an external database. There is one order associated with each Jira issue. What we want to do is query a date from the order table for each issue. So, the query that I am looking to create should return a single date for each given issue.

As such, rather than selecting all issue id's, I just want to select the "current" issue id for each issue. To extend the example:

Here is a table in our external database:

Order Id Order Date
1 5/11/2010
2 6/15/2010
3 7/18/2010

To correspond with each order, I will have three issues in my Jira project. Each Jira issue will have a custom field containing one of the order ids. For each issue, I want to select the date associated with the corresponding order.

I hope this makes sense -- thanks again!
--
Post by m_t - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

What Matt gave should help to do that. It goes through all issues and gets you the respective order ids. Or are you looking for something else? Like a selected list of issues?
--
Post by jobinkk - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

Hi guys,
So we finally got the plugin set up and I implemented the below query per your advice, but now I am getting an error from our Oracle database indicating that the "table or view does not exist".

{code}select id, order_date from orders where id = (select stringvalue from customfieldvalue where customfield='10052' and issue= (select distinct ID from jiraissue)){code}

I can see why this would happen, because it seems like if the query is being executed on our external database, it's probably trying to select the jira tables in the where clause from the external database as well. How is it possible to distinguish the external DB tables from the Jira tables in this query?

In the [plugin doc|https://studio.plugins.atlassian.com/wiki/display/JDVP/JIRA+Database+Values+Plugin?focusedCommentId=34013227#JIRADatabaseValuesPlugin-Dependingontheproject] the author makes reference to writing the query to depend on the "actual project being used", by using the following syntax:

{code}sql.query=select id, firstname, lastname, city, country from customer where jira_key like '${jira.project.key}'{code}

This is the same sort of thing I am trying to accomplish, but I want the query to depend on a custom field value within the "actual issue being used". I'm just not sure what the appropriate syntax is to achieve this result... many thanks in advance for any help you can offer!
--
Post by m_t - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671

[f***@atlassian.com]

Ok, thanks Jobin, I was thinking that what he provided would select all issues in the project, rather than each respective issue. I will try this out and see what happens -- thank you both again!
--
Post by m_t - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46671