[teiid-issues] [JBoss JIRA] Closed: (TEIID-1358) Getting wrong results when using JOINs in a query

[Wanja Pernath (JIRA)]

     [ https://jira.jboss.org/browse/TEIID-1358?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Wanja Pernath closed TEIID-1358.
--------------------------------

    Fix Version/s: 7.2
       Resolution: Done


Has already been fixed in 7.2

> Getting wrong results when using JOINs in a query
> -------------------------------------------------
>
>                 Key: TEIID-1358
>                 URL: https://jira.jboss.org/browse/TEIID-1358
>             Project: Teiid
>          Issue Type: Bug
>          Components: Salesforce Connector
>    Affects Versions: 7.1
>         Environment: Fedora 12 64bit, Java 1.6.0_21 64bit
> Teiid-7.1 is running on a fresh EAP 5.1 instance with 1.5GB of RAM.
>            Reporter: Wanja Pernath
>            Assignee: Steven Hawkins
>             Fix For: 7.2
>
>
> I am getting completely wrong results if I am using a JOIN in my SELECT statement:
> select o.Id,
> 	   o.Name, 
> 	   o.Amount, 
> 	   o.StageName, 
> 	   o.ForecastCategory
> from 	general.salesforce.Opportunity o
> where 1=1
> and   o.Fiscal = '2011 3'
> and   o.OwnerId = (select Id from general.salesforce.User where Name = '****') --> real name
> ;
> This statement returns 36 lines. I would now like to also get OpportunityLineItem(s) to this Opportunities, so I am adding a join
> select o.Id,
> 	   (select Name from general.salesforce.User where id = o.OwnerId) as OpportunityOwner,
> 	   (select Name from general.salesforce.Account where id = o.AccountId) as AccountName,
> 	   o.Name, 
> 	   o.Amount, 
> 	   o.StageName, 
> 	   o.ForecastCategory
> from general.salesforce.OpportunityLineItem oli
> LEFT INNER general.salesforce.Opportunity o ON oli.OpportunityId = o.id
> where 1=1
> and   o.Fiscal = '2011 3'
> and   o.OwnerId = (select Id from general.salesforce.User where Name = '****') --> same name as above
> ;
> Now I do not get any results back.
> However, one may think that there is no OpportunityLineItem for those Opportunities above. But if I am querying just OpportunityLineItems of one Opp above, I do get many of them:
> select * from general.salesforce.OpportunityLineItem oli where OpportunityId = '0066000000DoIHaAAN';
> Maybe I am missing something here?
> Thanks for any help. 
> Cheers,
>   Wanja

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira