Subquery problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subquery problem

Author

Topic

knichols
Starting Member

12 Posts

Posted - 2011-12-29 : 09:56:05

Hello All,

I have the following query


SELECT DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath 
from DimWorkItem DWI
inner join DimPerson DP
on DWI.System_AssignedTo__PersonSK = dp.PersonSK
inner join DimIteration DI
on DWI.IterationSK = DI.IterationSK 
and System_Id in (
select TargetWorkItemID from FactWorkItemLinkHistory    -- Links
where SourceWorkItemID in 
(SELECT DWI.System_Id FROM DimWorkItem DWI, DimIteration DI   -- All Work Items
where DWI.IterationSK = DI.IterationSK
--and DWI.System_Id in (7492, 7512, 7861, 10361,9259,7622,7654,7722,9340,10061,10071,10315,10430,10602,10629,9308)
and DWI.System_WorkItemType LIKE 'Bug'
and DWI.System_RevisedDate > '1/1/9991'
and DI.IterationPath like '\xxxxx%'
and DI.IterationPath not like '\xxxxx\(_Bug Migration)%'
and System_State not in ('Done', 'Removed'))
)
and DWI.System_WorkItemType = 'Task'
and DWI.System_RevisedDate > '1/1/9991'
and DWI.System_Title like '%Accept%'
and DWI.System_State = 'In Progress'
and DWI.System_ChangedDate > '12/1/2011'
and DWI.System_ChangedDate < '12/15/2011'

I am trying to get a total on DP.Name where Task and Accept match.

Can someone help me out?

Thanks,

Kurt

knichols
Starting Member

12 Posts

Posted - 2011-12-29 : 12:37:45

Hello again,

I now have this code:

SELECT Name, COUNT(DP.Name)
FROM DimPerson DP, DimWorkItem DWI
WHERE DWI.System_Title like '%Accept%'
(SELECT DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath
from DimWorkItem DWI
inner join DimPerson DP
on DWI.System_AssignedTo__PersonSK = dp.PersonSK
inner join DimIteration DI
on DWI.IterationSK = DI.IterationSK
and System_Id in (
select TargetWorkItemID from FactWorkItemLinkHistory -- Links
where SourceWorkItemID in
(SELECT DWI.System_Id FROM DimWorkItem DWI, DimIteration DI -- All Work Items
where DWI.IterationSK = DI.IterationSK
--and DWI.System_Id in (7492, 7512, 7861, 10361,9259,7622,7654,7722,9340,10061,10071,10315,10430,10602,10629,9308)
and DWI.System_WorkItemType LIKE 'Bug'
--and DWI.System_RevisedDate > '1/1/9991'
and DI.IterationPath like '\xxxxx%'
and DI.IterationPath not like '\xxxxx\(_Bug Migration)%'
and System_State not in ('Done', 'Removed'))
)
and DWI.System_WorkItemType = 'Task'
and DWI.System_RevisedDate > '1/1/9991'
and DWI.System_Title like '%Accept%'
and DWI.System_State = 'In Progress'
and DWI.System_ChangedDate > '12/15/2011'
and DWI.System_ChangedDate < '1/12/2012'
)

I am getting the following error message:

Msg 8120, Level 16, State 1, Line 1
Column 'DimPerson.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Yes I know the Name is a character, how do I get a subtotal on the name when it sees the Accept?

I really need some help on this thanks.

If you need further clarification please let me know.

Kurt

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-12-29 : 13:57:58

Your query is a mess:)

Provide some sample data, table structure and desired results. What you are looking to do is likely not all that difficult, but the way the query is written it will take 10+ minutes to properly figure out your code.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

knichols
Starting Member

12 Posts

Posted - 2011-12-29 : 14:41:49

Hello Vinnie,

Here is some sample data. I have to sanitize it here.

7492 Bug xxxx GS-1317: *REVIEWED2011* In nDI Project Manager, if I copy a unvalidated shell data reference to another project I wind up with bad URL - 123R Young, Mxxx M GSUSI-PTT/SIVI Committed \xxxx\G 4.0\(Release Backlog - unassigned)\(R4 Bugs)
7512 Bug JIRA GS-5132: Filtering wells with negative depth value in header is unpredictable when using multiple well managers Huang, Cxxxx GSUSI-PTT/SIVI Committed \xxxxx\G 4.0\Sprint_10\S10 - Planned
7622 Bug XXXX GS-6632: In Volume, Map and Traverse casper 4.0 (rev. 110520 6-15-11) out of Vista, the green arrow filtering icon is missing from "Surface Units" and "Elevation Units" in the Main tab of the Well Path Manager. Filtering capability is enabled for the Sanz, Wxxxx GSUSI-PTT/SIVI Committed \xxxx\G 4.0\Sprint_10\S10 - Interruptions
7654 Bug XXX GS-6844: Faults Not Visible in Horizon Intersection Display Mode Knott, Dxxxx DJ GSUSI-PTT/IIUP Committed \xxxxx\G 4.0\Sprint_10\S10 - Planned
7722 Bug xxx GS-7159: Inconsistency in the labeling of Minimum Curvature Gridding in Traverse and Map Sanz, Walter GSUSI-PTT/SIVI Committed \xxxxx\G 4.0\Sprint_10\S10 - Interruptions

I am wanting to get a total for Task for each name.

I hope this helps if you need more please let me know.

Kurt

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-12-29 : 15:29:13

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-29 : 20:09:24

quote:
Originally posted by knichols

Hello again,

I now have this code:

SELECT Name, COUNT(DP.Name)
FROM DimPerson DP, DimWorkItem DWI
WHERE DWI.System_Title like '%Accept%'...
....
....
<GARBAGE>
....
....

hnichols.

The immediate error is because your sql doesn't parse. The reason that you can't see that is because you haven't formatted it at all.

I'm just a little OCD so here is your code.


SELECT
  Name
  , COUNT(DP.Name)
FROM
  DimPerson DP
  , DimWorkItem DWI
WHERE
  DWI.System_Title like '%Accept%'


(
SELECT
  DWI.System_Id
  , DWI.System_WorkItemType
  , DWI.System_Title
  , DP.Name
  , DWI.System_State
  , DI.IterationPath 
from
  DimWorkItem DWI
  inner join DimPerson DP on
    DWI.System_AssignedTo__PersonSK = dp.PersonSK
  inner join DimIteration DI on
    DWI.IterationSK = DI.IterationSK 
    and System_Id in (
      select TargetWorkItemID
      from FactWorkItemLinkHistory    -- Links
      where SourceWorkItemID in (
        SELECT DWI.System_Id
        FROM
          DimWorkItem DWI
          , DimIteration DI   -- All Work Items
        where
          DWI.IterationSK = DI.IterationSK
          --and DWI.System_Id in (7492, 7512, 7861, 10361,9259,7622,7654,7722,9340,10061,10071,10315,10430,10602,10629,9308)
          and DWI.System_WorkItemType LIKE 'Bug'
          --and DWI.System_RevisedDate > '1/1/9991'
          and DI.IterationPath like '\xxxxx%'
          and DI.IterationPath not like '\xxxxx\(_Bug Migration)%'
          and System_State not in ('Done', 'Removed'))
        )
        and DWI.System_WorkItemType = 'Task'
        and DWI.System_RevisedDate > '1/1/9991'
        and DWI.System_Title like '%Accept%'
        and DWI.System_State = 'In Progress'
        and DWI.System_ChangedDate > '12/15/2011'
        and DWI.System_ChangedDate < '1/12/2012'
      )

As you can see it's actually 2 different statements. The first one I have marked in red.

Hopefully the error is now obvious.

As to your original question....

Please give us some sample data and expected result. and if you are going to post code. PLEASE at least clean it up. Otherwise people will not read it and will not bother to help

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources