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     newbie question on IF THEN ELSE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
newbie question on IF THEN ELSE
 Login/Join
 
Member
posted
I have looked at the discussions on the forums and I think I understand a bit more but I am still a bit confused. I am trying to put this SQL statement from our old reporting system into WebFOCUS and I realize there is no CASE on WF and I have to use the Define statement but I am unsure how you do multiple ones. The query looks like this:

SELECT COURSE_GROUP, SCHOOLNUM
SUM(CASE WHEN MARK = 'A' THEN 1 ELSE 0 END) AS TOT_A,
SUM(CASE WHEN MARK = 'B' THEN 1 ELSE 0 END) AS TOT_B,
SUM(CASE WHEN MARK = 'C' THEN 1 ELSE 0 END) AS TOT_C,
SUM(CASE WHEN MARK = 'D' THEN 1 ELSE 0 END) AS TOT_D,
SUM(CASE WHEN MARK = 'F' THEN 1 ELSE 0 END) AS TOT_F) AS TOTAL_FAIL
FROM SSASIDB1.ACHS_COURSE_HIST
WHERE COURSE_GROUP = '2270' AND SCHLYEAR = '2007'
GROUP BY COURSE_GROUP, SCHOOLNUM

Do I have to go into the text and code the compound IF statement? Being new we have done little coding in the body of the text instead. Thanks for the help.

I am going to keep the results in an hold table. There I will sum up TOT_A + TOT_B + TOT_C + TOT_D = TOT_PASS and write out to another hold file. Once all the groups are done (8), they will be read into the final report and percentages calculated for pass and fail for each group. But the hardest part is trying to figure out how to transfer the CASE into IF THEN ELSE.


7.6.5, Windows 2003, PDF, email, extract files
 
Posts: 10 | Location: Las Vegas NV | Registered: June 16, 2008Report This Post
Expert
posted Hide Post
If you actually looked at a manual, this task would be as simple as it gets:

  
DEFINE FILE WHATEVER
  TOT_A/I9 = IF MARK EQ 'A' THEN 1 ELSE 0;
  TOT_B/I9 = IF MARK EQ 'B' THEN 1 ELSE 0;
  TOT_C/I9 = IF MARK EQ 'C' THEN 1 ELSE 0;
  TOT_D/I9 = IF MARK EQ 'D' THEN 1 ELSE 0;
  TOT_F/I9 = IF MARK EQ 'F' THEN 1 ELSE 0;
END

TABLE FILE WHATEVER
SUM
   TOT_A
   TOT_B
   TOT_C
   TOT_D
   TOT_F
     COMPUTE TOT_PASS/I9 = TOT_A + TOT_B + TOT_C + TOT_D;
  BY COURSE_GROUP 
  BY SCHOOLNUM
WHERE SCHLYEAR EQ '2007';
  ON TABLE HOLD AS GOT_EM_ALL
END
-RUN

TABLE FILE GOT_EM_ALL
PRINT *
END
-EXIT


Try the above, check out the output, do some business analysis and see if you can't figure the rest out on your own...

Download some manuals...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
You might also look into using SQL passthru option. Don't forget all of your selection criteria if you use Tom's example. Defines occur on each record selected.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Guru
posted Hide Post
You can use Toms idea, but instead of using Defines, you use Computes.
Sum
Compute TOT_A/I9 = IF MARK EQ 'A' THEN 1 ELSE 0;
Compute TOT_B/I9 = IF MARK EQ 'B' THEN 1 ELSE 0;
Compute TOT_C/I9 = IF MARK EQ 'C' THEN 1 ELSE 0;
Compute TOT_D/I9 = IF MARK EQ 'D' THEN 1 ELSE 0;
Compute TOT_F/I9 = IF MARK EQ 'F' THEN 1 ELSE 0;
COMPUTE TOT_PASS/I9 = TOT_A + TOT_B + TOT_C + TOT_D;
etc.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
Bear in mind that all the fields in the formula for a Compute are taken from the summary values ("internal matrix"). So when you code the Computes,as sketched by RSquared, the references to MARK will all be to a single value.

(Under the covers, TABLE will insert MARK [in effect LST.MARK] NOPRINT as a hidden verb object, and the Computes will refer to its "summed" value).

Since that value can match at most one of the listed letters, at most one of the TOT_x Compute values will be 1 and the rest 0; and TOT_PASS will thus evaluate to 1 (or perhaps 0), not the sum of counts for all the letter values.

Stick with DEFINE.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
What manuals do you suggest I download? We have taken 351 and 354 but obviously those classes only taught the surface. Now I need to get under the covers, so to speak. Do most of you code behind the scenes or with the GUI?


7.6.5, Windows 2003, PDF, email, extract files
 
Posts: 10 | Location: Las Vegas NV | Registered: June 16, 2008Report This Post
Expert
posted Hide Post
Most of us do a little bit of everything.

If you want to look at coding, check out the wf761crlang.pdf (Creating Reports with the WebFOCUS Language) and wf761func.pdf (Using Functions) and wf761appslang (Developing Reporting Applications).


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
vegaskitkat,

What Ginny said! THANKS! Ginny...

Also, in Developer Studio, there is a Help link on the top banner.

Mr. Jack Gross,

Thank you! couldn't have said it better myself...
COMPUTE is for ROW manipulation(flags, totals, percentages, etc.) AFTER extract based on the BY columns.

When interviewing, asking the difference between DEFINE and COMPUTE is very telling...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Tom,

What are you talking about "When interviewing, asking the difference between DEFINE and COMPUTE is very telling"? This has nothing to do with the question posed by vegaskitkat, who stated right at the beginning that this was a newbie question - you can't interview a newbie and expect them to know the difference between DEFINE and COMPUTE.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
quote:
SELECT COURSE_GROUP, SCHOOLNUM
SUM(CASE WHEN MARK = 'A' THEN 1 ELSE 0 END) AS TOT_A,
SUM(CASE WHEN MARK = 'B' THEN 1 ELSE 0 END) AS TOT_B,
SUM(CASE WHEN MARK = 'C' THEN 1 ELSE 0 END) AS TOT_C,
SUM(CASE WHEN MARK = 'D' THEN 1 ELSE 0 END) AS TOT_D,
SUM(CASE WHEN MARK = 'F' THEN 1 ELSE 0 END) AS TOT_F) AS TOTAL_FAIL
FROM SSASIDB1.ACHS_COURSE_HIST
WHERE COURSE_GROUP = '2270' AND SCHLYEAR = '2007'
GROUP BY COURSE_GROUP, SCHOOLNUM


I would do this

TABLE FILE SSASIDB1.ACHS_COURSE_HIST
SUM CNT.MARK 
BY COURSE_GROUP
BY SCHOOLNUM
ACROSS MARK
ON COURSE_GROUP SUBHEAD
"<COURSE_GROUP"
ON COURSE_GROUP SUBTOTAL
ON TABLE ROW-TOTAL
END


I don't know how to calculate percentage pass or fail (I live in Europe and we have an other system) but I'm rather sure that the calculation can be done in the same process.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
Francis,

Wow! Just making a statement, maybe the "newbies" will look up the differences.
I didn't ask "you" to paint my house...Geez

A little touchy, "again", today??


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
Sorry guys, I guess I wasn't thinking. Mea Culpa, mea culpa.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 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     newbie question on IF THEN ELSE

Copyright © 1996-2020 Information Builders