CODEWITHSUNDEEP
sql-servert-sql
user3780248
user3780248

Reputation: 43

SQL Statement - Trying to base a where clause off of a case statement

I am trying to write a where clause based on a case statement. I am getting a multi-part identifier "k.Author" Could not be bound.

I did some research and found that the where clause is processed before the Case statement so I put the case statement in a sub Query. The Sub Query ends as K and the column ends as Author.

can someone Help me figure out how I can get this working. I only want the columns to show where K.Author = 'Coordinator 1,Referrals'.

Error 

Msg 4104, Level 16, State 1, Line 42
The multi-part identifier "k.Author" could not be bound.

SQL Query

SELECT MCON.MailHeader_DateSent,
       VP.Person_Name AS [Patient Name],
       MCON.MailHeader_Subject,
       COUNT(MCON.MailHeader_ID) AS [Inboxed Mesage]
       MTA.MailDetail_Folder,
       EP.Person_Name AS [To],
       N.Note_DateOccurred,
       CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)), --Missing a Column Alias here
       CASE WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%ashlee%' THEN 'Ashlee ' + CHAR(10) + 'Castro' --I have assumed the line break mid name is meant to be there
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%cordova%' THEN 'Adrian ' + CHAR(10) + 'Cordova'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%lyndsay%' THEN 'Lyndsay ' + CHAR(10) + 'Frommer'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Lyndsay%' THEN 'Lyndsay ' + CHAR(10) + 'Frommer'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%rivera%' THEN 'Rivera ' + CHAR(10) + 'Margaret'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Margaret%' THEN 'Rivera ' + CHAR(10) + 'Margaret'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Jtroy%' THEN 'Jennifer ' + CHAR(10) + 'Troy'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Adrian C%' THEN 'Adrian ' + CHAR(10) + 'Cordova'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Ann B%' THEN 'Ann ' + CHAR(10) + 'Burdge'
            ELSE 'N/A' 
      END AS [Referal Agent],
      (SELECT CASE WHEN CAST(CN.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Coordinator 1, ' + CHAR(10) + 'Referrals%' THEN 'Coordinator 1, Referrals' --Assumed same about line break here
                   ELSE 'Not A Referral Note'
              END AS 'Author'  
      FROM CHRT_Note CN) AS k
FROM MSG_MailHeader MH
     JOIN MSG_MailDetail MD on MCON.MailHeader_ID = MTA.MailHeader_ID
     JOIN ENTY_Person EP on MTA.Entity_ID  = EP.Entity_ID
     JOIN TASK_TaskAttachment TA on MCON.MailHeader_ID = TA.MailHeader_ID
     JOIN View_Patient VP on TA.Patient_ID = VP.Patient_ID
     JOIN CHRT_Visit CV on VP.Patient_ID = CV.Patient_ID
     JOIN CHRT_VisitCPT CVC on CV.Note_ID = CVC.Note_ID
     JOIN CHRT_OtherNote CON on TA.Patient_ID = CON.Patient_ID
     JOIN CHRT_Note N on CON.Note_ID = N.Note_ID
WHERE MTA.MailDetail_Folder = 3 
  AND MCON.MailHeader_DateSent BETWEEN '1/22/2018' AND '1/22/2018 23:59:59' 
  AND EP.Person_Name = 'Coordinator 1, Referrals'
  AND CVC.VisitCPT_Code IN  (...RemoveTA.. For example)
  AND N.Note_DateOccurred > MCON.MailHeader_DateSent
  AND k.Author = 'Coordinator 1, Referrals'
GROUP BY VP.Person_Name,
         MCON.MailHeader_DateSent,
         MTA.MailDetail_FolderP.Person_Name,
         N.Note_DateOccurred,
         MCON.MailHeader_Subject,
         CAST(N.Note_SummaryRTF AS NVARCHAR(MAX));

Upvotes: 0

Views: 64

Answers (1)

Thom A
Thom A

Reputation: 95554

OK, now that I've foratted the SQL to something readable, yes the others are right, you don't have a table called or aliased k. You do, however, have a column with an alias of k (which you won't be able to reference in the WHERE clause by alias).

The others are, again, right, use CROSS APPLY. Thus, you end up with this:

SELECT MCON.MailHeader_DateSent,
       VP.Person_Name AS [Patient Name],
       MCON.MailHeader_Subject,
       COUNT(MCON.MailHeader_ID) AS [Inboxed Mesage]
       MTA.MailDetail_Folder,
       EP.Person_Name AS [To],
       N.Note_DateOccurred,
       CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)), --Missing a Column Alias here
       CASE WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%ashlee%' THEN 'Ashlee ' + CHAR(10) + 'Castro' --I have assumed the line break mid name is meant to be there
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%cordova%' THEN 'Adrian ' + CHAR(10) + 'Cordova'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%lyndsay%' THEN 'Lyndsay ' + CHAR(10) + 'Frommer'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Lyndsay%' THEN 'Lyndsay ' + CHAR(10) + 'Frommer'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%rivera%' THEN 'Rivera ' + CHAR(10) + 'Margaret'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Margaret%' THEN 'Rivera ' + CHAR(10) + 'Margaret'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Jtroy%' THEN 'Jennifer ' + CHAR(10) + 'Troy'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Adrian C%' THEN 'Adrian ' + CHAR(10) + 'Cordova'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Ann B%' THEN 'Ann ' + CHAR(10) + 'Burdge'
            ELSE 'N/A' 
      END AS [Referal Agent],

