CODEWITHSUNDEEP
sqlsql-servercase
WixLove
WixLove

Reputation: 73

Case When Exists query not working

I am trying this in Microsoft SQL Server:

SELECT DISTINCT 
    a.InteractionID, 
    a.Program, 
    a.[Client Name],
    CASE 
        WHEN EXISTS (
            SELECT * 
            FROM [Interaction Behaviors] 
            JOIN Behaviors 
                ON [Interaction Behaviors].BehaviorID = Behaviors.BehaviorID
            JOIN Interactions
                ON Interactions.InteractionID = [Interaction Behaviors].InteractionID 
            WHERE 
                [Interaction Behaviors].InteractionID = Interactions.InteractionID 
                AND Behaviors.BehaviorID = 1
            ) 
                THEN 1 
        ELSE 0 
        END AS 'Alcohol/Drug Use By Client',
    CASE 
        WHEN EXISTS (
            SELECT * 
            FROM [Interaction Behaviors] JOIN 
            Behaviors 
                ON [Interaction Behaviors].BehaviorID = Behaviors.BehaviorID 
            JOIN Interactions 
                ON Interactions.InteractionID = [Interaction Behaviors].InteractionID 
            WHERE 
                [Interaction Behaviors].InteractionID = Interactions.InteractionID
                AND Behaviors.BehaviorID = 5
            ) 
                THEN Behavior 
        ELSE '' 
    END AS 'Allegation--Text', 
    c.InterventionID,
    d.Intervention ASIntervention_Name  
FROM (
    (
        [Interactions Query] AS a
        INNER JOIN  [Interaction Interventions] AS c 
            ON a.InteractionID = c.InteractionID
    )
LEFT JOIN Interventions AS d 
    ON c.InterventionID = d.InterventionID

The first Case statement executes fine.

The problem is in the second Case statement where I don't just want a 1 or 0 but the actual text.

For that one, I get an error message:

Msg 207, Level 16, State 1, Line 19 Invalid column name 'Behavior'

Behavior is a column in the [Interaction Behaviors] table.

I understand that I need to explicitly join the [Interaction Behaviors] table in the from statement. However, doing so gives me odd duplicated rows so I need to take care of that in the Case.

Thanks for time!

Upvotes: 0

Views: 626

Answers (1)

Gokuldas.Palapatta
Gokuldas.Palapatta

Reputation: 72

Replace Behavior with the below code

                                (    SELECT Behavior  
                                FROM [Interaction Behaviors] JOIN 
                                Behaviors 
                                ON [Interaction Behaviors].BehaviorID = Behaviors.BehaviorID 
                                JOIN Interactions 
                                ON Interactions.InteractionID = [Interaction Behaviors].InteractionID 
                                WHERE 
                                [Interaction Behaviors].InteractionID = Interactions.InteractionID
                                AND Behaviors.BehaviorID = 5
                                )

Upvotes: 1

Related Questions