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
3
  • So i tried a few things. I updated my question above.
    – eazy4me
    Nov 29, 2018 at 11:48
  • is the column a part of Placement__c_Salesforce ?
    – 0xsegfault
    Nov 29, 2018 at 12:25
  • Hi Data_kid, yes Actual_End_Date__c is part of Placement__c_Salesforce.
    – eazy4me
    Nov 29, 2018 at 12:28

1 Answer 1

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
1
  • Thank you Adam! that did the job. And i learned a thing or two :) Thanks for the explanation as well.
    – eazy4me
    Nov 29, 2018 at 13:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .