Continuation of 'Simple Query'

Hi Guys,

I posted a question here yesterday concerning a query about streets and houses etc., and you came up with the brilliant solution of using DECODE. Now I got so engrossed in the possibilities of DECODE that I spent half the night just seeing what it could do, creating more and more complicated examples until I hit on one that I just can't get my head around, getting absolutely lost in a maze of DECODES and GROUP BYs. This is actually based on a course system that is being used at my workplace (I have a very limited imagination).

I'm almost at a point that I think it can't be done this way at all. But I'd really like your opinion on this example.

I hope you don't think it's a waste of your time, but this is driving me crazy.

This is the example: I have a number of students that have to complete a number of qualifications. Each of these qualifications are made up of courses that are either required or recommended. Now, the completed courses have a completion date and I want to know the status of the qualifications for each student, but now it gets complicated:

A qualification for a student is marked as completed if:

1) All of the required courses for this qualification are completed, regardless of whether any of the recommended courses have been completed or not.
2) If even only one of the required courses is uncompleted, the qualification is uncompleted.
3) And this is the part I can't work out: If a qualification only has recommended and no required courses, the qualification is regarded as completed, regardless of whether any of the courses were completed or not!!!! Yikes!

This is an example table:

--Student---------Qualification----------Course-------------Course Status---------Course Completion Date

1) ---A----------------Q1----------------C1-------------------required--------------------has date-----
------A----------------Q1----------------C2-------------------required--------------------has date-----

2)----A----------------Q2----------------C3-------------------recommended--------------has date-----
------A----------------Q2----------------C5-------------------required---------------------no date-----
------A----------------Q2----------------C8-------------------required--------------------has date-----

3)----B----------------Q5----------------C3-------------------recommended---------------no date-----
------B----------------Q5----------------C7-------------------recommended---------------no date-----

4)----C----------------Q4----------------C6-------------------recommended---------------no date-----
------C----------------Q4----------------C8-------------------required---------------------no date-----

So as a result, I'd like to know how you get the following results for the table above:

Block 1): Stundent A's qualification Q1 is completed because all courses are required and have been completed
Block 2): Stundent A's qualification Q2 is uncompleted because one required course is uncompleted
Block 3): Stundent B's qualification Q5 is completed !!!!!!, but please don't ask me why, this is an actual example from work
Block 4) Stundent C's qualification Q4 is uncompleted because the one required course is uncompleted.

So the result should be:

Student-----Qualification--------Completed
---A--------------Q1------------------Y------
---A--------------Q2------------------N------
---B--------------Q5------------------Y------
---C--------------Q4------------------N------

Please help me here, otherwise my weekend will be gone. Can you still use DECODE on this one?

Your weekend is gone by now, but if you are still looking for the solution on this one with DECODE, here is one:

Code:

---

SQL> CREATE TABLE blah
  2    (student VARCHAR2(1),
  3    qualification VARCHAR2(2),
  4    course VARCHAR2(2),
  5    course_status VARCHAR2(15),
  6    completion_date DATE);

Table created.

SQL> INSERT INTO blah VALUES ('A', 'Q1', 'C1', 'required', SYSDATE);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('A', 'Q1', 'C2', 'required', SYSDATE);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('A', 'Q2', 'C3', 'recommended', SYSDATE);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('A', 'Q2', 'C5', 'required', NULL);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('A', 'Q2', 'C8', 'required', SYSDATE);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('B', 'Q5', 'C3', 'recommended', NULL);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('B', 'Q5', 'C7', 'recommended', NULL);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('C', 'Q4', 'C6', 'recommended', NULL);

1 row created.

SQL>
SQL> INSERT INTO blah VALUES ('C', 'Q4', 'C8', 'required', NULL);

1 row created.

SQL> SELECT student, qualification,
  2        DECODE(COUNT(DECODE(course_status, 'required',
  3                        DECODE(completion_date, NULL, 1))),
  4          0, 'Y', 'N') completed
  5  FROM blah
  6  GROUP BY student, qualification;

STUDENT QUALIFICATION COMPLETED
------- ------------- ---------
A      Q1            Y
A      Q2            N
B      Q5            Y
C      Q4            N

---

Note that this is for 8i or earlier - if you are using 9i or above you could use NULLIF() instead of that third DECODE. I guess with NULLIF it would become a bit more "readable". Of course, it can be done without nested third DECODE or NULLIF, but then I think the inner (seccond) DECODE would become even more "cryptic".

But generally I would suggest that you concentrate less in the usage of DECODE() and more on the usage of the CASE. With CASE things become much more "readable" and "ubderstandable"....

Hi jmodic,

I can't tell you haow grateful I am for your reply. It works fantastically and I'm going to spend quite a while on examining exaclty how you did it.

I've taken note of your recommendation using CASE and I'm getting interested in that as well, so I'm going to spend some time having fun working this problem out using that instead.

Bye
Peer