• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Strange SQL Query Issue

I am querying my company's Atlassian JIRA tracking system and reporting on trouble tickets by status assigned to the username "tristanboughton." I have tried writing and re-writing the query multiple times and get one of two results: (1) all tickets (regardless of whether or not they were originally) are displayed as assigned to "tristanboughton" or (2) all tickets, despite filters applied to only display issues assigned to "tristanboughton," are displayed, including ones assigned to other users.

What am I missing?
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (PROJECT = '10180') AND (ASSIGNEE = 'tristanboughton') OR
                      (PROJECT = '10160') AND (ASSIGNEE = 'tristanboughton') OR
                      (PROJECT = '10182') AND (ASSIGNEE = 'tristanboughton') OR
                      (PROJECT = '10100') AND (ASSIGNEE = 'tristanboughton') OR
                      (PROJECT = '10230') AND (issuetype <> '12') AND (issuestatus IN (@StatusParameter)) AND (ASSIGNEE = 'tristanboughton')
ORDER BY ProjectName, PRIORITY

Open in new window

0
rdracer58
Asked:
rdracer58
  • 17
  • 12
  • 10
  • +1
2 Solutions
 
ralmadaCommented:
something like this?

...WHERE     (ASSIGNEE = 'tristanboughton') AND 
(PROJECT in  ('10180', '10160', '10182', '10100', '10230')) AND 
(issuetype <> '12') AND 
(issuestatus IN (@StatusParameter)) ...

Open in new window

0
 
dqmqCommented:
You have to many AND's and OR's without explicit parens to designate the order of operations.  You can also simplify your list of projects.

WHERE  issuetype <> '12'
AND ASSIGNEE = 'tristanboughton'
AND PROJECT IN ( '10180', '10160', '10182', '10100', '10230')
AND issuestatus = @StatusParameter

Finally, I don't know what you are trying to accomplish with @StatusParameter, but I do not think your IN operator works as you think.  The construct I have shown is equivalent to yours (and I suspect, also not what you intended).

0
 
Terry WoodsIT GuruCommented:
You just need some extra brackets in your where clause I think
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications'
                        WHEN '10160' THEN 'Corporate Reporting' 
                        WHEN '10182' THEN 'CMMI' 
                        WHEN '10100' THEN 'Information Technology Office' 
                        WHEN '10230' THEN 'Domain Controller/Migration' 
           END AS ProjectName,
           REPORTER,
           ASSIGNEE, 
           CASE issuetype WHEN '1' THEN 'Product Defect'
                          WHEN '3' THEN 'Task' 
                          WHEN '4' THEN 'Risk'
                          WHEN '20' THEN 'Report'
                          WHEN '21' THEN 'Change' 
                          WHEN '22' THEN 'Customization' 
                          WHEN '23' THEN 'Updated' 
                          WHEN '24' THEN 'Research' 
                          WHEN '25' THEN 'Applications Issue Type' 
                          WHEN '26' THEN 'Enhancement'
           END AS IssueTypeDef, 
           SUMMARY, 
           DESCRIPTION, 
           CASE PRIORITY WHEN '1' THEN 'Very High' 
                         WHEN '2' THEN 'High' 
                         WHEN '3' THEN 'Moderate' 
                         WHEN '4' THEN 'Low' 
                         WHEN '5' THEN 'Very Low' 
           END AS IssuePriority, 
           PRIORITY, 
           CASE issuestatus WHEN '1' THEN 'Open' 
                            WHEN '3' THEN 'In Progress' 
                            WHEN '4' THEN 'Reopened' 
                            WHEN '5' THEN 'Resolved' 
                            WHEN '6' THEN 'Closed'
           END AS IssueStatusName,
           CREATED, 
           UPDATED, 
           DUEDATE, 
           ID, 
           PROJECT, 
           TIMESPENT, 
           issuetype, 
           issuestatus
FROM         jiraissue
WHERE     ( (PROJECT = '10180') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10160') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10182') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10100') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10230') AND (issuetype <> '12')
            AND (issuestatus IN (@StatusParameter)) AND (ASSIGNEE = 'tristanboughton') 
          )
ORDER BY ProjectName, PRIORITY

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ralmadaCommented:
Also change issuestatus IN (@StatusParameter) to
 issuestatus = @Statusparameter
0
 
Terry WoodsIT GuruCommented:
Improved where clause - I think this is slightly different to the other suggestions.
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications'
                        WHEN '10160' THEN 'Corporate Reporting' 
                        WHEN '10182' THEN 'CMMI' 
                        WHEN '10100' THEN 'Information Technology Office' 
                        WHEN '10230' THEN 'Domain Controller/Migration' 
           END AS ProjectName,
           REPORTER,
           ASSIGNEE, 
           CASE issuetype WHEN '1' THEN 'Product Defect'
                          WHEN '3' THEN 'Task' 
                          WHEN '4' THEN 'Risk'
                          WHEN '20' THEN 'Report'
                          WHEN '21' THEN 'Change' 
                          WHEN '22' THEN 'Customization' 
                          WHEN '23' THEN 'Updated' 
                          WHEN '24' THEN 'Research' 
                          WHEN '25' THEN 'Applications Issue Type' 
                          WHEN '26' THEN 'Enhancement'
           END AS IssueTypeDef, 
           SUMMARY, 
           DESCRIPTION, 
           CASE PRIORITY WHEN '1' THEN 'Very High' 
                         WHEN '2' THEN 'High' 
                         WHEN '3' THEN 'Moderate' 
                         WHEN '4' THEN 'Low' 
                         WHEN '5' THEN 'Very Low' 
           END AS IssuePriority, 
           PRIORITY, 
           CASE issuestatus WHEN '1' THEN 'Open' 
                            WHEN '3' THEN 'In Progress' 
                            WHEN '4' THEN 'Reopened' 
                            WHEN '5' THEN 'Resolved' 
                            WHEN '6' THEN 'Closed'
           END AS IssueStatusName,
           CREATED, 
           UPDATED, 
           DUEDATE, 
           ID, 
           PROJECT, 
           TIMESPENT, 
           issuetype, 
           issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton')
          AND ( (PROJECT in ('10180', '10160', '10182', '10100') OR
                (PROJECT = '10230') AND (issuetype <> '12')
                AND (issuestatus IN (@StatusParameter)) 
                AND (ASSIGNEE = 'tristanboughton') 
              )
ORDER BY ProjectName, PRIORITY

Open in new window

0
 
rdracer58Author Commented:
I am using the query in a SQL Server Reporting Services report, and with the @StatusParamter I am trying to allow the report user to display issues in different statuses (e.g. open, in progress, closed, etc.) or select combinations thereof.

ralmada's modification brings back zero results--null data set, in other words.

dgmg's modification same as above.

TerryAtOpus's shows all as assigned to "tristanboughton" as opposed to filtering those assigned to "tristanboughton," which is one of the original problems I had.

0
 
rdracer58Author Commented:
Second post by TerryAtOpus: "Error in IN Clause" error when I try to run it.
0
 
rdracer58Author Commented:
In response to ralmada's second post: there are multiple values that could be passed to the @StatusParameter, so doesn't it need the "IN" as opposed to "="?
0
 
Terry WoodsIT GuruCommented:
My last suggestion has a mistake actually, the where clause should be:
WHERE     ASSIGNEE = 'tristanboughton'
          AND ( PROJECT in ('10180', '10160', '10182', '10100') OR
                ( PROJECT = '10230' AND issuetype <> '12'
                  AND issuestatus IN (@StatusParameter) 
                  AND ASSIGNEE = 'tristanboughton'
                )
              )

Open in new window

0
 
ralmadaCommented:
you will have to declare a variable like this:

declare @strSQL varchar(3000)
 
set @strSQL = 'SELECT     
	CASE PROJECT WHEN ''10180'' THEN ''Corporate Applications'' WHEN ''10160'' THEN ''Corporate Reporting'' WHEN ''10182'' THEN ''CMMI'' WHEN ''10100'' THEN
                       ''Information Technology Office'' WHEN ''10230'' THEN ''Domain Controller/Migration'' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN ''1'' THEN ''Product Defect'' WHEN ''3'' THEN ''Task'' WHEN ''4'' THEN ''Risk'' WHEN ''20'' THEN ''Report'' WHEN ''21'' THEN ''Change'' WHEN
                       ''22'' THEN ''Customization'' WHEN ''23'' THEN ''Updated'' WHEN ''24'' THEN ''Research'' WHEN ''25'' THEN ''Applications Issue Type'' WHEN ''26'' THEN ''Enhancement''
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN ''1'' THEN ''Very High'' WHEN ''2'' THEN ''High'' WHEN ''3'' THEN ''Moderate'' WHEN ''4'' THEN ''Low'' WHEN ''5'' THEN ''Very Low'' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN ''1'' THEN ''Open'' WHEN ''3'' THEN ''In Progress'' WHEN ''4'' THEN ''Reopened'' WHEN ''5'' THEN ''Resolved'' WHEN ''6'' THEN ''Closed''
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = ''tristanboughton'') AND 
(PROJECT in  (''10180'', ''10160'', ''10182'', ''10100'', ''10230'')) AND 
(issuetype <> ''12'') AND 
(issuestatus IN (' + @StatusParameter + ')) 
ORDER BY ProjectName, PRIORITY'
 
