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     Override the SQL in a Webfocus report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Override the SQL in a Webfocus report
 Login/Join
 
<Leah Bell>
posted
Can you override the SQL generated when building the report? If so, where do you go to the background SQL and rewrite it instead of depending solely on the drag and drop method of building the report?
 
Report This Post
Gold member
posted Hide Post
I'm not sure I am understanding your question...Could you expand upon what it is that you are trying to do?

In MRE, you can use the editor to get to the base FOCUS code to manipulate that. You can also always embed sql in FOCUS code as well...we do that with some large queries quite frequently so that we can be sure the indexes will be used.

But again, I'm not sure I totally follow what you are wanting to do, so feel free to expand your question for us!
 
Posts: 90 | Registered: April 15, 2004Report This Post
<Leah Bell>
posted
I have a sql statement that I am running in DB2 for the report I am trying to create in Webfocus. How can I embed the SQL directly into the code. I see that there is a Source tab in Webfocus where I can see the code. But it breaks up the SQL statement with code.
 
Report This Post
<monte2000>
posted
What tool are you using, Developer Studio?
 
Report This Post
<Leah Bell>
posted
Yes, I am using Developer Studio. How do I embed my own SQL to override the SQL that the software is creating when I drag and drop?
 
Report This Post
Virtuoso
posted Hide Post
Leah, You seem to be using Business Objects terminology. In Business Objects you can create a request using drag and drop and then view the SQL code and make changes if desired. There is no equivalent capability in Developer Studio. In Developer Studio you cannot directly replace SQL code generated by draging and dropping because Developer Studio creates FOCUS code based on your draging and droping, NOT SQL code. The translation to SQL code happens when you you execute the request. The WebFOCUS Server translates as much of the FOCUS code as possible to SQL code to pass to the RDBMS for processing. So, in other words, if you want to "substitute" your own SQL for the request, you can't. There is no "substituting" the SQL code translated by WebFOCUS based on the FOCUS code. You need to just write the SQL code into the request to create the data extract from the RDBMS and then write FOCUS code to finish the request for presentation.
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
Leah,
Don't open your focexec in Report Painter. Instead, right click on it and choose Edit as Text. Embed your SQL code there, and then NEVER go back into Report Painter (It will blow away your SQL code).
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Report This Post
<WFUser>
posted
Jen is correct. Use can't generate the SQL in the painter but you can absolutley embed your own SQL. In edit mode enter:

SQL SQLORA

;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS holdname
END

If you have more than one Oracle SID defined you might need to define the server first using a SQL SQLORA SET SERVER servername. You also might want to add APP HOLD appname to the top. This will save the results of the query allowing you to use the painter to create your report.
 
Report This Post
Member
posted Hide Post
Leah, if you are using DB2, the passthru sql would start as "SQL DB2". If you get a diagnostic message to the effect that the database object is not found, you may need additional connect statements, like ..

SQL DB2 CONNECT TO PRODDB
SQL DB2 SET CURRENT SCHEMA PRODDBA
SQL DB2
select stu_id, etc, etc
;
 
Posts: 26 | Location: Mercy College | Registered: May 06, 2003Report 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     Override the SQL in a Webfocus report

Copyright © 1996-2020 Information Builders