?
Solved

SQL Server 2005  Group by on Subselect in Case

Posted on 2008-11-17
2
Medium Priority
?
1,528 Views
Last Modified: 2012-05-05
Hi Experts,
I have a group by  that has a subquery in a case statement.  The select runs buts does not do a "final" group by.  Thanks!

Result now:
NUMBER       [User Interface-2]          [Doc ID-4]
----------          ------------------ --------------------
898G6A3292                  NULL             43100328
898G6A3292            SAP GUI                     NULL  

Desired result:
NUMBER          [User Interface-2]          [Doc ID-4]
----------              ------------------ --------------------
898G6A3292                SAP GUI           43100328

SELECT * 
FROM (
 
SELECT  INCIDENTS.NUMBER
 
		,CASE INCIDENT_CUSTOM_FIELDS.ID_FIELD WHEN 2 THEN 
		(SELECT ICF.DATA
		FROM INCIDENT_CUSTOM_FIELDS ICF
		WHERE ICF.ID_INCIDENT = INCIDENT_CUSTOM_FIELDS.ID_INCIDENT
		AND ICF.ID_FIELD = INCIDENT_CUSTOM_FIELDS.ID_FIELD)  ELSE '' END AS [User Interface-2]  
 
		,CASE INCIDENT_CUSTOM_FIELDS.ID_FIELD WHEN 4 THEN  
		(SELECT ICF.DATA
		FROM INCIDENT_CUSTOM_FIELDS ICF
		WHERE ICF.ID_INCIDENT = INCIDENT_CUSTOM_FIELDS.ID_INCIDENT
		AND ICF.ID_FIELD = INCIDENT_CUSTOM_FIELDS.ID_FIELD)  ELSE '' END  AS [Doc ID-4]   
FROM    PROBLEM_RELATED_INCIDENTS INNER JOIN
        INCIDENTS ON PROBLEM_RELATED_INCIDENTS.ID_INCIDENT = INCIDENTS.ID INNER JOIN
        INCIDENT_CUSTOM_FIELDS ON INCIDENTS.ID = INCIDENT_CUSTOM_FIELDS.ID_INCIDENT INNER JOIN
        CUSTOM_FIELD_DEFS ON INCIDENT_CUSTOM_FIELDS.ID_FIELD = CUSTOM_FIELD_DEFS.ID
WHERE   (PROBLEM_RELATED_INCIDENTS.ID_PROBLEM = 42)
GROUP BY INCIDENTS.NUMBER,INCIDENT_CUSTOM_FIELDS.INCIDENT_NUMBER,
         INCIDENTS.ID,INCIDENT_CUSTOM_FIELDS.ID_INCIDENT
        ,INCIDENT_CUSTOM_FIELDS.ID_FIELD
 ) temp
 
 GROUP BY temp.NUMBER,temp.[User Interface-2],temp.[Doc ID-4]

Open in new window

0
Comment
Question by:Aveeightus
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 22976468
select NUMBER         ,max([User Interface-2]    ), max(   [Doc ID-4])

from (
SELECT *
FROM (
 
SELECT  INCIDENTS.NUMBER
 
            ,CASE INCIDENT_CUSTOM_FIELDS.ID_FIELD WHEN 2 THEN
            (SELECT ICF.DATA
            FROM INCIDENT_CUSTOM_FIELDS ICF
            WHERE ICF.ID_INCIDENT = INCIDENT_CUSTOM_FIELDS.ID_INCIDENT
            AND ICF.ID_FIELD = INCIDENT_CUSTOM_FIELDS.ID_FIELD)  ELSE '' END AS [User Interface-2]  
 
            ,CASE INCIDENT_CUSTOM_FIELDS.ID_FIELD WHEN 4 THEN  
            (SELECT ICF.DATA
            FROM INCIDENT_CUSTOM_FIELDS ICF
            WHERE ICF.ID_INCIDENT = INCIDENT_CUSTOM_FIELDS.ID_INCIDENT
            AND ICF.ID_FIELD = INCIDENT_CUSTOM_FIELDS.ID_FIELD)  ELSE '' END  AS [Doc ID-4]  
FROM    PROBLEM_RELATED_INCIDENTS INNER JOIN
        INCIDENTS ON PROBLEM_RELATED_INCIDENTS.ID_INCIDENT = INCIDENTS.ID INNER JOIN
        INCIDENT_CUSTOM_FIELDS ON INCIDENTS.ID = INCIDENT_CUSTOM_FIELDS.ID_INCIDENT INNER JOIN
        CUSTOM_FIELD_DEFS ON INCIDENT_CUSTOM_FIELDS.ID_FIELD = CUSTOM_FIELD_DEFS.ID
WHERE   (PROBLEM_RELATED_INCIDENTS.ID_PROBLEM = 42)
GROUP BY INCIDENTS.NUMBER,INCIDENT_CUSTOM_FIELDS.INCIDENT_NUMBER,
         INCIDENTS.ID,INCIDENT_CUSTOM_FIELDS.ID_INCIDENT
        ,INCIDENT_CUSTOM_FIELDS.ID_FIELD
 ) temp
 
 GROUP BY temp.NUMBER,temp.[User Interface-2],temp.[Doc ID-4]
) t
group by NUMBER          
0
 
LVL 3

Author Closing Comment

by:Aveeightus
ID: 31517462
Thanks!!!   I see my error.  I did not give the GROUP BY a 'handle' on the columns I needed to 'compress'.  Simply create a SELECT shell and Group By on the single returned  value.  
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

807 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