As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
I have an InfoAssist report that produces 4 records. However, if I run the actual SQL from the report trace in DBVisualizer, it produces 11 records. Something odd is happening. Any suggestions why this would be. I have posted the SQL trace and the code to the InfoAssist report.
SQL Trace:
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.`value` = 0.1698) AND (T1.`unit_of_measure` = 'KWH_USAGE') AND (T1.`ami_meter_id` = '135628301') LIMIT 500 ; 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
Code:
*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
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE MDM_READINGS
PRINT MDM_READINGS.MDM_READINGS.AMI_METER_ID
MDM_READINGS.MDM_READINGS.REPORTED_DATE_TIME
MDM_READINGS.MDM_READINGS.START_DATE_MILLIS
MDM_READINGS.MDM_READINGS.UNIT_OF_MEASURE
MDM_READINGS.MDM_READINGS.VALUE
MDM_READINGS.MDM_READINGS.CHANNEL
MDM_READINGS.MDM_READINGS.FLOW_DIRECTION
WHERE MDM_READINGS.MDM_READINGS.AMI_METER_ID EQ '135628301';
WHERE MDM_READINGS.MDM_READINGS.UNIT_OF_MEASURE EQ 'KWH_USAGE';
WHERE MDM_READINGS.MDM_READINGS.VALUE EQ 0.1698;
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: AMC,
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
what happens if you don't include the joins at all and just run directly against the MDM_READINGS table? do you get 4 or 11 records?
I find that sometimes when FOCUS is trying to be "smart" it will apply its own logic after the retrieval of data. In this case it might be issuing some join that you don't realize. Although, I'm not convinced thats whats happening here, I would try that first to make sure.
Next I would check how many records from the Table vs Lines are being returned. If those numbers don't match (Maybe its 11 and 4), then most likely WebFOCUS feels that it needs to apply a distinct function or join to the result set that you weren't expecting. I would go to my join statements and change all the Unigue commands to Multiple. Where as SQL doesn't have the concept of Unique, WebFOCUS most certainly does. This can cause less records to return then expected as well. I hate the way the syntax works, but since all Holds follow Focus rules and not SQL rules, that syntax won't change.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013
Thanks for the replies. The SQL gets optimized I guess. So since there is no columns coming from the other tables in the join, the other table are not included in the generated SQL.
When I don't include the joins at all and run directly against MDM_READINGS, I still get 11 records in SQL. However, I still get the 4 records in WebFOCUS. I made this change in both.
Also, I changed the join statements from Unique to Multiple and I get the same results of the 4 records for the report.
Any other suggestions?
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
Thanks MartinY. How do you edit the specific first post again?
The issue was related to my connection in DBVis and my connection for my WebFOCUS adapter being slightly different, and thus, referring to different data in the data sources. So the issue was solved by using the exact same connection for both.
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
Thanks for the info. I will do that for this post.
I have another post open out there that I just added to this morning...."Join Issue in Hadoop Hive". You have replied on this thread before a few times. If you have any other ideas on what I just posted little earlier today. please share. Thanks.
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL