SQL-SERVER: Properly using ISNULL to return default value -
i have multiple queries tied generating ranked list selecting top each. problem t4.area in final select nullable , may have homecoming null trying set default value of 'tbd'.
if break out query , run below, runs fine:
select isnull(max(area),'tbd') mdc mdccte3 row = 5
but including in main query next returns no results:
select t1.area growth, t2.area efficiency, t3.area risk, isnull(t4.area,'tbd') mdc gcte3 t1, ecte3 t2, rcte3 t3, mdccte3 t4 t1.row = 1 , t2.row = 1 , t3.row = 1 , t4.row = 5 grouping t1.area, t2.area, t3.area, t4.area
declare @mainhospital varchar(50)='hospital1'; --growth area rank gcte ( select 4 score, growth1 area survey mainhospital = @mainhospital union select 3 score, growth2 area survey mainhospital = @mainhospital union select 2 score, growth3 area survey mainhospital = @mainhospital union select 1 score, growth4 area survey mainhospital = @mainhospital union select 0 score, growth5 area survey mainhospital = @mainhospital ), gcte2 ( select area, sum(score) score gcte grouping area having area not null ), gcte3 ( select area, score, row_number() on (order score desc) row, rank() on (order score desc) rank gcte2 ), --efficiency area rank ecte ( select 4 score, efficiency1 area survey mainhospital = @mainhospital union select 3 score, efficiency2 area survey mainhospital = @mainhospital union select 2 score, efficiency3 area survey mainhospital = @mainhospital union select 1 score, efficiency4 area survey mainhospital = @mainhospital union select 0 score, efficiency5 area survey mainhospital = @mainhospital ), ecte2 ( select area, sum(score) score ecte grouping area having area not null ), ecte3 ( select area, score, row_number() on (order score desc) row, rank() on (order score desc) rank ecte2 ), --risk area rank rcte ( select 4 score, risk1 area survey mainhospital = @mainhospital union select 3 score, risk2 area survey mainhospital = @mainhospital union select 2 score, risk3 area survey mainhospital = @mainhospital union select 1 score, risk4 area survey mainhospital = @mainhospital union select 0 score, risk5 area survey mainhospital = @mainhospital ), rcte2 ( select area, sum(score) score rcte grouping area having area not null ), rcte3 ( select area, score, row_number() on (order score desc) row, rank() on (order score desc) rank rcte2 ), --all mdc's ger rank mdccte ( select 2 score, growthmdc1 area survey mainhospital = @mainhospital union select 1 score, growthmdc2 area survey mainhospital = @mainhospital union select 0 score, growthmdc3 area survey mainhospital = @mainhospital union select 2 score, efficiencymdc1 area survey mainhospital = @mainhospital union select 1 score, efficiencymdc2 area survey mainhospital = @mainhospital union select 0 score, efficiencymdc3 area survey mainhospital = @mainhospital union select 2 score, riskmdc1 area survey mainhospital = @mainhospital union select 1 score, riskmdc2 area survey mainhospital = @mainhospital union select 0 score, riskmdc3 area survey mainhospital = @mainhospital ), mdccte2 ( select area, sum(score) score mdccte grouping area having area not null ), mdccte3 ( select area, score, row_number() on (order score desc) row, rank() on (order score desc) rank mdccte2 ) select t1.area growth, t2.area efficiency, t3.area risk, isnull(t4.area,'tbd') mdc gcte3 t1, ecte3 t2, rcte3 t3, mdccte3 t4 t1.row = 1 , t2.row = 1 , t3.row = 1 , t4.row = 4 grouping t1.area, t2.area, t3.area, t4.area
group needs tbd well.
select t1.area growth, t2.area efficiency, t3.area risk, isnull(t4.area,'tbd') mdc gcte3 t1, ecte3 t2, rcte3 t3, mdccte3 t4 t1.row = 1 , t2.row = 1 , t3.row = 1 , (t4.row = 5 or t4.row null) grouping t1.area, t2.area, t3.area, isnull(t4.area,'tbd')
i don't way handling joins results in poor performance db has generate cartesian before filter, if don't have performance issues. shrug , wasn't part of question.
i think should bring together this... tables relate eachother?
select t1.area growth, t2.area efficiency, t3.area risk, isnull(t4.area,'tbd') mdc gcte3 t1, left bring together ecte3 t2 on t1.row = t2.row left bring together rcte3 t3 on t2.trow = t3.row left bring together mdccte3 t4 on t3.row = t4.row , t4.row = 5 t1.row = 1 grouping t1.area, t2.area, t3.area, isnull(t4.area,'tbd')
this may work need see sample info in t1, t2, t3 , t4 improve understand dilemma , expected results you're after.
as far why it works in single query not in group. single query returning max value , if 1 isn't found returning tbd row 5 doesn't have contend joins.
when you're joinging records each record first combined records of other table.
so if t1 has 10 records, t2 has 10 records, t3 has 10 records , t4 has 5... 10*10*10*5 rows generated 5000 database engine filters out ones t1.row <> 1 t2.row <> 2 , t3.row <> 1. filter out t4.row <> 5. there no row five. no records returned.
this why think left joins work improve homecoming records preceding table , match , nulls processed tbd t4.area.
but work tables have relate on something; , nil stated far indicates how relate.
sql-server