exec (@strSQL)

Open in new window

0
 
ralmadaCommented:
I'm assuming @Statusparameter is passing a string like this:   'open', 'in progress', 'closed'
0
 
ralmadaCommented:
or numbers like this: 1, 2, 3, 4. In order to use the IN clause you need to use it like this
issuestatus IN (1, 2, 3, 4)
or this way
issuestatus in ('open', 'string1', 'string2)
depending on the type of field
0
 
rdracer58Author Commented:
- Declaring variables is not supported in SQL Server 2005 Reporting Services.
- Even with the correction by TerryAtOpus to the WHERE clause, their query still showsall trouble tickets as having been assigned to "tristanboughton" as opposed to filtering those that are actually assigned to "tristanboughton."
0
 
Terry WoodsIT GuruCommented:
Can you please copy and paste the query in as you currently have it?
0
 
rdracer58Author Commented:
Here is the current query which shows all tickets as though they have been assigned to "tristanboughton" even if they aren't in our JIRA system.
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100')) OR
                      (ASSIGNEE = 'tristanboughton') AND (PROJECT = '10230') AND (issuetype <> '12') AND (issuestatus IN (@StatusParameter)) AND 
                      (ASSIGNEE = 'tristanboughton')
ORDER BY ProjectName, PRIORITY

Open in new window

0
 
ralmadaCommented:
Maybe you can create a stored procedure and execute it like this
exec yourprocedure @StatusParameter = .... whatever...

create procedure yourprocedure
@StatusParameter 
as
declare @strSQL varchar(3000)
 
set @strSQL = 'SELECT     
	CASE PROJECT WHEN ''10180'' THEN ''Corporate Applications'' WHEN ''10160'' THEN ''Corporate Reporting'' WHEN ''10182'' THEN ''CMMI'' WHEN ''10100'' THEN
                       ''Information Technology Office'' WHEN ''10230'' THEN ''Domain Controller/Migration'' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN ''1'' THEN ''Product Defect'' WHEN ''3'' THEN ''Task'' WHEN ''4'' THEN ''Risk'' WHEN ''20'' THEN ''Report'' WHEN ''21'' THEN ''Change'' WHEN
                       ''22'' THEN ''Customization'' WHEN ''23'' THEN ''Updated'' WHEN ''24'' THEN ''Research'' WHEN ''25'' THEN ''Applications Issue Type'' WHEN ''26'' THEN ''Enhancement''
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN ''1'' THEN ''Very High'' WHEN ''2'' THEN ''High'' WHEN ''3'' THEN ''Moderate'' WHEN ''4'' THEN ''Low'' WHEN ''5'' THEN ''Very Low'' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN ''1'' THEN ''Open'' WHEN ''3'' THEN ''In Progress'' WHEN ''4'' THEN ''Reopened'' WHEN ''5'' THEN ''Resolved'' WHEN ''6'' THEN ''Closed''
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = ''tristanboughton'') AND 
(PROJECT in  (''10180'', ''10160'', ''10182'', ''10100'', ''10230'')) AND 
(issuetype <> ''12'') AND 
(issuestatus IN (' + @StatusParameter + ')) 
ORDER BY ProjectName, PRIORITY'
 
