[selectRecords]
select
	row_number() over (order by syu_cat_cd) as s_row_no
	, 'No.'||uh.mitsumori_no as mitsumori_no
	, uh.tok_nm
	, uh.title1
	, uh.title2
--	, coalesce(han.sort_key,'') as sort_key
	, syu_cat_cd
    ,(case coalesce(um2.cat_cd,'')
		when '' then '未分類'
		ELSE(case coalesce(han.koumoku6,'')
			when '1' then '【部品（集約）】'
			else han.han_name
		end)
    end) as cat_cd_nm
	, coalesce(sum(um2.tanka * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)),0) as mitsumori_gaku
	, coalesce(sum(um2.genka_gaku * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)),0) as sir_gaku
	,(coalesce(sum(um2.tanka * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)),0) - coalesce(sum(um2.genka_gaku * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)),0)) as arari_gaku
	,(case 
		when sum(coalesce(um2.tanka,0)) = 0 then null
		--else round((coalesce(sum(um2.tanka * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)) - coalesce(sum(um2.genka_gaku * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)),0)) / sum(um2.tanka * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0))*100),1)||'%'
		else (coalesce(sum(um2.tanka * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)) - coalesce(sum(um2.genka_gaku * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)),0)) / sum(um2.tanka * coalesce(um.jucyu_su,0) * coalesce(um2.jucyu_su,0)))
	end) as arari_ritu
	, max(ne.kingaku) as nebiki --値引
from t_uri uh
left join t_uri_m um on (uh.den_no = um.den_no and um.orow_no is null)
left join t_uri_m um2 on (um2.den_no = um.den_no and um.row_no = um2.orow_no)
left join (select nebiki.den_no, sum(nebiki.kingaku) as kingaku from t_uri_m nebiki where (&3 = nebiki.den_no and nebiki.orow_no is null and nebiki.hin_cd ='99') group by nebiki.den_no) ne on uh.den_no = ne.den_no
left join m_hanyo han on (um2.cat_cd = han.han_cd and mst_kbn = '14')
left join(
	select
--		(case coalesce(han.koumoku6,'')
--			when '' then(case coalesce(um2.cat_cd,'')
--            	when '' then '0'
--            	else coalesce(um2.cat_cd,'')
--            	end)
--            else '1'
--		end) as syu_cat_cd
		(case when(han.koumoku6 = '1')then('0')else('1'||coalesce(um2.cat_cd,''))end) as syu_cat_cd
        ,coalesce(um2.cat_cd,'') AS cat_cd
    from t_uri uh
	left join t_uri_m um on (uh.den_no = um.den_no and um.orow_no is null)
	left join t_uri_m um2 on (um2.den_no = um.den_no and um.row_no = um2.orow_no)
	left join (select nebiki.den_no, sum(nebiki.kingaku) as kingaku from t_uri_m nebiki where (&3 = nebiki.den_no and nebiki.orow_no is null and nebiki.hin_cd ='99') group by nebiki.den_no) ne on uh.den_no = ne.den_no
	left join m_hanyo han on (um2.cat_cd = han.han_cd and mst_kbn = '14')
	where 1 = 1
	and um.hin_cd !='99'
	and uh.jucyu_flg = 0
	and uh.del_flg = 0
	and ((uh.syori_ymd >= '&1') or ('&1' = ''))
	and ((uh.syori_ymd <= '&2') or ('&2' = ''))
	and ((cast(uh.den_no as char(6)) = '&3') or ('&3' = ''))
	and ((uh.tok_cd = '&4') or ('&4' = ''))
	and ((um.hin_nm like '%&5%') or ('&5' = ''))
	group by um2.cat_cd,uh.mitsumori_no,uh.title1,title2,uh.tok_nm,han.koumoku6
) mm on um2.cat_cd = mm.cat_cd
where 1 = 1
and um.hin_cd !='99'
and uh.jucyu_flg = 0
and uh.del_flg = 0
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(uh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
group by
--coalesce(han.sort_key,''),coalesce(mm.syu_cat_cd,'0'),cat_cd_nm,uh.mitsumori_no,uh.title1,title2,uh.tok_nm,
syu_cat_cd,cat_cd_nm,uh.mitsumori_no,uh.title1,title2,uh.tok_nm
--,(case coalesce(um2.cat_cd,'')
--	when '' then '未分類'
--	ELSE (case coalesce(han.koumoku6,'') 
--		when '' then han.han_name 
--		else  han.koumoku6  
--	end)
--end)
--order by coalesce(han.sort_key,''),coalesce(mm.syu_cat_cd,'0');
order by syu_cat_cd
--order by coalesce(mm.syu_cat_cd,'0')