Oracle : How to use if then in a select statement

select ma.TITLE,ma.ID as aid,ur.USER_ID  
from LEO_MENU_ACTIVITY_RELATION mr 
inner join LEO_MENU_MASTER mm on mm.ID=mr.MENU_ID 
INNER join LEO_MENUACTIVITY ma on mr.ACTIVITY_ID=ma.ID
LEFT OUTER JOIN LEO_USER_RIGHTS ur on ma.ID=ur.MENU_R开发者_开发技巧ELATION_ID and ur.MENU_ID=mm.ID and ur.USER_ID='141' 
where mm.ID='1'  
UNION (SELECT 
       'List' as TITLE, 
        1 as ID, 
        case (WHEN ur.MENU_RELATION_ID=1 THEN NULL ELSE USER_ID END)as USER_ID 
     from 
LEO_USER_RIGHTS)

In the UNION i want perform a conditional select like if ur.MENU_RELATION_ID=1 then the USER_ID should be selected as NULL otherwise the the original value from the 'LEO_USER_RIGHTS' table must be retrieved.

How can i do this ? Please help

Krishnik

If you want to combine in a UNION something based on the first table I think you can only do it by repeating the whole thing like this:

select ma.TITLE,ma.ID as aid,ur.USER_ID  
from LEO_MENU_ACTIVITY_RELATION mr 
inner join LEO_MENU_MASTER mm on mm.ID=mr.MENU_ID 
INNER join LEO_MENUACTIVITY ma on mr.ACTIVITY_ID=ma.ID
LEFT OUTER JOIN LEO_USER_RIGHTS ur on ma.ID=ur.MENU_RELATION_ID and ur.MENU_ID=mm.ID and ur.USER_ID='141' 
where mm.ID='1'  
UNION (SELECT 
       'List' as TITLE, 
        1 as ID, 
        case (WHEN ur.MENU_RELATION_ID=1 THEN NULL ELSE USER_ID END)as USER_ID 
     from 
 LEO_MENU_ACTIVITY_RELATION mr 
inner join LEO_MENU_MASTER mm on mm.ID=mr.MENU_ID 
INNER join LEO_MENUACTIVITY ma on mr.ACTIVITY_ID=ma.ID
LEFT OUTER JOIN LEO_USER_RIGHTS ur on ma.ID=ur.MENU_RELATION_ID and ur.MENU_ID=mm.ID and ur.USER_ID='141' 
where mm.ID='1'  
) 

If this is used often I would create a view to avoid duplicate code. In ORACLE (I do not know for other SQL dialects) there is a WITH statement enables you to make a sort of "temporary view".