Help with SQL Query Sum(Case When

[cantona]

I am trying to write a query that will total up the number of people on a particular course over and above a set age. For the example, the total number of students aged 19 and over. This is the query I have so far..

Select Sum(Case When date_of_birth Is Null Then 0 Else Floor(Months_Between(To_Date('3108' || Substr(Calocc_occurrence_code,1,2),'DDMMRR'),DATE_OF_BIRTH)/12) >19 End)) As 19
from people,registration_units,unit_instance_occurrences
Where people.person_code = registration_units.fes_per_person_code and registration_units.fes_unit_instance_code = Unit_instance_occurrences.fes_uins_instance_code and registration_units.uio_occurrence_code = unit_instance_occurrences.calocc_occurrence_code
group by fes_unit_instance_occurrences.fes_uins_instance_code

calocc_occurrence code displays the current academic year, so Substr(calocc_occurrence_code,1,2) would pull out 06. It is held within the database as 0607. The age function is calculating age as at the beginning of the academic year.

At the moment I am getting the following error;

ERROR at line 1:
ORA-00905: missing keyword

Is there something obvious that I have missed?

What I was hoping for was a course code followed by the number of 19+ people on the course

Can anyone help?

 

[hoinz]

just a first guess:
I don't think you may use a number as a column alias:
... End)) As 19 from people, ...

 

[cantona]

Hi Hoinz,

Thanks for the advice. Ive tried using an alias of Test, but still get the same error??

 

[blom0344]

Floor(Months_Between(To_Date('3108' || Substr(Calocc_occurrence_code,1,2),'DDMMRR'),DATE_OF_BIRTH)/12) >19

That is logical expression, not a value..

Ties Blom

 

[slaing]

2nd guess :

Brackets ??

You seem to be opening 5 but closing 6


Steve

 

[hoinz]

On a second look, there seem to be quite a few syntax and logic errors...
May I recommend you have a look at the use of case and others in your manual?
Homework, btw?

That being said, here are some hints:
1) If I understand correctly, you won't count persons without known birth date:
sum(Case date_of_birth when Null Then 0 Else <other logic here> end)
However it might be a better idea to cover this restriction in your where clause.
2) In <other logic> you will make sure that there is a count of 1, if the age is above 19. Your query seems to suggest a count of 19 (or >19) in this case.
Again, maybe put this logic in the where clause?
3) In your select you missed the course code.
4) So maybe an approach like this:
select fes_unit_instance_occurrences.fes_uins_instance_code, count(*)
from <list of tables>
where <join conditions>
and <age condition>
group by fes_unit_instance_occurrences.fes_uins_instance_code
5) and by the way, if you count those entries where birth date was before some given date, then null values for birth date will not be counted; not extra logic needed.

hope this helps

 

[cantona]

Hi Blom0344,

Thanks,

What would i need to change to correct?

 

[blom0344]

Put the logical expression in the WHEN part of the case construct. I think Hoinz has some points to add as well
You needn't work in null values for birthdates in the CASE when you can suppress them in the WHERE clause..

Ties Blom