October 18, 2007 at 9:42 am
select sum(gam.DB_AMOUNT_01+gam.CR_AMOUNT_01) * curr_conv =
case when gam.company in
(select distinct Business_Unit_ID from FinanceMart.dbo.Intl_Businessunits with (nolock))
then (cuam.DB_AMOUNT_01+cuam.CR_AMOUNT_01)
end
from cb.dbo.GLAmounts gam with (nolock)
join fin.dbo.L_viewhier a12 with (nolock)
on (gam.Account = a12.lev4acct)
left outer join cb.dbo.cuamount cuam with (nolock)
on gam.COMPANY = cuam.COMPANY and
gam.FISCAL_YEAR = cuam.FISCAL_YEAR and
gam.ACCT_UNIT collate SQL_Latin1_General_CP1_CI_AS = cuam.ACCT_UNIT and
gam.ACCOUNT = cuam.ACCOUNT and
cuam.AMT_TYPE='3'
where gam.COMPANY = '550'
and gam.FISCAL_YEAR = '2007'
and gam.account between 40000 and 99999
October 18, 2007 at 10:25 am
your CASE is not the problem it's that you're trying to return a boolean value from
(calculation1)=(calculation2) in a select. That particular syntax doesn't work without wrapping IT in a case statement Try this (modifications are in bold):
select case when (sum(gam.DB_AMOUNT_01+gam.CR_AMOUNT_01) * curr_conv =
case when gam.company in
(select distinct Business_Unit_ID from FinanceMart.dbo.Intl_Businessunits with (nolock))
then (cuam.DB_AMOUNT_01+cuam.CR_AMOUNT_01)
end ) then 1 else 0 end as MyResult
from cb.dbo.GLAmounts gam with (nolock)
join fin.dbo.L_viewhier a12 with (nolock)
on (gam.Account = a12.lev4acct)
left outer join cb.dbo.cuamount cuam with (nolock)
on gam.COMPANY = cuam.COMPANY and
gam.FISCAL_YEAR = cuam.FISCAL_YEAR and
gam.ACCT_UNIT collate SQL_Latin1_General_CP1_CI_AS = cuam.ACCT_UNIT and
gam.ACCOUNT = cuam.ACCOUNT and
cuam.AMT_TYPE='3'
where gam.COMPANY = '550'
and gam.FISCAL_YEAR = '2007'
and gam.account between 40000 and 99999
----------------------------------------------------------------------------------
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?
October 18, 2007 at 1:39 pm
it is saying invalid column name - curr_conv
Msg 207, Level 16, State 1, Line 1
Invalid column name 'curr_conv'.
October 18, 2007 at 2:30 pm
Then I am not understanding what you're doing. You included it on line 1 of your query - is it not one of your fields in a table?
----------------------------------------------------------------------------------
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply