The multi-part identifier could not be bound.

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)

The multi-part identifier could not be bound.

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-09-30 : 16:13:37

The multi-part identifier "fr44feelookup.fr44_fee" could not be bound.

Original query:

SELECT fee_order, fee_code, fee_name,
sum(case when fee_code = 'PRM' then pure_premium
when fee_code = 'POL' then IsNull(policy_fee,0)
when fee_code = 'SR22' then IsNull(SR22_Fees,0)
when fee_code = 'FLH' then IsNull(umf_fee,0)
when fee_code = 'OTH' then IsNull(NSF_Fees,0)
+ IsNull(MVR_Fees,0)
+ IsNull(Installment_Fees,0)
+ isnull(Theft_Prevention_Fee,0)
else 0 end) as fee_amount
FROM ( select 1 as fee_order, 'PRM' as fee_code,'Total Written Premium:' as fee_name
union all
select 2, 'POL','Policy Fee:'
union all
select 3, 'SR22','SR22 Fee:'
union all
select 4, 'FLH','FL Hurricane CAT Fund:'
union all
select 5, 'OTH','Other Fees:') v_fee_master, pos_policy pp, rate_table r
WHERE pp.pos_id = :pos_id
AND pp.rate_table = r.rate_table
AND ( fee_code <> 'FLH' or IsNull(r.flh_fee,0) > 0 )
GROUP BY fee_order, fee_code, fee_name

Trying to add the fr44_fee into it, which requires looking into another table.

Here's what I came up with:

with fr44feelookup (pos_id,fr44_fee)
as (
select pos_id, fee_amount
from pos_fee
where pos_id = 1629419
and fee_code = 'fr44'
)
SELECT fee_order, fee_code, fee_name,
sum(case when fee_code = 'PRM' then pure_premium
when fee_code = 'POL' then IsNull(policy_fee,0)
when fee_code = 'SR22' then IsNull(SR22_Fees,0)
when fee_code = 'FR44' then IsNull(fr44feelookup.fr44_fee,0)
when fee_code = 'FLH' then IsNull(umf_fee,0)
when fee_code = 'OTH' then IsNull(NSF_Fees,0)
+ IsNull(MVR_Fees,0)
+ IsNull(Installment_Fees,0)
+ isnull(Theft_Prevention_Fee,0)
else 0 end) as fee_amount
FROM ( select 1 as fee_order, 'PRM' as fee_code,'Total Written Premium:' as fee_name
union all
select 2, 'POL','Policy Fee:'
union all
select 3, 'SR22','SR22 Fee:'
union all
select 4, 'FR44','FR44 Fee:'
union all
select 5, 'FLH','FL Hurricane CAT Fund:'
union all
select 6, 'OTH','Other Fees:') v_fee_master, pos_policy pp, rate_table r
WHERE pp.pos_id = 1629419
AND pp.rate_table = r.rate_table
AND ( fee_code <> 'FLH' or IsNull(r.flh_fee,0) > 0 )
AND fr44feelookup.pos_id =
GROUP BY fee_order, fee_code, fee_name

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-30 : 20:48:04

You defined the cte "fr44feelookup" but it must appear in your FROM clause, which it doesn't. Also, right before the GROUP BY you have AND fr44feelookup.pos_id = , which needs a value

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:43:14

i guess this is what jimf meant


;with	fr44feelookup (pos_id,fr44_fee)
as	 (
select	pos_id, fee_amount
from	pos_fee
where	pos_id = 1629419
and	 fee_code = 'fr44'
)
SELECT fee_order, fee_code, fee_name, 
sum(case when fee_code = 'PRM' then pure_premium 
when fee_code = 'POL' then IsNull(policy_fee,0)
when fee_code = 'SR22' then IsNull(SR22_Fees,0)
when fee_code = 'FR44' then IsNull(fr44feelookup.fr44_fee,0)
when fee_code = 'FLH' then IsNull(umf_fee,0)
when fee_code = 'OTH' then IsNull(NSF_Fees,0) 
+ IsNull(MVR_Fees,0) 
+ IsNull(Installment_Fees,0) 
+ isnull(Theft_Prevention_Fee,0)
else 0 end) as fee_amount
FROM ( select 1 as fee_order, 'PRM' as fee_code,'Total Written Premium:' as fee_name
union all
select 2, 'POL','Policy Fee:'
union all
select 3, 'SR22','SR22 Fee:'
union all
select 4, 'FR44','FR44 Fee:'
union all
select 5, 'FLH','FL Hurricane CAT Fund:'
union all
select 6, 'OTH','Other Fees:') v_fee_master, pos_policy pp, rate_table r,fr44feelookup
WHERE pp.pos_id = 1629419
AND pp.rate_table = r.rate_table
AND ( fee_code <> 'FLH' or IsNull(r.flh_fee,0) > 0 )
AND fr44feelookup.pos_id = pp.pos_id
GROUP BY fee_order, fee_code, fee_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources