SQL Error - "Field name cannot be blank"

Question

We are trying to run the following query:

SELECT
a.nihrm__BookingContact__c as "BOOKING_CONTACT",
a.Name as "ACCOUNT_NAME",
b.Email as "EMAIL",
b.FirstName as "FIRST_NAME",
b.LastName as "LAST_NAME",
c.FirstName as "BOOKING_OWNER_FIRST_NAME",
c.LastName as "BOOKING_OWNER_LAST_NAME",
FROM nihrm__Booking__c_Salesforce a
JOIN Contact_Salesforce b ON a.Id = b.Id
JOIN User_Salesforce c ON a.Id = c.Id
WHERE
a.nihrm__BookingStatus__c = ‘Definite’ AND
a.nihrm__BookingTypeName__c = ‘Group’ AND
a.nihrm__DepartureDate__c >= dateadd(day,datediff(day,1,GETDATE,0))
a.Id NOT IN (SELECT Id FROM nihrm__Booking__c_Salesforce WHERE Date > dateadd(day,datediff(day,),GETDATE,0))

But are continuously getting a new error every time we are able to solve the current one. The most recent error we keep getting is "Field Name cannot be blank". We have tried ensuring the attributes in the DE that was created for this automation are not nullable and that didn't work so we edited them to nullable and we still got the same error.

Any help would be appreciated!

Thank you.

Answer 1

I think there are multiple errors within your query.

/* 1) After the following line you're missing an "AND" */
a.nihrm__DepartureDate__c >= dateadd(day,datediff(day,1,GETDATE,0))

/* 2) Your datediff is missing a paramater at the following line. */
dateadd(day,datediff(day,),GETDATE,0)

/* 3) You've an extra comma at the following line (after day,) , since its your last select you can get rid of it */ 
c.LastName as "BOOKING_OWNER_LAST_NAME",

NEW ANSWER BASED ON COMMENTS

I've adjusted the code a few times more, there was still an issue in your last SELECT statement with the functionality of the dateadd. I've also removed all the double quotes from the SELECT aaa AS xxxx statements since thats not mandatory. Also adjusted the GETDATE -> GETDATE() since its an function, not just a variable. I believe syntax wise it should be fine, however i'm in doubt what you're trying to achieve with the joins on the Contact_Salesforce & User_Salesforce both on the nihrm_booking__c_Salesforce dataextension. As far as my Salesforce knowledge I believe the Contact ID & User ID are never identical (correct me if im wrong). But you're doing both a JOIN on both tables. So even after the syntax errors are gone i assume you won't retrieve any results.

SELECT 
a.nihrm__BookingContact__c as BOOKING_CONTACT, 
a.Name as ACCOUNT_NAME, 
b.Email as EMAIL, 
b.FirstName as FIRST_NAME, 
b.LastName as LAST_NAME, 
c.FirstName as BOOKING_OWNER_FIRST_NAME, 
c.LastName as BOOKING_OWNER_LAST_NAME 
FROM nihrm__Booking__c_Salesforce a 
JOIN Contact_Salesforce b ON a.Id = b.Id 
JOIN User_Salesforce c ON a.Id = c.Id 
WHERE a.nihrm__BookingStatus__c = 'Definite' AND 
a.nihrm__BookingTypeName__c = 'Group' AND
a.nihrm__DepartureDate__c >= dateadd(day,datediff(day,1,GETDATE(),0)) AND 
a.Id NOT IN (
  SELECT Id FROM nihrm__Booking__c_Salesforce WHERE Date > dateadd(day,datediff(day,1,GETDATE(),0)
)