Creating a view

From: Vern Reynolds <vernreynolds_at_yahoo.com>
Date: Mon, 24 Mar 2003 12:55:11 -0500
Message-ID: <fxHfa.11513$_U4.9306_at_fe07.atl2.webusenet.com>

Hello experts,

I am trying to overcome what is probably a simple problem but I'm getting very frustrated. I do the bulletin for my church and I'm developing databases to lighten my load each time it's time to print. I have two tables, one called schedule and one called hymnal. The hymnal contains two fields, page and title. The schedule contains various fields but to shorten things one called SSOPENINGHYMNNUMBER, one called SSCLOSINGHYMNNUMBER, one called WSOPENINGHYMNNUMBER and one called WSCLOSINGHYMNNUMBER.

By now you've probably realized where I'm going with this... I want to create a view that will read the value in each of the above fields in the schedule table and lookup the title in the hymnal table.

I am able to successfully do this for one, SSOPENINGHYMNNUMBER, but whenever I try to get the others, I get errors. Here's what I have...

create view vern (ssopen, ssclose, wsopen, wsclose) as SELECT TITLE from HYMNAL, SCHEDULE

where HYMNAL.PAGE=CHURCH.SCHEDULE.SSOPENINGHYMNNUMBER
where HYMNAL.PAGE=CHURCH.SCHEDULE.SSCLOSINGHYMNNUMBER
where HYMNAL.PAGE=CHURCH.SCHEDULE.WSOPENINGHYMNNUMBER
where HYMNAL.PAGE=CHURCH.SCHEDULE.WSCLOSINGHYMNNUMBER;

I get this message in Oracle...

SQL> create view vern (ssopen, ssclose, wsopen, wsclose)   2 as SELECT TITLE from HYMNAL, SCHEDULE

  3  where HYMNAL.PAGE=CHURCH.SCHEDULE.SSOPENINGHYMNNUMBER
  4  where HYMNAL.PAGE=CHURCH.SCHEDULE.SSCLOSINGHYMNNUMBER
  5  where HYMNAL.PAGE=CHURCH.SCHEDULE.WSOPENINGHYMNNUMBER
  6  where HYMNAL.PAGE=CHURCH.SCHEDULE.WSCLOSINGHYMNNUMBER;

where HYMNAL.PAGE=CHURCH.SCHEDULE.SSCLOSINGHYMNNUMBER
*

ERROR at line 4:
ORA-00933: SQL command not properly ended

SQL> /
where HYMNAL.PAGE=CHURCH.SCHEDULE.SSCLOSINGHYMNNUMBER
*

ERROR at line 4:
ORA-00933: SQL command not properly ended

Could someone explain what I'm doing wrong?

Thanks,

Vern Received on Mon Mar 24 2003 - 18:55:11 CET