Hi Friends,

I have a code in my webfocus environment which is supposed to pick distinct value of search field and display. But the query which is getting generated order the result set by primary column of the table which was mentioned no where in the code.

  
 DEFINE FILE SALE_IND
 ID/A10 = HIGH_NET_WORTH_IND;
 DESCR/A10 = HIGH_NET_WORTH_IND;
 SRCHDBFLD/A10 = UPCASE(10,HIGH_NET_WORTH_IND,SRCHDBFLD);
 SELFIELD/A255 = '<OPTION Value="' | ID || '">' | DESCR | '</OPTION>';
 END
 TABLE FILE SALE_IND
 SUM SELFIELD
 BY HIGH_NET_WORTH_IND NOPRINT
 WHERE HIGH_NET_WORTH_IND NE ' '
 WHERE HIGH_NET_WORTH_IND NE '_BLANK_'
 ON TABLE HOLD
 END

If I see the query on running this procedure, it includes the primary column of the table in query. See below

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED : SELFIELD
SELECT T1."SALE_IND_ID",T1."HIGH_NET_WORTH_IND" FROM SALE_IND
T1 WHERE (T1."HIGH_NET_WORTH_IND" <> '_BLANK_') AND
(T1."HIGH_NET_WORTH_IND" <> ' ') ORDER BY T1."SALE_IND_ID";
0 NUMBER OF RECORDS IN TABLE= 119382 LINES= 4

The problem is the selection window taking minutes to fetch these values and the performance looks very poor.

Can anybody suggest why this happens?

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

quote:

(FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED : SELFIELD

The message is clear as to explaining that you cannot SUM the SELFIELD column as it is unknown to the database (it is a virtual field in WebFOCUS).

In such cases, WebFOCUS needs to retrieve detail records from the database (which could be many depending on your filters) and will attempt to do the aggregation locally which usually shows poor performance as you have noticed.

Try something like this to see if it makes a difference:

TABLE FILE SALE_IND
SUM
COMPUTE SELFIELD/A255 = '<option value="> || MAX.HIGH_NET_WORTH_IND || ">' || MAX.HIGH_NET_WORTH_IND || '</option>';
BY HIGH_NET_WORTH_IND
WHERE HIGH_NET_WORTH_IND NE ' '
WHERE HIGH_NET_WORTH_IND NE '_BLANK_'
ON TABLE HOLD
END

Obviously I don't know your data but do you really have ' ' and '_BLANK_' as possible values in your database? or are you perhaps trying to retrieve any records where that field is NOT NULL?

If the latter applies, you may try this condition:

WHERE HIGH_NET_WORTH_IND NE MISSING

Hope that helps!

- Neftali.