oracle的if else查询

<!-- 根据bpmID查询buLow列表 -->
<select id="getBuListByAppUserId" parameterType="com.alibaba.meri.dal.common.BuLowCodeDO"
resultMap="buLowCodeResult">


SELECT * FROM (
SELECT V1.GROUP_CODE,V1.GROUP_NAME,V1.TYPE
FROM TMI_GROUP_VL
V1 WHERE
V1.PARENT_CODE IN(
SELECT V.GROUP_CODE
FROM
TMI_GROUP_VL V,
TMI_USER_GROUP U
WHERE V.GROUP_CODE = U.GROUP_CODE
AND
U.USER_ID =
#{bpmId}
AND U.IS_ACTIVE = 'Y'
AND V.LANGUAGE=#{language}
)
AND
V1.LANGUAGE = #{language}
AND V1.TYPE='BU_LOW'


UNION ALL
SELECT
V2.GROUP_CODE,V2.GROUP_NAME,V2.TYPE FROM TMI_GROUP_VL V2
WHERE
V2.PARENT_CODE IN (
SELECT V1.GROUP_CODE FROM TMI_GROUP_VL V1
WHERE
V1.PARENT_CODE IN(
SELECT V.GROUP_CODE
FROM TMI_GROUP_VL V,
TMI_USER_GROUP U
WHERE V.GROUP_CODE = U.GROUP_CODE
AND U.USER_ID =
#{bpmId}
AND U.IS_ACTIVE = 'Y'
AND V.LANGUAGE=#{language}
) AND
V1.LANGUAGE = #{language}
) AND V2.LANGUAGE=#{language}
AND
V2.TYPE='BU_LOW'


UNION ALL
SELECT V.GROUP_CODE,V.GROUP_NAME,V.TYPE
FROM
TMI_GROUP_VL V,
TMI_USER_GROUP U
WHERE V.GROUP_CODE = U.GROUP_CODE
AND
U.USER_ID =
#{bpmId}
AND U.IS_ACTIVE = 'Y'
AND V.LANGUAGE=#{language}
AND
V.TYPE='BU_LOW'
)
</select>