Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Strange SQL Query Issue

Posted on 2009-02-10
42
Medium Priority
?
218 Views
Last Modified: 2016-12-08
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
Comment
Question by:rdracer58
  • 17
  • 12
  • 10
  • +1
42 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 23605697
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
 
LVL 42

Expert Comment

by:dqmq
ID: 23605717
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23605720
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:ralmada
ID: 23605736
Also change issuestatus IN (@StatusParameter) to
 issuestatus = @Statusparameter
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23605759
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
 

Author Comment

by:rdracer58
ID: 23605784
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
 

Author Comment

by:rdracer58
ID: 23605801
Second post by TerryAtOpus: "Error in IN Clause" error when I try to run it.
0
 

Author Comment

by:rdracer58
ID: 23605824
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23605854
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23605858
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23605889
I'm assuming @Statusparameter is passing a string like this:   'open', 'in progress', 'closed'
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23605908
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
 

Author Comment

by:rdracer58
ID: 23605912
- 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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23605984
Can you please copy and paste the query in as you currently have it?
0
 

Author Comment

by:rdracer58
ID: 23606006
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23606031
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23606040
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23606045
(Create the procedure in Management Studio, not reporting services)
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23606092
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
 
LVL 42

Expert Comment

by:dqmq
ID: 23606292
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23607793
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
 

Author Comment

by:rdracer58
ID: 23611501
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23611824
Can you try your query in Management Studio first?
0
 

Author Comment

by:rdracer58
ID: 23611921
Tried the query in management studio--same result as my prior comment.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23614725
>>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
 
LVL 42

Expert Comment

by:dqmq
ID: 23614884
>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
 

Author Comment

by:rdracer58
ID: 23615074
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23615714
>>(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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23615812
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23615816
Minor correction: "less than 276" should be "equal to or less than 276"
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23615828
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23615898
Or different server...
select * from yourserver.yourdatabase.jiraissue
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 23615901
Great idea ralmada - I have seen that happen a few times before!
0
 

Author Comment

by:rdracer58
ID: 23615912
Nope. Both app and report are accessing the correct database.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23615961
And what about the views? can you check the views that you have created in the database?
0
 

Author Comment

by:rdracer58
ID: 23615981
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
 
LVL 35

Accepted Solution

by:
Terry Woods earned 750 total points
ID: 23616072
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23623279

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
 

Author Comment

by:rdracer58
ID: 23623421
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
 
LVL 41

Expert Comment

by:ralmada
ID: 23623526

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
 

Author Comment

by:rdracer58
ID: 23623892
Nope, just five distinct projects, no projects with the same id but created on different dates.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 750 total points
ID: 23625038
Then my only advise will be to contact JIRA's technical support team and ask them how their application is reading the database.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question