ORA-00933: SQL command not properly ended - Create View - Join

Question

I'm trying to create new view from 2 different table of same schema. This is my query, let me know if I'm missing anything. When I check the syntax it is fine and test it, throws 00933 error.

CREATE OR REPLACE FORCE VIEW "APDA"."countview" 
        (
        "dealidint", "companyidint", "nametxt", "county", "street", 
        "state", "city", "zip", "geocodelatdec", "geocodelongdec", 
        "volidint", "reportdate", "vehicletotalint", "salvagetotalint"
         )
AS 

SELECT a."dealidint",
          a."companyidint",
          a."nametxt",
          a."county",
          a."street",
          a."state",
          a."city",
          a."zip",
          a."geocodelatdec",
          a."geocodelongdec",
          c."dealervolumeidint",
          c."reportdate",
          c."vehicletotalint",
          c."salvagetotalint"

     FROM "APDA"."company" a
     JOIN
          "APDA"."volume" c
     ON   c."dealidint" = a."dealidint";

Are your table names and column names really specified in lower case with double-quotes? If yes, then yuck. If no, then try removing the double-quotes (preferred) or putting all names in upper case. — APC
– APC, Commented Jul 21, 2018 at 2:00
How is the CREATE OR REPLACE VIEW being executed? If you're using some kind of client-server connection, try getting rid of the trailing semicolon on the CREATE OR REPLACE VIEW. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 21, 2018 at 2:53
Try running the SELECT query alone and see if it throws up any error — shrek
– shrek, Commented Jul 21, 2018 at 5:15
If you are using SQL*Plus, then the empty lines will act as a statement delimiter. — user330315
– user330315, Commented Jul 21, 2018 at 10:10

Dave Costa · Accepted Answer · 2018-07-21 00:06:54Z

0

I don't see the reason. The only suspect thing I notice is the two blank lines. In SQLPlus I don't think these would cause this error, but they would cause the command to be misinterpreted.

My suggestions are: - try a different tool. If you are getting the error in SQL Developer, try it in SQLPlus. It won't necessarily work, but you might get different feedback. - reduce it to a minimal statement that works, then add elements back in one at a time until the error occurs

answered Jul 21, 2018 at 0:06

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Atchouda Over a year ago

Thanks for your comments.. .OK will try and revert. Infact, we have a similar view syntax and it is working. When I tried the similar working view and try to test that, throws an error. It is really weird. Have to dig deep ..

Yakamuz · Accepted Answer · 2018-07-21 09:49:25Z

0

your column name "volidint" not in selected column, i see "dealervolumeidint" select vs "volidint".

answered Jul 21, 2018 at 9:49

Yakamuz

857 bronze badges

2 Comments

Atchouda Over a year ago

It was my typo.. I had to scrub some of the columns with a diff name.

Dave Costa Over a year ago

In any case, the column names projected by the view don't need to match the column names being selected. That's the point of being able to specify column names for the view.

Nilay shah · Accepted Answer · 2018-07-23 07:25:18Z

0

Column name "volidint" is not available in your below select query. Please correct that by using "As".

CREATE OR REPLACE FORCE VIEW "APDA"."countview" 
        (
        "dealidint", "companyidint", "nametxt", "county", "street", 
        "state", "city", "zip", "geocodelatdec", "geocodelongdec", 
        "volidint", "reportdate", "vehicletotalint", "salvagetotalint"
         )
AS 

SELECT a."dealidint",
          a."companyidint",
          a."nametxt",
          a."county",
          a."street",
          a."state",
          a."city",
          a."zip",
          a."geocodelatdec",
          a."geocodelongdec",
          c."dealervolumeidint" as "volidint",
          c."reportdate",
          c."vehicletotalint",
          c."salvagetotalint"

     FROM "APDA"."company" a
     JOIN
          "APDA"."volume" c
     ON   c."dealidint" = a."dealidint";

edited Jul 23, 2018 at 7:25

user330315

answered Jul 23, 2018 at 7:18

Nilay shah

773 bronze badges

1 Comment

user330315 Over a year ago

The alias in the select list is irrelevant if a column list is specified in the create view part. Plus: this was already mentioned by Yakamuz.

Atchouda · Accepted Answer · 2018-07-26 17:58:25Z

0

Thanks to all you pitched to answer my query. The one I have shared is a stripped down query with the same syntax. The query was fine but my actual query has an extra character on the join. Which is like - "APDA"."vvolume".

After that I was able to create the view.

Thanks again.

answered Jul 26, 2018 at 17:58

Atchouda

112 bronze badges

Collectives™ on Stack Overflow

ORA-00933: SQL command not properly ended - Create View - Join

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related