quote:

---

Originally posted by rdw72777

I have a basic insert function that uses two fields, one from each of two tables.

However, the table being inserted into doesn't allow nulls in the 2 insert fields (and i want to keep that) but the data being inserted might have Nulls due to the left outer join.

here's the code:

insert into tbl_cohort (cohort_key, family_key)
select a.cohort_key, ISNULL(cu.family_key,'')
from tbl_cohort a
left outer join
tbl_usage cu on a.family_key = cu.family_key
where a.import_control_key = ‘10’


So basically, if there is a value in tbl_cohort that is not found in table "tbl_usage" then the family key field is NULL and the code errors out (I'm calling this code from VS2008, though I don't think that matters). What i really want to happen is that any record where the left join causes a NULL in the family key column to make family key "OTHER" instead of NULL.

I understand the ISNULL syntax when the null value is in the data before runnign the ISNULL, but not sure how to make it happen in an Insert statement where the left join causes the NULL.

Thanks

---

quote:

---

Originally posted by rdw72777

Is there a better way than ISNULL. I'm relatively new and this is what I know, so if there's a better way I'm all ears.

---

DECLARE @default_family_code varchar(20)

SELECT @default_family_code = family_code
from yourmastertable
where <default condition here>

then use it in select as follows

insert into tbl_cohort 
(import_control_key, date_key, currency_key, business_unit_code, management_code, product_code, deferral_type, curve_family_code, node_key, amount, most_recent_interval) 
(select 34, a.date_key, b.[Currency Key], a.Unit, a.[Mgmt Cd], a.Prod, case a.deferral_type when 'ABC' then 'DEF' else a.deferral_type end as deferral_type, coalesce(a.curve_family, @default_family_code ), n.node_key, Sum(a.[Sum Amount]) as amount, 0 
from zitbl_sol_cost a 
left outer join [Dim Currency] b on a.[Base Curr] = b.[Currency Abbreviation]
left outer join tbl_node n on a.node = n.node_code
where a.deferral_type <> 'NDC' 
group by a.date_key, b.[Currency Key], a.Unit, a.[Mgmt Cd], a.Prod, case a.deferral_type when 'NDC' then 'DSC' else a.deferral_type end, a.curve_family, n.node_key 
having sum(a.[Sum Amount]) <> 0 )

SELECT COUNT(*) FROM tbl_curve_family where curve_family_code='OTHER'