with t_TSTAFFTITLE (STAFF_ID,ANTIBIO_LEVEL,TITLE_CODE) as
(
select a.STAFF_ID,max(a.ANTIBIO_LEVEL) as ANTIBIO_LEVEL ,
max(a.TITLE_CODE) as TITLE_CODE
from NORTHOPI.TSTAFFTITLE as a
group by a.STAFF_ID
)
SELECT STF.STAFF_ID AS Code, PS.PERSON_NAME as Name,
integer(CASE WHEN PS.GENDER='0300000001' THEN 1
WHEN PS.GENDER='0300000002' THEN 2 ELSE 0 END) AS Sex, PS.ID_NO AS PersonalID,
CAST(PS.BIRTH_DATE AS TIMESTAMP) AS Birthday,
TITLE.TITLE_CODE AS TitleCode,STF.PROF_CODE AS Business, STF.BP_NO AS Tel, STF.CELL_PHONE AS Phone,
integer(CASE WHEN TITLE.ANTIBIO_LEVEL>=0 then TITLE.ANTIBIO_LEVEL else 0 end) AS AntibioticLevel,
sub.TEAMID as SubjectCode,sub.TEAMNAME as SubjectName
FROM NORTHOPI.TSTAFF AS STF
INNER JOIN NORTHOPI.TPERSON AS PS ON STF.PERSON_ID = PS.PERSON_ID
LEFT OUTER JOIN t_TSTAFFTITLE AS TITLE ON STF.STAFF_ID = TITLE.STAFF_ID
LEFT OUTER JOIN NORTHOPI.TTEAMDTL taff on STF.STAFF_ID=taff.STAFFID
LEFT OUTER JOIN NORTHOPI.TTEAMM sub on sub.TEAMID=taff.TEAMID Where STF.STAFF_ID='000000' with ur