Trouble Creating a view

[dgillz]

I am trying to create a view and I am getting a syntax error. Can anyone assist with this?

Create View ModifiedScreens as

select screen_id, screen_name from arscrfil_sql where (screen_name <> 'MACOLA') union
select screen_id, screen_name from apscrfil_sql where (screen_name <> 'MACOLA')

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[dgillz]

OK, I have my view created, and I check my syntax with no errors, but when I click OK or apply, the screen disappears, and Pervasive control center closes with no warning, and nothing is saved.

I am running P.SQL 2000i, workstation edition.

Ideas?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[TomKane]

Are you creating your view through the PCC interface or are you using a SQL statement to do it?

 

[dgillz]

I am using the PCC interface, opening the database tree, right click, and new view.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Linda84]

Pervasive.SQL does not currently support UNION in a VIEW (refer to the CREATE VIEW documentation). We are looking at adding this in the future.

As of Pervasive.SQL v8 SP1, we have added support the following:

Support a single VIEW UNION with a single VIEW or a base table (if a view appears in the FROM clause within a UNION then the view must be the only item in the FROM clause, no other views or base tables and no joins). Examples:

create view v1 as select * from person

select * from v1 union select * from v1

create view v2 (vid, vsum) as select id, sum(id)
from person group by id

select vid, vsum from v2 union select id, sum(id)
from person group by id


Linda
Pervasive Software Support