function MONTH

[gmennessier]

Hi everybody!

I've got a problem,
I'm programing in ASP and I want to do:
SELECT MONTH(date)as mois,year(date) as année FROM LOGON

But it returns the error:
Microsoft OLE DB Provider for Oracle error '80040e14'
ORA-00904: "MONTH" : identificateur non valide

What does it significate?
Does the function MONTH exist?
What must I do?

Please,Help me.
Thanks a lot.

 

[ThomVF]

Try:

SELECT TO_CHAR(date,'Month')as mois,
TO_CHAR(date,'YYYY') as année
FROM LOGON

=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[gmennessier]

A little harder now.
I have a request which ran under SQL server, but when I try with Oracle, it bugs:
SELECT Sum(RAPPORT_MAINOEUVRE.DureeIntervention) AS SumDuree, Count(RAPPORT_MAINOEUVRE.refRapport) AS Actions, TO_CHAR(DateCreation,'MM') AS Mois,
TO_CHAR(DateCreation,'yyyy') AS Annee
FROM (((RAPPORT RIGHT OUTER JOIN RAPPORT_MAINOEUVRE ON RAPPORT.ID = RAPPORT_MAINOEUVRE.refRapport)
LEFT OUTER JOIN ARBORESCENCE ON RAPPORT.refArborescence = ARBORESCENCE.ID)
LEFT OUTER JOIN EQUIPEMENT ON RAPPORT.refEquipement = EQUIPEMENT.ID)
LEFT OUTER JOIN EMPLOYE ON RAPPORT_MAINOEUVRE.refIntervenant = EMPLOYE.ID WHERE (RAPPORT.DateCreation BETWEEN '24/06/2002' AND '24/07/2002') AND (ARBORESCENCE.ID=1)
GROUP BY CHAR_TO(DateCreation,'yyyy'), CHAR_TO(DateCreation,'MM')
ORDER BY CHAR_TO(DateCreation,'yyyy'), CHAR_TO(DateCreation,'MM')

CHAR_TO doesn't work in the GROUP BY expression.

Please help me.
Thanks a lot.

 

[ThomVF]

In ORACLE, CHAR_TO should be "TO_CHAR"

Also, I am not sure but I think ORACLE will not like the RIGHT OUTER JOIN,LEFT OUTER JOIN syntax.

Try something like this:

select ....
from RAPPORT r, RAPPORT_MAINOEUVRE rm,
ARBORESCENCE a, EQUIPEMENT eq, EMPLOYE em
where r.ID = rm.refRapport
and r.refArborescence = a.ID
and r.refEquipement = eq.ID
and rm.refIntervenant = em.ID
and r.DateCreation BETWEEN
to_date('24/06/2002','DD/MM/YYYY') AND
to_date('24/07/2002','DD/MM/YYYY')
and a.ID=1
group by
TO_CHAR(DateCreation,'yyyy'),
TO_CHAR(DateCreation,'MM')
ORDER BY
TO_CHAR(DateCreation,'yyyy'),
TO_CHAR(DateCreation,'MM')
;



=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Guest_imported]

CHAR_TO .... TO_CHAR,
What a stupid error!!!

Thak you very much, Thom, for your precious help.

 

[ThomVF]

non probleme
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com