[selectRecords] --&1:画面の年月 --&2:画面の締日 --&3:0:締日全て 1:締切のみ 2:都度のみ --&4:0:全て 1:請求残ありのみ select * from ( select 1 as type ,substring(t.seikyu_ymd,1,6) as orderkey ,to_date(substring(t.seikyu_ymd,1,6),'yyyymmdd') as orderkey2 ,to_date(t.seikyu_ymd,'yyyymmdd') as seikyu_ymd -- ●請求日 ,t.sir_cd as sir_cd -- ●仕入先コード ,null::date as syori_ymd -- ●処理日 ,m.sir_nm as sir_nm -- ●仕入先名 ,'' as den_no -- ●伝票 ,to_date(t.zen_seikyu_ymd,'yyyymmdd') as zen_seikyu_ymd -- 前回支払締日 ,t.zen_seikyu_zan as zen_seikyu_zan -- ●前回支払残高 ,t.syukin_gaku as syukin_gaku -- ●今回支払額 ,t.kurikoshi_gaku as kurikoshi_gaku -- ●繰越額 ,t.zeikomi_gaku as zeikomi_gaku -- ●仕入額（税込） ,t.zeinuki_gaku as zeinuki_gaku -- 仕入額（税抜） ,t.zei_gaku as zei_gaku -- 仕入税額 ,t.seikyu_zan as seikyu_zan -- ●今回支払請求額 ,v.syukin_gaku as syukin_gaku_now -- ●支払額 ,coalesce(t.seikyu_zan,0) - coalesce(v.syukin_gaku,0) as syukin_zan -- ●今回支払残額 ,m.shime_dd as shime_dd -- ●締日 ,mh.han_name as han_name -- ●回収サイト ,m.pay_d as pay_d -- ●回収日 ,m.tori_cond as tori_cond -- ●取引条件 from t_kaikake t left join m_sir m on m.sir_cd = t.sir_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join v_siharai v on v.sir_cd = t.sir_cd and v.seikyu_ymd = t.seikyu_ymd where ( t.shime_dd::varchar = '&2' or '' = '&2' ) --&2:画面の締日 and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ UNION select 2 as type ,substring(t.seikyu_ymd,1,6) as orderkey ,to_date(substring(t.seikyu_ymd,1,6),'yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- ●請求日 ,'' as sir_cd -- ●仕入先コード ,null::date as syori_ymd -- ●処理日 ,'' as sir_nm -- ●仕入先名 ,'' as den_no -- ●伝票 ,null::date as zen_seikyu_ymd -- 前回支払締日 ,sum(t.zen_seikyu_zan) as zen_seikyu_zan -- ●前回支払残高 ,sum(t.syukin_gaku) as syukin_gaku -- ●今回支払額 ,sum(t.kurikoshi_gaku) as kurikoshi_gaku -- ●繰越額 ,sum(t.zeikomi_gaku) as zeikomi_gaku -- ●仕入額（税込） ,sum(t.zeinuki_gaku) as zeinuki_gaku -- 仕入額（税抜） ,sum(t.zei_gaku) as zei_gaku -- 仕入税額 ,sum(t.seikyu_zan) as seikyu_zan -- ●今回支払請求額 ,sum(v.syukin_gaku) as syukin_gaku_now -- ●支払額 ,sum(coalesce(t.seikyu_zan,0) - coalesce(v.syukin_gaku,0)) as syukin_zan -- ●今回支払残額 ,null as shime_dd -- ●締日 ,'' as han_name -- ●回収サイト ,null as pay_d -- ●回収日 ,'' as tori_cond -- ●取引条件 from t_kaikake t left join m_sir m on m.sir_cd = t.sir_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join v_siharai v on v.sir_cd = t.sir_cd and v.seikyu_ymd = t.seikyu_ymd where ( t.shime_dd::varchar = '&2' or '' = '&2' ) --&2:画面の締日 and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ group by substring(t.seikyu_ymd,1,6) UNION select 3 as type ,substring(t.syori_ymd,1,6) as orderkey ,to_date(substring(t.syori_ymd,1,6),'yyyymmdd') as orderkey2 ,to_date(t.seikyu_ymd,'yyyymmdd') as seikyu_ymd -- 請求日 ,t.sir_cd as sir_cd -- 仕入先コード ,to_date(t.syori_ymd,'yyyymmdd') as syori_ymd -- 処理日 ,m.sir_nm as sir_nm -- 仕入先名 ,t.den_no::varchar as den_no --　伝票 ,null::date as zen_seikyu_ymd -- 前回支払締日 ,null as zen_seikyu_zan -- 前回支払残高 ,null as syukin_gaku -- 今回支払額 ,null as kurikoshi_gaku -- 繰越額 ,null as zeikomi_gaku -- 仕入額（税込） ,null as zeinuki_gaku -- 仕入額（税抜） ,null as zei_gaku -- 仕入税額 ,t.zeikomi_gaku as seikyu_zan -- 今回支払請求額 ,v.syukin_gaku as syukin_gaku_now -- 支払額 ,coalesce(t.zeikomi_gaku,0) - coalesce(v.syukin_gaku,0) as syukin_zan -- 今回支払残額 ,m.shime_dd as shime_dd -- 締日 ,mh.han_name as han_name -- 回収サイト ,m.pay_d as pay_d -- 回収日 ,m.tori_cond as tori_cond -- 取引条件 from t_sir t left join m_sir m on m.sir_cd = t.sir_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join (select sir_no ,sum(syukin_gaku) as syukin_gaku from t_syu_keshi group by sir_no ) k on k.sir_no = t.den_no left join v_siharai v on v.sir_cd = t.sir_cd and v.seikyu_ymd = t.seikyu_ymd where m.tori_kbn <> '1' and t.hacyu_flg = '0' and t.del_flg = '0' and ( '0' = '&3' or '2' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or (coalesce(t.zeikomi_gaku,0)::integer) <> 0) --&4:0:全て 1:請求残ありのみ UNION select 4 as type ,substring(t.syori_ymd,1,6) as orderkey ,to_date(substring(t.syori_ymd,1,6),'yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- 請求日 ,'' as sir_cd -- 仕入先コード ,null::date as syori_ymd -- 処理日 ,'' as sir_nm -- 仕入先名 ,'' as den_no --　伝票 ,null::date as zen_seikyu_ymd -- 前回支払締日 ,null as zen_seikyu_zan -- 前回支払残高 ,null as syukin_gaku -- 今回支払額 ,null as kurikoshi_gaku -- 繰越額 ,null as zeikomi_gaku -- 仕入額（税込） ,null as zeinuki_gaku -- 仕入額（税抜） ,null as zei_gaku -- 仕入税額 ,sum(t.zeikomi_gaku) as seikyu_zan -- 今回支払請求額 ,sum(v.syukin_gaku) as syukin_gaku_now -- 支払額 ,sum(coalesce(t.zeikomi_gaku,0) - coalesce(v.syukin_gaku,0)) as syukin_zan -- 今回支払残額 ,NULL as shime_dd -- 締日 ,'' as han_name -- 回収サイト ,NULL as pay_d -- 回収日 ,'' as tori_cond -- 取引条件 from t_sir t left join m_sir m on m.sir_cd = t.sir_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join (select sir_no ,sum(syukin_gaku) as syukin_gaku from t_syu_keshi group by sir_no ) k on k.sir_no = t.den_no left join v_siharai v on v.sir_cd = t.sir_cd and v.seikyu_ymd = t.seikyu_ymd where m.tori_kbn <> '1' and t.hacyu_flg = '0' and t.del_flg = '0' and ( '0' = '&3' or '2' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or (coalesce(t.zeikomi_gaku,0)::integer) <> 0) --&4:0:全て 1:請求残ありのみ group by substring(t.syori_ymd,1,6) union ------------------------- select 5 as type ,orderkey as orderkey ,orderkey2 as orderkey2 ,null::date as seikyu_ymd -- ●請求日 ,'' as sir_cd -- ●仕入先コード ,null::date as syori_ymd -- ●処理日 ,'' as sir_nm -- ●仕入先名 ,'' as den_no -- ●伝票 ,null::date as zen_seikyu_ymd -- 前回支払締日 ,sum(f.zen_seikyu_zan) as zen_seikyu_zan -- ●前回支払残高 ,sum(f.syukin_gaku) as syukin_gaku -- ●今回支払額 ,sum(f.kurikoshi_gaku) as kurikoshi_gaku -- ●繰越額 ,sum(f.zeikomi_gaku) as zeikomi_gaku -- ●仕入額（税込） ,sum(f.zeinuki_gaku) as zeinuki_gaku -- 仕入額（税抜） ,sum(f.zei_gaku) as zei_gaku -- 仕入税額 ,sum(f.seikyu_zan) as seikyu_zan -- ●今回支払請求額 ,sum(f.syukin_gaku_now) as syukin_gaku_now -- ●支払額 ,sum(f.syukin_zan) as syukin_zan -- ●今回支払残額 ,null as shime_dd -- ●締日 ,'' as han_name -- ●回収サイト ,null as pay_d -- ●回収日 ,'' as tori_cond -- ●取引条件 from ( select 5 as type ,substring(t.seikyu_ymd,1,6) as orderkey ,to_date(substring(t.seikyu_ymd,1,6),'yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- ●請求日 ,'' as sir_cd -- ●仕入先コード ,null::date as syori_ymd -- ●処理日 ,'' as sir_nm -- ●仕入先名 ,'' as den_no -- ●伝票 ,null::date as zen_seikyu_ymd -- 前回支払締日 ,sum(t.zen_seikyu_zan) as zen_seikyu_zan -- ●前回支払残高 ,sum(t.syukin_gaku) as syukin_gaku -- ●今回支払額 ,sum(t.kurikoshi_gaku) as kurikoshi_gaku -- ●繰越額 ,sum(t.zeikomi_gaku) as zeikomi_gaku -- ●仕入額（税込） ,sum(t.zeinuki_gaku) as zeinuki_gaku -- 仕入額（税抜） ,sum(t.zei_gaku) as zei_gaku -- 仕入税額 ,sum(t.seikyu_zan) as seikyu_zan -- ●今回支払請求額 ,sum(v.syukin_gaku) as syukin_gaku_now -- ●支払額 ,sum(coalesce(t.seikyu_zan,0) - coalesce(v.syukin_gaku,0)) as syukin_zan -- ●今回支払残額 ,null as shime_dd -- ●締日 ,'' as han_name -- ●回収サイト ,null as pay_d -- ●回収日 ,'' as tori_cond -- ●取引条件 from t_kaikake t left join m_sir m on m.sir_cd = t.sir_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join v_siharai v on v.sir_cd = t.sir_cd and v.seikyu_ymd = t.seikyu_ymd where ( t.shime_dd::varchar = '&2' or '' = '&2' ) --&2:画面の締日 and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ group by substring(t.seikyu_ymd,1,6) UNION select 5 as type ,substring(t.syori_ymd,1,6) as orderkey ,to_date(substring(t.syori_ymd,1,6),'yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- 請求日 ,'' as sir_cd -- 仕入先コード ,null::date as syori_ymd -- 処理日 ,'' as sir_nm -- 仕入先名 ,'' as den_no --　伝票 ,null::date as zen_seikyu_ymd -- 前回支払締日 ,null as zen_seikyu_zan -- 前回支払残高 ,null as syukin_gaku -- 今回支払額 ,null as kurikoshi_gaku -- 繰越額 ,null as zeikomi_gaku -- 仕入額（税込） ,null as zeinuki_gaku -- 仕入額（税抜） ,null as zei_gaku -- 仕入税額 ,sum(t.zeikomi_gaku) as seikyu_zan -- 今回支払請求額 ,sum(v.syukin_gaku) as syukin_gaku_now -- 今回支払額 ,sum(coalesce(t.zeikomi_gaku,0) - coalesce(v.syukin_gaku,0)) as syukin_zan -- 今回支払残額 ,NULL as shime_dd -- 締日 ,'' as han_name -- 回収サイト ,NULL as pay_d -- 回収日 ,'' as tori_cond -- 取引条件 from t_sir t left join m_sir m on m.sir_cd = t.sir_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join (select sir_no ,sum(syukin_gaku) as syukin_gaku from t_syu_keshi group by sir_no ) k on k.sir_no = t.den_no left join v_siharai v on v.sir_cd = t.sir_cd and v.seikyu_ymd = t.seikyu_ymd where m.tori_kbn <> '1' and t.hacyu_flg = '0' and t.del_flg = '0' and ( '0' = '&3' or '2' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or (coalesce(t.zeikomi_gaku,0)::integer) <> 0) --&4:0:全て 1:請求残ありのみ group by substring(t.syori_ymd,1,6)) F group by orderkey ,orderkey2 ------------------------- ) f2 where orderkey = '&1' order by f2.type,f2.seikyu_ymd,f2.sir_cd,f2.syori_ymd