[selectRecords1]
	position(' ' in (replace(hi.sir_hin_cd,'　',' '))) as t,
	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then hi.sir_hin_cd
	else substring(hi.sir_hin_cd,0,(position(' ' in (replace(hi.sir_hin_cd,'　',' ')))))
	end as s_hin_cd,
	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then hi.sir_hin_nm
	else substring(hi.sir_hin_nm,0,(position(' ' in (replace(hi.sir_hin_nm,'　',' ')))))
	end as s_hin_nm,
	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then hi.sir_hin_cd
	else substring(hi.sir_hin_cd,(1+position(' ' in (replace(hi.sir_hin_cd,'　',' ')))))
	end as cor,
	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then hi.sir_hin_nm
	else substring(hi.sir_hin_nm,(1+position(' ' in (replace(hi.sir_hin_nm,'　',' ')))))
	end as cor_nm,
仕入品番と仕入品名、色番と色名を出力する為のSQLの忘備録
0判定の時は空白になるので、なにも出力されない、色番色名を取る為？
入力されているデータに問題ある可能性あり。
月曜日対応する。


[selectRecords]
select 
	sh.den_no,
	(1+sm.row_no) as row_no,
	trunc((row_no)/15) as row_page,
	to_char(to_date(sh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	case sh.syori_ymd
	when '' then ''
	else to_char(to_date(sh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as syori_ymd,
	sh.sir_cd,
	sh.sir_nm,
	t.tan_nm,
	han.han_name as tori_cond,
	sh.zeinuki_gaku,
	sh.zei_gaku,
	sh.sir_tan,
	sh.zeikomi_gaku,
	sh.tan_cd,
	sm.hin_cd,
	replace(sm.hin_nm,chr(10),'')as hin_nm,
	sm.hacyu_no,
	--sm.hacyu_su as hacyu_su,
	case when hi.tani ='g' then (round(sm.hacyu_su/1000,2))
		when hi.tani ='ｇ' then (round(sm.hacyu_su/1000,2))
	else sm.hacyu_su
	end as hacyu_su,
	sm.sir_su,
	case when hi.tani ='g' then (round(1000*sm.tanka))
		when hi.tani ='ｇ' then (round(1000*sm.tanka))
	else sm.tanka
	end as tanka,
	--round(1000*sm.tanka) as tanka,
	sm.kingaku,
	coalesce(cast(sm.hacyu_no as varchar(10)),'')||'-'|| coalesce (cast(sm.row_no as varchar(10)),'') as hacyu_row_no,
	--to_char(to_date(sm.nyuka_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as nyuka_yotei_ymd,
	case sh.nyuka_yotei_ymd
	when '' then ''
	else to_char(to_date(sh.nyuka_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as nouki_ymd,
	sh.biko1,
	sh.biko2,
	--t.biko3,
	--t.biko4,
	--t.tok_nm,
	--'〒'||substr(t.zip_no,1,3)||'-'||substr(t.zip_no,4,7) as yubin_no,
	--t.addr1,
	--t.addr2,
	--t.addr3,
	--t.addr4,
	--t.tori_cond,
	hi.sir_hin_cd,
--	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then ''
	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then hi.sir_hin_cd
	else substring(hi.sir_hin_cd,0,(position(' ' in (replace(hi.sir_hin_cd,'　',' ')))))
	end as s_hin_cd,
	hi.sir_hin_nm,
--	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then ''
	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then hi.sir_hin_nm
	else substring(hi.sir_hin_nm,0,(position(' ' in (replace(hi.sir_hin_nm,'　',' ')))))
	end as s_hin_nm,
	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then ''
	else substring(hi.sir_hin_cd,(1+position(' ' in (replace(hi.sir_hin_cd,'　',' ')))))
	end as cor,
	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then ''
	else substring(hi.sir_hin_nm,(1+position(' ' in (replace(hi.sir_hin_nm,'　',' ')))))
	end as cor_nm,
	(sm2.sum_hacyu_su/1000)as sum_hacyu_su,
	hi.sir_bante as sir_bante,
	sm.hosoku1 as sitate,
	sm.meisai_biko as m_biko,
	case when hi.tani = 'g' then 'Kg'
	else hi.tani
	end as tani,
	case ms.sir_tan
	when '' then '御中'
	else ''
	end as keisyou1,
	case ms.sir_tan
	when '' then ''
	else '様'
	end as keisyou2,
	sm.meisai_biko
from t_sir sh
left join m_tanto t on(sh.tan_cd = t.tan_cd)
left join t_sir_m sm on(sh.den_no = sm.den_no)
left join m_sir ms on(sh.sir_cd = ms.sir_cd)
left join m_hanyo han on(ms.tori_cond = han.han_cd and mst_kbn = '10')
left join m_hin hi on(sm.hin_cd = hi.hin_cd)
left join (select sm2.den_no,sum(1000*sm2.hacyu_su) as sum_hacyu_su from t_sir_m sm2 group by sm2.den_no ) sm2 on sm2.den_no = sh.den_no
where 1 = 1
and sh.hacyu_flg = '1'
and ((sh.syori_ymd >= '&1') or ('&1' = ''))
and ((sh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(sh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((sh.sir_cd = '&4') or ('&4' = ''))
--and ((sm.hin_nm like '%&5%') or ('&5' = ''))
order by sh.den_no,sm.row_no;