IS THERE ANY WAY TO VIEW THE SQL'S THAT ARE GENERATED BY THE WEBFOCUS SERVER.

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Read-Only Topic

Notify

Tools

Personal Zone	»
Member Directory
Focal Point Forums	»

Printer Friendly Format

Help

IS THERE ANY WAY TO VIEW THE SQL'S THAT ARE GENERATED BY THE WEBFOCUS SERVER.

posted

IS THERE ANY WAY TO VIEW THE SQL'S THAT ARE GENERATED BY THE WEBFOCUS SERVER.

<Vipul>

posted

Kalyan,

copy this in your focexec:
SET TRACEON=STMTRACE
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
you can look at the sqls on view source on html.

or you can look at the edatemp files on setting the traces on.

Vipul

posted

This does not work for me.In view source I am not able to see anything
Is there some specific place where this code should be kept

quote:
Originally posted by Vipul:
[qb] Kalyan,

copy this in your focexec:
SET TRACEON=STMTRACE
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
you can look at the sqls on view source on html.

or you can look at the edatemp files on setting the traces on.

Vipul [/qb]

<Kyle>

posted

You might also want to try adding

SET XRETRIEVAL=OFF

to those list of set statements. This will cause WebFOCUS to generate the SQL but not actually submit it to the database. This way if the request takes a minute to run or so, you will be able to view the SQL right away.

<Vipul>

posted

copy these at the top of your focexec. This will work only for HTML reports and you shld be able to see your sqls on view source.
or add these
-SET ECHO=ALL;
-RUN
-*TO DISPLAY THE SQLS GENERATED
SET TRACEOFF = ALL
SET TRACEON=STMTRACE
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON

These work for me.

Vipul

posted

Ok these work now.Butin my where clause I am checking if a computed field is satisfying some condition.
Eg:
DEFINE FILE AA
TOD/YYMD= '&DATEYYMD';
DIFF/I4 = DATEDIF(column4,TOD,'D');
BAL_DUE/D13.2MB = column6 - ( column7 + column8);
END
TABLE FILE AA
PRINT *
WHERE
column1 EQ '12' and
column2 IN ('12','112') and
BAL_DUE NE 0.00

IF &SEL EQ 'TOT' THEN GOTO ONE;
ELSE IF &SEL EQ 'CUR' THEN GOTO TWO;

-ONE
AND DIFF LT (column3+1) AND DIFF GT (column4 +12)

-TWO
AND DIFF LT (column3+13) AND DIFF GT (column4 +21 )

END

When I run my fex The sql does not show the 3rd and 4th where clause. This is the error message in view sources
(FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : WHERE exp
01.34.52 BA (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : BAL_DUE
01.34.52 BA (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
01.34.52 BA (FOC2596) ONE OR MORE EXPRESSION(S) CAN NOT BE TRANSLATED TO SQL

jimster06

Guru

posted

Hide Post

The message that you are getting is correct.
The compute is done on data that is selected, ie after the fact, so trying to get SQL to select data based on data selected is asking SQL a bit much.

Posts: 252 | Location: USA | Registered: April 15, 2003

Ignored post by jimster06 posted

Show Post

posted

does any one have any suggestions to overcome this

Leah

Virtuoso

posted

Hide Post

It is my understanding that any selection on defined fields is done after the pull from the data base, that is, Focus pulls as much as possible and then does the remaining selection at the application server level. I don't use imbedded SQL in my reporting so I may be all wet on that one, But I think you may need to do subselect type SQL.

Pseudo coding

Select .....
where ..(select ...)

Don't have the SQL manual in front of me.

Leah C.

Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004

Ignored post by Leah posted

Show Post

mgrackin

Virtuoso

posted

Hide Post

Instead of stringing all your conditions together in one WHERE statement, separate the WHERE condition on the BAL_DUE into its own WHERE clause. There is an implied AND condition between WHERE clauses in a FOCUS request. Separating BAL_DUE MIGHT allow it to translate to SQL asssuming the computation in the DEFINE for BAL_DUE is something SQL can perform. If the calculation for BAL_DUE is not translatable into SQL then it will always be processed by FOCUS no matter what you do.

Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003

Ignored post by mgrackin posted

Show Post

<Vipul>

posted

Hey Kalyan,

If you can write the sql to do what you want to do then you can embed it. The beauty of letting focus spit the sql for you is the optimization but then this will not be done for defined fields. This is because defined fields are not part of the database tables.

There is another solution - If this can be done on db side then you can write a database procedure and call it from webfocus. There are number of posibilities.

Vipul

Piipster

Guru

posted

Hide Post

You can also write the WHERE statement as

WHERE column6 - ( column7 + column8) NE 0.00;

Whenever possible use database fields in your WHERE statements. You will automatically build efficiencies into your requests this way.

Also, if you have a condition that is not be passed to the SQL try to do something called overscreening. Put as many WHERE statements into your requests as possible ... try to think of a way to break your calculations down if you can and include at lesat part of the conditions in individual WHERE statements. This will reduce the size of the answer set that gets returned to WebFOCUS for final processing of conditions that couldn't be passed.

Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003

Ignored post by Piipster posted

Show Post

Piipster

Guru

posted

Hide Post

So, looking at DIFF ... is column4 a date field formatted the same way as TOD?

You should be able to do

WHERE (column4 - TOD) LT (column3+1);
WHERE (column4 - TOD) GT (column4 +12);

This way you are not using any WebFOCUS specific functions and you have a better chance of this getting translated to SQL.

Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003

Ignored post by Piipster posted

Show Post

Silver Member

posted

Hide Post

Is there a way to turn off the 15.51.14 BR and other stuff around the sql code? I just want to see sql code.

15.51.14 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
15.51.14 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
15.51.14 AE SELECT T1."EMPLOYEE_NBR",T1."FIRST_NM",T1."MIDDLE_INIT",
15.51.14 AE T1."LAST_NM",T1."ORG_CD" FROM HUMAN_RESOURCE.EMPLOYEE T1 WHERE
15.51.14 AE (T1."ORG_CD" LIKE '7%');
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

Posts: 47 | Registered: March 29, 2005

Ignored post by db posted

Show Post

KevinG

Platinum Member

posted

Hide Post

db,

Our code is almost entirely dynamically (Dialogue Manager generated)SQL pass-thru. We have not found a way to turn off AE portion returned. Wewe copy the code out to a text editor, do a REPLACE all of the TIME-> AE strings with a blank, and usually paste the remaining text into a tool like Squirrel for SQL formatting. This let's us run and tune the SQL directly against the SQL server. We can then change the way our SQL code is built in the WF code to match the performance tuning. THE time->AE actually comes in handy for rapidly identifying the code that is passed to the RDBMS.

Kevin

WF 7.6.10 / WIN-AIX

Posts: 141 | Location: Denver, CO | Registered: December 09, 2005

Ignored post by KevinG posted

Show Post

Silver Member

posted

Hide Post

Thanks for the info, KevinG.

Posts: 47 | Registered: March 29, 2005

Ignored post by db posted

Show Post

kalyanswarna