> I can run this select
> /* this will later be a view */
> select Ap.Id, Ap.AgencyId,
> Ag.FullName, Ag.Abbreviation,
> Pro.FullName, Pro.Abbrev, Pro.ProgStart, Pro.ProgEnd,
> Fb.FullName, Fb.Abbrev
> from AgenciesPrograms Ap
> left join Agencies Ag on Ag.AgencyId = Ap.AgencyId
> left join Programs Pro on Pro.ProgId = Ap.ProgId
> left join FundingBodies Fb on Fb.FbId = Pro.FbId
> ;
>
> but when I attempt to convert it to view by prefixing
> create View AViewName
> as
>
> then select statement
>
> I receive this error message
>
> /*ISC ERROR CODE:335544351
>
> ISC ERROR MESSAGE:
> unsuccessful metadata update
> STORE RDB$RELATION_FIELDS failed
> attempt to store duplicate value (visible to active transactions) in
> unique index "RDB$INDEX_15"*/
>
> What did I do wrong?

As you reference more fields called FullName and Abbrev
in your SELECT statement above, the view would end up
with equally named field names.

Specify the view field names explicitely and you should
be safe. For example:

CREATE VIEW AVIEWNAME (
AP_ID,
AP_AGENCYID,
AG_FULLNAME,
AG_ABBREVIATION,
PRO_FULLNAME,
PRO_ABBREV,
...
)
AS
...


HTH,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com