extract month to order, from calculated field

[Tracey]

Hi guys

I have a query which returns a calculated field of type date

i would like to order my records by the month of that field

something like:

  select distinct pe.commencedate, sn.skillid, s.name sname, sn.timeperiodid, tp.weeks, p.name pname, p.companyid, (pe.commencedate + (weeks * 7)) as RequiredBy
from personemployment pe
join person p on p.personid = pe.personid
join skillneeded sn on sn.employmentid = pe.employmentid
left join timeperiod tp on tp.timeperiodid = sn.timeperiodid
join skill s on s.skillid = sn.skillid
where p.companyid = 1
and ((pe.commencedate + (weeks * 7)) >= '3/12/03' and (pe.commencedate + (weeks * 7)) <= '3/12/04')
and sn.skillid not in
(select skillid from skillattempt where (personid = p.PersonID and skillid = sn.skillid
and result = 'P' and (validto is null or validto > '3/12/03')))
                order by extract(month from 8)

only firebird1.0 doesnt like extract there. is there a way i can do this?

Tracey
Software Developer / Web Master
Logis
tracey@logis.co.nz
(025) 213-1065

 

[Otto]

Hi!

Put the 'extract(...' into the start of the select statement (to simplify the 'order') and use the field number only in the 'order'.

select distinct extract(month from (pe.commencedate+(weeks*7))), ...
order by 1

Otto