exec (@strSQL)

Open in new window

0
 
Terry WoodsIT GuruCommented:
The where clause nesting still isn't right. Try this:
WHERE    (ASSIGNEE = 'tristanboughton')
         AND (  (PROJECT IN ('10180', '10160', '10182', '10100'))
                OR (  (PROJECT = '10230') 
                      AND (issuetype <> '12')
                      AND (issuestatus IN (@StatusParameter))
                   )
             )

Open in new window

0
 
ralmadaCommented:
(Create the procedure in Management Studio, not reporting services)
0
 
Terry WoodsIT GuruCommented:
Also, can you please review the where clause and check to make sure it's doing what you expect -

My latest suggestion:
* Returns every record with PROJECT in '10180', '10160', '10182', '10100'  for tristanboughton
* Plus also return every record for tristanboughton with PROJECT '10230' that has issuetype <> '12' and issuestatus IN (@StatusParameter)
0
 
dqmqCommented:
This where clause should work as expected.

WHERE  issuetype <> '12'
AND ASSIGNEE = 'tristanboughton'
AND PROJECT IN ( '10180', '10160', '10182', '10100', '10230')
--AND issuestatus IN (@StatusParameter)


As for the last line, you cannot  use an IN operator like that.  Let's get the query working without the issuestatus criteria and then deal with that as a separate question.


0
 
ralmadaCommented:
Because you cannot use the IN clause like that, is that you need to build the query during run-time. That's why you could use a stored procedure as I pointed out. So if your issuestatus column is an integer, @statusparameter should look like this:
exec yourprocedure @StatusParameter = '1, 2, 3, 4, 5'
if your issuestatus column is alphanumeric, it should look like this (please note that I'm using two single quotes for each value and then the whole string enclosed in single quotes again.
exec yourprocedure @StatusParameter = ' ''in progress'', ''open'', ''closed'' '
As a second alternative, but honestly I prefer the stored procedure is passing each status to filter as a separate parameter with a value of 0 or 1 depending if you want to filter it show that status or not. See code below (Second alternative) :
Finally, although it has nothing to do with the query you should consider defining another table and joining it to jiraissue instead of using so many CASE statements.

create procedure yourprocedure
@StatusParameter varchar(1000)
as
declare @strSQL varchar(3000)
 
set @strSQL = 'SELECT     
	CASE PROJECT WHEN ''10180'' THEN ''Corporate Applications'' WHEN ''10160'' THEN ''Corporate Reporting'' WHEN ''10182'' THEN ''CMMI'' WHEN ''10100'' THEN
                       ''Information Technology Office'' WHEN ''10230'' THEN ''Domain Controller/Migration'' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN ''1'' THEN ''Product Defect'' WHEN ''3'' THEN ''Task'' WHEN ''4'' THEN ''Risk'' WHEN ''20'' THEN ''Report'' WHEN ''21'' THEN ''Change'' WHEN
                       ''22'' THEN ''Customization'' WHEN ''23'' THEN ''Updated'' WHEN ''24'' THEN ''Research'' WHEN ''25'' THEN ''Applications Issue Type'' WHEN ''26'' THEN ''Enhancement''
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN ''1'' THEN ''Very High'' WHEN ''2'' THEN ''High'' WHEN ''3'' THEN ''Moderate'' WHEN ''4'' THEN ''Low'' WHEN ''5'' THEN ''Very Low'' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN ''1'' THEN ''Open'' WHEN ''3'' THEN ''In Progress'' WHEN ''4'' THEN ''Reopened'' WHEN ''5'' THEN ''Resolved'' WHEN ''6'' THEN ''Closed''
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = ''tristanboughton'') AND 
(PROJECT in  (''10180'', ''10160'', ''10182'', ''10100'', ''10230'')) AND 
(issuetype <> ''12'') AND 
(issuestatus IN (' + @StatusParameter + ')) 
ORDER BY ProjectName, PRIORITY'
 
