I reformatted your query so that I could better follow it. I don't see an issue.

Here's the CTE without the join to the temp table. Try highlighting and executing each of the subqueries and seeing if they all work as intended.

Kent
WITH temp2 as 
(
  SELECT   distinct
    value_tx, county_cd,referral_id,ch_client_id,ch_last_nm,ch_first_nm,ch_middle_nm,ch_dob_dt,investigation_start_dt		
    ,inv_completion_dt,supervisor_first_nm,supervisor_last_nm,worker_first_nm,worker_last_nm
    ,row_number() over (partition by county_cd order by bias) AS RN
    ,BIAS
	FROM
	(
		select   
			value_tx,pl_vals.county_cd,referral_id,ch_client_id,ch_last_nm,ch_first_nm,ch_middle_nm,ch_dob_dt		
			,investigation_accepted_dt as investigation_start_dt,action_dt as inv_completion_dt
			,supervisor_first_nm,supervisor_last_nm,worker_first_nm,worker_last_nm
			,cast(rand() * 10000 as integer) as BIAS
		from 
		(	
			SELECT
				picklist_value_cd  as county_cd,value_tx
			FROM tb_picklist_values	
			WHERE picklist_type_id=104  
				AND DELETE_SW ='N'
				AND picklist_value_cd NOT IN('3824','3825','3826')
		) pl_vals
		join
		(
			select
				t1.referral_id,worker_id,investigation_accepted_dt,action_dt,sf.primary_county_cd as county_cd
				,ssf.first_nm      AS supervisor_first_nm,ssf.last_nm       AS supervisor_last_nm
				,sf.first_nm       AS worker_first_nm,    sf.last_nm        AS worker_last_nm	
				,tbl_chs.client_id as ch_client_id,       tbl_chs.last_nm   as ch_last_nm
				,tbl_chs.first_nm  as  ch_first_nm,       tbl_chs.middle_nm as ch_middle_nm	
				,tbl_chs.dob_dt as ch_dob_dt		
			from
			(
				select 
					referral_id 			
					,CHESSIE.F_PRIM_WORKER_ID(referral_id ,'2957') as worker_id 
					,investigation_accepted_dt,action_dt
				from tb_referral rf			
				left join tb_supervisor_approvals sa
					on (sa.approval_key_id = rf.referral_id  
         and (sa.delete_sw = 'N' or sa.delete_sw ='N')
         and sa.approval_status_cd ='3047' 
				 and sa.approval_nature_cd ='2967'
				 and sa.entity_type_cd ='2957')
				WHERE (rf.delete_sw='N' or rf.expungement_sw='Y')	
					and upper(referral_type_sw)='C' 
					AND cps_recommend_sw='Y'
					and ref_approval_status_cd='3047'
					and  investigation_accepted_dt between DATE('08/20/2010') and DATE('10/20/2010') 
			)  t1
			inner join tb_staff sf
				 on (sf.staff_id = t1.worker_id AND sf.delete_sw ='N')
			inner join tb_county_unit cu
				 on (sf.primary_county_unit_id = cu.county_unit_id AND cu.delete_sw ='N' )
			inner join tb_staff ssf
				 on (ssf.staff_id = cu.unit_supervisor_id AND ssf.delete_sw ='N' )	
			inner join
			(
				select cl.client_id ,cl.dob_dt, referral_id ,first_nm ,last_nm,middle_nm
				from tb_referral_clients rc 
				inner join tb_client cl 
					on ( cl.client_id  = rc.client_id  and cl.delete_sw ='N' and rc.delete_sw ='N')
				where rc.delete_sw ='N' 
					and referral_client_id in (select  referral_client_id  from tb_client_roles 
                                     where  delete_sw = 'N' 
                                       and role_cd ='2085' 
                                       and referral_client_id in (select referral_client_id 
                                                                  from tb_referral_clients 
                                                                  where  delete_sw ='N'
                                                                 )
                                    )
			) tbl_chs
				on ( tbl_chs.referral_id = t1.referral_id )
		) t2
		on t2.county_cd = pl_vals.county_cd
		order by  pl_vals.county_cd
	) AS T
	ORDER BY 15
) 
select temp2.*
CTE, with a Global Temporary Table doesn't work
