[selectRecords]
select '1' as kbn,
	u.uri_no as uri_no,
	m.uri_row_no as uri_row,
	u.jyucyu_no as jyucyu,
	m.jyucyu_no as jyucyum,
	m.jyucyu_row_no as jyucyu_row,
	case when u.uri_ymd = '' then '日付を入力してください'
       	else to_char(to_date(u.uri_ymd,'yyyymmdd'),'yyyy/mm/dd')
       	end as u_ymd,
	--u.uri_ymd as u_ymd,
	u.tokui_cd as tokui_cd,
	m.uri_suryo as uri_s,
	m.uri_tanka as uri_t,
	m.uri_gaku as uri_g,
	u.zeinuki_gaku as zn_gaku,
	u.zei_gaku as z_gaku,
	u.zeikomi_gaku as zi_gaku,
	u.seikyu_ymd as s_ymd,
	t.tokui_nm as tokui_nm,
	t.tokui_nm2 as tokui_nm2,
	t.tokui_kana as tokui_kana,
	left(t.post_no,3)||'-'||right(t.post_no,4) as post_n,
	t.address1,
	t.address2,
	t.address3,
	j.syohin_nm
from t_uri u
	left join t_uri_m m on u.uri_no = m.uri_no
	left join m_tokui t on u.tokui_cd = t.tokui_cd
	left join t_jyucyu j on u.jyucyu_no = j.jyucyu_no
where 1=1
and ((u.uri_ymd >= '&1') or ('&1' = '')) 
and ((u.uri_ymd <= '&2') or ('&2' = ''))
and ((cast(u.uri_no as char(6))= '&3') or ('&3' = ''))
and ((t.tokui_cd = '&4') or ('&4' = ''))
and ((j.syohin_nm like '&5') or ('&5' like '')) 
order by kbn;

[selectRecords3]
select foo.kbn,
	foo.uri_no as uri_no,
	foo.uri_row as uri_row,
foo.jyucyu as jyucyu,
		foo.jyucyum as jyucyum,
		foo.jyucyu_row as jyucyu_row,
		foo.u_ymd as u_ymd,
		foo.tokui_cd as tokui_cd,
		foo.uri_s as uri_s,
		foo.uri_t as uri_t,
		foo.uri_g as uri_g,
		foo.zn_gaku as zn_gaku,
		foo.z_gaku as z_gaku, 
		foo.zi_gaku as zi_gaku,
		foo.s_ymd as s_ymd
--t.tokui_nm as tokui_nm,
/*t.tokui_nm2 as tokui_nm2,
		t.tokui_kana as tokui_kana,
		t.post_no as post_n,
		t.address1,
		t.address2,
		t.address3*/
