use core_data
set nocount on

SELECT 

'corpus:' + corpus.[corpusName] as [corpusName],
'file:' + corpus.[file] as [FileName],
'boundary:' + isnull(corpus.[boundary], '') as [boundary],
'pos:' + isnull(corpus.[pos], '') as [pos],
'cType:' + isnull(corpus.[sysCType], '') as [cType],
'cForm:' + isnull(corpus.[cForm], '') as [cForm],
'lForm:' + isnull(corpus.[reading], '') as [lForm],
case when isnull(corpus.[meaning],'')='' then 'lemma:' + isnull(corpus.[lemma], '') else 'lemma:' + isnull(corpus.[lemma], '') + '-' + corpus.[meaning] end as [lemma],
'orth:' + isnull(corpus.[orthToken], '') as [orth],
'pron:' + isnull(corpus.[pronToken], '') as [pron],
'kana:' + isnull(lex.[kanaToken], '') as [kana],
'form:' + isnull(lex.[form], '') as [form],
'orthBase:' + isnull(lex.[orthBase], '') as [orthBase],
'pronBase:' + isnull(lex.[pronBase], '') as [pronBase],
'kanaBase:' + isnull(lex.[kanaBase],'') as [kanaBase],
'formBase:' + isnull(lex.[formBase], '') as [formBase],
'goshu:' + isnull(lex.[wType], '') as [goshu],
'iType:' + isnull(lex.[iType], '') as [iType],
'iForm:' + isnull(lex.[iForm], '') as [iForm],
'iConType:' + isnull(lex.[iConType], '') as [iConType],
'fType:' + isnull(lex.[fType], '') as [fType],
'fForm:' + isnull(lex.[fForm], '') as [fForm],
'fConType:' + isnull(lex.[fConType], '') as [fConType],
'aType:' + isnull(lex.[aType],'') as [aType],
'aConType:' + isnull(lex.[aConType],'') as [aConType],
'aModType:' + isnull(lex.[aModType],'') as [aModType], 
'lid:' + convert(varchar, isnull(lex.[lid], '')) as [lid],
'type:' + isnull(lex.[type], '') as [type],
'apply:' + isnull(corpus.[apply],'') as [apply]



FROM dbo.corpus as corpus with (nolock)


-- lexにしか載ってない情報があるbaseとか
left join 
dbo.Lex_Full as lex with (nolock)
on	lex.[lemma] = corpus.[lemma]
and lex.[reading] = corpus.[reading]
and isnull(lex.[meaning], '') = isnull(corpus.[meaning], '')
and lex.[pos] = corpus.[pos]
and isnull(lex.[sysCType], '') = isnull(corpus.[sysCType], '')
and isnull(lex.[cForm], '') = isnull(corpus.[cForm], '')
and lex.[orthToken] = corpus.[orthToken]
and lex.[pronToken] = corpus.[pronToken]

-- 進行表を参照しないといけない場合もある
left join Kindai_wabun.dbo.[ファイル進行表] as k with (nolock) on
corpus.[file]=k.[file]

where
(
	(corpus.[corpusName] in ('BWJ_OW_core', 'BWJ_PB_core', 'BWJ_PN_core', 'BWJ_PM_core', 'BWJ_OC_core','BWJ_OY_core', 'BWJ_OW_hand', 'BWJ_PB_hand', 'BWJ_PN_hand'))
	or (corpus.[corpusName] in ('CSJ_コア'))
	or ((corpus.[corpusName] = 'CNV日常会話_モニター') and (k.[作業状態]='完了'))
)

and isnull(corpus.[apply], '') not like '%[YZ]%'


order by corpus.[file], corpus.[order]
