I guess some of t8.sire_bhid, t3s.herd_id, etc. in the following
expression(The last row is " then 'Unknown' ") are not CHARACTER type.

case
when t8.sire_bhid IS NOT NULL
THEN t8.sire_bhid
when t1.BRED_TYPE='A'
then t3s.herd_id
when t1.BRED_TYPE='O'
then t4s.herd_id
when t1.BRED_TYPE='P'
then t5s.herd_id
when t1.BRED_TYPE='I'
then t6s.herd_id
when t1.BRED_TYPE='U'
then 'Unknown'
end as Sire_ID

One reason I guessed so, is that you treat sometime xxxx_bhid or
xxxx_id as number.
For example:
......
t8a.recip_bhid<>0
......
t8a.recip_bhid=0
......
t8d.herd_owner_id=1