FROM MSG_MailHeader MH
     JOIN MSG_MailDetail MD on MCON.MailHeader_ID = MTA.MailHeader_ID
     JOIN ENTY_Person EP on MTA.Entity_ID  = EP.Entity_ID
     JOIN TASK_TaskAttachment TA on MCON.MailHeader_ID = TA.MailHeader_ID
     JOIN View_Patient VP on TA.Patient_ID = VP.Patient_ID
     JOIN CHRT_Visit CV on VP.Patient_ID = CV.Patient_ID
     JOIN CHRT_VisitCPT CVC on CV.Note_ID = CVC.Note_ID
     JOIN CHRT_OtherNote CON on TA.Patient_ID = CON.Patient_ID
     JOIN CHRT_Note N on CON.Note_ID = N.Note_ID
     CROSS APPLY (SELECT CASE WHEN CAST(ca.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Coordinator 1, ' + CHAR(10) + 'Referrals%' THEN 'Coordinator 1, Referrals' --Assumed same about line break here
                              ELSE 'Not A Referral Note'
                         END AS Author
                   FROM CHRT_Note ca) AS CNca
WHERE MTA.MailDetail_Folder = 3 
  AND MCON.MailHeader_DateSent BETWEEN '1/22/2018' AND '1/22/2018 23:59:59' 
  AND EP.Person_Name = 'Coordinator 1, Referrals'
  AND CVC.VisitCPT_Code IN  (...RemoveTA.. For example)
  AND N.Note_DateOccurred > MCON.MailHeader_DateSent
  AND CNca.Author = 'Coordinator 1, Referrals'
GROUP BY VP.Person_Name,
         MCON.MailHeader_DateSent,
         MTA.MailDetail_FolderP.Person_Name,
         N.Note_DateOccurred,
         MCON.MailHeader_Subject,
         CAST(N.Note_SummaryRTF AS NVARCHAR(MAX));

Note, I have changed your aliases. When using an Alias, use something representative. Calling a table a, then the next b, the next c, etc, isn't going to help anyone. Anyone looking at your query will have to look at your WHERE clause to find out what an alias is every time they see an alias (not just one off scan). If you have 3 tables called Customer, Sale, and CustomerSale, then alias them something like C, S and CS respectively, and stick to those alias through your solution. Those aliases actually mean something.

Otherwise, Customer might be a in one query, as it's the first table in your FROM clause, but c or even e in another, because it's the 3rd or 5th.

Upvotes: 2

Related Questions