SELECT FROM SELECT TMP_PAGE ROWNUM ROW_ID FROM select from select as drugFlag CTL_ID as dataValue CTL_NAME as dataText CTL_CODE as ctlCode catalog as type as pid as fontColor SORT_ID as secre

SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( SELECT * FROM ( SELECT '' AS "drugFlag", CTL_ID AS "dataValue", CTL_NAME AS "dataText", CTL_CODE AS "ctlCode", 'catalog' AS "type", ? AS "pid", '' AS "fontColor", SORT_ID, '' AS "secretCode", '' AS "secretName", 0 AS "sort", 'false' AS "leaf" FROM EMR.EMR_CATALOG WHERE PARENT_CTL_ID = ? AND show_flag IS NULL

        UNION ALL
        
        SELECT 'drug' AS "drugFlag",
            hdd.drug_id AS "dataValue",
            hdd.drug_name_all AS "dataText",
            hdd.py_code || ',' || hdd.wb_code AS "ctlCode",
            'dict' AS "type",
            ? AS "pid",
            hdd.font_color AS "fontColor",
            1000 AS "SORT_ID",
            dd.SECRET_CODE AS "secretCode",
            dd.secret_name AS "secretName",
            (
                CASE
                    WHEN (
                        dd.secret_code LIKE '%24%'
                        OR dd.secret_code LIKE '%,8%'
                        OR dd.secret_code LIKE '8%'
                    )
                        AND dd.secret_code LIKE '%23%'
                        AND (
                            dd.secret_code LIKE '%,9%'
                            OR dd.secret_code LIKE '9%'
                            OR dd.secret_code LIKE '%20%'
                            OR dd.secret_code LIKE '%21%'
                            OR dd.secret_code LIKE '%22%'
                        ) THEN 1
                    WHEN (
                        dd.secret_code LIKE '%24%'
                        OR dd.secret_code LIKE '%,8%'
                        OR dd.secret_code LIKE '8%'
                    )
                        AND dd.secret_code LIKE '%23%' THEN 2
                    WHEN (
                        dd.secret_code LIKE '%24%'
                        OR dd.secret_code LIKE '%,8%'
                        OR dd.secret_code LIKE '8%'
                    )
                        AND (
                            dd.secret_code LIKE '%,9%'
                            OR dd.secret_code LIKE '9%'
                            OR dd.secret_code LIKE '%20%'
                            OR dd.secret_code LIKE '%21%'
                            OR dd.secret_code LIKE '%22%'
                        ) THEN 3
                    WHEN dd.secret_code LIKE '%24%'
                        OR dd.secret_code LIKE '%,8%'
                        OR dd.secret_code LIKE '8%' THEN 4
                    WHEN dd.secret_code LIKE '%23%'
                        AND (
                            dd.secret_code LIKE '%,9%'
                            OR dd.secret_code LIKE '9%'
                            OR dd.secret_code LIKE '%20%'
                            OR dd.secret_code LIKE '%21%'
                            OR dd.secret_code LIKE '%22%'
                        ) THEN 5
                    WHEN dd.secret_code LIKE '%23%' THEN 6
                    WHEN dd.secret_code LIKE '%,9%'
                        OR dd.secret_code LIKE '9%'
                        OR dd.secret_code LIKE '%20%'
                        OR dd.secret_code LIKE '%21%'
                        OR dd.secret_code LIKE '%22%' THEN 7
                    ELSE 99
                END
            ) AS "sort",
            'false' AS "leaf"
        FROM SYSMN.SYS_HIS_DRUG_DICT hdd
        LEFT JOIN SYSMN.SYS_DRUG_DICT dd ON hdd.DRUG_CODE = dd.DRUG_CODE
        WHERE hdd.STOP_FLAG = '0'
            AND dd.STOP_FLAG = '0'
    )
    ORDER BY "sort"
)
TMP_PAGE

) WHERE ROW_ID <= ? AND ROW_ID >