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'

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

Hello Eduardo,

I think Christian means the gs.* part.

Best wishes,
Markus

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

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

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

Thanks for the workaround solution
Has it something to do with, that both tables “Items” and “Customers” has a field called “GroupCode”?