CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectjoin
Reeya Oberoi
Reeya Oberoi

Reputation: 861

Invalid column error even when column exists in SQL Server

SELECT 
    S.EY_BNF_ADDR,S.[EY_BNF_COUNTRY], [Expected Country Code]
FROM 
    STG_TRXN S
JOIN 
    CCD_NULL_BNF C ON C.EY_BNF_ADDR = S.EY_BNF_ADDR

I am getting

Msg 207, Level 16, State 1, Line 3
Invalid column name 'EY_BNF_ADDR'.

For above query on the join column, but when I run select on the join columns separately, they run fine. This is so weird. One join column is varchar(765) null and other is varchar(8000) null

Here is the structure

CREATE TABLE [dbo].[CCD_NULL_BNF]
(
    [EY_ BNF_ADDR] [varchar](8000) NULL,
    [EY_BNF_COUNTRY] [varchar](255) NULL,
    [Expected Country Code] [varchar](255) NULL
) ON [PRIMARY]
GO

I apologize for pasting the huge structure of below table -

CREATE TABLE [dbo].[STG_TRXN]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EY_ID] [bigint] NOT NULL,
    [SOURCE_FILE] [varchar](255) NOT NULL,
    [ACC_NO] [varchar](255) NULL,
    [ACC_TYPE] [varchar](255) NULL,
    [CR_ACC_NO] [varchar](255) NULL,
    [CR_ACC_TYP] [varchar](255) NULL,
    [SERVICE] [varchar](255) NULL,
    [VALUE_DATE] [varchar](255) NULL,
    [AMOUNT] [varchar](255) NULL,
    [OGB] [varchar](255) NULL,
    [OGB_ID] [varchar](255) NULL,
    [OGB_BIC] [varchar](255) NULL,
    [OGB_ADDR1] [varchar](255) NULL,
    [OGB_ADDR2] [varchar](255) NULL,
    [OGB_ADDR3] [varchar](255) NULL,
    [ORG] [varchar](255) NULL,
    [ORG_ID] [varchar](255) NULL,
    [ORG_ADDR1] [varchar](255) NULL,
    [ORG_ADDR2] [varchar](255) NULL,
    [ORG_ADDR3] [varchar](255) NULL,
    [ORG_BIC] [varchar](255) NULL,
    [OBI] [varchar](255) NULL,
    [BBI] [varchar](255) NULL,
    [OUTSERVICE] [varchar](255) NULL,
    [DIRECTION] [varchar](255) NULL,
    [MID] [varchar](255) NULL,
    [LOCAL_REF] [varchar](255) NULL,
    [IBK] [varchar](255) NULL,
    [IBK_ADDR1] [varchar](255) NULL,
    [IBK_ADDR2] [varchar](255) NULL,
    [IBK_ADDR3] [varchar](255) NULL,
    [IBK_BIC] [varchar](255) NULL,
    [BNF] [varchar](255) NULL,
    [BNF_ADDR1] [varchar](255) NULL,
    [BNF_ADDR2] [varchar](255) NULL,
    [BNF_ADDR3] [varchar](255) NULL,
    [BNF_BIC] [varchar](255) NULL,
    [BNF_ID] [varchar](255) NULL,
    [BBK] [varchar](255) NULL,
    [BBK_BIC] [varchar](255) NULL,
    [BBK_ADDR1] [varchar](255) NULL,
    [BBK_ADDR2] [varchar](255) NULL,
    [BBK_ADDR3] [varchar](255) NULL,
    [COUNTRYCODE] [varchar](255) NULL,
    [orig_rfb] [varchar](255) NULL,
    [EY_IBK_ADDR1] [varchar](255) NULL,
    [EY_IBK_ADDR2] [varchar](255) NULL,
    [EY_IBK_ADDR3] [varchar](255) NULL,
    [EY_OGB_ADDR1] [varchar](255) NULL,
    [EY_OGB_ADDR2] [varchar](255) NULL,
    [EY_OGB_ADDR3] [varchar](255) NULL,
    [EY_ORG_ADDR1] [varchar](255) NULL,
    [EY_ORG_ADDR2] [varchar](255) NULL,
    [EY_ORG_ADDR3] [varchar](255) NULL,
    [EY_BNF_ADDR1] [varchar](255) NULL,
    [EY_BNF_ADDR2] [varchar](255) NULL,
    [EY_BNF_ADDR3] [varchar](255) NULL,
    [EY_BBK_ADDR1] [varchar](255) NULL,
    [EY_BBK_ADDR2] [varchar](255) NULL,
    [EY_BBK_ADDR3] [varchar](255) NULL,
    [EY_IBK_COUNTRY] [varchar](255) NULL,
    [EY_OGB_COUNTRY] [varchar](255) NULL,
    [EY_BNF_COUNTRY] [varchar](255) NULL,
    [EY_BBK_COUNTRY] [varchar](255) NULL,
    [EY_OGB] [varchar](255) NULL,
    [EY_ORG] [varchar](255) NULL,
    [EY_BNF] [varchar](255) NULL,
    [EY_OBI] [varchar](255) NULL,
    [EY_BBI] [varchar](255) NULL,
    [IS_DELETE] [bit] NULL,
    [CREATE_DT] [datetime] NULL,
    [UPDATE_DT] [datetime] NULL,
    [IS_MOVED] [char](1) NULL,
    [IS_MOVED_DT] [datetime] NULL,
    [COMMENT] [varchar](255) NULL,
    [BNF_BANK_COUNTRY] [varchar](255) NULL,
    [ORG_BANK_COUNTRY] [varchar](255) NULL,
    [INTER_BANK_COUNTRY] [varchar](255) NULL,
    [EY_OGB_ADDR] [varchar](765) NULL,
    [EY_BNF_ADDR] [varchar](765) NULL,
    [EY_IBK_ADDR] [varchar](765) NULL,
    [EY_ORG_ADDR] [varchar](765) NULL,
    [EY_ORG_COUNTRY] [varchar](255) NULL,
    [EY_BBK_ADDR] [varchar](800) NULL,
    [EY_ORG_ID] [varchar](255) NULL,
    [EY_BNF_ID] [varchar](255) NULL,
    [EY_ORG_PARTY] [varchar](255) NULL,
    [EY_BNF_PARTY] [varchar](255) NULL
) ON [PRIMARY]
GO

Does anybody know what is happening?

Upvotes: 0

Views: 2227

Answers (2)

Amit Maru
Amit Maru

Reputation: 142

Just Remove space in field name [EY_BNF_ADDR] : 

CREATE TABLE [dbo].[CCD_NULL_BNF]
(
    [EY_BNF_ADDR] [varchar](8000) NULL,
    [EY_BNF_COUNTRY] [varchar](255) NULL,
    [Expected Country Code] [varchar](255) NULL
) ON [PRIMARY]
GO

Upvotes: 0

crthompson
crthompson

Reputation: 15865

In your table:

CREATE TABLE [dbo].[CCD_NULL_BNF](
    [EY_ BNF_ADDR] [varchar](8000) NULL,
    [EY_BNF_COUNTRY] [varchar](255) NULL,
    [Expected Country Code] [varchar](255) NULL
) ON [PRIMARY]

Notice [EY_ BNF_ADDR] There is a space after the EY_

You can see the error duplicated here Fixed here

Upvotes: 4

Related Questions