Missing data when multiple companies being selected

Question

Hi,

I'm facing following problem. With below mentioned data model I get data when selecting company 00001, I get data when selecting company 00004, I get data when selecting multiple items from which I'm sure they should return data for both companies 00001 and 00004 but I only get data for company 00001 when I don't select anything specific although I expect data for both company 00001 and 00004.

Can somebody give me a clue where to look for please?

SELECT
   0 s_0,
   "ERP Procurement"."Companies"."CompanyNr" CompanyNr,
   "ERP Procurement"."Contract Attributes"."ContractNr" ContractNr,
   "ERP Procurement"."Contract Attributes"."DateEnd" DateEnd,
   "ERP Procurement"."Contract Attributes"."FreightHandlingCode" FreightHandlingCode,
   "ERP Procurement"."Products"."ItemNumber" ItemNumber,
   "ERP Procurement"."Products"."PriceUoM" PriceUoM,
   "ERP Procurement"."Products"."SubGroup" SubGroup,
   CAST(NULL AS DATE) NullAsDate,
   DESCRIPTOR_IDOF("ERP Procurement"."Products"."SubGroup") IDSubgroup,
   "ERP Procurement"."Contract Facts"."AvgDayPrice" AvgDayPrice,
   "ERP Procurement"."Contract Facts"."QuantityAvailable" QuantityAvailable,
   "ERP Procurement"."Contract Facts"."QuantityAvailable"*("ERP Procurement"."Contract Facts"."AvgDayPrice"-case "ERP Procurement"."Products"."PriceUoM"
when 'KG' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'LT' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'EA' then "ERP Procurement"."Contract Facts"."ContractPrice"
else "ERP Procurement"."Contract Facts"."ContractPrice"/1000 end
) QuantityAvailableXCp_DP,
 "ERP Procurement"."Suppliers"."SupplierNr" SupplierNr,

case "ERP Procurement"."Products"."PriceUoM"
when 'KG' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'LT' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'EA' then "ERP Procurement"."Contract Facts"."ContractPrice"
else "ERP Procurement"."Contract Facts"."ContractPrice"/1000 end ContractPrice
FROM "ERP Procurement"
WHERE
"Contract Facts"."QuantityAvailable" > 0 AND "Companies"."CompanyNr" IN ('00001', '00004') AND "Products"."StockingTypeCode" = 'P' AND "Products"."GLClassCode" IN ('100', '400') AND "Products"."SubGroupCode" NOT IN ('121', '171', '132', '136', '130', '165')

ORDER BY 1, 2 ASC NULLS LAST, 8 ASC NULLS LAST, 6 ASC NULLS LAST, 7 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

Thanking in advance,

Regards,

Arie

Bouman, Arie · Answer

Appeared to be an error in the datamodel caused by unknown reasons. Problem solved by rebuilding the datamodel and not using the logical SQL but creating queries directly to the database.

Bouman, Arie · Answer

Dear Joel and Gianni,

Thank you for your quick reply. I will have a look and keep you informed.

Gianni Ceresa · Answer

You posted the LSQL (logical SQL) of your analysis, this doesn't tell you much as you can't test it directly in your database.

Find the physical query that OBIEE generate and look at that one, the answer of what happen is there (and it also cover the point raised by Joel, if there is some row-level security the physical query is supposed to show the filters).