Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: ORA-00933 in CREATE VIEW

    Hi guys

    I want to create two views as followd:

    Code:
    CREATE VIEW Staff_advertisment AS
    SELECT F.FStaffName, PS.PStaffName, Count(A.AdsID)
    FROM FullStaff AS F, PartStaff AS PS, Advertisment AS A
    WHERE PS.PStaffID=A.PStaffID OR F.FStaffID=A.FStaffID
    GROUP BY F.FStaffName, PS.PStaffName;
    and

    Code:
    CREATE VIEW Customer_advertisment AS
    SELECT Sum(C.CPaymnetAmount), Count(A.AdsID), I.CusName, A1.AgencyName
    FROM Customer_Payment AS C, Advertisment AS A, IndCustomer AS I, AdsAgency AS A1
    WHERE C.CpaymentID=A.CpaymentID AND (I.CutomerID=C.CutomerID OR A1.AgencyID=C.AgencyID)
    GROUP BY I.CusName, A1.AgencyName
    ORDER BY C.CPaymnetAmount;
    but i receive the "ORA-00933: SQL command not properly ended" for both
    after some googling I removed the ORDER BY and GROUP BY, but it didn't help and i have same problem.

    any suggestion?
    Last edited by hadirj; 10-03-10 at 01:05. Reason: add missing P

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do as below -- remove " AS "
    Code:
      1  CREATE VIEW Staff_advertisment AS
      2  SELECT F.FStaffName, PS.PStaffName, Count(A.AdsID)
      3  FROM FullStaff F, PartStaff PS, Advertisment A
      4  WHERE PS.PStaffID=A.PStaffID OR F.FStaffID=A.FStaffID
      5* GROUP BY F.FStaffName, PS.PStaffName
    SQL> /
    FROM FullStaffF, PartStaffPS, AdvertismentA
                                  *
    ERROR at line 3:
    ORA-00942: table or view does not exist
    I get error since I don't have your tables
    Last edited by anacedent; 10-03-10 at 01:26.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Thanks problems solved but after removing "AS" i received "ORA-00998: must name this expression with a column alias" error and I solved it like:

    Code:
    CREATE VIEW Staff_advertisment (FullStaff , PartStaff , Advertisment) AS 
    SELECT F.FStaffName, PS.PStaffName, Count(A.AdsID)
    FROM FullStaff F, PartStaff PS, Advertisment A
    WHERE PS.PStaffID=A.PStaffID OR F.FStaffID=A.FStaffID
    GROUP BY F.FStaffName, PS.PStaffName;
    putting tables name after name of view as mentioned here ora-00998


  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    that was because you never gave a name to the column "count(a.adsid)"
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •