no records in "with" portion = no records

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)

no records in "with" portion = no records

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-10-01 : 09:26:16

Here is my query. It works fine if there is a record for the with portion, i.e. a record in pos_fee. If there is no record in pos_fee, none of the other records are returned.
--------------------------------------------------------]
with fr44feelookup (pos_id,fr44_fee)
as (
select pos_id, fee_amount
from pos_fee
where pos_id = 1630746
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 = 1630746
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
--------------------------------------------------------
I tried a left outer join but I get:
The multi-part identifier "pos_policy.pos_id" could not be bound.
--------------------------------------------------------
WITH fr44feelookup(pos_id,fr44_fee)
AS (SELECT pos_id,
fee_amount
FROM pos_fee
WHERE pos_id = 1630746
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
left outer join fr44feelookup
on pp.pos_id = fr44feelookup.pos_id
WHERE pp.pos_id = 1630746
AND pp.rate_table = r.rate_table
AND (fee_code <> 'FLH'
OR Isnull(r.flh_fee,0) > 0)
--AND fl.pos_id = pp.pos_id
GROUP BY fee_order,
fee_code,
fee_name

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-10-01 : 10:32:48

Came up with this, which works, but seems a bit cludgy. Is there a better way to do this?
-----------------------------------------------------
with fr44feelookup (pos_id,fr44_fee)
as (
select pos_id, fee_amount
from pos_fee
where pos_id = 1629419
and fee_code = 'fr44'
union all
select 1629419 as pos_id, null as fee_amount
)
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