I'm going to open this box again ... there have been previous (unanswered) questions regarding how to conduct quartile calculations in OBIEE ... I've seen suggestions about using NTILE, but that doesn't actually return the quartile value - it returns the position within an ordered list based on the tile value ... I've been tinkering around with EVALUATE on PERCENTILE_DISC, which should properly return the quartile value (e.g. it works fine in native SQL) - but OBIEE 11g barks at me with {ORA-00934: group function is not allowed here at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)}
Here's the SQL being issued by OBIEE - with offending statement in blue :
SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8, s_9, s_10, s_11, s_12, s_13, s_14, s_15, s_16, s_17, s_18, s_19 FROM ( SELECT 0 s_0, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Analyte Name" s_1, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Diet No" s_2, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Last Result Reported Date" s_3, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Material Number" s_4, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id" s_5, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Reported UOM" s_6, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value" s_7, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Sample Number" s_8, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Sample Type" s_9, "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Standard UOM" s_10, EVALUATE('percentile_disc(%1) within group (order by %2)','.25',"Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value") s_11, MEDIAN("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value") s_12, REPORT_AGGREGATE(MEDIAN("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value") BY "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id") s_13, REPORT_AGGREGATE(STDDEV("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value") BY "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id") s_14, REPORT_AVG("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value" BY "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id") s_15, REPORT_COUNT("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value" BY "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id") s_16, REPORT_MAX("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value" BY "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id") s_17, REPORT_MIN("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value" BY "Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Nutrient Id") s_18, STDDEV("Analytical Testing Non-Biological"."GNS Non-Bio Smpl Rslts Ag"."Result Value") s_19 FROM "Analytical Testing Non-Biological" WHERE (("GNS Non-Bio Smpl Rslts Ag"."Nutrient Id" IN ('ASH', 'CALCIUM', 'CRUDE_FIBER', 'FAT')) AND ("GNS Non-Bio Smpl Rslts Ag"."Last Result Reported Date" >= timestamp '2014-09-01 00:00:00')) ) djm FETCH FIRST 8000001 ROWS ONLY
Anyone have a clue why OBIEE is having a problem with the group function within the EVALUATE ?
Thanks,
Dave