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.

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

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.

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.

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,

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?

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).

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,

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.

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.

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??

Sorry guys, I guess I wasn't thinking. Mea Culpa, mea culpa.