Is there any way of counting values based on certain conditions and making the generated SQL efficient?

The following code gives me this error for all three defined fields:

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED : CNT_IN_USE

[code]
complex join...

DEFINE FILE BASEL_BSL_CL_RC_ACT_F
CNT_IN_USE/D8 = IF ACTV_MTH_CNT EQ 0 THEN 0 ELSE 1;
CNT_WITH_BAL/D8 = IF ACT_BEGIN_BAL_AMT LE 0 THEN 0 ELSE 1;
CNT_WROFF_KNOWN/D8 = IF ACT_BL_CD IN ('O', 'Y', 'R') THEN 1 ELSE 0;
END
-RUN
-IF &FOCERRNUM NE 0 THEN GOTO FOC_ERROR;

TABLE FILE BASEL_BSL_CL_RC_ACT_F
SUM
ACT_BEGIN_BAL_AMT/D15M
CNT.ACT_BEGIN_BAL_AMT AS 'CNT_ACT_BEGIN_BAL_AMT'
CNT_IN_USE
CNT_WITH_BAL
CLOS_PROD_AMT/D15M AS 'TOT_CRED_LIMIT'
CNT_WROFF_KNOWN

BY SP_ACT_KEY
BY SYS_ENTER_DT
BY DIVISION_NODE_NM
BY NCCS_DELQ_STAT_CD
BY CLOS_PROD_AMT

where statements...

ON TABLE HOLD AS R014H010

END

Francis,

You probably know by now that I would resort to SQL and control it myself, using FOCUS can be a lot of trial and error.

If Noreen, Ben or Art are watching then they are probably the best people to advise on this.

I don't really know sql, just Focus. I have however used Focus against DB2 files for a long time. It is my experience that if/then/else statements cannot be passed to DB2. I believe that this is a limit of DB2 and not Focus. Tips/techniques to try would include using the if/then/else in a compute rather than a define where possible, if total tests on the computed fields, over screening using real fields (ie if fieldx from 1000000 to 9000000, if fieldx not-from 1200000 to 1500000,etc.).
Because of the requirements, sometimes a table space scan cannot be avoided. I've seen cases where sql specialists have written multiple page sql calls to attempt get around this nested if limitation of DB2 (and possibly avoid writing a 3gl program to refine the results). A case that comes to mind involved 3 tables (600k to 700k rows each)where nested ifs were required to define and sum up numeric fields (answer set about 150 rows). Their SQL took over 10 minutes online. I used Focus to dump the fields needed for all the rows from each table and joined the flat files to come up the same answer in about 90 seconds.

Just my 2 cents worth. Good luck.

My two cents, I've summed counters created in defines, but never put a -run after the define so don't know the effect. I do know that is not the most efficient way to go. If you can avoid the define and use a compute, it's best and adding where totals for some selection.

Tony,

I wouldn't mind using SQL passthru to solve this.

Do you know how to code a Count based on selection criteria in SQL?

Thanks and cheers.

Francis,


Maybe you can try something like this

SQL DB2
SELECT SUM(ACT_BEGIN_BAL_AMT)
, COUNT(ACT_BEGIN_BAL_AMT) AS CNT_ACT_BEGIN_BAL_AMT
, SUM (CASE WHEN ACTV_MTH_CNT EQ 0 THEN 0 ELSE 1 END) AS CNT_IN_USE
, SUM (CASE WHEN CT_BEGIN_BAL_AMT LE 0 THEN 0 ELSE 1 END) AS CNT_WITH_BAL
, CLOS_PROD_AMT AS TOT_CRED_LIMIT
, SUM (CASE WHEN ACT_BL_CD = 'O' OR 
                 ACT_BL_CD = 'Y' OR 
                 ACT_BL_CD = 'R' THEN 1 ELSE 0 END) AS CNT_WROFF_KNOWN
FROM BASEL_BSL_CL_RC_ACT_F
GROUP BY SP_ACT_KEY
, SYS_ENTER_DT
, DIVISION_NODE_NM
, NCCS_DELQ_STAT_CD
, CLOS_PROD_AMT
FOR READ ONLY WITH UR

Hope that helps
M

This message has been edited. Last edited by: <Maryellen>,

Mickey,

Thank you for the syntax. I dug around on the web and did find this type of code, so I tried it.

Unfortunately, it now appears that this is even more inefficient than WebFOCUS! So I'm going back to the WebFOCUS code instead.

Thanks for the help.

Cheers.

This was exactly the point I was trying to make earlier with conplex sql code.

ET, thanks for the comments.