Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Error

Author  Topic 

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-20 : 15:58:48
Hi,
I am using SQL 2000
I got the error: invalid column name 'Bus' and 'D'
=============================
select OFC.OCD as OfficeCode,
([Domphone].[PHNAREA]) & ([Domphone].[PHNEXCH]) &
([Domphone].[PHNNUM])as
BusPhone,
([domphone].[PHNEXT])as BusPhoneExt,
([domphone].[FAXAREA]) & ([domphone].[FAXEXCH]) &
([domphone].[FAXNUM])as
BusFax,
([domphone].[FAXEXT])as BusFaxExt
from OFCNew as OFC LEFT join (OFCCNTCTNew as OfficeContact
LEFT join(OFCPHNNew as OfficePhone LEFT join DOMPHNNew as DomPhone On
OfficePhone.OFC_PHN_UID = DomPhone.OFC_PHN_UID) ON
OfficeContact.OFC_PHN_UID
= OfficePhone.OFC_PHN_UID) ON OFC.OCD = OfficeContact.OCD
where (((OfficeContact.PHN_USE_TYP)="BUS") and
((OfficePhone.PHN_SUBTYP)="D"))


Then I change like below but still get the same error
where (((OfficeContact.PHN_USE_TYP)LIKE "BUS") and
((OfficePhone.PHN_SUBTYP)LIKE "D"))

Then I change like below but still get the error like:Invalid operator for data type. Operator equals boolean AND, type equals
char. (BUS and D are Char data type)

where (((OfficeContact.PHN_USE_TYP)= 'BUS') and
((OfficePhone.PHN_SUBTYP)= 'D'))



Thanks


robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-20 : 16:06:21
One thing you need to look out for, if you came to SQL Server via MS Access, is that Access includes parentheses in JOIN conditions that are absolutely unnecessary, not to mention nested parentheses in WHERE clauses. Plus, Access will nest JOIN clauses and they will simply NOT WORK in SQL Server.

Do yourself a BIG favor, DON'T copy Access queries into SQL Server, do them over. You'll spend just as much time cleaning them up as writing them from scratch.

SELECT OFC.OCD as OfficeCode,
Domphone.PHNAREA + Domphone.PHNEXCH + Domphone.PHNNUM as BusPhone,
domphone.PHNEXT as BusPhoneExt,
domphone.FAXAREA + domphone.FAXEXCH + domphone.FAXNUM as BusFax,
domphone.FAXEXT as BusFaxExt
FROM OFCNew as OFC LEFT join OFCCNTCTNew as OfficeContact ON OFC.OCD = OfficeContact.OCD
LEFT join OFCPHNNew as OfficePhone ON OfficeContact.OFC_PHN_UID = OfficePhone.OFC_PHN_UID
LEFT join DOMPHNNew as DomPhone On OfficePhone.OFC_PHN_UID = DomPhone.OFC_PHN_UID
WHERE OfficeContact.PHN_USE_TYP='BUS' and OfficePhone.PHN_SUBTYP='D'


Go to Top of Page
   

- Advertisement -