asked on

SQL Server 2005 Group by on Subselect in Case

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

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aveeightus

ASKER

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.