Creating SQL Views

Creating SQL Views

Post by Kiran » Sun, 28 May 2000 04:00:00



Hi,

I could not able to executing .sql file in sqlquery analizer. The file
contails some create views statements it is like follows:
The whole thing is a exmp.sql file, when I am executing this firl I am
getting error at  the end of the semi colon';'
When I am creating tables by this manner it is accepting. Why it is not
accepting for views? Please let me know How can I resolve this issue. I want
to execute number of views which are there in a single .sql file

Thank you,

regards,
Kiran


CREATE VIEW VEmployee AS
                     (
                    SELECT
                    CEmployee.a_objectID a_objectID,
                    CEmployee.a_objectType a_objectType,
                    CEmployee.a_createdOn a_createdOn,
                    CEmployee.a_updatedOn a_updatedOn,
                    CEmployee.a_EmpNumber a_EmpNumber,
                    CEmployee.a_title a_title,
                    CEmployee.a_salary a_salary,
                    CEmployee.a_dateOfBirth a_dateOfBirth,
                    CPerson.a_firstName a_firstName,
                    CPerson.a_lastName a_lastName,
                    CPerson.a_BusinessID a_Business,
                    CEmployee.a_WorkingDepartmentID a_WorkingDepartment,
                    CEmployee.a_WorkingDepartmentSeq a_WorkingDepartmentSeq
                    FROM
                    CEmployee,CPerson
                    WHERE
                    CEmployee.a_objectID = CPerson.a_objectID
                    );

CREATE VIEW VManager AS
                     (
                    SELECT
                    CManager.a_objectID a_objectID,
                    CManager.a_objectType a_objectType,
                    CManager.a_createdOn a_createdOn,
                    CManager.a_updatedOn a_updatedOn,
                    CManager.a_isExperienced a_isExperienced,
                    CEmployee.a_EmpNumber a_EmpNumber,
                    CEmployee.a_title a_title,
                    CEmployee.a_salary a_salary,
                    CEmployee.a_dateOfBirth a_dateOfBirth,
                    CPerson.a_firstName a_firstName,
                    CPerson.a_lastName a_lastName,
                    CPerson.a_BusinessID a_Business,
                    CEmployee.a_WorkingDepartmentID a_WorkingDepartment,
                    CEmployee.a_WorkingDepartmentSeq a_WorkingDepartmentSeq
                    FROM
                    CManager,CEmployee,CPerson
                    WHERE
                    CManager.a_objectID = CEmployee.a_objectID AND
                    CEmployee.a_objectID = CPerson.a_objectID
                    );

CREATE VIEW VDealer AS
                     (
                    SELECT
                    CDealer.a_objectID a_objectID,
                    CDealer.a_objectType a_objectType,
                    CDealer.a_createdOn a_createdOn,
                    CDealer.a_updatedOn a_updatedOn
                    FROM
                    CDealer,COrganization,CBusiness
                    WHERE
                    CDealer.a_objectID = COrganization.a_objectID AND
                    COrganization.a_objectID = CBusiness.a_objectID
                    );

CREATE VIEW VClassB AS
                     (
                    SELECT
                    CClassB.a_objectID a_objectID,
                    CClassB.a_objectType a_objectType,
                    CClassB.a_createdOn a_createdOn,
                    CClassB.a_updatedOn a_updatedOn,
                    CClassA.a_ClassCID a_ClassC,
                    CClassB.a_ClassCID a_ClassC
                    FROM
                    CClassB,CClassA
                    WHERE
                    CClassB.a_objectID = CClassA.a_objectID
                    );

 
 
 

Creating SQL Views

Post by Kalen Delane » Sun, 28 May 2000 04:00:00


Please do not post the same Q to multiple sections. This has already been
answered.

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


> Hi,

> I could not able to executing .sql file in sqlquery analizer. The file
> contails some create views statements it is like follows:
> The whole thing is a exmp.sql file, when I am executing this firl I am
> getting error at  the end of the semi colon';'
> When I am creating tables by this manner it is accepting. Why it is not
> accepting for views? Please let me know How can I resolve this issue. I
want
> to execute number of views which are there in a single .sql file

> Thank you,

> regards,
> Kiran


