CODEWITHSUNDEEP
sqlsql-server
David Jacobson
David Jacobson

Reputation: 179

Use 'Stuff' variable in WHERE clause

So here is the query code we are using:

SELECT 
    CONVERT(DATE, Nominations.Nomination_Date_Created) AS Nomination_Date_Created, 
    Nominations.Nomination_Status,  
    (CASE 
        WHEN MIN(EPORT.dbo.FDA_Divisions.division_name) = MAX(EPORT.dbo.FDA_Divisions.division_name) 
           THEN MIN(EPORT.dbo.FDA_Divisions.division_name) 
           ELSE 'Multiple Divisions' 
     END) AS Employee_Division, 
    Nominations.Nomination_Awarded_For, 
    Nominations.Nomination_Awarded_Other, 
    Nom.First_Name + ' ' + Nom.Last_Name AS Nominator_Name, 
    Nominations.Nomination_Group_UUID, 
    Nominations.Nomination_Group_Name, 
    Nominations.Nomination_Group_Time_off_Sum, 
    Nominations.Nomination_Group_Cash_Sum, 
    Nominations.Nomination_Type, 
    Nominations.Nomination_Identifier, Nominations.Nomination_Employee_UUID, 
    Nominations.Nomination_Nominator_ID, Nominations.Nomination_NOAC,
    STUFF((SELECT ', ' + NOMGroup.division_name  
           FROM vw_group_nomination_divisions NOMGroup
           WHERE NOMGroup.Nomination_Group_UUID = Nominations.Nomination_Group_UUID
           FOR XML PATH('')), 1, 1, '') divList          
FROM             
    Nominations 
INNER JOIN
    ePort.dbo.Employees AS Employees_1 ON Employees_1.CapHR_ID = Nominations.Nomination_Employee_CapHR_ID 
LEFT OUTER JOIN
    ePort.dbo.FDA_Offices ON Employees_1.office_id = ePort.dbo.FDA_Offices.office_id 
LEFT OUTER JOIN
    ePort.dbo.FDA_Centers ON Employees_1.center_ID = ePort.dbo.FDA_Centers.Center_ID 
LEFT OUTER JOIN
    ePort.dbo.FDA_Divisions ON Employees_1.division_id = ePort.dbo.FDA_Divisions.division_ID 
LEFT OUTER JOIN
    ePort.dbo.Employees AS Nom ON Nominations.Nomination_Nominator_ID = Nom.CapHR_ID 
LEFT OUTER JOIN
    ePort.dbo.Employees AS NomAppRTO ON Nominations.Nomination_Approving_Officer_NED_ID = NomAppRTO.CapHR_ID
GROUP BY 
    CONVERT(DATE, Nominations.Nomination_Date_Created), 
    Nominations.Nomination_Awarded_For, Nominations.Nomination_Status, 
    Nominations.Nomination_Awarded_Other, 
    Nom.First_Name + ' ' + Nom.Last_Name, 
    Nominations.Nomination_Type, Nominations.Nomination_Group_UUID, 
    Nominations.Nomination_Group_Name, 
    Nominations.Nomination_Group_Time_off_Sum, 
    Nominations.Nomination_Group_Cash_Sum, 
    Nominations.Nomination_Identifier, Nominations.Nomination_Type, 
    Nominations.Nomination_Employee_UUID, 
    Nominations.Nomination_Nominator_ID, Nominations.Nomination_NOAC
HAVING        
    (Nominations.Nomination_Type = 'Group') 
    AND (YEAR(CONVERT(DATE, Nominations.Nomination_Date_Created)) IN ('2020')) 
ORDER BY 
    Nomination_Date_Created DESC, Nominations.Nomination_Group_UUID

Output:

| Id                                   | divList           |
+--------------------------------------+-------------------+
| 3462BF9B-5056-9C58-994BFFC6A38E7368  | DLR, DTD, OHCM    |
| 3B8202C2-5056-9C58-99C591AA86B3A1C9  | OHCM              |
| CB5A722C-5056-9C58-9983C1F6C66C0AD7  | DTD, STMD         |

And the output is how we need it, however, we need to be able to search it and we cannot get that working. So how does one reference the column 'Name' that the Stuff function creates in the WHERE clause of the query?

We need to do a search within the HAVING OR WHERE clause for a value within the 'divList' column if possible. Such as divList IN ('OHCM').

Anytime I reference 'divList', I get the error: Invalid column name 'divList'.

This would filter the results to records 1 and 2.

I hope that better explains it.

Upvotes: 1

Views: 721

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You don't want the string. Use more basic logic instead:

having sum(case when division_name = 'ccc' then 1 else 0 end) > 0

Upvotes: 4

VTi
VTi

Reputation: 1319

How is your temp table associated with the view. You need to join your temp table with the nominations view.

SELECT ID,
STUFF((SELECT ', ' + NOMGroup.division_name  
       FROM vw_group_nomination_divisions NOMGroup
       WHERE NOMGroup.Nomination_Group_UUID = nom.Nomination_Group_UUID
        FOR XML PATH('')), 1, 1, '') Name
FROM temp1
JOIN vw_group_nomination_divisions nom ON temp1.ID = nom.ID
WHERE Nominations.[Name] = 'ccc'
GROUP by ID

Upvotes: 0

Related Questions