Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Generated SQL Trace

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Generated SQL Trace
 Login/Join
 
Platinum Member
posted
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
 
Posts: 175 | Registered: March 24, 2017Report This Post
Virtuoso
posted Hide Post
Try adding at least one column from the other two tables in your JOIN. Unless you have those, the join won't get passed to the dbms.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
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, 2013Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 175 | Registered: March 24, 2017Report This Post
Virtuoso
posted Hide Post
Is your TABLE request using the files that you think it's using?

It's possible that there are some files stored locally, earlier in the search path. WF uses the first ones it finds.

WHENCE MDM_READINGS MASTER
WHENCE MDM_READINGS FOCUS
etc...


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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
I only have one out there as shown below. Any other suggestions.


0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
/share/ibi/apps/nisc/mdms/mdm_readings.mas


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Report This Post
Virtuoso
posted Hide Post
Have you tried change the "SET ALL" ?

SET ALL and Short path
Multipath and SET ALL


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Yes, I have tried the SET ALL = ON and I get the same results.


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Report This Post
Platinum Member
posted Hide Post
[SOLVED} This issue is resolved.


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Report This Post
Virtuoso
posted Hide Post
Please, as for reader knowledge and further similar issue, describe how you solved it.

And the [SOLVED] tag as to be placed in the subject : edit your first post to add it to the subject.

Tks


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 175 | Registered: March 24, 2017Report This Post
Virtuoso
posted Hide Post
Go to your first post then click on the edit button located at the bottom right corner : a folder icon with a pen.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 175 | Registered: March 24, 2017Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Generated SQL Trace

Copyright © 1996-2020 Information Builders