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.
Author |
Topic |
vicki
Posting Yak Master
117 Posts |
Posted - 2002-03-20 : 15:58:48
|
Hi,I am using SQL 2000I got the error: invalid column name 'Bus' and 'D' =============================select OFC.OCD as OfficeCode, ([Domphone].[PHNAREA]) & ([Domphone].[PHNEXCH]) &([Domphone].[PHNNUM])asBusPhone, ([domphone].[PHNEXT])as BusPhoneExt, ([domphone].[FAXAREA]) & ([domphone].[FAXEXCH]) &([domphone].[FAXNUM])asBusFax, ([domphone].[FAXEXT])as BusFaxExtfrom OFCNew as OFC LEFT join (OFCCNTCTNew as OfficeContact LEFT join(OFCPHNNew as OfficePhone LEFT join DOMPHNNew as DomPhone OnOfficePhone.OFC_PHN_UID = DomPhone.OFC_PHN_UID) ONOfficeContact.OFC_PHN_UID= OfficePhone.OFC_PHN_UID) ON OFC.OCD = OfficeContact.OCDwhere (((OfficeContact.PHN_USE_TYP)="BUS") and((OfficePhone.PHN_SUBTYP)="D"))Then I change like below but still get the same errorwhere (((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 equalschar. (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 BusFaxExtFROM OFCNew as OFC LEFT join OFCCNTCTNew as OfficeContact ON OFC.OCD = OfficeContact.OCDLEFT join OFCPHNNew as OfficePhone ON OfficeContact.OFC_PHN_UID = OfficePhone.OFC_PHN_UIDLEFT join DOMPHNNew as DomPhone On OfficePhone.OFC_PHN_UID = DomPhone.OFC_PHN_UIDWHERE OfficeContact.PHN_USE_TYP='BUS' and OfficePhone.PHN_SUBTYP='D' |
|
|
|
|
|
|
|