CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Takkun
Takkun

Reputation: 6361

Oracle SQL: invalid identifier

Could anyone explain to me why dLoad is an invalid identifier in the GROUP BY line? This runs fine on Mysql but I can't get it to work with Oracle.

CREATE OR REPLACE VIEW DoctorsLoad AS
    SELECT dID, gender, specialty, 'Overloaded' AS dLoad
    FROM Doctor D, Examine E
    WHERE D.dID = E.doctor_id
    GROUP BY dID, gender, specialty, dLoad
    HAVING COUNT(*) > 10
    UNION
    SELECT dID, gender, specialty, 'Underloaded' AS dLoad
    FROM Doctor D, Examine E
    WHERE D.dID = E.doctor_id
    GROUP BY dID, gender, specialty, dLoad
    HAVING COUNT(*) <= 10;

Upvotes: 0

Views: 1414

Answers (1)

WoMo
WoMo

Reputation: 7246

With Oracle RDMS, you cannot use an alias in the GROUP BY clause, but for a literal like 'Overloaded' you do not need to include it in the group by anyway.

CREATE OR REPLACE VIEW DoctorsLoad AS
    SELECT dID, gender, specialty, 'Overloaded' AS dLoad
    FROM Doctor D, Examine E
    WHERE D.dID = E.doctor_id
    GROUP BY dID, gender, specialty
    HAVING COUNT(*) > 10
    UNION ALL
    SELECT dID, gender, specialty, 'Underloaded' AS dLoad
    FROM Doctor D, Examine E
    WHERE D.dID = E.doctor_id
    GROUP BY dID, gender, specialty
    HAVING COUNT(*) <= 10;

I also suggest using a UNION ALL which avoids a filter operation to remove duplicate rows.

Upvotes: 3

Related Questions