Hi,

Does anyone know why SALES=0.00 and SALES1=336.00 as in the code shown below? We only have one row in the table for IDINVN='128464449'.

IDAMCF | IDNSVA  | RTRAMT 
1        336.0000  .0000 

 DEFINE FILE SALES_VP
  SALES/D20.2 = (IDAMCF * IDNSVA) + RTRAMT;
END
TABLE FILE SALES_VP
SUM SALES
COMPUTE SALES1 = (IDAMCF * IDNSVA) + RTRAMT;

BY IDINVN
WHERE IDINVN EQ '128464449'
END 

===============================
Below is the trace of sql:
 SELECT
 T1."IDINVN",
 SUM(((T1."IDAMCF" * T1."IDNSVA") + T1."RTRAMT")),
 SUM(T1."IDAMCF"),
 SUM(T1."IDNSVA"),
 SUM(T1."RTRAMT")
 FROM
 EDADBA.sales_vp T1
 WHERE
 (T1."IDINVN" = 148024752)
 GROUP BY
 T1."IDINVN"
 ORDER BY
 T1."IDINVN";

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

DEFINEs are applied before aggregation, COMPUTEs after.

If you change the SUM to PRINT what happens, you should see the detail of each line

Waz,

Both SALES and SALES1 yielded the same number 336.00 when I changed the SUM to PRINT. I might be wrong, both SALES and SALES1 should get the same number regardless aggregation since there is only one row.

What is the field format of fields IDAMCF, IDNSAV and RTRAMT ?

Since you have not assigned any format to SALES1 in your COMPUTE a format is assigned automatically and maybe the one from the DEFINE gives an invalid result and just return 0

This has the aroma of a compiler bug.

Are there any other fields in the table that begin with "SALES"?

I'd show all the fields in the SUM version to see if anything surprising shows up.

Martin,

I did reassign the format of all the fields in the DEFINE, still the issue still persistent. the format of the fields as below.

 
FILENAME=  SALES_VP
 IDINVN        IDINVN        P13
 IDAMCF        IDAMCF        P2
 IDNSVA        IDNSVA        P19.4 
 RTRAMT        RTRAMT        P22.4

 

John_Edwards,

I checked the MFD and did not see any fields starting with 'SALES'.

Did you assigned a format in the COMPUTE and have the same result ?

MartinY,

The same result when I assigned a format in the COMPUTE.

Are any of the fields MISSING (Null)?

Try the following.
Do you still only have 1 line ?

TABLE FILE SALES_VP
PRINT IDAMCF
      IDNSVA
      RTRAMT
BY IDINVN
WHERE IDINVN EQ '128464449';
END

Share the result (data) with us

jgelona,

The field RTRAMT has missing value.

MartinY,

It returns one line with the code. The result shows as below:

 IDINVN IDAMCF IDNSVA RTRAMT 
128464449 1 336.0000 .0000 
 

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

Huh.

I'd figure a Null should cast a Null into the Defined field. I think that's a thing.

This should have thrown an error. Strong Typing on the Define field should have insisted on values.

John_Edwards,

You're right. Redefine the field seems correcting the problem.

 
DEFINE FILE SALES_VP
 N_RTRAMT/D20.2 MISSING ON = IF RTRAMT IS MISSING THEN 0 ELSE RTRAMT; 
 SALES/D20.2 = (IDAMCF * IDNSVA) + N_RTRAMT;
END

TABLE FILE SALES_VP
SUM
   SALES
COMPUTE SALES1/D20.2 = (IDAMCF * IDNSVA) + RTRAMT;
BY IDINVN
WHERE IDINVN EQ '128464449'
END

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

In DEFINE, I believe there are options in the DEFINE for handling missing fields, they are NEEDS ALL or NEEDS SOME, with NEEDS ALL being the default.

Thanks, jgelona. I found the documentation that has the options as you mentioned: https://webfocusinfocenter.inf...s141.htm#dmis1042098