Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Override the SQL in a Webfocus report
Go
New
Search
Notify
Tools
Reply
  
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?
 
Reply With QuoteReport 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, 2004Reply With QuoteReport 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.
 
Reply With QuoteReport This Post
<monte2000>
posted
What tool are you using, Developer Studio?
 
Reply With QuoteReport 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?
 
Reply With QuoteReport 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, 2003Reply With QuoteReport 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, 2003Reply With QuoteReport 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.
 
Reply With QuoteReport 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, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Override the SQL in a Webfocus report

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.