> CREATE VIEW VEmployee AS
>                      (
>                     SELECT
>                     CEmployee.a_objectID a_objectID,
>                     CEmployee.a_objectType a_objectType,
>                     CEmployee.a_createdOn a_createdOn,
>                     CEmployee.a_updatedOn a_updatedOn,
>                     CEmployee.a_EmpNumber a_EmpNumber,
>                     CEmployee.a_title a_title,
>                     CEmployee.a_salary a_salary,
>                     CEmployee.a_dateOfBirth a_dateOfBirth,
>                     CPerson.a_firstName a_firstName,
>                     CPerson.a_lastName a_lastName,
>                     CPerson.a_BusinessID a_Business,
>                     CEmployee.a_WorkingDepartmentID a_WorkingDepartment,
>                     CEmployee.a_WorkingDepartmentSeq

a_WorkingDepartmentSeq

- Show quoted text -

Quote:>                     FROM
>                     CEmployee,CPerson
>                     WHERE
>                     CEmployee.a_objectID = CPerson.a_objectID
>                     );

> CREATE VIEW VManager AS
>                      (
>                     SELECT
>                     CManager.a_objectID a_objectID,
>                     CManager.a_objectType a_objectType,
>                     CManager.a_createdOn a_createdOn,
>                     CManager.a_updatedOn a_updatedOn,
>                     CManager.a_isExperienced a_isExperienced,
>                     CEmployee.a_EmpNumber a_EmpNumber,
>                     CEmployee.a_title a_title,
>                     CEmployee.a_salary a_salary,
>                     CEmployee.a_dateOfBirth a_dateOfBirth,
>                     CPerson.a_firstName a_firstName,
>                     CPerson.a_lastName a_lastName,
>                     CPerson.a_BusinessID a_Business,
>                     CEmployee.a_WorkingDepartmentID a_WorkingDepartment,
>                     CEmployee.a_WorkingDepartmentSeq

a_WorkingDepartmentSeq

- Show quoted text -

Quote:>                     FROM
>                     CManager,CEmployee,CPerson
>                     WHERE
>                     CManager.a_objectID = CEmployee.a_objectID AND
>                     CEmployee.a_objectID = CPerson.a_objectID
>                     );

> CREATE VIEW VDealer AS
>                      (
>                     SELECT
>                     CDealer.a_objectID a_objectID,
>                     CDealer.a_objectType a_objectType,
>                     CDealer.a_createdOn a_createdOn,
>                     CDealer.a_updatedOn a_updatedOn
>                     FROM
>                     CDealer,COrganization,CBusiness
>                     WHERE
>                     CDealer.a_objectID = COrganization.a_objectID AND
>                     COrganization.a_objectID = CBusiness.a_objectID
>                     );

> CREATE VIEW VClassB AS
>                      (
>                     SELECT
>                     CClassB.a_objectID a_objectID,
>                     CClassB.a_objectType a_objectType,
>                     CClassB.a_createdOn a_createdOn,
>                     CClassB.a_updatedOn a_updatedOn,
>                     CClassA.a_ClassCID a_ClassC,
>                     CClassB.a_ClassCID a_ClassC
>                     FROM
>                     CClassB,CClassA
>                     WHERE
>                     CClassB.a_objectID = CClassA.a_objectID
>                     );


 
 
 

1. Creating a local view using CREATE SQL VIEW

I need to create a local view in a database using FoxPro/SQL commands.

I can create the view with fields & join conditions by:
    CREATE SQL VIEW viewname AS SELECT...
but I do not seem to have control over the items on the "Update Criteria"
tab in the View Designer.

How about copying a local view from one database to another?

I have seen reference to Stonefield (SDT) a number of times. Would it solve
my problem?

TIA,

yuki

2. Problems with printing using Crystal Reports

3. Remote view prompting for parameters on CREATE SQL VIEW

4. create new table

5. Remote View prompting for parameters on CREATE SQL VIEW

6. Nonlogged updates

7. VFP 3: Create SQL VIEW?

8. CREATE SQL VIEW REMOTE (gets all records, Why?)

9. create sql view in vfp 3.0

10. CREATE SQL VIEW is s-l-o-w

11. create sql view trouble - please help

12. Any Way Around Create SQL View Limitation