Join - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Join

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-11 : 12:25:53

I don't understand why I am getting this error message as the column does exists in dbo.v_all_prd_info table

Thanks,
SA

Code:
------
select a.orig_frcst_altr_id, b.upc, b.ean_cd
from dbo.v_all_prd_info a
Join dbo.int_upc_ean b
on a.frcst_altr_id = b.orig_frcst_altr_id
where a.shp_mdia_cd = 'BOX' and a.fg_mtrl_sts_cd = 'AC'

Error Message:
--------------
Msg 207, Level 16, State 1, Line 4
Invalid column name 'orig_frcst_altr_id'

SA

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-11 : 12:32:10

Does it exist in int_upc_ean

Post the DDL of the 2 tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-11 : 12:58:40

When I run just a 1 table select it works fine. Here's what I ran to check the column issue. Also, upc, ean_cd exists in both tables. orig_frcst_altr_id is in v_all_prd_info and
frcst_altr_id is in int_upc_ean table

Thanks,
SA

select a.orig_frcst_altr_id, upc, ean_cd
from dbo.v_all_prd_info a
--Join dbo.int_upc_ean b
--on a.frcst_altr_id = b.orig_frcst_altr_id
where a.shp_mdia_cd = 'BOX' and a.fg_mtrl_sts_cd = 'AC'

SA

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-11 : 13:02:20

Do you know how to script the DDL for the 2 tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-11 : 13:14:11

CREATE TABLE [dbo].[int_upc_ean](
[frcst_altr_id] [varchar](40) NOT NULL,
[upc] [varchar](15) NULL,
[ean_cd] [varchar](13) NULL,
[upperIDSID] [char](8) NULL,
[change_date] [datetime] NULL,
[cmnt] [varchar](50) NULL
) ON [PRIMARY]

The rest of the columns are from v_all_prd_info consisting of 80 columns. I am referencing only 1 column from there which is org_frcst_altr_id. It is there I ahve checked.

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-11 : 13:14:59

CREATE TABLE [dbo].[v_all_prd_info](
[orig_frcst_altr_id] [varchar](40) NULL,

SA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-11 : 13:27:12

Looks like you've reversed the column/Alias associations:


from   dbo.v_all_prd_info a
Join   dbo.int_upc_ean b
       on a.frcst_altr_id = b.orig_frcst_altr_id

this column is in [a] not [b]: orig_frcst_altr_id

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-11 : 13:47:05

Brilliant TG!! Thanks for helping me .

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources