[selectRecords]
select 
	 kbn
	,cd
	,han
	,tok_cd
	,tok_nm
	,tan_cd
	,tan_nm
	,kingaku
	,genka
	,arari
	,ritu
	,(case ('&16')
		when '' then '未指定'
		else to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') 
		end) as dt_fr
	,(case ('&17')
		when '' then '未指定'
		else to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd')
		end) as dt_to
	,(select han_name from m_hanyo where mst_kbn = '99' and han_cd = 'tok-cat') as tok_cat_flg --ToDo 得意先分類管理するかしないか
	,(select tok_nm from m_tokui where tok_cd = '&20') as tok_nm_fr --ToDo 開始得意先
	,(select tok_nm from m_tokui where tok_cd = '&21') as tok_nm_to --ToDo 終了得意先
from(
	select
		1 as kbn
		,coalesce(m.cat_cd,'') as cd
		,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han
		,coalesce(t.tok_cd,'') as tok_cd
		,coalesce(t.tok_nm,'')||'('||coalesce(t.tok_cd,'')||')' as tok_nm
		,coalesce(t.tan_cd,'') as tan_cd
		,coalesce(tan.tan_nm,'') as tan_nm
		,sum(coalesce(um.zeinuki_gaku,0)) as kingaku
		,sum(coalesce(um.genka_gaku,0)) as genka
		,sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)) as arari
		,(case (sum(coalesce(um.zeinuki_gaku,0))) when 0 then 0
			else round((sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)))*100/(sum(coalesce(um.zeinuki_gaku,0))),1)
			end) as ritu
		,(case ('&16')
			when '' then '未指定'
			else to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') 
			end) as dt_fr
		,(case ('&17')
			when '' then '未指定'
			else to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd')
			end) as dt_to
	from t_uri_m um
	left join t_uri t on (t.den_no = um.den_no)
	left join m_tanto tan on (tan.tan_cd = t.tan_cd)
	left join m_tokui m on (m.tok_cd = t.tok_cd)
	left join m_hin hi on (hi.hin_cd = um.hin_cd)
	left join m_hanyo hn on (hn.mst_kbn = '35' and hn.han_cd = m.cat_cd)
	--left join m_tanto tn on (tn.tan_cd = t.tan_cd)
	--left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd)else(m.tan_cd)end))
	left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd) when('&31' = 'eigyou_tan_cd ')then(t.eigyou_tan_cd )else(m.tan_cd)end))
	left join m_hanyo hn1 on (hn1.mst_kbn = '55' and hn1.han_cd = tn.bu_cd)
	left join m_hanyo h01 on (h01.mst_kbn = '01' and h01.han_cd = t.prc_sts)
	where 1 = 1
	and t.jucyu_flg = 0 and t.del_flg = 0
	and coalesce(t.mitsumori_flg,0) = 0 
	and coalesce(h01.koumoku3, '') = ''
	--and hi.del_flg = '0'
	and (('' = '&2') or (tn.bu_cd = '&2'))
	and (('' = '&4') or (m.cat_cd = '&4'))
	and case '&31'
		when 'tan_cd' then (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25'))
		when 'eigyou_tan_cd' then (('' = '&24') or (t.eigyou_tan_cd >= '&24')) and (('' = '&25') or (t.eigyou_tan_cd <= '&25'))
		else (('' = '&24') or (tn.tan_cd >= '&24')) and (('' = '&25') or (tn.tan_cd <= '&25')) end
	and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21'))
	and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17'))
	and (('' = '&33') or (t.eig_cd >= '&33')) and (('' = '&34') or (t.eig_cd <= '&34'))
	group by coalesce(m.cat_cd,''),hn.han_name,t.tok_cd,t.tok_nm,coalesce(t.tan_cd,''),tan.tan_nm
	union all select
		2 as kbn
		,coalesce(m.cat_cd,'') as cd
		,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han
		,'' as tok_cd
		,'得意先分類合計' as tok_nm
		,'' as tan_cd
		,'' as tan_nm
		,sum(coalesce(um.zeinuki_gaku,0)) as kingaku
		,sum(coalesce(um.genka_gaku,0)) as genka
		,sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)) as arari
		,(case (sum(coalesce(um.zeinuki_gaku,0))) when 0 then 0
			else round((sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)))*100/(sum(coalesce(um.zeinuki_gaku,0))),1)
			end) as ritu
		,(case ('&16')
			when '' then '未指定'
			else to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') 
			end) as dt_fr
		,(case ('&17')
			when '' then '未指定'
			else to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd')
			end) as dt_to
	from t_uri_m um
	left join t_uri t on (t.den_no = um.den_no)
	left join m_tokui m on (m.tok_cd = t.tok_cd)
	left join m_hin hi on (hi.hin_cd = um.hin_cd)
	left join m_hanyo hn on (hn.mst_kbn = '35' and hn.han_cd = m.cat_cd)
	--left join m_tanto tn on (tn.tan_cd = t.tan_cd)
	--left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd)else(m.tan_cd)end))
	left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd) when('&31' = 'eigyou_tan_cd ')then(t.eigyou_tan_cd )else(m.tan_cd)end))
	left join m_hanyo hn1 on (hn1.mst_kbn = '55' and hn1.han_cd = tn.bu_cd)
	left join m_hanyo h01 on (h01.mst_kbn = '01' and h01.han_cd = t.prc_sts)
	where 1 = 1
	and t.jucyu_flg = 0 and t.del_flg = 0
	and coalesce(t.mitsumori_flg,0) = 0 
	and coalesce(h01.koumoku3, '') = ''
	--and hi.del_flg = '0'
	and (('' = '&2') or (tn.bu_cd = '&2'))
	and (('' = '&4') or (m.cat_cd = '&4'))
	and case '&31'
		when 'tan_cd' then (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25'))
		when 'eigyou_tan_cd' then (('' = '&24') or (t.eigyou_tan_cd >= '&24')) and (('' = '&25') or (t.eigyou_tan_cd <= '&25'))
		else (('' = '&24') or (tn.tan_cd >= '&24')) and (('' = '&25') or (tn.tan_cd <= '&25')) end
	and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21'))
	and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17'))
	and (('' = '&33') or (t.eig_cd >= '&33')) and (('' = '&34') or (t.eig_cd <= '&34'))
	group by coalesce(m.cat_cd,''),hn.han_name
	union all select
		3 as kbn
		,null as cd
		,null as han
		,null as tok_cd
		,'全体合計' as tok_nm
		,null as tan_cd
		,null as tan_nm
		,sum(coalesce(um.zeinuki_gaku,0)) as kingaku
		,sum(coalesce(um.genka_gaku,0)) as genka
		,sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)) as arari
		,(case (sum(coalesce(um.zeinuki_gaku,0))) when 0 then 0
			else round((sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)))*100/(sum(coalesce(um.zeinuki_gaku,0))),1)
			end) as ritu
		,(case ('&16')
			when '' then '未指定'
			else to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') 
			end) as dt_fr
		,(case ('&17')
			when '' then '未指定'
			else to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd')
			end) as dt_to
	from t_uri_m um
	left join t_uri t on (t.den_no = um.den_no)
	left join m_tokui m on (m.tok_cd = t.tok_cd)
	left join m_hin hi on (hi.hin_cd = um.hin_cd)
	left join m_hanyo hn on (hn.mst_kbn = '35' and hn.han_cd = m.cat_cd)
	--left join m_tanto tn on (tn.tan_cd = t.tan_cd)
	left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd) when('&31' = 'eigyou_tan_cd ')then(t.eigyou_tan_cd )else(m.tan_cd)end))
	left join m_hanyo hn1 on (hn1.mst_kbn = '55' and hn1.han_cd = tn.bu_cd)
	left join m_hanyo h01 on (h01.mst_kbn = '01' and h01.han_cd = t.prc_sts)
	where 1 = 1
	and t.jucyu_flg = 0
	and coalesce(t.mitsumori_flg,0) = 0 
	and t.del_flg = 0
	and coalesce(h01.koumoku3, '') = ''
	--and hi.del_flg = '0'
	and (('' = '&2') or (tn.bu_cd = '&2'))
	and (('' = '&4') or (m.cat_cd = '&4'))
	and case '&31'
		when 'tan_cd' then (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25'))
		when 'eigyou_tan_cd' then (('' = '&24') or (t.eigyou_tan_cd >= '&24')) and (('' = '&25') or (t.eigyou_tan_cd <= '&25'))
		else (('' = '&24') or (tn.tan_cd >= '&24')) and (('' = '&25') or (tn.tan_cd <= '&25')) end
	and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21'))
	and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17'))
	and (('' = '&33') or (t.eig_cd >= '&33')) and (('' = '&34') or (t.eig_cd <= '&34'))
) s1
order by s1.cd,s1.kbn,s1.tok_cd,s1.tok_nm;


