[CASE OPENED] Join Issue in Hadoop Hive

It appears that I am having an issue with joining in this report I created in InfoAssist within a Hadoop Hive instance. My errors from the SQL trace preview are below and my code is below. I get an error about "JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM" and "RDBMS-MANGED JOIN HAS BEEN DISABLED". So therefore, it's not joining and breaking the report query into 4 multiple SQL queries on the backend. These are native synonyms being used in the join. I have to join from one table to another table to get to the other table (I have to join from the MDM_REDADINGS table to the MDM_METER_LOCATION table to get to MDM_VEE_GROUP table). Any suggestions, please let me know.

Error with SQL trace preview:

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.`reading_reported_date_time`,
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.`vee_group_uuid`
FROM
mdm_meter_location T2
WHERE
(T2.`uuid` = ?)
LIMIT 500 ;
SELECT
T3.`name`
FROM
mdm_vee_group T3
WHERE
(T3.`uuid` = ?) AND
(T3.`name` = 'Residential-Group 2 (100-500)')
LIMIT 500 ;
SELECT
T4.`description`
FROM
mdm_rate_schedule T4
WHERE
(T4.`uuid` = ?) AND
(T4.`description` = 'Residential - Single Phase')
LIMIT 500 ;
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

Code Logic:

 

-*COMPONENT=Join_J001
JOIN INNER MDM_READINGS.MDM_READINGS.METER_LOCATION_UUID IN MDM_READINGS
TO UNIQUE MDM_METER_LOCATION.MDM_METER_LOCATION.UUID IN MDM_METER_LOCATION TAG J001 AS J001
END
-*COMPONENT=Join_J002
JOIN INNER J001.MDM_METER_LOCATION.VEE_GROUP_UUID IN MDM_READINGS
TO UNIQUE MDM_VEE_GROUP.MDM_VEE_GROUP.UUID IN MDM_VEE_GROUP TAG J002 AS J002
END
-*COMPONENT=Join_J003
JOIN INNER MDM_READINGS.MDM_READINGS.RATE_SCHEDULE_UUID IN MDM_READINGS
TO UNIQUE MDM_RATE_SCHEDULE.MDM_RATE_SCHEDULE.UUID IN MDM_RATE_SCHEDULE TAG J003 AS J003
END
-*COMPONENT=Define_MDM_READINGS
DEFINE FILE MDM_READINGS
ST_DATE/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ) ;
END_DATE/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ) ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE MDM_READINGS
BY MDM_READINGS.MDM_READINGS.AMI_METER_ID
BY MDM_READINGS.MDM_READINGS.READING_REPORTED_DATE_TIME
BY MDM_READINGS.MDM_READINGS.REPORTED_DATE_TIME
BY ST_DATE
BY END_DATE
BY MDM_READINGS.MDM_READINGS.UNIT_OF_MEASURE
BY MDM_READINGS.MDM_READINGS.VALUE
BY MDM_READINGS.MDM_READINGS.CHANNEL
BY MDM_READINGS.MDM_READINGS.FLOW_DIRECTION
WHERE J002.MDM_VEE_GROUP.NAME EQ &p_vee_group_name.(OR(FIND MDM_VEE_GROUP.MDM_VEE_GROUP.NAME IN MDM_VEE_GROUP |FORMAT=A32765V)).VEE Group Name.;
WHERE J003.MDM_RATE_SCHEDULE.DESCRIPTION EQ &p_rate_desc.(OR(FIND MDM_RATE_SCHEDULE.MDM_RATE_SCHEDULE.DESCRIPTION IN MDM_RATE_SCHEDULE |FORMAT=A32765V)).Rate Description.;
WHERE MDM_READINGS.MDM_READINGS.AMI_METER_ID EQ '135628301';
ON TABLE PCHOLD FORMAT AHTML
ON TABLE NOTOTAL
ON TABLE SET AUTOFIT OFF
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET WEBVIEWTARG OFF
ON TABLE SET WEBVIEWER ON
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/Mosaic.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ARGRAPHENGINE=JSCHART, $
ENDSTYLE
END

-RUN

This message has been edited. Last edited by: FP Mod Chuck,

WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL

Posts: 175 | Registered: March 24, 2017

MartinY

Virtuoso

posted