Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 7 of 7 total |
SQL JOIN issue |
Sun, Apr 27 2014 11:19 AM | Permanent Link |
Christian Sanggaard Admind A/S | Hi
There is something that surprised me. Why returned no record of GLPostingSetups in the following SQL statement. I have enclosed data. Best Regards Christian select w.sessionid, w.custno, cu.groupcode, cu.name , wo.itemno,i.groupcode, gs.* from weborders w left join weborderbasket wo on wo.sessionid = w.sessionid left join customers cu on cu.no = w.custno left join items i on i.no = wo.itemno LEFT JOIN glpostingsetups gs on gs.ItemGrpCode=i.Groupcode and gs.Type=0 and gs.CustGrpCode =cu.GroupCode where w.sessionid = '0000002014042515353927781' and w.CustNo = '0053638887' Attachments: Data.zip |
Mon, Apr 28 2014 5:58 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Christian Sanggaard
<< Why returned no record of GLPostingSetups in the following SQL statement. >> I have tried the SQL with your data and it results one record and not an empty dataset. What version of DBISAM are you using ? I am using 4.37 build 1. Eduardo |
Mon, Apr 28 2014 6:06 AM | Permanent Link |
Markus Gnam | Hello Eduardo,
I think Christian means the gs.* part. Best wishes, Markus |
Mon, Apr 28 2014 9:52 AM | Permanent Link |
Raul Team Elevate | On 4/27/2014 11:19 AM, Christian Sanggaard wrote:
> Hi > > There is something that surprised me. > Why returned no record of GLPostingSetups in the following SQL statement. > Definitely curious. If you remove the gs.Type=0 clause then the record returned does contain data from glpostingsetups but with Type = 1. This explains the no records in the original query. If you remove the glpostingsetups from query though and just look at the join condition values remaining (cu.groupcode'INDLAND' and i.groupcode='100USA') then those do have a matching record in the glpostingsetups with type=0. I wonder if it's the double join condition on the glpostingsetups (from both items and customers) but i'd open a support case with ElevateSoft and get Tim to comment on whether this a limitation or bug. Raul |
Mon, Apr 28 2014 12:13 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Christian,
<< There is something that surprised me. Why returned no record of GLPostingSetups in the following SQL statement. >> It's a bug. As a workaround, switch the customers and items join conditions around: Original: select w.sessionid, w.custno, cu.groupcode, cu.name , wo.itemno,i.groupcode, gs.* from weborders w left join weborderbasket wo on wo.sessionid = w.sessionid left join customers cu on cu.no = w.custno left join items i on i.no = wo.itemno LEFT JOIN glpostingsetups gs on gs.ItemGrpCode=i.Groupcode and gs.Type=0 and gs.CustGrpCode =cu.GroupCode where w.sessionid = '0000002014042515353927781' and w.CustNo = '0053638887' Switched: select w.sessionid, w.custno, cu.groupcode, cu.name , wo.itemno,i.groupcode, gs.* from weborders w left join weborderbasket wo on wo.sessionid = w.sessionid left join items i on i.no = wo.itemno left join customers cu on cu.no = w.custno LEFT JOIN glpostingsetups gs on gs.ItemGrpCode=i.Groupcode and gs.Type=0 and gs.CustGrpCode =cu.GroupCode where w.sessionid = '0000002014042515353927781' and w.CustNo = '0053638887' Tim Young Elevate Software www.elevatesoft.com |
Mon, Apr 28 2014 12:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Christian,
Looking into this further, I may not be able to fix this in DBISAM. There is an architectural issue with how DBISAM stores the joins, and it has issues with representing joins that are like your GLPostingSetups join. The problem is that it has to pick a source table to use for driving the join during the nested loops, and so it has to pick the customers or items table as that source table. However, picking one over the other is a problem, since the data may require (and this would probably be pretty common) that *both* source tables be evaluated first before the join is evaluated further, and this is where DBISAM has an issue. I'll be looking at this again before B3 is released, but the end solution may just be the workaround that I mentioned in my previous email. And, for the record, ElevateDB does *not* have this issue - it represents joins internally in a completely different manner. Tim Young Elevate Software www.elevatesoft.com |
Sun, May 18 2014 7:44 AM | Permanent Link |
Christian Sanggaard Admind A/S | Thanks for the workaround solution
Has it something to do with, that both tables “Items” and “Customers” has a field called “GroupCode”? |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |