Hi,

In my report, I am retrieving the data from an SQL query and the data recieved I am displaying it in the following format.

Client ID 000001
Bank Name HSBC Client Name XYZ
Date Projected Amount Average Rate Current Day's Rate
28-Feb $1,000 4.40% 2%

Total $1,000 Average Rate 4.40%


I use DST operator to iterate through the records row by row.

My fex file structure is as follows

-*SQL Query to fetch the records
SQL SQLORA
-----SQL QUERY-----
END
TABLE
ON TABLE HOLD AS SQLOUT
-*Taking out the distinct values
TABLE FILE SQLOUT
PRINT
DST.ID AS ID
DST.BNK_ID AS BNK_ID
ON TABLE HOLD AS RESULT FORMAT ALPHA
-*Reading the distinct values to print the results
-SET &I=0;
-READ RESULT &ID.P19.,&BNK_ID.P19.
TABLE FILE SQLOUT
PRINT
DATE AS 'Date'
AMOUNT AS 'Projected Amount'
AVG_RATE AS 'Average Rate'
RATE AS 'Current Day''s Rate'
WHERE ID EQ '&ID.&I'
WHERE BNK_ID EQ '&ID.&I'
SUBHEADING
"CLIENT ID <+0>"
"BANK NAME <+0> <+0>CLIENT_NAME"
SUBFOOT
"TOTAL<+0>"
"AVERAGE RATE<+0>"
ON TABLE HOLD AS EXL2K



My problem is, due to the use of DST and reading the resultset row by row the response time of the report is around 70 sec which needs to be reduced to 50 sec.

The query which fetches the data executes in 5 sec. So remaining 65 sec are consumed for displaying the data.

Is there any way I can avoid the use of WHERE and still get the same output with increased performance


Thanks for Help

This message has been edited. Last edited by: Kerry,

Webfocus 7.7.03
Windows XP
Excel, PDF, HTML, APDF, AHTML, Maintain

It always iterates through the data row-by-row. a PRINT DST.ID doesn't get you anything additional. It is the same as PRINT ID.

I'm not sure what it is you are trying to accomplish with the HOLD and -READ. Just leave all of that out and add BY ID BY BNK_ID to your second table request and I think you'll get what your looking for.

Why have your WHERE statement in your third pass of any data? Move it to the SQL step and thereby handle less data throughout. It looks like you are planning to process the SQLOUT several times although your code for that is incomplete, so this is maybe not possible?

Also, move your distinct processing into your SQL - get the RDBMS to do the most work for you - and only retrieve those records you want WF to process.

T

The above output is like a single sub table.
If I use BY ID and BY BNK_ID, it creates only a
single table.


What is happeing in my report is as follows

Header
Client ID 000001
Bank Name HSBC Client Name XYZ

Body
Date Projected Amount Average Rate Current Day's Rate
28-Feb $1,000 4.40% 2%


Footer
Total $1,000 Average Rate 4.40%


This whole record is a single table. Suppose there are 500 rows returned by query, then there will be 500 such tables shown in the report in the above format. I have tried using BY, but it concatenates all the tables to a single table which is not required.

Is there any means of tuning Webfocus Code like we have in Java and SQL



-Tony
The Distinct is also used in the query. Here I am using it to keep all the distinct IDs in a temp table RESULT. From this table I am processing the data to divide them into subtables.

If you have 500 distinct values, then how can you read it to a single variable.
Do you need 500 records to be in 500 different hold files.

Its not a single variable

&I acts as a counter which I keep on incrementing till the last record is reached.

For 1st record

WHERE ID EQ '&ID.&I'
WHERE BNK_ID EQ '&ID.&I'

will correspond to
WHERE ID EQ '&ID0'
WHERE BNK_ID EQ '&BNK_ID0'

for next
WHERE ID EQ '&ID1'
WHERE BNK_ID EQ '&BNK_ID1'

and so on until the last record is reached.

Hope this might help you a bit.

The below report using car file displays the values based on a computed field named CTR that runs through a loop.

  
TABLE FILE CAR
PRINT
COMPUTE CTR/I9=CTR+1;
COUNTRY
CAR
MODEL
SALES
ON TABLE HOLD AS CHK1
ON TABLE SET ASNAMES ON AND HOLDLIST PRINTONLY
END
-SET &LOOP_CNT = &LINES;
-RUN

-SET &CT =0;
-REPEAT CAR_LOOP FOR &CT FROM 1 TO &LOOP_CNT
-IF &CT EQ &LOOP_CNT THEN GOTO LAST_LOOP;
SET COMPOUND=OPEN NOBREAK
TABLE FILE CHK1
PRINT 
COUNTRY
CAR
MODEL
SALES
BY CTR NOPRINT
ON CTR SUBFOOT
" "
[B][I]WHERE CTR EQ &CT;[/I][/B]
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
END
-RUN
-GOTO CAR_LOOP
-LAST_LOOP
SET COMPOUND=CLOSE
TABLE FILE CHK1
PRINT 
COUNTRY
CAR
MODEL
SALES
BY CTR NOPRINT
[B]WHERE CTR EQ &CT;[/B]
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
END
-RUN
-CAR_LOOP
-EXIT

Thanks a Million Suresh,

It worked wonderfully. Now the response time has reduced to 10 seconds.

However there is one more small doubt.

I want to control the increment of the counter based on condition.
What is happening in the report is now for each record a table is created. Suppose for ID 100 there are 3 records returned by the query then 3 tables will be created. I want that on a single table.

So is there any way that I can control the increment of the counter based on the IDs?

Thanks once again

As other have suggested I think the loop is overkill.
The reason you have multiple records is because you are doing a PRINT you need a SUM.
PRINT gives you details and SUM gives you summary.

Same output no loop.

  
TABLE FILE CAR
SUM 
     COUNTRY AS ''
     CAR     AS ''
     MODEL   AS ''
     SALES   AS ''
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
     
ON MODEL SUBHEAD
"COUNTRY<+0>CAR<+0>MODEL<+0>SALES"
ON MODEL SUBFOOT
" "
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=10,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=SUBHEAD,
     HEADALIGN=BODY,
$
TYPE=SUBHEAD,
     BY=2,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     JUSTIFY=LEFT,
     WIDTH=1.000,
$
TYPE=SUBHEAD,
     BY=2,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     JUSTIFY=LEFT,
     WIDTH=1.000,
$
TYPE=SUBHEAD,
     BY=2,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     JUSTIFY=LEFT,
     WIDTH=1.000,
$
TYPE=SUBHEAD,
     BY=2,
     LINE=1,
     OBJECT=TEXT,
     ITEM=4,
     JUSTIFY=LEFT,
     WIDTH=1.000,
$
ENDSTYLE
END

I think you went for the loop so you could get the column titles for each record.
You can just leave the column titles blank and specify them in a heading.

If you want to create tables grouped by id column, then you can go for below example:
counter for loop is created for distinct country values taken as ID column.
(COMPUTE CTR/I9=IF (COUNTRY EQ LAST COUNTRY) )
This ll display values grouped by each country

  
TABLE FILE CAR
PRINT
-*COMPUTE CTR/I9=CTR+1;
COMPUTE CTR/I9=IF (COUNTRY EQ LAST COUNTRY) THEN CTR ELSE CTR+1;
COUNTRY
CAR
MODEL
SALES
BY COUNTRY NOPRINT
ON TABLE HOLD AS CHK1
ON TABLE SET ASNAMES ON AND HOLDLIST PRINTONLY
END
-SET &LOOP_CNT = &LINES;
-RUN

-SET &CT =0;
-REPEAT CAR_LOOP FOR &CT FROM 1 TO &LOOP_CNT
-IF &CT EQ &LOOP_CNT THEN GOTO LAST_LOOP;
SET COMPOUND=OPEN NOBREAK
TABLE FILE CHK1
PRINT 
COUNTRY
CAR
MODEL
SALES
BY CTR NOPRINT
ON CTR SUBFOOT
" "
WHERE CTR EQ &CT;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
END
-RUN
-GOTO CAR_LOOP
-LAST_LOOP
SET COMPOUND=CLOSE
TABLE FILE CHK1
PRINT 
COUNTRY
CAR
MODEL
SALES
BY CTR NOPRINT
WHERE CTR EQ &CT;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
END
-RUN
-CAR_LOOP
-EXIT

Thanks a lot for your help.

The report is working as expected