DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Continuation of 'Simple Query'

  1. #1
    Join Date
    Nov 2001
    Posts
    118

    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?
    Last edited by Peer Jones; 04-01-2005 at 04:03 AM.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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"....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2001
    Posts
    118
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width