Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Peter Vazny

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...