I have two NetContact Business Objects, holding firms and employees, linked via a CsRelationship Business Object. I want to first collect all the firm records in which have the relation type 'CR_PARTN', and I particularly want to keep track of firms that DO NOT have a relation of type 'CR_PARTN' (for them, the CsRelationship.Relation field will be NULL). I then want to link all of these firm records on the left-hand side to the employees in the second NetContact, matched by CsRelation. I should hopefully end up with a collection of firms linked by the relation CR_PARTN to employees and some firms with NULL relations, meaning that they do not have any CR_PARTN relationship with any employee.
I'm trying to run this SQL Query as an IQA:
SELECT DISTINCT VNC_FIRM.ID AS FIRM_ID, VNC_FIRM.FullName AS FIRM_NAME, VNC_FIRM.MemberType AS FIRM_TYPE, VNC_FIRM.FullAddress AS FIRM_ADDRESS,
VCR.Relationship, VCR.BeginDate, VCR.EndDate,
VNC_STAFF.ID AS STAFF_ID, VNC_STAFF.FullName AS STAFF_NAME, VNC_STAFF.FullAddress AS STAFF_ADDRESS
FROM dbo.vBoNetContact AS VNC_FIRM
LEFT JOIN dbo.vBoCsRelationship AS VCR
ON VNC_FIRM.ID = VCR.ID
AND VCR.Relationship = 'CR_PARTN'
AND (VCR.EndDate IS NULL OR VCR.EndDate>= CONVERT(DATETIME, CONVERT(DATETIME, GETDATE(), 103), 103))
LEFT JOIN dbo.vBoNetContact AS VNC_STAFF
ON VNC_STAFF.ID = VCR.RelatedToID
WHERE VNC_FIRM.MemberType IN ('SLFI', 'MNP', 'SLFP')
AND VNC_FIRM.Status = 'A'
AND VNC_FIRM.IsCompany = 1
AND (VNC_STAFF.IsCompany = 0 OR VNC_STAFF.IsCompany IS NULL)
ORDER BY FIRM_ID ASC
This query gives the correct results in SQL, but I can't translate it into IQA because you can't have a value as one of the conditions in a LEFT JOIN on sources in an IQA. I have tried moving the test for the 'CR_PARTN' value into the WHERE clause (because I could then replicate the modified query in IQA), but if I do so, all the firms where there is no relation of type CR_PARTN are removed.
Does anyone have any ideas how I could reproduce this SQL query as an IQA?
Thanks for any help you can give.