1

I try to de-deduplicate my SQL Query but it gives this error:

An error occurred while checking the query syntax. Errors: An error occurred while checking the query syntax. Errors: Invalid column name 'Actual_End_Date__c'.

The field actually IS a valid column name. The error refers to this line:

x.Actual_End_Date__c AS ActualEndDate

My Query:

SELECT 

x.id as Id
, x.Id as ContactId
, x.firstname as FirstName
, x.lastname as LastName
, x.Email as Email
, x.Actual_End_Date__c AS ActualEndDate


FROM 

(SELECT 
placement.id AS Id
, contact.Id AS ContactId
, contact.firstname AS FirstName
, contact.lastname AS LastName
, contact.Email AS Email
, placement.Actual_End_Date__c AS ActualEndDate
, ROW_NUMBER()OVER(PARTITION BY placement.candidate__c ORDER BY placement.Actual_End_Date__c DESC) AS rank 
FROM [Placement__c_Salesforce] placement 
        LEFT JOIN [contact_salesforce] contact ON contact.id = placement.candidate__c
        LEFT JOIN [Privacy_Settings__c_Salesforce] optin ON optin.contact__c = contact.id

WHERE placement.status__c = 'completed'        
AND contact.special_status__c is NULL
AND optin.Opt_in_for_Survey_emails__c = 'True'
AND datediff(m,placement.Actual_End_Date__c,GetUTCDate()) > -11) x


WHERE x.rank = 1
  • So i tried a few things. I updated my question above. – wfrankhuizen Nov 29 '18 at 11:48
  • is the column a part of Placement__c_Salesforce ? – Samuel Dare Nov 29 '18 at 12:25
  • Hi Data_kid, yes Actual_End_Date__c is part of Placement__c_Salesforce. – wfrankhuizen Nov 29 '18 at 12:28
3

I think you were referencing the inner column names incorrectly in the outer select. Columns in the outer query don't need to be re-aliased -- they'll be what aliases you've assigned inside.

SELECT 
  x.id
, x.ContactId
, x.firstname
, x.lastname
, x.Email
, x.ActualEndDate
FROM 
(
    SELECT 
      placement.id
    , contact.Id AS ContactId
    , contact.firstname 
    , contact.lastname
    , contact.Email 
    , placement.Actual_End_Date__c AS ActualEndDate
    , ROW_NUMBER() OVER (PARTITION BY placement.candidate__c ORDER BY placement.Actual_End_Date__c DESC) AS rank 
    FROM [Placement__c_Salesforce] placement 
    LEFT JOIN [contact_salesforce] contact ON contact.id = placement.candidate__c
    LEFT JOIN [Privacy_Settings__c_Salesforce] optin ON optin.contact__c = contact.id
    WHERE placement.status__c = 'completed'        
    AND contact.special_status__c is NULL
    AND optin.Opt_in_for_Survey_emails__c = 'True'
    AND datediff(m,placement.Actual_End_Date__c,GetUTCDate()) > -11
) x
WHERE x.rank = 1
| improve this answer | |
  • Thank you Adam! that did the job. And i learned a thing or two :) Thanks for the explanation as well. – wfrankhuizen Nov 29 '18 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.