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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

117 Posts

Posted - 03/20/2002 :  15:58:48  Show Profile  Reply with Quote
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].[PHNEXT])as BusPhoneExt,
([domphone].[FAXAREA]) & ([domphone].[FAXEXCH]) &
([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
= OfficePhone.OFC_PHN_UID) ON OFC.OCD = OfficeContact.OCD
where (((OfficeContact.PHN_USE_TYP)="BUS") and

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'))


Most Valuable Yak

15732 Posts

Posted - 03/20/2002 :  16:06:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000