Focal Point
[SOLVED] DEFINE vs COMPUTE

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4317066496

October 21, 2020, 04:36 PM
Pku
[SOLVED] DEFINE vs COMPUTE
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,


Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 21, 2020, 06:27 PM
Waz
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

October 22, 2020, 08:48 AM
Pku
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.


Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 22, 2020, 11:23 AM
MartinY
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
October 22, 2020, 11:41 AM
John_Edwards
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.



October 22, 2020, 11:56 AM
Pku
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

 



Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 22, 2020, 12:04 PM
Pku
John_Edwards,

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


Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 22, 2020, 12:10 PM
MartinY
Did you assigned a format in the COMPUTE and have the same result ?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
October 22, 2020, 12:21 PM
Pku
MartinY,

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


Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 23, 2020, 09:18 AM
jgelona
Are any of the fields MISSING (Null)?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
October 23, 2020, 10:15 AM
MartinY
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
October 23, 2020, 11:18 AM
Pku
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,


Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 23, 2020, 11:29 AM
John_Edwards
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.



October 23, 2020, 12:29 PM
Pku
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,


Thanks,
Pku

Focus, WebFocus 8201 on Windows
October 26, 2020, 10:34 AM
jgelona
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.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
October 27, 2020, 09:45 AM
Pku
Thanks, jgelona. I found the documentation that has the options as you mentioned: https://webfocusinfocenter.inf...s141.htm#dmis1042098


Thanks,
Pku

Focus, WebFocus 8201 on Windows