exec (@strSQL)
 
--------------------------------------------------------------
Second alternative:
 
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND 
(PROJECT in  ('10180', '10160', '10182', '10100', '10230')) AND 
(issuetype <> '12') AND 
(
(@StatusParameter_inprogress = 1 and issuestatus = 'In Progress' ) -- 0 means "In progress" not filtered
OR (@StatusParameter_open = 1 and issuestatus = 'Open') -- 0 means "Open" not filtered
OR (@StatusParameter_close = 1 and issuestatus = 'Close') -- 0 means "Close not filtered
)

Open in new window

0
 
rdracer58Author Commented:
I would prefer not having to do the stored procedure. It would seem relatively straightforward to pull the trouble tickets in projects '10180', '10160', '10182', '10100', and '10230' where the "Assignee" is equal to "tristanboughton," however, I am still having difficulty.

In TerryAtOpus's and dgmg's latest suggestions, I am still having the problem where it is pulling back ALL of the tickets in the project (regardless of whether or not the assignee is "tristanboughton") and overwriting all assignees with "tristanboughton" as opposed to only displaying those tickets assigned to "tristanboughton."

Any ideas why it is doing this? I have attached my most recent version of the query.
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100', '10230')) AND (issuetype <> '12')
ORDER BY ProjectName, PRIORITY

Open in new window

0
 
ralmadaCommented:
Can you try your query in Management Studio first?
0
 
rdracer58Author Commented:
Tried the query in management studio--same result as my prior comment.
0
 
