For views that are declared as anything other than "select * from one_table" you need to declare the output column list.
CREATE VIEW AVIEW (COL1, COL2, ...)
AS
SELECT....

Select the columns in exactly the same order as the declared field list. Name the duplicate columns in the output list, not as aliases in the SELECT clauses.

Regards,
Helen


At 02:59 PM 07-07-01 -0500, you wrote:

>Hi,
>
>I'm having problems accessing particular columns in views. the view consists of tables with columns that have the same name (i.e Company.Phone, Users.Phone) with a synonym uPhone for Users.Phone and just Phone for Company.Phone.
>
>The metadata for the view is shown below with the problems stated after.
>
>
> CREATE VIEW "SUSERCOMPANY" (
> "COMPANY",
> "ADDRESS1",
> "ADDRESS2",
> "CITY",
> "STATE",
> "POSTAL",
> "COUNTRY",
> "WEBSITE",
> "DUNS",
> "EMPLOYEES",
> "PHONE",
> "FAX",
> "YEARSINOPERATION",
> "ISINVITEE",
> "USERID",
> "FIRSTNAME",
> "LASTNAME",
> "TITLE",
> "uPhone",
> "uFax",
> "EMAIL",
> "PASSWORDTEXT",
> "SUBSCRIBERLEVEL",
> "COMPANYID",
> "TOS",
> "TEMPID",
> "LASTLOGINTIME",
> "LASTLOGINIP",
> "LOGINID"
>) AS
>
>SELECT Company.company, Company.address1,
> Company.address2, Company.city, Company.state,
> Company.postal, Company.country, Company.website,
> Company.duns, Company.employees, Company.phone,
> Company.fax, Company.YearsInOperation, Company.IsInvitee,
> Users.UserID, Users.firstname, Users.lastname, Users.title,
> Users.phone, Users.fax,
> Users.email,
> Users.passwordtext, Users.SubscriberLevel, Users.CompanyID,
> Users.TOS, Users.TempID, Users.LastLoginTime,
> Users.LastLoginIP, Users.LoginID
>FROM Company INNER JOIN
> Users ON Company.CompanyID = Users.CompanyID
>;
>
>
>If I try to access the uPhone column with the statement "SELECT uPhone FROM SUSERCOMPANY;", it throws an error -206 (column unknown) It works with any other column though. I also tried UPHONE thinking it was case sensitive but that didn't work either.
>
>Doing a SELECT * FROM SUSERCOPANY DOES return the uPhone and uFax fields though with appropriate values. Is there a specific method for accessing these kind of fields? Thanks in advance! I'll be happy to supply more information if necessary.
>
>-Surojit Niyogi
>saniyogi@...
>
>
>[Non-text portions of this message have been removed]
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________