--j.product_nm,
--j.syukka_yotei_dt,
--j.row_no*/
from(select '1' as kbn,
		u.uri_no as uri_no,
		m.uri_row_no as uri_row,
		u.jyucyu_no as jyucyu,
		m.jyucyu_no as jyucyum,
		m.jyucyu_row_no as jyucyu_row,
		u.uri_ymd as u_ymd,
		u.tokui_cd as tokui_cd,
		m.uri_suryo as uri_s,
		m.uri_tanka as uri_t,
		m.uri_gaku as uri_g,
		u.zeinuki_gaku as zn_gaku,
		u.zei_gaku as z_gaku,
		u.zeikomi_gaku as zi_gaku,
		u.seikyu_ymd s_ymd --t.tokui_nm as tokui_nm,
/*t.tokui_nm2 as tokui_nm2,
		t.tokui_kana as tokui_kana,
		t.post_no as post_n,
		t.address1,
		t.address2,
		t.address3*/
--j.product_nm,
--j.syukka_yotei_dt,
--j.row_no
	from t_uri u
	left join t_uri_m m
	on u.uri_no = m.uri_no --inner join m_tokui t on u.tokui_cd = t.tokui_cd
--inner join t_jyucyu_m j on u.jyucyu_no = j.jyucyu_no

	union all
	select '2',
		u.uri_no as uri_no,
		m.uri_row_no as uri_row,
		u.jyucyu_no as jyucyu,
		m.jyucyu_no as jyucyum,
		m.jyucyu_row_no as jyucyu_row,
		u.uri_ymd as u_ymd,
		u.tokui_cd as tokui_cd,
		m.uri_suryo as uri_s,
		m.uri_tanka as uri_t,
		m.uri_gaku as uri_g,
		u.zeinuki_gaku as zn_gaku,
		u.zei_gaku as z_gaku,
		u.zeikomi_gaku as zi_gaku,
		u.seikyu_ymd s_ymd --t.tokui_nm as tokui_nm,
/*t.tokui_nm2 as tokui_nm2,
		t.tokui_kana as tokui_kana,
		t.post_no as post_n,
		t.address1,
		t.address2,
		t.address3*/
--j.product_nm,
--j.syukka_yotei_dt,
--j.row_no
	from t_uri u
	left join t_uri_m m
	on u.uri_no = m.uri_no --inner join m_tokui t on u.tokui_cd = t.tokui_cd
--inner join t_jyucyu_m j on u.jyucyu_no = j.jyucyu_no

	union all
	select '3',
		u.uri_no as uri_no,
		m.uri_row_no as uri_row,
		u.jyucyu_no as jyucyu,
		m.jyucyu_no as jyucyum,
		m.jyucyu_row_no as jyucyu_row,
		u.uri_ymd as u_ymd,
		u.tokui_cd as tokui_cd,
		m.uri_suryo as uri_s,
		m.uri_tanka as uri_t,
		m.uri_gaku as uri_g,
		u.zeinuki_gaku as zn_gaku,
		u.zei_gaku as z_gaku,
		u.zeikomi_gaku as zi_gaku,
		u.seikyu_ymd s_ymd --t.tokui_nm as tokui_nm,
/*t.tokui_nm2 as tokui_nm2,
		t.tokui_kana as tokui_kana,
		t.post_no as post_n,
		t.address1,
		t.address2,
		t.address3*/
--j.product_nm,
--j.syukka_yotei_dt,
--j.row_no
	from t_uri u
	left join t_uri_m m
	on u.uri_no = m.uri_no --inner join m_tokui t on u.tokui_cd = t.tokui_cd
--inner join t_jyucyu_m j on u.jyucyu_no = j.jyucyu_no
) foo
where foo.uri_no = '6'
order by foo.kbn;


[selectRecords1]
select 	u.uri_no as uri_no,
		m.uri_row_no as uri_row,
		u.jyucyu_no as jyucyu,
		m.jyucyu_no as jyucyum,
		m.jyucyu_row_no as jyucyu_row,
		u.uri_ymd as u_ymd,
		u.tokui_cd as tokui_cd,
		m.uri_suryo as uri_s,
		m.uri_tanka as uri_t,
		m.uri_gaku as uri_g,
		u.zeinuki_gaku as zn_gaku,
		u.zei_gaku as z_gaku, 
		u.zeikomi_gaku as zi_gaku,
		u.seikyu_ymd s_ymd,
		t.tokui_nm as tokui_nm,
		t.tokui_nm2 as tokui_nm2,
		t.tokui_kana as tokui_kana,
		t.post_no as post_n,
		t.address1,
		t.address2,
		t.address3,
		j.product_nm
from t_uri u 
		inner join t_uri_m m on u.uri_no = m.uri_no 
		inner join m_tokui t on u.tokui_cd = t.tokui_cd
		inner join t_jyucyu j on u.jyucyu_no = j.jyucyu_no
order by u.uri_no,j.row_no


[selectRecords2]
select to_char(to_date(m.nouki_dt,'yyyymmdd'),'yyyy/mm/dd') as nouki_dt,
		cast(h.haccyu_no as char(6)), 
		h.siire_nm,
		m.product_nm,
		m.size_biko,
		a.han_name,
		m.kigata_flg,
		m.haccyu_suryo,
		m.tokui_nm,
		to_char(to_date(m.hannyu_dt,'yyyymmdd'),'yyyy/mm/dd') as hannyu_dt,
		h.biko1,
		h.biko2
from (t_haccyu_h h inner join t_haccyu_m m on h.haccyu_no = m.haccyu_no ) inner join m_hanyo a on m.color = a.han_cd and a.mst_kbn = '16'
where 1=1
and ((cast(m.nouki_dt as char(8))= '&1') or ('&1' = ''))
or ((cast(m.nouki_dt as char(8))= '&2') or ('&2' = ''))
and ((cast(h.haccyu_no as char(6))= '&3') or ('&3' = ''))
and ((h.siire_nm = '&4') or ('&4' = ''))
and ((m.product_nm like '&5') or ('&5' like '')) 
--order by h.haccyu_no