{oj and data return

[djj55]

Hello, I am using XI however I have a report converted from 8.5. With the converted report I get 52 records returned, if I build a new report I get 1833 records. No errors are returned from the 8.5 conversion reports. The only thing I can see as different is the {oj ... }.
Yes I have checked the selection formula and the sql. I even updated the link when I converted the 8.5 so the underlying database location uses the same connection.

This is going to a Progress database that is the backend to a software package. We do not have Progress with a direct interface.

1) What is going on with the the {oj?
2) Is this something we can simulate for testing?
Thank you for any words of wisdom,
djj

 

[lbass]

Sounds like an outer join in 8.5 got converted to an inner join when you converted to XI.

-LB

 

[djj55]

The problem is that I have two reports in XI. One is a converted report that uses the {oj and one is a new report without the {oj.

SELECT DISTINCT "Part1"."PartNum", "Part1"."InActive", "Part1"."OnHold", 
    "Part1"."NonStock", "Vendor1"."VendorID", "VendPart1"."BaseUnitPrice", 
    "PartWhse1"."OnHandQty", "PartWhse1"."PartNum", "Part1"."IUM", 
    "PartPlant1"."MinimumQty", "PartWhse1"."WarehouseCode", 
    "Vendor1"."PurPoint", "PartCost1"."AvgMaterialCost", "PartPlant1"."MaximumQty"
FROM  {oj (((("PUB"."Part" "Part1" 
INNER JOIN "PUB"."VendPart" "VendPart1" 
ON ("Part1"."Company"="VendPart1"."Company") AND ("Part1"."PartNum"="VendPart1"."PartNum")) 
INNER JOIN "PUB"."PartPlant" "PartPlant1" 
ON ("Part1"."Company"="PartPlant1"."Company") AND ("Part1"."PartNum"="PartPlant1"."PartNum")) 
INNER JOIN "PUB"."PartWhse" "PartWhse1" 
ON ("Part1"."PartNum"="PartWhse1"."PartNum") AND ("Part1"."Company"="PartWhse1"."Company")) 
INNER JOIN "PUB"."PartCost" "PartCost1" 
ON ("Part1"."Company"="PartCost1"."Company") AND ("Part1"."PartNum"="PartCost1"."PartNum")) 
INNER JOIN "PUB"."Vendor" "Vendor1" 
ON ("VendPart1"."Company"="Vendor1"."Company") AND ("VendPart1"."VendorNum"="Vendor1"."VendorNum")}
WHERE  "Part1"."OnHold"=0 
    AND "Part1"."InActive"=0 
    AND "Part1"."PartNum" LIKE 'mro-%' 
    AND "Vendor1"."VendorID" IS  NOT  NULL  
    AND "Part1"."NonStock"=0
ORDER BY "Vendor1"."VendorID", "Part1"."PartNum"

versus

SELECT DISTINCT "Part1"."Company", "Part1"."InActive", "Part1"."OnHold", 
    "Part1"."NonStock", "Part1"."PartNum", "Vendor1"."VendorID", 
    "PartWhse1"."OnHandQty", "Vendor1"."PurPoint", "PartCost1"."AvgMaterialCost", 
    "PartPlant1"."MaximumQty"
FROM (((("PUB"."Part" "Part1" 
INNER JOIN "PUB"."PartCost" "PartCost1" 
ON ("Part1"."Company"="PartCost1"."Company") AND ("Part1"."PartNum"="PartCost1"."PartNum")) 
INNER JOIN "PUB"."PartPlant" "PartPlant1" 
ON ("Part1"."Company"="PartPlant1"."Company") AND ("Part1"."PartNum"="PartPlant1"."PartNum")) 
INNER JOIN "PUB"."PartWhse" "PartWhse1" 
ON ("Part1"."Company"="PartWhse1"."Company") AND ("Part1"."PartNum"="PartWhse1"."PartNum")) 
INNER JOIN "PUB"."VendPart" "VendPart1" 
ON ("Part1"."Company"="VendPart1"."Company") AND ("Part1"."PartNum"="VendPart1"."PartNum")) 
INNER JOIN "PUB"."Vendor" "Vendor1" 
ON ("VendPart1"."Company"="Vendor1"."Company") AND ("VendPart1"."VendorNum"="Vendor1"."VendorNum")
WHERE  "Part1"."Company"='CYT' 
    AND "Part1"."InActive"=0 
    AND "Part1"."OnHold"=0 
    AND "Part1"."NonStock"=0 
    AND "Part1"."PartNum" LIKE 'MRO-%' 
    AND "Vendor1"."VendorID" IS  NOT  NULL  
ORDER BY "Part1"."PartNum", "Vendor1"."VendorID"

 

[lbass]

These reports are not the same. You have not selected a field from the VendPartI table in the second report. If no fields are used in a report, the table join is not activated, unless in XI you use the link option->enforce join. I usually check "enforce both" if I want a table to limit records without using a field from it. Try adding a field from vendPartI to the second report and then see if the numbers match.

-LB

 

[djj55]

Thanks for the reply. The problem was with the Group Select. I forgot to check that and that was where the criteria was.

Thanks
djj