select *
		from (
		SELECT DISTINCT mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , COALESCE(MAX(count_day),0) as count_day , COALESCE(MAX(count),0) as count , regist_target_number
		from  mst_medium_etc_relation LEFT OUTER JOIN mst_medium AS medium_target ON
		mst_medium_etc_relation.target_medium = medium_target.medium_id LEFT OUTER JOIN mst_medium AS medium_set ON
		mst_medium_etc_relation.set_medium = medium_set.medium_id LEFT OUTER JOIN trn_advertiser_program ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number LEFT OUTER JOIN
		(select count(*) as count , medium_id , attestation_program_id
					from trn_action_log LEFT OUTER JOIN
					trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
					where mailaddress is not null and trn_advertiser_program.start_date <= trn_action_log.action_date
			group by medium_id , attestation_program_id ) action ON
		trn_advertiser_program.attestation_id = action.attestation_program_id LEFT OUTER JOIN
		(select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		LEFT OUTER JOIN mst_medium_etc_relation ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number
		where set_medium = '' and mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and used_flag = 't' and  view_order = '0'  group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id
		LEFT OUTER JOIN mst_program_medium_relation ON
		mst_medium_etc_relation.target_medium = mst_program_medium_relation.medium and
		trn_advertiser_program.attestation_id = mst_program_medium_relation.attestation_id_program LEFT OUTER JOIN
		(SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule ON mst_medium_etc_relation.client = mst_client_schedule.client and
		  mst_medium_etc_relation.target_medium = mst_client_schedule.medium
		where set_medium = '' and medium_target.asp_id = 1 and medium_set.asp_id = 1 and mst_medium_etc_relation.used_flag = 't' and trn_advertiser_program.asp_id = 1 and view_random = 'f' and  view_order = '0' and mst_program_medium_relation.used_flag = 't' and trn_advertiser_program.view_rank <> '0' and (
				(trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00')) <= '0801' and
				trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00')) >= '0801' and schedule_kind = '6' and mst_client_schedule.used_flag = 't' ) or
				(trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null )
		group by mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , regist_target_number) sub
		where COALESCE(count ,0) < regist_target_number and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE regist_target_number END)
		order by random()

select *
		from (
		SELECT DISTINCT mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , COALESCE(MAX(count_day),0) as count_day , COALESCE(MAX(count),0) as count , regist_target_number
		from  mst_medium_etc_relation LEFT OUTER JOIN mst_medium AS medium_target ON
		mst_medium_etc_relation.target_medium = medium_target.medium_id LEFT OUTER JOIN mst_medium AS medium_set ON
		mst_medium_etc_relation.set_medium = medium_set.medium_id LEFT OUTER JOIN trn_advertiser_program ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number LEFT OUTER JOIN
		(select count(*) as count , medium_id , attestation_program_id
					from trn_action_log LEFT OUTER JOIN
					trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
					where mailaddress is not null and trn_advertiser_program.start_date <= trn_action_log.action_date
			group by medium_id , attestation_program_id ) action ON
		trn_advertiser_program.attestation_id = action.attestation_program_id LEFT OUTER JOIN
		(select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		LEFT OUTER JOIN mst_medium_etc_relation ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number
		where set_medium = '' and mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and used_flag = 't' and  view_order = '1'  group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id
		LEFT OUTER JOIN mst_program_medium_relation ON
		mst_medium_etc_relation.target_medium = mst_program_medium_relation.medium and
		trn_advertiser_program.attestation_id = mst_program_medium_relation.attestation_id_program LEFT OUTER JOIN
		(SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule ON mst_medium_etc_relation.client = mst_client_schedule.client and
		  mst_medium_etc_relation.target_medium = mst_client_schedule.medium
		where set_medium = '' and medium_target.asp_id = 1 and medium_set.asp_id = 1 and mst_medium_etc_relation.used_flag = 't' and trn_advertiser_program.asp_id = 1 and view_random = 'f' and  view_order = '1' and mst_program_medium_relation.used_flag = 't' and trn_advertiser_program.view_rank <> '0' and (
				(trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00')) <= '0801' and
				trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00')) >= '0801' and schedule_kind = '6' and mst_client_schedule.used_flag = 't' ) or
				(trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null )
		group by mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , regist_target_number) sub
		where COALESCE(count ,0) < regist_target_number and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE regist_target_number END)
		order by random()

select *
		from (
		SELECT DISTINCT mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , COALESCE(MAX(count_day),0) as count_day , COALESCE(MAX(count),0) as count , regist_target_number
		from  mst_medium_etc_relation LEFT OUTER JOIN mst_medium AS medium_target ON
		mst_medium_etc_relation.target_medium = medium_target.medium_id LEFT OUTER JOIN mst_medium AS medium_set ON
		mst_medium_etc_relation.set_medium = medium_set.medium_id LEFT OUTER JOIN trn_advertiser_program ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number LEFT OUTER JOIN
		(select count(*) as count , medium_id , attestation_program_id
					from trn_action_log LEFT OUTER JOIN
					trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
					where mailaddress is not null and trn_advertiser_program.start_date <= trn_action_log.action_date
			group by medium_id , attestation_program_id ) action ON
		trn_advertiser_program.attestation_id = action.attestation_program_id LEFT OUTER JOIN
		(select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		LEFT OUTER JOIN mst_medium_etc_relation ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number
		where set_medium = '' and mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and used_flag = 't' and  view_order = '2'  group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id
		LEFT OUTER JOIN mst_program_medium_relation ON
		mst_medium_etc_relation.target_medium = mst_program_medium_relation.medium and
		trn_advertiser_program.attestation_id = mst_program_medium_relation.attestation_id_program LEFT OUTER JOIN
		(SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule ON mst_medium_etc_relation.client = mst_client_schedule.client and
		  mst_medium_etc_relation.target_medium = mst_client_schedule.medium
		where set_medium = '' and medium_target.asp_id = 1 and medium_set.asp_id = 1 and mst_medium_etc_relation.used_flag = 't' and trn_advertiser_program.asp_id = 1 and view_random = 'f' and  view_order = '2' and mst_program_medium_relation.used_flag = 't' and trn_advertiser_program.view_rank <> '0' and (
				(trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00')) <= '0801' and
				trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00')) >= '0801' and schedule_kind = '6' and mst_client_schedule.used_flag = 't' ) or
				(trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null )
		group by mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , regist_target_number) sub
		where COALESCE(count ,0) < regist_target_number and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE regist_target_number END)
		order by random()

select *
		from (
		SELECT DISTINCT mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , COALESCE(MAX(count_day),0) as count_day , COALESCE(MAX(count),0) as count , regist_target_number
		from  mst_medium_etc_relation LEFT OUTER JOIN mst_medium AS medium_target ON
		mst_medium_etc_relation.target_medium = medium_target.medium_id LEFT OUTER JOIN mst_medium AS medium_set ON
		mst_medium_etc_relation.set_medium = medium_set.medium_id LEFT OUTER JOIN trn_advertiser_program ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number LEFT OUTER JOIN
		(select count(*) as count , medium_id , attestation_program_id
					from trn_action_log LEFT OUTER JOIN
					trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
					where mailaddress is not null and trn_advertiser_program.start_date <= trn_action_log.action_date
			group by medium_id , attestation_program_id ) action ON
		trn_advertiser_program.attestation_id = action.attestation_program_id LEFT OUTER JOIN
		(select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		LEFT OUTER JOIN mst_medium_etc_relation ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number
		where set_medium = '' and mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and used_flag = 't' and  view_order = '3'  group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id
		LEFT OUTER JOIN mst_program_medium_relation ON
		mst_medium_etc_relation.target_medium = mst_program_medium_relation.medium and
		trn_advertiser_program.attestation_id = mst_program_medium_relation.attestation_id_program LEFT OUTER JOIN
		(SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule ON mst_medium_etc_relation.client = mst_client_schedule.client and
		  mst_medium_etc_relation.target_medium = mst_client_schedule.medium
		where set_medium = '' and medium_target.asp_id = 1 and medium_set.asp_id = 1 and mst_medium_etc_relation.used_flag = 't' and trn_advertiser_program.asp_id = 1 and view_random = 'f' and  view_order = '3' and mst_program_medium_relation.used_flag = 't' and trn_advertiser_program.view_rank <> '0' and (
				(trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00')) <= '0801' and
				trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00')) >= '0801' and schedule_kind = '6' and mst_client_schedule.used_flag = 't' ) or
				(trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null )
		group by mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , regist_target_number) sub
		where COALESCE(count ,0) < regist_target_number and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE regist_target_number END)
		order by random()

select *
		from (
		SELECT DISTINCT mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , COALESCE(MAX(count_day),0) as count_day , COALESCE(MAX(count),0) as count , regist_target_number
		from  mst_medium_etc_relation LEFT OUTER JOIN mst_medium AS medium_target ON
		mst_medium_etc_relation.target_medium = medium_target.medium_id LEFT OUTER JOIN mst_medium AS medium_set ON
		mst_medium_etc_relation.set_medium = medium_set.medium_id LEFT OUTER JOIN trn_advertiser_program ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number LEFT OUTER JOIN
		(select count(*) as count , medium_id , attestation_program_id
					from trn_action_log LEFT OUTER JOIN
					trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
					where mailaddress is not null and trn_advertiser_program.start_date <= trn_action_log.action_date
			group by medium_id , attestation_program_id ) action ON
		trn_advertiser_program.attestation_id = action.attestation_program_id LEFT OUTER JOIN
		(select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		LEFT OUTER JOIN mst_medium_etc_relation ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number
		where set_medium = '' and mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and used_flag = 't' and  view_order = '4'  group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id
		LEFT OUTER JOIN mst_program_medium_relation ON
		mst_medium_etc_relation.target_medium = mst_program_medium_relation.medium and
		trn_advertiser_program.attestation_id = mst_program_medium_relation.attestation_id_program LEFT OUTER JOIN
		(SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule ON mst_medium_etc_relation.client = mst_client_schedule.client and
		  mst_medium_etc_relation.target_medium = mst_client_schedule.medium
		where set_medium = '' and medium_target.asp_id = 1 and medium_set.asp_id = 1 and mst_medium_etc_relation.used_flag = 't' and trn_advertiser_program.asp_id = 1 and view_random = 'f' and  view_order = '4' and mst_program_medium_relation.used_flag = 't' and trn_advertiser_program.view_rank <> '0' and (
				(trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00')) <= '0801' and
				trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00')) >= '0801' and schedule_kind = '6' and mst_client_schedule.used_flag = 't' ) or
				(trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null )
		group by mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , regist_target_number) sub
		where COALESCE(count ,0) < regist_target_number and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE regist_target_number END)
		order by random()

select *
		from (
		SELECT DISTINCT mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , COALESCE(MAX(count_day),0) as count_day , COALESCE(MAX(count),0) as count , regist_target_number
		from  mst_medium_etc_relation LEFT OUTER JOIN mst_medium AS medium_target ON
		mst_medium_etc_relation.target_medium = medium_target.medium_id LEFT OUTER JOIN mst_medium AS medium_set ON
		mst_medium_etc_relation.set_medium = medium_set.medium_id LEFT OUTER JOIN trn_advertiser_program ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number LEFT OUTER JOIN
		(select count(*) as count , medium_id , attestation_program_id
					from trn_action_log LEFT OUTER JOIN
					trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
					where mailaddress is not null and trn_advertiser_program.start_date <= trn_action_log.action_date
			group by medium_id , attestation_program_id ) action ON
		trn_advertiser_program.attestation_id = action.attestation_program_id LEFT OUTER JOIN
		(select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		LEFT OUTER JOIN mst_medium_etc_relation ON
		mst_medium_etc_relation.client = trn_advertiser_program.old_afc_number
		where set_medium = '' and mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and used_flag = 't' and  view_order = '5'  group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id
		LEFT OUTER JOIN mst_program_medium_relation ON
		mst_medium_etc_relation.target_medium = mst_program_medium_relation.medium and
		trn_advertiser_program.attestation_id = mst_program_medium_relation.attestation_id_program LEFT OUTER JOIN
		(SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule ON mst_medium_etc_relation.client = mst_client_schedule.client and
		  mst_medium_etc_relation.target_medium = mst_client_schedule.medium
		where set_medium = '' and medium_target.asp_id = 1 and medium_set.asp_id = 1 and mst_medium_etc_relation.used_flag = 't' and trn_advertiser_program.asp_id = 1 and view_random = 'f' and  view_order = '5' and mst_program_medium_relation.used_flag = 't' and trn_advertiser_program.view_rank <> '0' and (
				(trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00')) <= '0801' and
				trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00')) >= '0801' and schedule_kind = '6' and mst_client_schedule.used_flag = 't' ) or
				(trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null )
		group by mst_client_schedule.limit_count , medium_target.medium_domain , contents_folder ,old_afc_number , medium_target.medium_id , site_name , link_text , regist_target_number) sub
		where COALESCE(count ,0) < regist_target_number and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE regist_target_number END)
		order by random()

select link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , (count) as count from (SELECT limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number ,
		sum(COALESCE(count,0)) as count , trn_action_log.attestation_program_id
		FROM mst_program_medium_relation left outer join trn_advertiser_program ON
		 mst_program_medium_relation.attestation_id_program = trn_advertiser_program.attestation_id left outer join
		 mst_advertiser ON trn_advertiser_program.advertiser_id = mst_advertiser.advertiser_id left outer join
		  (select action_date , count(*) as count , medium_id , attestation_program_id from trn_action_log
		  where medium_id = '' and mailaddress is not null group by medium_id , attestation_program_id , action_date )
		  trn_action_log ON mst_program_medium_relation.medium = trn_action_log.medium_id and trn_advertiser_program.attestation_id = trn_action_log.attestation_program_id
		  and action_date >= trn_advertiser_program.start_date LEFT OUTER JOIN
		  (SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule
		  ON trn_advertiser_program.old_afc_number = mst_client_schedule.client LEFT OUTER JOIN
		  (select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		where mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and trn_action_log.medium_id = ''
		group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id 1 and ((trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null ) group by limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number  , trn_action_log.attestation_program_id
		  ) action LEFT OUTER JOIN mst_program_medium_relation ON mst_program_medium_relation.attestation_id_program = action.attestation_program_id
		  WHERE action.regist_target_number > count and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE action.regist_target_number END)
		  group by link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , count
		  order by random() 

select link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , (count) as count from (SELECT limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number ,
		sum(COALESCE(count,0)) as count , trn_action_log.attestation_program_id
		FROM mst_program_medium_relation left outer join trn_advertiser_program ON
		 mst_program_medium_relation.attestation_id_program = trn_advertiser_program.attestation_id left outer join
		 mst_advertiser ON trn_advertiser_program.advertiser_id = mst_advertiser.advertiser_id left outer join
		  (select action_date , count(*) as count , medium_id , attestation_program_id from trn_action_log
		  where medium_id = '' and mailaddress is not null group by medium_id , attestation_program_id , action_date )
		  trn_action_log ON mst_program_medium_relation.medium = trn_action_log.medium_id and trn_advertiser_program.attestation_id = trn_action_log.attestation_program_id
		  and action_date >= trn_advertiser_program.start_date LEFT OUTER JOIN
		  (SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule
		  ON trn_advertiser_program.old_afc_number = mst_client_schedule.client LEFT OUTER JOIN
		  (select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		where mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and trn_action_log.medium_id = ''
		group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id 2 and ((trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null ) group by limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number  , trn_action_log.attestation_program_id
		  ) action LEFT OUTER JOIN mst_program_medium_relation ON mst_program_medium_relation.attestation_id_program = action.attestation_program_id
		  WHERE action.regist_target_number > count and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE action.regist_target_number END)
		  group by link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , count
		  order by random() 

select link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , (count) as count from (SELECT limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number ,
		sum(COALESCE(count,0)) as count , trn_action_log.attestation_program_id
		FROM mst_program_medium_relation left outer join trn_advertiser_program ON
		 mst_program_medium_relation.attestation_id_program = trn_advertiser_program.attestation_id left outer join
		 mst_advertiser ON trn_advertiser_program.advertiser_id = mst_advertiser.advertiser_id left outer join
		  (select action_date , count(*) as count , medium_id , attestation_program_id from trn_action_log
		  where medium_id = '' and mailaddress is not null group by medium_id , attestation_program_id , action_date )
		  trn_action_log ON mst_program_medium_relation.medium = trn_action_log.medium_id and trn_advertiser_program.attestation_id = trn_action_log.attestation_program_id
		  and action_date >= trn_advertiser_program.start_date LEFT OUTER JOIN
		  (SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule
		  ON trn_advertiser_program.old_afc_number = mst_client_schedule.client LEFT OUTER JOIN
		  (select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		where mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and trn_action_log.medium_id = ''
		group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id 3 and ((trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null ) group by limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number  , trn_action_log.attestation_program_id
		  ) action LEFT OUTER JOIN mst_program_medium_relation ON mst_program_medium_relation.attestation_id_program = action.attestation_program_id
		  WHERE action.regist_target_number > count and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE action.regist_target_number END)
		  group by link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , count
		  order by random() 

select link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , (count) as count from (SELECT limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number ,
		sum(COALESCE(count,0)) as count , trn_action_log.attestation_program_id
		FROM mst_program_medium_relation left outer join trn_advertiser_program ON
		 mst_program_medium_relation.attestation_id_program = trn_advertiser_program.attestation_id left outer join
		 mst_advertiser ON trn_advertiser_program.advertiser_id = mst_advertiser.advertiser_id left outer join
		  (select action_date , count(*) as count , medium_id , attestation_program_id from trn_action_log
		  where medium_id = '' and mailaddress is not null group by medium_id , attestation_program_id , action_date )
		  trn_action_log ON mst_program_medium_relation.medium = trn_action_log.medium_id and trn_advertiser_program.attestation_id = trn_action_log.attestation_program_id
		  and action_date >= trn_advertiser_program.start_date LEFT OUTER JOIN
		  (SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule
		  ON trn_advertiser_program.old_afc_number = mst_client_schedule.client LEFT OUTER JOIN
		  (select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		where mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and trn_action_log.medium_id = ''
		group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id 4 and ((trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null ) group by limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number  , trn_action_log.attestation_program_id
		  ) action LEFT OUTER JOIN mst_program_medium_relation ON mst_program_medium_relation.attestation_id_program = action.attestation_program_id
		  WHERE action.regist_target_number > count and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE action.regist_target_number END)
		  group by link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , count
		  order by random() 

select link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , (count) as count from (SELECT limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number ,
		sum(COALESCE(count,0)) as count , trn_action_log.attestation_program_id
		FROM mst_program_medium_relation left outer join trn_advertiser_program ON
		 mst_program_medium_relation.attestation_id_program = trn_advertiser_program.attestation_id left outer join
		 mst_advertiser ON trn_advertiser_program.advertiser_id = mst_advertiser.advertiser_id left outer join
		  (select action_date , count(*) as count , medium_id , attestation_program_id from trn_action_log
		  where medium_id = '' and mailaddress is not null group by medium_id , attestation_program_id , action_date )
		  trn_action_log ON mst_program_medium_relation.medium = trn_action_log.medium_id and trn_advertiser_program.attestation_id = trn_action_log.attestation_program_id
		  and action_date >= trn_advertiser_program.start_date LEFT OUTER JOIN
		  (SELECT * FROM mst_client_schedule WHERE
		  to_date(trim(to_char(start_year,'0000'))||'/'||trim(to_char(start_month,'00'))||'/'||trim(to_char(start_day,'00')),'YYYY/MM/DD' ) =
		  to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD')) mst_client_schedule
		  ON trn_advertiser_program.old_afc_number = mst_client_schedule.client LEFT OUTER JOIN
		  (select count(*) as count_day , medium_id , attestation_program_id from trn_action_log LEFT OUTER JOIN
		trn_advertiser_program ON trn_action_log.attestation_program_id = trn_advertiser_program.attestation_id
		where mailaddress is not null and to_date(to_char(action_date,'YYYY/MM/DD'), 'YYYY/MM/DD')
		= to_date(to_char(CURRENT_DATE,'YYYY/MM/DD'), 'YYYY/MM/DD') and trn_action_log.medium_id = ''
		group by medium_id , attestation_program_id ) action_day ON
		trn_advertiser_program.attestation_id = action_day.attestation_program_id 5 and ((trim(to_char(start_year,'0000'))||trim(to_char(start_month,'00'))||trim(to_char(start_day,'00'))||trim(to_char(start_hour,'00'))||trim(to_char(start_minute,'00'))
						<= '201201280801' and
								  trim(to_char(end_year,'0000'))||trim(to_char(end_month,'00'))||trim(to_char(end_day,'00'))||trim(to_char(end_hour,'00'))||trim(to_char(end_minute,'00'))
								  >= '201201280801' and schedule_kind = '9') or mst_client_schedule.client is null ) group by limit_count , count_day , link_text , site_name , contents_folder , trn_advertiser_program.old_afc_number , regist_target_number  , trn_action_log.attestation_program_id
		  ) action LEFT OUTER JOIN mst_program_medium_relation ON mst_program_medium_relation.attestation_id_program = action.attestation_program_id
		  WHERE action.regist_target_number > count and COALESCE(count_day ,0) < (CASE WHEN limit_count is not null THEN limit_count ELSE action.regist_target_number END)
		  group by link_text , site_name , contents_folder , old_afc_number , action.regist_target_number , count
		  order by random() 

QueryError<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="Cache-Control" content="no-cache">
<meta http-equiv="Expires" content="0">
<link rel="stylesheet" href="css/info.css" type="text/css">
<title></title>
<link rel="stylesheet" href="css/info.css" type="text/css">
<SCRIPT language="JavaScript">
<!--
function OpenWin8(){
    win=window.open("http://fincpc.com/pc/sformmail2.php","new","width=700,height=800,menubar=no,location=no,toolbar=yes,scrollbars=yes,resizable=yes");
}
function OpenWin9(){
    win=window.open("http://fincpc.com/pc/sformmail3.php","new","width=700,height=800,menubar=no,location=no,toolbar=yes,scrollbars=yes,resizable=yes");
}
function OpenWin10(){
    win=window.open("http://fincpc.com/pc/sformmail.php","new","width=700,height=800,menubar=no,location=no,toolbar=yes,scrollbars=yes,resizable=yes");
}
// -->
</SCRIPT>
</head>

<body>
<p align="center">
<img src="logo.gif"><br>
<br>
は、モバイル・スマートフォン専用サイトです。<br>
携帯電話またはスマートフォンで、下記よりアクセス下さい。<br>
<img src="http://fincpc.com/qr/php/qr_img.php?d=http%3A%2F%2Ffincpc.com%3F9lK11d2i63kg576&e=M&s=3" alt="" />
<br>
<A href="mailto:xxx@docomo.ne.jp?subject=モバイルキャッシング！&body=http%3A%2F%2Ffincpc.com%3F9lK11d2i63kg576">携帯電話にメール</A><BR>
</p>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
          <td align="center" bgcolor="#0000CC" style="padding:1px;font-size:12px;font-weight:normal;letter-spacing:1px;font-family:"sans-serif";">お問い合わせ</td>
        </tr>
 <tr>
          <td><img src="img/spacer.gif" width="1" height="5"></td>
        </tr>
        <tr>
          <td><font color="#FF9900">■</font>に関しての<br>
            　 お問い合わせは<a href="javascript:OpenWin9()">こちら</a> </td>
        </tr>
  <tr>
          <td><img src="img/spacer.gif" width="1" height="5"></td>
        </tr>
        <tr>
          <td align="center" bgcolor="#0000CC" style="padding:1px;font-size:12px;font-weight:normal;letter-spacing:1px;font-family:"sans-serif";">掲載に関して</td>
        </tr>
        <tr>
          <td><img src="img/spacer.gif" width="1" height="5"></td>
        </tr>
        <tr>
          <td><font color="#FF9900">■</font>広告主様は<a href="javascript:OpenWin8()">こちら</a><br>
            　広告を出稿しPRしたい広告主様 </td>
        </tr>
        <tr>
          <td><img src="img/spacer.gif" width="1" height="5"></td>
        </tr>
        <tr>
          <td height="26"><font color="#FF9900">■</font>掲載者様は<a href="javascript:OpenWin10()">こちら</a><br>
            　広告収入を得たいサイト掲載者様 </td>
        </tr>
  <tr>
          <td><img src="img/spacer.gif" width="1" height="5"></td>
        </tr>
      </table>
</body>
</html>