Is an "Enforced Both Inner Join" type supported by the SOTAMAS90 ODBC DSN?

I seem to recall a Sage KB that indicated there were only 3 Join Types supported by Sage (MAS, back in the day). Inner Join, Left Outer and something else. I don't recall hearing about the "Enforced" setting. I ask because we have a client who are very sharp and have created many new report and report settings. In this case they are linking in standard table into the work table using this join (which I have never before seen):

I can tell you that this report in its current state takes over 40 minutes to run and I think it is not running quick enough for Task Scheduler to send it to Microsoft's Print to PDF printer. Does that make sense?

TIA

  • Is it not possible to create a UDF for the BOM info?

  • in reply to BigLouie

    If not work table UDF's, a sub-report might be worth a try.

  • Hi Cullen, I've never used any of the enforcement options other than "not enforced"

    From SAP Help

    Using the various join enforcement options can ensure that linked tables are included in the SQL query, even when none of the fields in the table are used in the report.

    How long does the report without the additional table take to run?   Without seeing the RPT,  I'd follow the advice of BL and Kevin add a UDF if the data needed from BM_BillHeader is just a couple columns or try changing the enforcement to "not enforced" validate the results and compare performance.

    Thanks John

  • in reply to Kevin M

    Hey BL & KM,

    Both great suggestions, thanks - and tools in my toolbelt that I frequently use. However in this case I am not being asked to troubleshoot the report itself (yet), nor did I have a hand in creating it - so at this point I'm really just trying to gather info re this odd (IMHO) join type.

    I am also hoping to get some feedback on whether Task Scheduler might have a timeout feature for a report that takes a long time to run and send to PDF.

    If and when I'm asked to look deeper into the nuts and bolts of the report I will definitely try your suggestions. In fact, I just pulled this report over to take a look at and there don't appear to be any fields from BM_BillHeader anywhere on the report. Curiouser and curiouser..

  • in reply to rclowe

    I believe the enforced link is like a filter for items that have bills.  Without that option you'd have to put a filter for BillNo not being NULL (so the work table is generating data not being displayed... filtered by the enforced link). 

    A checkbox in items for "has a bill" should add an option in the Selection list and then Sage has to do less work in generating the report data.

  • in reply to jcnichols

    Thanks John,

    I found similar info on a different site. However I still don't really understand it: "Enforced Both: When you select this option, if either the "from" table or the "to" table for this link is used, the link is enforced."

    I may get a chance to run more tests on the report, and if so I will try your suggestion.

    Follow up question: is it possible there is a timeout feature in Task Scheduler, Deferred Printing and/or Microsoft Print to PDF that is coming into play here?

    Thanks again,
    Cullen

  • in reply to rclowe

    You can't print to PDF in a scheduled task because of the file name prompt.

  • in reply to Kevin M

    That makes sense, thanks again Kevin.

  • in reply to rclowe

    From my understanding of the join enforcement, if you have two tables without the join enforced and your report doesn't contain fields from table 2, then the report will not enforce the join to table 2 so you effectively only have table one being read.  Having it set to Both means even if you don't reference any fields from table 2, then the join is still used so it will expect a match between both tables depending on the join type (inner, left, etc).  I believe you should be able to put this to the test by viewing the SQL statement that crystal reports generates by playing with the different enforcement options and including/excluding fields from table 2 in-between refreshing the report. 

  • in reply to David Speck

    Here are some examples.

    With the following tables in a report.

    If the join is not enforced as seen below.

    Then if I only add the ItemCode field from CI_Item into the report and view the SQL query, I get the following.

    Now if I change the enforcement of the join to Both while still only having the ItemCode field from CI_Item in the report, the SQL query looks like this.