Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] DEFINE vs COMPUTE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] DEFINE vs COMPUTE
 Login/Join
 
Silver Member
posted
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
 
Posts: 43 | Registered: May 28, 2008Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
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
 
Posts: 43 | Registered: May 28, 2008Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
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.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Silver Member
posted Hide Post
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
 
Posts: 43 | Registered: May 28, 2008Report This Post
Silver Member
posted Hide Post
John_Edwards,

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


Thanks,
Pku

Focus, WebFocus 8201 on Windows
 
Posts: 43 | Registered: May 28, 2008Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
MartinY,

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


Thanks,
Pku

Focus, WebFocus 8201 on Windows
 
Posts: 43 | Registered: May 28, 2008Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
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
 
Posts: 43 | Registered: May 28, 2008Report This Post
Virtuoso
posted Hide Post
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.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Silver Member
posted Hide Post
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
 
Posts: 43 | Registered: May 28, 2008Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Silver Member
posted Hide Post
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
 
Posts: 43 | Registered: May 28, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] DEFINE vs COMPUTE

Copyright © 1996-2020 Information Builders