SELECT
    CH0911.ORSJGS_CD
   ,CH0911.ORSJGS_TENDYMD
   ,(CASE WHEN CH0911_SUB.ORSJGS_NM IS NULL THEN CH0911.ORSJGS_NM
     ELSE CH0911_SUB.ORSJGS_NM END
    ) AS CHLST08001_ORSJGS_NM
FROM
    CH_M_ORSJGS CH0911
LEFT JOIN
   (SELECT
        CH0911_SUB.ORSJGS_CD
       ,CH0911_SUB.ORSJGS_NM
    FROM
        CH_M_ORSJGS CH0911_SUB
    WHERE
        CH0911_SUB.GENE_ADD_DTM = 
       (SELECT 
            MAX(CH0911_SUB_GENE.GENE_ADD_DTM) AS CH0911_MAX
        FROM
            CH_M_ORSJGS CH0911_SUB_GENE
        WHERE
            CH0911_SUB_GENE.ORSJGS_CD = CH0911_SUB.ORSJGS_CD
        AND CH0911_SUB_GENE.ORSJGS_TSTAYMD <= ':EVENT_YMD'
        AND CH0911_SUB_GENE.ORSJGS_TENDYMD >= ':EVENT_YMD'
        AND CH0911_SUB.MK_FLG = '0'
       )
    ) CH0911_SUB
ON  CH0911_SUB.ORSJGS_CD = CH0911.ORSJGS_CD
WHERE
    CH0911.ORSJGS_TENDYMD || CH0911.GENE_ADD_DTM = 
   (SELECT 
        MAX(CH0911_GENE.ORSJGS_TENDYMD || CH0911_GENE.GENE_ADD_DTM) AS CH0911_MAX
    FROM
        CH_M_ORSJGS CH0911_GENE
    WHERE
        CH0911_GENE.ORSJGS_CD = CH0911.ORSJGS_CD
    AND CH0911_GENE.ORSJGS_TSTAYMD <= ':EVENT_YMD'
    AND CH0911_GENE.MK_FLG = '0'
   )
ORDER BY CH0911.ORSJGS_CD