February 18, 2010 at 9:53 am
I am having an issue with a process and would like to request another set of eyes to assist. I believe that it is something trivial but I can't seem to figure this out. Thank you in advance for any help or direction of what I can try... the error I am receiving as well as the code follows.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "fa.Acct_unit" could not be bound.
declare @listfiscalyears varchar(max)
Declare @fiscalyears table (fiscal_year smallint)
Set @listfiscalyears = '2009,2010'
insert into @fiscalyears
Select * from dbo.split(@listfiscalyears)
;with glamounts
as
(
Select
Company, fa.Fiscal_Year, Acct_Unit, Account, Sub_Account, Total_amount_01, Total_amount_02, Total_amount_03, Total_Amount_04, Total_Amount_05,
Total_Amount_06, Total_amount_07, Total_amount_08, Total_amount_09,
Total_Amount_10, Total_amount_11, Total_amount_12
from dbo.gl_amounts ga
inner join @fiscalyears fa
on ga.fiscal_year = fa.fiscal_year
), glunits as
(
Select
Company, fa.Fiscal_Year, Acct_Unit, Account, Sub_Account, Total_Units_01, Total_Units_02, Total_Units_03, Total_Units_04, Total_Units_05,
Total_Units_06, Total_Units_07, Total_Units_08, Total_Units_09,
Total_Units_10, Total_Units_11, Total_Units_12
from dbo.gl_units ga
inner join @fiscalyears fa
on ga.fiscal_year = fa.fiscal_year
),fbdetail as
(
select
Company, fb.Fiscal_Year, Acct_Unit, Account, Sub_Account, Total_Units_01, Total_Units_02, Total_Units_03, Total_Units_04, Total_Units_05,
Total_Units_06, Total_Units_07, Total_Units_08, Total_Units_09,
Total_Units_10, Total_Units_11, Total_Units_12, Total_amount_01, Total_amount_02, Total_amount_03, Total_Amount_04, Total_Amount_05,
Total_Amount_06, Total_amount_07, Total_amount_08, Total_amount_09,
Total_Amount_10, Total_amount_11, Total_amount_12
From dbo.fb_detail fb
inner join @fiscalyears fa
on fb.fiscal_year = fa.fiscal_year
),deptmaster as
(
select
dm.Company, dm.Acct_Unit, dm.Description, dm.Dept_Type
From dbo.Dept_Master_old dm
inner join dbo.Dept_Master_old ON fa.Acct_unit = dm.ACCT_UNIT
)
Select ga.*, gu.*, fb.*, DEPT_MASTER_old.*
from glamounts ga
Full outer join glunits gu
on ga.company = gu.company and ga.fiscal_year = gu.fiscal_year
and ga.acct_unit = gu.acct_unit and ga.account = gu.account
and ga.sub_account = gu.sub_account
Full outer join fbdetail fb
on (ga.company =fb.company and ga.fiscal_year = fb.fiscal_year
and ga.acct_unit = fb.acct_unit and ga.account = fb.account
and ga.sub_account = fb.sub_account) or
(gu.company = fb.company and gu.fiscal_year = fb.fiscal_year
and gu.acct_unit = fb.acct_unit and gu.account = fb.account
and gu.sub_account = fb.sub_account)
Full outer join deptmaster dm
on (ga.company =dm.company and ga.acct_unit = dm.acct_unit)
or (gu.company = dm.company and gu.acct_unit = dm.acct_unit)
or (fb.company = dm.company and fb.acct_unit = dm.acct_unit)
where (gu.account in ('800001', '800101', '800201', '800301', '800401', '800501', '800601', '800701', '800801', '800901'))
and (gu.ACCT_UNIT LIKE '6%')AND (dbo.DEPT_MASTER_old.DEPT_TYPE = 'R') OR
(dbo.gu.ACCOUNT IN ('800001', '800101', '800201', '800301', '800401', '800501', '800601', '800701', '800801', '800901')) AND
(dbo.DEPT_MASTER_old.ACCT_UNIT IN ('7400', '7010', '7012', '7570', '7593', '7070', '7072', '7741', '7742',
'7743', '7744', '7310', '7290', '7420', '7423', '7427', '7430', '7450', '7761', '7231', '7235')) AND (dbo.DEPT_MASTER_old.DEPT_TYPE = 'A') OR
(gu.ACCOUNT IN ('800001', '800101', '800201', '800301', '800401', '800501', '800601', '800701', '800801', '800901')) AND
(dbo.DEPT_MASTER_old.ACCT_UNIT = '6531' OR
dbo.DEPT_MASTER_old.ACCT_UNIT = '7428') AND (dbo.DEPT_MASTER_old.DEPT_TYPE = 'S')
February 18, 2010 at 10:42 am
Hi,
You've declared the table alias inner join @fiscalyears fa within the cte. As a result, you cant use it outside when you try to join on Acct_Unit.
You need to use your CTE alias instead.
Regards, Iain
February 18, 2010 at 11:11 am
Hello,
I think I understand what you mean but I know I am not sure with how to fix this. Can you direct me if it is not a hard task but if it is can you point me to an article or some tutorial that will assist with how to fix this... I do not want to waste your time but I also need to resolve this.
Thank you for your help,
cprice
February 18, 2010 at 11:20 am
Change the middle bit:
select
dm.Company, dm.Acct_Unit, dm.Description, dm.Dept_Type
From dbo.Dept_Master_old dm
inner join dbo.Dept_Master_old ON fbdetail.Acct_unit = dm.ACCT_UNIT
February 18, 2010 at 11:28 am
I tried that and then received
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "fbdetail.Acct_unit" could not be bound.
February 18, 2010 at 1:29 pm
You've still got a problem here:
deptmaster as
(
select
dm.Company, dm.Acct_Unit, dm.Description, dm.Dept_Type
From dbo.Dept_Master_old dm
inner join dbo.Dept_Master_old ON fa.Acct_unit = dm.ACCT_UNIT
)
Notice two problems:
- you're linking a table to itself, but with NO join criteria
- you're referring to "fa" but have not defined it within this CTE section.
Whatever you're linking to, which has acct_unit, needs to be referenced from within the query here.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply