HHVet cannot be null

[eanders]

I'm getting an error with this section of code:

LSASampleCode/07 LSAExit.sql

Lines 757 to 784 in a01adee

	insert into lsa_Exit (RowTotal
	, Cohort, Stat, ExitFrom, ExitTo, ReturnTime, HHType
	, HHVet, HHChronic, HHDisability, HHFleeingDV, HoHRace, HoHEthnicity
	, HHAdultAge, HHParent, AC3Plus, SystemPath, ReportID)
	select count (distinct HoHID + cast(HHType as nvarchar))
	, Cohort, Stat, ExitFrom, ExitTo
	, case when ReturnTime between 15 and 30 then 30
	when ReturnTime between 31 and 60 then 60
	when ReturnTime between 61 and 90 then 90
	when ReturnTime between 91 and 180 then 180
	when ReturnTime between 181 and 365 then 365
	when ReturnTime between 366 and 547 then 547
	when ReturnTime between 548 and 730 then 730
	else ReturnTime end
	, HHType, HHVet, HHChronic, HHDisability, HHFleeingDV, HoHRace, HoHEthnicity
	, HHAdultAge, HHParent, AC3Plus, SystemPath, ReportID
	from tlsa_Exit
	group by Cohort, Stat, ExitFrom, ExitTo
	, case when ReturnTime between 15 and 30 then 30
	when ReturnTime between 31 and 60 then 60
	when ReturnTime between 61 and 90 then 90
	when ReturnTime between 91 and 180 then 180
	when ReturnTime between 181 and 365 then 365
	when ReturnTime between 366 and 547 then 547
	when ReturnTime between 548 and 730 then 730
	else ReturnTime end
	, HHType, HHVet, HHChronic, HHDisability, HHFleeingDV, HoHRace, HoHEthnicity
	, HHAdultAge, HHParent, AC3Plus, SystemPath, ReportID

Cannot insert the value NULL into column 'HHVet', table 'lsa_Exit'

Here's my current attempt to track down the nulls.

select count (distinct HoHID + cast(HHType as nvarchar))
	, Cohort, Stat, ExitFrom, ExitTo
	, case when ReturnTime between 15 and 30 then 30
		when ReturnTime between 31 and 60 then 60
		when ReturnTime between 61 and 90 then 90
		when ReturnTime between 91 and 180 then 180
		when ReturnTime between 181 and 365 then 365
		when ReturnTime between 366 and 547 then 547
		when ReturnTime between 548 and 730 then 730
		else ReturnTime end
	, HHType, HHVet, HHChronic, HHDisability, HHFleeingDV, HoHRace, HoHEthnicity
	, HHAdultAge, HHParent, AC3Plus, SystemPath, ReportID
from tlsa_Exit
group by Cohort, Stat, ExitFrom, ExitTo
	, case when ReturnTime between 15 and 30 then 30
		when ReturnTime between 31 and 60 then 60
		when ReturnTime between 61 and 90 then 90
		when ReturnTime between 91 and 180 then 180
		when ReturnTime between 181 and 365 then 365
		when ReturnTime between 366 and 547 then 547
		when ReturnTime between 548 and 730 then 730
		else ReturnTime end
	, HHType, HHVet, HHChronic, HHDisability, HHFleeingDV, HoHRace, HoHEthnicity
	, HHAdultAge, HHParent, AC3Plus, SystemPath, ReportID

It looks like the top row here is the culprit.

I'm going to submit an issue. Originally I thought this was not a bug, but it appears to be an edge case that will need coding assistance. (And as far as I can tell I can't delete this discussion.)

[eanders]

I believe this has been fixed in the sample code.