nvl with query inside

Its possible to do something like this:开发者_C百科

and zmt.mediatypeid in nvl(:P21_MEDIA, select mediatypeid from zbx.media_type)

:P21_MEDIA is a variable

Yes, it is called a scalar subquery expression:

select x, (select y from another_table where foo = x) from the_table

select x from the_table where foo = (select y from another_table where bar = x)

It can only return a single column and a single row, anything else is an error.

I cannot test it right now, but I would assume you can also use it inside a function such as NVL. The documentation only mentions that it cannot be used in GROUP BY.

yes, you can use CASE statement to check the condition:

and 1 = CASE
         WHEN :P21_MEDIA IS NOT NULL THEN
            CASE 
               WHEN zmt.mediatypeid = :P21_MEDIA THEN 1
               ELSE 0
            END
         WHEN zmt.mediatypeid IN (select mediatypeid from zbx.media_type)  THEN 1
         ELSE 0
        END