[selectRecords] select kbn ,skbn ,c2kbn ,c1kbn ,cd ,han ,sir_cd ,sir_nm ,cd2 ,han2 ,cd1 ,han1 ,hin_cd ,hin_nm ,suryo ,kingaku ,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 sir_nm from m_sir where sir_cd = '&22') as sir_nm_fr --ToDo 開始仕入先 ,(select sir_nm from m_sir where sir_cd = '&23') as sir_nm_to --ToDo 終了仕入先 from( select 1 as kbn ,1 as skbn ,1 as c2kbn ,1 as c1kbn ,coalesce(m.cat_cd,'') as cd ,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han ,coalesce(t.sir_cd,'') as sir_cd ,coalesce(t.sir_nm,'')||'('||coalesce(t.sir_cd,'')||')' as sir_nm ,coalesce(hn2.han_cd,'') as cd2 ,'('||coalesce(hn2.han_cd,'')||')'||coalesce(hn2.han_name,'') as han2 ,coalesce(hn1.han_cd,'') as cd1 ,'('||coalesce(hn1.han_cd,'')||')'||coalesce(hn1.han_name,'') as han1 ,sm.hin_cd ,sm.hin_nm ,sum(sm.suryo) as suryo ,sum(sm.kingaku) as kingaku ,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_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1=1 and t.del_flg = '0' and t.hacyu_flg = '0' --and hi.del_flg = '0' and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) group by coalesce(m.cat_cd,''),hn.han_name,t.sir_cd,t.sir_nm,coalesce(hn2.han_cd,''),hn2.han_name,coalesce(hn1.han_cd,''),hn1.han_name,sm.hin_cd,sm.hin_nm union all select 1 as kbn ,1 as skbn ,1 as c2kbn ,2 as c1kbn ,coalesce(m.cat_cd,'') as cd ,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han ,coalesce(t.sir_cd,'') as sir_cd ,coalesce(t.sir_nm,'')||'('||coalesce(t.sir_cd,'')||')' as sir_nm ,coalesce(hn2.han_cd,'') as cd2 ,'('||coalesce(hn2.han_cd,'')||')'||coalesce(hn2.han_name,'') as han2 ,coalesce(hn1.han_cd,'') as cd1 ,'('||coalesce(hn1.han_cd,'')||')'||coalesce(hn1.han_name,'') as han1 ,'' as hin_cd ,'商品分類合計' as hin_nm ,sum(sm.suryo) as suryo ,sum(sm.kingaku) as kingaku ,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_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1=1 and t.del_flg = '0' and t.hacyu_flg = '0' --and hi.del_flg = '0' and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) group by coalesce(m.cat_cd,''),hn.han_name,t.sir_cd,t.sir_nm,coalesce(hn2.han_cd,''),hn2.han_name,coalesce(hn1.han_cd,''),hn1.han_name union all select 1 as kbn ,1 as skbn ,2 as c2kbn ,2 as c1kbn ,coalesce(m.cat_cd,'') as cd ,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han ,coalesce(t.sir_cd,'') as sir_cd ,coalesce(t.sir_nm,'')||'('||coalesce(t.sir_cd,'')||')' as sir_nm ,coalesce(hn2.han_cd,'') cd2 ,'('||coalesce(hn2.han_cd,'')||')'||coalesce(hn2.han_name,'') as han2 ,'' as cd1 ,'' as han1 ,'' as hin_cd ,'商品大分類合計' as hin_nm ,sum(sm.suryo) as suryo ,sum(sm.kingaku) as kingaku ,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_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1=1 and t.hacyu_flg = '0' and t.del_flg = '0' --and hi.del_flg = '0' and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) group by coalesce(m.cat_cd,''),hn.han_name,t.sir_cd,t.sir_nm,coalesce(hn2.han_cd,''),hn2.han_name union all select 1 as kbn ,2 as skbn ,2 as c2kbn ,2 as c1kbn ,coalesce(m.cat_cd,'') as cd ,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han ,coalesce(t.sir_cd,'') as sir_cd ,coalesce(t.sir_nm,'')||'('||coalesce(t.sir_cd,'')||')' as sir_nm ,'' as cd2 ,'' as han2 ,'' as cd1 ,'' as han1 ,'' as hin_cd ,'仕入先合計' as hin_nm ,sum(sm.suryo) as suryo ,sum(sm.kingaku) as kingaku ,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_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1=1 and t.hacyu_flg = '0' and t.del_flg = '0' --and hi.del_flg = '0' and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) group by coalesce(m.cat_cd,''),hn.han_name,t.sir_cd,t.sir_nm union all select 2 as kbn ,2 as skbn ,1 as c2kbn ,1 as c1kbn ,coalesce(m.cat_cd,'') as cd ,coalesce(hn.han_name,'')||'('||coalesce(m.cat_cd,'')||')' as han ,'' as sir_cd ,'' as sir_nm ,'' as cd2 ,'' as han2 ,'' as cd1 ,'' as han1 ,'' as hin_cd ,'仕入先分類合計' as hin_nm ,sum(sm.suryo) as suryo ,sum(sm.kingaku) as kingaku ,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_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1=1 and t.hacyu_flg = '0' and t.del_flg = '0' --and hi.del_flg = '0' and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) group by coalesce(m.cat_cd,''),hn.han_name union all select 3 as kbn ,null as skbn ,null as c2kbn ,null as c1kbn ,null as cd ,null as han ,null as sir_cd ,null as sir_nm ,null as cd2 ,null as han2 ,null as cd1 ,null as han1 ,null as hin_cd ,'全仕入先合計' as hin_nm ,sum(sm.suryo) as suryo ,sum(sm.kingaku) as kingaku ,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_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1=1 and t.hacyu_flg = '0' and t.del_flg = '0' --and hi.del_flg = '0' and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) ) s1 order by s1.cd,s1.kbn,s1.sir_cd,s1.sir_nm,s1.skbn,s1.cd2,s1.c2kbn,s1.cd1,s1.c1kbn,s1.hin_cd,s1.hin_nm; [selectGraph] select sm.hin_cd ,sm.hin_nm ,sum(coalesce(sm.kingaku,0)) as kingaku from t_sir_m sm left join t_sir t on (t.den_no = sm.den_no) left join m_sir m on (m.sir_cd = t.sir_cd) left join m_hin hi on (hi.hin_cd = sm.hin_cd and hi.del_flg = 0) left join m_hanyo hn on (hn.mst_kbn = '45' and hn.han_cd = m.cat_cd) left join m_hanyo hn1 on (hn1.mst_kbn = '05' and hn1.han_cd = hi.cat_cd) left join m_hanyo hn2 on (hn2.mst_kbn = '14' and hn2.han_cd = hn1.koumoku1) left join m_tanto tn on (tn.tan_cd = t.tan_cd) left join m_hanyo hn3 on (hn3.mst_kbn = '55' and hn3.han_cd = tn.bu_cd) where 1 = 1 and t.hacyu_flg = 0 and t.del_flg = 0 and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&6') or (m.cat_cd = '&6')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&22') or (m.sir_cd >= '&22')) and (('' = '&22') or (m.sir_cd <= '&23')) AND (('' = '&8') or (hn1.han_cd is not null and hn1.koumoku1 >= '&8')) AND (('' = '&9') or (hn1.han_cd is not null and hn1.koumoku1 <= '&9')) AND (('' = '&12') or (hi.cat_cd >= '&12')) AND (('' = '&13') or (hi.cat_cd <= '&13')) and (('' = '&16') or (t.syori_ymd >= '&16')) and (('' = '&17') or (t.syori_ymd <= '&17')) group by sm.hin_cd, sm.hin_nm order by kingaku DESC limit 5