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'
  • It should be o.department since you are alias department__c as department – EazyE Jan 6 at 22:21
  • Hi Eazy I get an unbound error if I make the make it MAX(o.Department__c) as "Department" – Cameron Jan 6 at 22:57
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'
| improve this answer | |
  • Thank you so much! It worked perfectly! Sorry I didn't understand your comment. – Cameron Jan 6 at 23:21

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.