[teiid-issues] [JBoss JIRA] Commented: (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:comment-tabpanel&focusedCommentId=12563264#comment-12563264 ] 

Wanja Pernath commented on TEIID-1358:
--------------------------------------

This seems to be fixed in 7.2. -- Thanks a lot!


> 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
>
> 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