[selectRecords_2]
select 
	 kbn
	,cd
	,han
	,&32
	,tan_cd
	,tan_nm
	,tok_cd
	,tok_nm
	,kingaku
	,genka
	,arari
	,ritu
	,(case ('&16')
		when '' then '未指定'
		else to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') 
		end) as dt_fr
	,(case ('&17')
		when '' then '未指定'
		else to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd')
		end) as dt_to
from(
	select
		1 as kbn
		,coalesce(m.cat_cd,'') as cd
		,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han
		,coalesce(t.tan_cd,'') as tan_cd
		,coalesce(tan.tan_nm,'') as tan_nm
		,coalesce(t.tok_cd,'') as tok_cd
		,coalesce(t.tok_nm,'')||'('||coalesce(t.tok_cd,'')||')' as tok_nm
		,sum(coalesce(um.zeinuki_gaku,0)) as kingaku
		,sum(coalesce(um.genka_gaku,0)) as genka
		,sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)) as arari
		,(case (sum(coalesce(um.zeinuki_gaku,0))) when 0 then 0
			else round((sum(coalesce(um.zeinuki_gaku,0))-sum(coalesce(um.genka_gaku,0)))*100/(sum(coalesce(um.zeinuki_gaku,0))),1)
			end) as ritu
		,(case ('&16')
			when '' then '未指定'
			else to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') 
			end) as dt_fr
		,(case ('&17')
			when '' then '未指定'
			else to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd')
			end) as dt_to
	from t_uri_m um
	left join t_uri t on (t.den_no = um.den_no)
	left join m_tanto tan on (tan.tan_cd = t.tan_cd)
	left join m_tokui m on (m.tok_cd = t.tok_cd)
	left join m_hin hi on (hi.hin_cd = um.hin_cd)
	left join m_hanyo hn on (hn.mst_kbn = '35' and hn.han_cd = m.cat_cd)
	--left join m_tanto tn on (tn.tan_cd = t.tan_cd)
	--left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd)else(m.tan_cd)end))
	left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd) when('&31' = 'eigyou_tan_cd ')then(t.eigyou_tan_cd )else(m.tan_cd)end))
	left join m_hanyo hn1 on (hn1.mst_kbn = '55' and hn1.han_cd = tn.bu_cd)
	left join m_hanyo h01 on (h01.mst_kbn = '01' and h01.han_cd = t.prc_sts)
	where 1 = 1
	and t.jucyu_flg = 0 and t.del_flg = 0 and t.henpin_flg = 0
	and coalesce(t.mitsumori_flg,0) = 0 
	and coalesce(h01.koumoku3, '') = ''
	--and hi.del_flg = '0'
	and (('' = '&2') or (tn.bu_cd = '&2'))
	and (('' = '&4') or (m.cat_cd = '&4'))
	and case '&31'
		when 'tan_cd' then (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25'))
		when 'eigyou_tan_cd' then (('' = '&24') or (t.eigyou_tan_cd >= '&24')) and (('' = '&25') or (t.eigyou_tan_cd <= '&25'))
		else (('' = '&24') or (tn.tan_cd >= '&24')) and (('' = '&25') or (tn.tan_cd <= '&25')) end
	and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21'))
	and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17'))
	--group by coalesce(m.cat_cd,''),hn.han_name,coalesce(t.tan_cd,''),tan.tan_nm,t.tok_cd,t.tok_nm
	group by coalesce(m.cat_cd,''),hn.han_name,coalesce(t.&32,''),tan.tan_nm,t.tok_cd,t.tok_nm
) s1
--order by s1.tan_cd,s1.cd,s1.kbn,s1.tok_cd,s1.tok_nm;
order by s1.&32,s1.cd,s1.kbn,s1.tok_cd,s1.tok_nm;



