0

After I added the Group by statement I got an error that now is saying the column name is invalid, but it does match with the table. Any other ideas of what might be causing the error?

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

SELECT a.PersonContactId as "ContactId",a.PersonEmail as "Email",a.FirstName 
FROM
[Account_Salesforce] a
INNER JOIN (
        SELECT ContactID__c, MAX(Department__c) as "Department"
        FROM [OpportunityLineItem_Salesforce]
        GROUP BY ContactID__c) o 
ON o.ContactID__c = a.PersonContactId  
AND a.PersonHasOptedOutOfEmail = 'FALSE'
AND NOT(ISNULL(a.FirstName,'')='') 
AND NOT(ISNULL(a.PersonEmail,'')='')
AND NOT(ISNULL(o.Department__c,'')='')
AND o.Department__c = 'WEIGHT LOSS MEDICATION'
2
  • It should be o.department since you are alias department__c as department
    – EazyE
    Jan 6, 2020 at 22:21
  • Hi Eazy I get an unbound error if I make the make it MAX(o.Department__c) as "Department"
    – Cameron K
    Jan 6, 2020 at 22:57

1 Answer 1

3

The issue is your o table fields.

Change o.department__c to o.department

SELECT a.PersonContactId as "ContactId",a.PersonEmail as "Email",a.FirstName 
FROM
[Account_Salesforce] a
INNER JOIN (
        SELECT ContactID__c, MAX(Department__c) as "Department"
        FROM [OpportunityLineItem_Salesforce]
        GROUP BY ContactID__c) o 
ON o.ContactID__c = a.PersonContactId  
AND a.PersonHasOptedOutOfEmail = 'FALSE'
AND NOT(ISNULL(a.FirstName,'')='') 
AND NOT(ISNULL(a.PersonEmail,'')='')
AND NOT(ISNULL(o.Department,'')='')
AND o.Department = 'WEIGHT LOSS MEDICATION'
1
  • Thank you so much! It worked perfectly! Sorry I didn't understand your comment.
    – Cameron K
    Jan 6, 2020 at 23:21

You must log in to answer this question.

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