I ran into a problem where I am using WHEN to conditionally ...

[Peter Vazny]

I ran into a problem where I am using WHEN to conditionally display multiple subfoots on field A and then have a subfoot on field B to display count of records. If more than one subfoot for field A meats the criteria, the count is off. Pseudo code:

DEFINE FILE TABLE1

Counter/I9 = 1;

END

TABLE FILE TABLE1

BY FieldB

ON FieldB

SUBFOOT

"Counter: <ST.Coutner or Counter: <ST.CNT.FieldA"

BY FieldA

ON FieldA

SUBFOOT

"Long text 1"

WHEN FieldC IS NOT MISSING

SUBFOOT

"Long text 2"

WHEN FieldD IS NOT MISSING

END

 

Then the output is this

FieldB TieldA

------ ------

TextB TextA_1

Long text 1

Long text 2

TextA_2

Counter: 3 or Counter: 3

[Peter Vazny]

This is the closest I can get to the problem not using actual DBMS:

SET PAGE=NOLEAD

SET HTMLCSS=ON

TABLE FILE course

BY SOURCE

RANKED BY CTITLE

BY TUITION

ON TABLE HOLD AS TMP1 FORMAT FOCUS

END

-RUN

 

DEFINE FILE TMP1

C1/A35 MISSING ON= IF RANK EQ 1 THEN CTITLE ELSE MISSING;

C2/A35 MISSING ON= IF RANK EQ 2 THEN CTITLE ELSE MISSING;

C3/A35 MISSING ON= IF RANK EQ 3 THEN CTITLE ELSE MISSING;

END

 

TABLE FILE TMP1

SUM TUITION

MAX.C1 NOPRINT

MAX.C2 NOPRINT

MAX.C3 NOPRINT

BY SOURCE

ON SOURCE SUBFOOT

"<MAX.C1"

WHEN C1 IS NOT MISSING

SUBFOOT

"<MAX.C2"

WHEN C2 IS NOT MISSING

SUBFOOT

"<MAX.C3"

WHEN C3 IS NOT MISSING

 

ON TABLE SUBFOOT

"Counter: <ST.CNT.SOURCE"

 

END

 

Except here I know where the 29 comes from, because there is 29 records in the original file. Also it will not allow me to use WHEN MAX.C1 IS NOT MISSING, but it will let me do that when using DBMS.

When I run SQL trace with my code, I can see that aggregation was done, but the number of rows returned does not match the counter. It is off exactly by the number of extra subfoots.

[Peter Vazny]

peter.vazny:

 

Counter: <ST.Coutner or Counter: <ST.CNT.FieldA

 

 

OMG! so the fix to my problem is to use <ST.MAX.Counter. No such thing as <ST.CNT.MAX.FieldA.

Another day, another 3 hours wasted chasing unpredictable behavior