Sorry, not my sweet spot. I suspect it has something to do with the WITHIN syntax. I suggest you open a case with Techsupport or close this thread off and open a new one regarding add defines to hierarchies in the metadata.

I did open a case with IBI Tech Support. If anyone else has any suggestions, please post?

dhagen - These are the error messages that I'm getting now on this issue. In respect to the epoch dates conversion, my original date function that is in the code in the initial post here is converting the dates correctly based on epoch calculator so I went back using that function. In regards to your DB_EXP function, I can not use the WITH command within InfoAssist as IBI has confirmed but they have put in a new feature request to maybe include this in future release.

However, it appears I suppose that when I reverted back to my initial function (d_st_date/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS )), I am getting the disabling of the database joins with the four separate passes and FOCUS trying to handle the joins. Also, I get the additional messages/warnings below in the SQL trace...FOC36264 and FOC144. So the report produces no data...."EDA no data" Any ideas, please let me know.

0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
FOC2510 - FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
FOC2519 - THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED
SELECT
T1.`ami_meter_id`,
T1.`channel`,
T1.`end_date_millis`,
T1.`flow_direction`,
T1.`meter_location_uuid`,
T1.`rate_schedule_uuid`,
T1.`reported_date_time`,
T1.`start_date_millis`,
T1.`unit_of_measure`,
T1.`value`
FROM
mdm_readings T1
WHERE
(T1.`ami_meter_id` = '135628301')
LIMIT 500 ;
SELECT
T2.`description`
FROM
mdm_rate_schedule T2
WHERE
(T2.`uuid` = ?) AND
(T2.`description` = 'Residential - Single Phase')
LIMIT 500 ;
SELECT
T3.`vee_group_uuid`
FROM
mdm_meter_location T3
WHERE
(T3.`uuid` = ?)
LIMIT 500 ;
SELECT
T4.`name`
FROM
mdm_vee_group T4
WHERE
(T4.`uuid` = ?) AND
(T4.`name` = 'Residential-Group 2 (100-500)')
LIMIT 500 ;
0 ERROR AT OR NEAR LINE 59 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36264) A segment's active fields are larger than the cache limit.
0 ERROR AT OR NEAR LINE 59 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC144) WARNING. TESTING IN INDEPENDENT SETS OF DATA:
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

So actually it is not the date function that is causing the database join to be disabled and producing the four separate SQL queries. It is related to the filters on the fields from the other tables being joined to. When I remove those filter and just have the filter on my main parent table, I get this SQL trace produced below. Any suggestions, please let me know.

0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT
T1.`ami_meter_id`,
T1.`channel`,
T1.`flow_direction`,
T1.`reported_date_time`,
T1.`start_date_millis`,
T1.`unit_of_measure`,
T1.`value`
FROM
mdm_readings T1
WHERE
(T1.`ami_meter_id` = '135628301')
LIMIT 500 ;
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0