Focal Point
Override the SQL in a Webfocus report

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3951097331

August 17, 2004, 12:54 AM
<Leah Bell>
Override the SQL in a Webfocus report
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?
August 17, 2004, 01:39 PM
Stan
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!
August 17, 2004, 02:11 PM
<Leah Bell>
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.
August 18, 2004, 06:49 PM
<monte2000>
What tool are you using, Developer Studio?
August 19, 2004, 03:06 AM
<Leah Bell>
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?
August 19, 2004, 02:16 PM
mgrackin
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.
August 20, 2004, 08:12 PM
Jen
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).
August 21, 2004, 01:13 AM
<WFUser>
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.
August 21, 2004, 06:24 PM
Tom Walker
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
;