SQL Query with multiple joins

SQL Query with multiple joins

Postby stevechown » Fri 12 Feb 2016 16:40

With the SSIS source component I am trying to run a query that uses inner joins with 3 tables. This always takes a long time as it appears unable to translate the query to SOQL. An example of this is:
Select qli.Id, q.Id,o.Id
FROM QuoteLineItem qli inner join Quote q
on qli.QuoteId = q.Id inner join Opportunity o
on q.OpportunityId = o.Id

If I do similar queries but using just the QuoteLineItem and Quote tables or just the Quote and Opportunity tables it does translate this and works well. Am I doing something wrong ?

Extract from the website documentation for Query Translation :
Two kinds of JOINs can be translated to SOQL: LEFT JOIN (or LEFT OUTER JOIN) and INNER JOIN. INNER JOIN can be used several times to connect more than two tables. However if you use LEFT JOIN, it should be the only JOIN in the statement.
stevechown
 
Posts: 3
Joined: Tue 09 Feb 2016 12:16

Re: SQL Query with multiple joins

Postby Shalex » Mon 15 Feb 2016 12:04

Thank you for your report. Indeed, your SQL-92 query is translated to 3 separate SOQL SELECT statements. So all data from 3 tables are loaded in a local SQLite database, then joins are applied (locally). We have reproduced the issue. We will notify you when it is fixed.
Shalex
Devart Team
 
Posts: 7884
Joined: Thu 14 Aug 2008 12:44

Re: SQL Query with multiple joins

Postby Shalex » Fri 26 May 2017 18:30

The bug with translating multiple joins in SQL-92 statement to one SOQL query is fixed in the new build dotConnect for Salesforce 3.2.280: https://www.devart.com/dotconnect/salesforce/revision_history.html.
Shalex
Devart Team
 
Posts: 7884
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Cloud Applications