[selectGraph]
select
	 t.tok_cd
	,coalesce(t.tok_nm,'')||'('||coalesce(t.tok_cd,'')||')' as tok_nm
	,sum(coalesce(um.zeinuki_gaku,0))-sum((case when(&31 = 1)then(coalesce(um.genka_gaku,0))else(0)end)) as kingaku
from t_uri_m um
left join t_uri t on (t.den_no = um.den_no)
left join m_tokui m on (m.tok_cd = t.tok_cd)
left join m_hin hi on (hi.hin_cd = um.hin_cd)
left join m_hanyo hn on (hn.mst_kbn = '35' and hn.han_cd = m.cat_cd)
--left join m_tanto tn on (tn.tan_cd = t.tan_cd)
left join m_tanto tn on (tn.tan_cd = (case when('&31' = 'tan_cd')then(t.tan_cd) when('&31' = 'eigyou_tan_cd ')then(t.eigyou_tan_cd )else(m.tan_cd)end))
left join m_hanyo hn1 on (hn1.mst_kbn = '55' and hn1.han_cd = tn.bu_cd)
left join m_hanyo h01 on (h01.mst_kbn = '01' and h01.han_cd = t.prc_sts)
where 1 = 1
and t.jucyu_flg = 0 and t.del_flg = 0 
and coalesce(t.mitsumori_flg,0) = 0  
and coalesce(h01.koumoku3, '') = ''
and (('' = '&2') or (tn.bu_cd = '&2'))
and (('' = '&4') or (m.cat_cd = '&4'))
and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17'))
and (('' = '&24') or (tn.tan_cd >= '&24')) and (('' = '&25') or (tn.tan_cd <= '&25'))
and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21'))
and (('' = '&32') or (t.eig_cd >= '&32')) and (('' = '&33') or (t.eig_cd <= '&33'))
group by t.tok_cd ,coalesce(t.tok_nm,'')||'('||coalesce(t.tok_cd,'')||')'
order by kingaku DESC
limit 5

[SelectRecords_Jisya]
select
--	 'TEL:' || j_tel as j_tel_s
--	,'FAX:' || j_fax as j_fax_s
	 (case coalesce(j_tel_s,'') when '' then '' else 'TEL：'||coalesce(j_tel_s,'') end) as j_tel --tel
	,(case coalesce(j_fax_s,'') when '' then '' else 'FAX：'||coalesce(j_fax_s,'') end) as j_fax --fax
	,(case j_url when '' then '' else 'URL:' || j_url end) as j_url_s
	--,'URL:' || j_url as j_url_s
	,(case j_daihyo when '' then '' else '担当者:' || j_daihyo end) as j_daihyo_s
	,'EMAIL:' ||j_email as j_email_s
	,w.*
from(
	select
		 j.mst_kbn
		,j.han_cd
		,j.han_name as j_nm
		,j.koumoku1 as j_post_s
		,j.koumoku2 as j_address
		,j.koumoku3 as j_address2
		,j.koumoku4 as j_address3
		,j.koumoku5 as j_tel_s
		,j.koumoku6 as j_fax_s
		,j.koumoku7 as j_url
		,j.koumoku10 as j_daihyo
--		,j.koumoku9 as j_daihyo koumoku9の0がなんなのかわからないので救急措置
		,j.koumoku8 as j_email
--		,j.koumoku9
--		,j.koumoku10
		,j.sort_key
		,j2.mst_kbn
		,j2.han_cd
		,j2.han_name
		,j2.koumoku1 as koumoku1
		,j2.koumoku2 as koumoku2
		,j2.koumoku3 as koumoku3
		,j2.koumoku4 as koumoku4
		,j2.koumoku5 as koumoku5
		,j2.koumoku6 as koumoku6
		,j2.koumoku7 as koumoku7
		,j2.koumoku8 as koumoku8
		,j2.koumoku9 as koumoku9
		,j2.koumoku10 as koumoku10
		,case when coalesce(m1.koumoku1,'') = '' then ''
			else coalesce(m1.koumoku1,'') end as mongon1 --1行目
		,case when coalesce(m1.koumoku2,'') = '' then ''
			else coalesce(m1.koumoku2,'') end as mongon2 --1行目
		,case when coalesce(m1.koumoku3,'') = '' then ''
			else coalesce(m1.koumoku3,'') end as mongon3 --2行目
		,case when coalesce(m1.koumoku4,'') = '' then ''
			else coalesce(m1.koumoku4,'') end as mongon4 --2行目
		,case when coalesce(m1.koumoku5,'') = '' then ''
			else coalesce(m1.koumoku5,'') end as mongon5 --3行目
	from m_hanyo j
	left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'jisya2') --自社キャッチフレーズマスタ
	left join m_hanyo m1 on (m1.mst_kbn = '90' and m1.han_cd = '&2') --文言マスタ mst_kbn=03,koumoku7(ex: r0020.s,r0020.n)
	left join m_hanyo m2 on (m2.mst_kbn = '90' and m2.han_cd = 'hin-title') --見出し行対応（数量・単価・金額の非表示）
	where 1=1
	and j.mst_kbn = '90'
	and j.han_cd = 'jisya'
)w