Need help creating view in oracle

[raf35]

Hi Codeguru,
I am new to oracle. I have created some tables and now would like to create view but the following code generate error.

CREATE VIEW PRESCRIPTIONCOUNT
AS SELECT FIRSTNAME, D.DOCTOR_LIC_NO, D.DOCTOR_LIC_STATE, COUNT(*), MAX(PDATE)
FROM DOCTOR D, PRESCRIPTION P
WHERE D.Doctor_Lic_No = P.Doctor_Lic_No
AND D.Doctor_Lic_State = P.Doctor_Lic_State
GROUP BY FIRSTNAME, D.Doctor_Lic_No, D.Doctor_Lic_State;


The description of the error is:

ORA-00998: must name this expression with a column alias

I appreciate any help.
Thanks,
raf35

[Davey]

The problem is that you are selecting count(*) as a column, but you have not given that column a name.

Change your SQL to read "count(*) as mycolumn" instead of "count(*)"

e.g.

CREATE VIEW PRESCRIPTIONCOUNT
AS SELECT FIRSTNAME, D.DOCTOR_LIC_NO, D.DOCTOR_LIC_STATE, COUNT(*) as MYCOLUMN, MAX(PDATE)
FROM DOCTOR D, PRESCRIPTION P
WHERE D.Doctor_Lic_No = P.Doctor_Lic_No
AND D.Doctor_Lic_State = P.Doctor_Lic_State
GROUP BY FIRSTNAME, D.Doctor_Lic_No, D.Doctor_Lic_State;