Home » blog » alanr's blog

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

Posted October 16th, 2009 by alanr

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.

 

alanr's blog | login or register to post comments | printer friendly version

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

On October 19th, 2009 Eric Means says:

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.

login or register to post comments

Great idea - unfortunately ...

On October 21st, 2009 alanr says:

 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.

login or register to post comments

Okay, option 2. Create a new

On October 21st, 2009 Eric Means says:

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.

login or register to post comments

matching the LEFT JOIN

On November 11th, 2009 alanr says:

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.

login or register to post comments