Is there any way to match to a value in an IQA LEFT JOIN sources clause?

Hi,

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.

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

You should be able to do

You should be able to do this by creating subqueries. Basically, you would take the vBoCsRelationship join and create an IQA query just for that part, i.e. an IQA query with a source of vBoCsRelationship with filters where Relationship = 'CR_PARTN' and EndDate hasn't passed yet.

You then use that IQA query as a source in your main IQA query instead of vBoCsRelationship.

Great idea - unfortunately ...

 That's a great idea - thanks for that. Unfortunately, although I can add my sub-query as a source, in the Description of the Relations to link the sources together, I am only able to select fields from the actual table sources NetContact, NetContact1 or CsRelationships (which is odd, because I deleted that source and replaced with my sub-query). Just in case it was offering CsRelationships to mean my sub-query based on CsRelationships, I tried linking to it, but it gave an error, as I'd expect, since that source didn't exist.

  Thus, unfortunately, this method, while a great idea, does not work. But thanks for the help.

Okay, option 2. Create a new

Okay, option 2.

Create a new BOD object using the same tables/columns as CsRelationship, but add a Filter Expression to the Database tab that excludes records you don't want to see. Save, publish, and use the new object as a source instead of CsRelationship.

matching the LEFT JOIN

Thank you so much, Eric Means - that has worked fine and it's what I'm using now.

Sorry for taking so long to reply, and say thank you. Your help was invaluable in solving this problem.

Best wishes for now,

AlanR.