ralmadaCommented:
>>and overwriting all assignees with "tristanboughton" <<
What do you mean by that?
The crteria below will give you ASSIGNEE 'tristanboughton' only. There is no way it could give you tickets with a different value in ASSIGNEE.:
...
WHERE    
(ASSIGNEE = 'tristanboughton') AND
(PROJECT IN ('10180', '10160', '10182', '10100', '10230'))
AND (issuetype <> '12')
....
Can you try something like this first and post the result here? RUN THIS IN MANAGEMENT STUDIO
SELECT ASSIGNEE, count(*)
from jiraissue
WHERE    
(ASSIGNEE = 'tristanboughton')
GROUP BY ASSIGNEE
And finally can you post the definition of table "jiraissue" (column names and column type?
Thanks,
0
 
dqmqCommented:
>I am still having the problem where it is pulling back ALL of the tickets in the project (regardless of whether or not the assignee is "tristanboughton") and overwriting all assignees with "tristanboughton" as opposed to only displaying those tickets assigned to "tristanboughton."

Huh?
You request a list of tickets assigned to "tristanboughton".
You get back a list showing "tristanboughton" for all of them.
Then you claim, "tristanboughton" is not really assigned to some of them.

Feels like a Gracie Allen skit.




 

0
 
rdracer58Author Commented:
Yeah, it does feel like a Gracie Allen skit, but seriously if (1) I login to our JIRA system and/or (2) just do a Select * from the jiraissue table, those tickets are assigned to other users, which is obviously the crux of my problem right now. Could there be any issues, potentially outside of the query itself, causing this?

In response to ralmada: the result of

SELECT ASSIGNEE, count(*)
from jiraissue
WHERE    
(ASSIGNEE = 'tristanboughton')
GROUP BY ASSIGNEE

is "tristanboughton" and 276.

The definition of the "jiraissue" table is in the attached screen shot.
Table-Definition.png
0
 
ralmadaCommented:
>>(1) I login to our JIRA system and/or (2) just do a Select * from the jiraissue table, those tickets are assigned to other users, which is obviously the crux of my problem right nowIf <<
 
Then your application is querying a different table than jiraissue. It sounds illogical, but at this point you cannot discard any possibility. Can you check if you have any view defined in your Server?
0
 
Terry WoodsIT GuruCommented:
I have altered your latest query to just give a count of the results - if everything is working correctly, the count from the query should be less than 276, as the criteria is tighter than the above count you ran.

Secondly, can you please copy and paste your latest query that you posted and run it to double check that some id's from the latest query actually belong to a different assignee - this sort of issue is usually just a user misunderstanding (caused by something like running the wrong version of a query by mistake). The "OR" criteria is gone from your latest query, so every record returned should be for tristanboughton.
SELECT count(*)
FROM jiraissue
WHERE (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100', '10230')) AND (issuetype <> '12');

Open in new window

0
 
Terry WoodsIT GuruCommented:
Minor correction: "less than 276" should be "equal to or less than 276"
0
 
ralmadaCommented:
Just another thought. Could it be that your application is accessing another database?
try using the full schema like this
select * from yourdatabase.dbo.jiraissue
0
 
ralmadaCommented:
Or different server...
select * from yourserver.yourdatabase.jiraissue
0
 
Terry WoodsIT GuruCommented:
Great idea ralmada - I have seen that happen a few times before!
0
 
rdracer58Author Commented:
Nope. Both app and report are accessing the correct database.
0
 
ralmadaCommented:
And what about the views? can you check the views that you have created in the database?
0
 
rdracer58Author Commented:
I checked the views in the database. There are a number of system created views, none of them seem to have to do with trouble tickets/issues.
0
 
Terry WoodsIT GuruCommented:
Can you please compare the id's of these 2 queries in your JIRA system? The expected result is that the id's in the first query are a subset of id in the second query (which has a looser filter):
--First query (your latest posted query):
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100', '10230')) AND (issuetype <> '12')
ORDER BY ProjectName, PRIORITY;
 
--2nd query (all tristanboughton records):
SELECT id
from jiraissue
WHERE (ASSIGNEE = 'tristanboughton');

Open in new window

0
 
ralmadaCommented:

Again if your application is displaying a different resultset. The only reason has to be because it's querying a different table.
Can you run this query? It will tell you all the tables that contains a column name starting with 'Assign'. It will give you an idea of potential tables you should be looking at.

SELECT COLUMN_NAME, TABLE_NAME, TABLE_CATALOG, TABLE_SCHEMA
FROM yourdatabase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like  'ASSIGN%'  
order by 1, 2
 
0
 
rdracer58Author Commented:
I ran the query and the result set is in the attached screen shot. It appears that "jiraissue" is indeed the right table.

I have a suspicion this doesn't have to do with the query. Is there anything else I should be looking into/investigating?
Result-Set.png
0
 
ralmadaCommented:

What about the date the project was created?

select project, assignee, created
from jiraissue
where PROJECT IN ('10180', '10160', '10182', '10100', '10230')
For instance, per your table definition you could have more than one 10180 project created on different dates...
0
 
rdracer58Author Commented:
Nope, just five distinct projects, no projects with the same id but created on different dates.
0
 
ralmadaCommented:
Then my only advise will be to contact JIRA's technical support team and ask them how their application is reading the database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 17
  • 12
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now