Frustrating Error

[Dario1984]

I'm new to SQL and have tried a number of ways to fix my "missing right parenthesis" error. It puts an asterisk under the keywork ORDER.

CREATE VIEW DRINKER_LIKES_PUB_LOC_VIEW (NAME, DRINK, PUB, STREET, ADDNO) AS
(
SELECT DRINKER_LIKES_PUB_VIEW.Name, DRINKER_LIKES_PUB_VIEW.Drink, DRINKER_LIKES_PUB_VIEW.Pub, Street, Bldg#
FROM Drinker_Likes_Pub_View LEFT OUTER JOIN Located
ON Drinker_Likes_Pub_View.Pub = Located.Pub
ORDER BY Name, Drink
);

Any knowledge about this error would be apprecaited.

 

[SantaMufasa]

Dario,

Oracle does not allow an "ORDER BY" in the SELECT statement that defines a VIEW. If you wish to "ORDER BY Name, Drink", then you do that after you have created the view, as you assess the view.

Let us know how things go following that change.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Sorry, I meant to say "...access the view." (Note to self: Step 1...engage brain, Step 2...engage fingers.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dario1984]

Thanks mate. It's nice to pick up these little important pieces of knowledge from experienced users.

 

[Dario1984]

If I use that view to build up another view, can I sort it before I create the next view ?

 

[SantaMufasa]

Dario,

One of the specifications of a relational table is that the data is "unordered". You should never base any part of an application upon the physical order of a table's rows.

A practical/functional reason why you must not depend on the physical order of rows is because if someone DELETEs one or more rows from a table, when a subsequent INSERT occurs, that INSERT can place the new row into the physical bytes of a previously DELETEd row, thus ruining the sort order upon which your application inappropriately depends.

Therefore, it is good form to always use an ORDER BY in your access statements at query time; do not ever depend upon physical position of rows.

Thoughts???

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.