TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY HIRE_DATE
END
You get:
DEPARTMENT HIRE_DATE CURR_SAL
06/04/12 $11,000.00
MIS 81/07/01 $31,680.00
81/11/02 $27,062.00
82/04/01 $30,780.00
82/05/01 $18,480.00
PRODUCTION 82/01/04 $36,362.00
82/02/02 $16,100.00
82/07/01 $21,120.00
82/08/01 $29,700.00
I'd like to modify the query to get the rows with the highest hire_date that has a total curr_sal that is GT $30,0000 by department. You should then only return rows 4 and 6.
Is it possible to do this in one pass at the data using a combination of WHERE TOTAL and BY TOTAL?
Thanks, Bethany
WF 7.1.3 DevStudio 7.1.4This message has been edited. Last edited by: Bethany,
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
June 06, 2007, 01:40 PM
ET
I think this would work.
TABLE FILE EMPLOYEE SUM CURR_SAL BY DEPARTMENT BY highest 1 HIRE_DATE if total curr_sal gt 30000 END
Good luck
ET
FOCUS 7.6 MVS PDF,HTML,EXCEL
June 06, 2007, 01:56 PM
Bethany
Thanks for the suggestion ET, but that code returns no records.
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
June 06, 2007, 01:58 PM
Darin Lee
You wouldn't even need the "IF TOTAL". Just an "IF" would work.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
June 06, 2007, 02:42 PM
Leah
quote:
TABLE FILE EMPLOYEE SUM CURR_SAL BY DEPARTMENT BY highest 1 HIRE_DATE if total curr_sal gt 30000 END
If you take out the if total, you will see there are no records greater than 30000. Try 20000.
Leah
June 06, 2007, 02:45 PM
ET
What if there were 2 rows that came out for the highest date and one of the rows came out less than 30000 while the other row was greater ? Conversely what if the highest 1 date row had an amount less than 30000? I still think the if total is needed though maybe not in this example.
good luck
ET
FOCUS 7.6 MVS PDF,HTML,EXCEL
June 06, 2007, 02:45 PM
Bethany
When you sum by department and hire_date there are 3 records with totals higher than 30,000-2 for the MIS dept and 1 for PRODUCTION.
Using just IF CURR_SAL GT 30000, also returns no records.
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
June 06, 2007, 02:47 PM
Francis Mariani
DEPARTMENT HIRE_DATE CURR_SAL
06/04/12 $11,000.00
MIS 81/07/01 $31,680.00
81/11/02 $27,062.00
82/04/01 $30,780.00
82/05/01 $18,480.00
PRODUCTION 82/01/04 $36,362.00
82/02/02 $16,100.00
82/07/01 $21,120.00
82/08/01 $29,700.00
Interesting, BY HIGHEST 1 will return no values because it appears that WHERE TOTAL is filtering on each of the rows instead of the total by department. It looks to me that you need to do this in two passes.
TABLE FILE EMPLOYEE
SUM
CURR_SAL
BY DEPARTMENT
BY HIGHEST HIRE_DATE
WHERE TOTAL CURR_SAL GT 30000
ON TABLE HOLD
END
TABLE FILE HOLD
SUM
CURR_SAL
BY DEPARTMENT
BY HIGHEST 1 HIRE_DATE
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 06, 2007, 02:48 PM
Alan B
Bethany,
Try:
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY HIGHEST 1 HIRE_DATE
WHERE TOTAL TOT.CURR_SAL GT 30000;
END
Ignore, this must be a load of cods!This message has been edited. Last edited by: Alan B,
Alan. WF 7.705/8.007
June 06, 2007, 02:49 PM
ET
The test was and if total test (if TOTAL curr_sal gt tnat 30000) and not just an if test.
FOCUS 7.6 MVS PDF,HTML,EXCEL
June 06, 2007, 03:31 PM
Alan B
Okay, after last flop, try:
TABLE FILE EMPLOYEE
SUM CURR_SAL
COMPUTE
XSAL/I1=IF CURR_SAL GT 30000 THEN 1 ELSE 2;
COMPUTE
XDAT/I6YMD=IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 1 THEN HIRE_DATE ELSE
IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 2 THEN 0 ELSE
IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT GT HIRE_DATE THEN LAST XDAT ELSE
IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT LT HIRE_DATE THEN HIRE_DATE ELSE
LAST XDAT;
BY DEPARTMENT
BY HIRE_DATE
WHERE TOTAL XSAL EQ 1;
WHERE TOTAL HIRE_DATE EQ XDAT;
END
And 'cos it's late you should be able to improve my COMPUTE!
Alan. WF 7.705/8.007
June 06, 2007, 06:53 PM
dwf
Looks to me like Alan is almost there. Ran his code and got:
PAGE 1
DEPARTMENT HIRE_DATE CURR_SAL XSAL XDAT MIS 81/07/01 $31,680.00 1 81/07/01 82/04/01 $30,780.00 1 82/04/01 PRODUCTION 82/01/04 $36,362.00 1 82/01/04
Too many rows. But change BY HIRE_DATE to BY HIGHEST HIRE_DATE and you get the results you're looking for. Which I know Alan would have figured out in about two minnutes.
dwf
June 06, 2007, 09:30 PM
Alan B
DWF
Thank you, you are correct. I missed the HIGHEST transposing from one machine to another! Should be:
TABLE FILE EMPLOYEE
SUM CURR_SAL
COMPUTE
XSAL/I1=IF CURR_SAL GT 30000 THEN 1 ELSE 2;
COMPUTE
XDAT/I6YMD=IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 1 THEN HIRE_DATE ELSE
IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 2 THEN 0 ELSE
IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT GT HIRE_DATE THEN LAST XDAT ELSE
IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT LT HIRE_DATE THEN HIRE_DATE ELSE
LAST XDAT;
BY DEPARTMENT
BY HIGHEST HIRE_DATE
WHERE TOTAL XSAL EQ 1;
WHERE TOTAL HIRE_DATE EQ XDAT;
END
Alan. WF 7.705/8.007
June 07, 2007, 04:42 AM
Roland
Hi everybody,
Try this:
TABLE FILE EMPLOYEE SUM CURR_SAL BY DEPARTMENT BY TOTAL HIGHEST 1 HIRE_DATE BY HIGHEST HIRE_DATE WHERE TOTAL CURR_SAL GT 30000 END
With this I got the expected result.
Roland
Prod: WF 7.1.5 Test: WF 7.6.4 Unix Sun Solaris HTML, PDF, EXL2K
June 07, 2007, 05:06 AM
Alan B
Roland, you are right. Perfect, well done.
Don't know why I could not get that to work yesterday, I was obviously wrong.
(Ah,I see why, I got hung up on TOTAL TOT.CURR_SAL!)
Alan. WF 7.705/8.007
June 07, 2007, 01:46 PM
Bethany
Thanks to everyone for you help!
Bethany
Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
June 07, 2007, 02:01 PM
Francis Mariani
quote:
BY TOTAL HIGHEST 1 HIRE_DATE
Roland,
That's something I've not done before! Thanks for letting this out of the bag!
Cheers,
Francis.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 08, 2007, 02:23 AM
Roland
Everybody welcome.
It's the process WF goes through in preparing a report. Once all the data are in the internal matrix, WF is doing (and in this order):
COMPUTEs
WHERE/IF TOTAL
BY TOTAL
So if you are looking at the code and going through these steps, you can see.
Roland
Prod: WF 7.1.5 Test: WF 7.6.4 Unix Sun Solaris HTML, PDF, EXL2K