I created a Data Foundation (DF) in Business View Manager (BVM). Then added a command which is the subquery below (to get max ServiceID where BonusType is NULL. Then added left join from the Appoinment table to the command on the AppointmentID field. Trying to accomplish in the DF what is being done in the following SQL:
SELECT DISTINCT (Customer.LName + '', '' + Customer.FName) AS CustName, Customer.CustomerID, Appointment.Date, Service.BilledOn, Service.Units,
FROM Appointment
LEFT JOIN Service ON Appointment.AppointmentID = Service.AppointmentID AND ServiceID IN
(SELECT MAX(ServiceID) AS ServiceID
FROM Appointment APID
INNER JOIN Service S ON S.AppointmentID = Appointment.AppointmentID
WHERE Appointment.AppointmentID = APID.AppointmentID AND S.BonusType IS NULL
GROUP BY Appointment.AppointmentID)
INNER JOIN Customer ON Appointment.CustomerID = Customer.CustomerID
ORDER BY S
The command produces the desired result when run on its own and when joined to the Appoinment table in Crystal Reports. However, same table/command/join in the DF returns all AppointmentIDs even if BonusType is null.
Any direction is appreciated.
SELECT DISTINCT (Customer.LName + '', '' + Customer.FName) AS CustName, Customer.CustomerID, Appointment.Date, Service.BilledOn, Service.Units,
FROM Appointment
LEFT JOIN Service ON Appointment.AppointmentID = Service.AppointmentID AND ServiceID IN
(SELECT MAX(ServiceID) AS ServiceID
FROM Appointment APID
INNER JOIN Service S ON S.AppointmentID = Appointment.AppointmentID
WHERE Appointment.AppointmentID = APID.AppointmentID AND S.BonusType IS NULL
GROUP BY Appointment.AppointmentID)
INNER JOIN Customer ON Appointment.CustomerID = Customer.CustomerID
ORDER BY S
The command produces the desired result when run on its own and when joined to the Appoinment table in Crystal Reports. However, same table/command/join in the DF returns all AppointmentIDs even if BonusType is null.
Any direction is appreciated.
