Including not found criteria in the result

I have this query:

select  BUnit, value f开发者_开发知识库rom myTable where BUnit in (555,556,557,558)

and these values for Bunit are constructed dynamically, ie. a program builds queries on demand, and they won't always select these numbers.

A possible query result would be

Bunit            value
556              10.2
558              5.18  

However, I would like to construct a select statement to return

Bunit            value
556              10.2
558              5.18  
555              NULL
557              NULL

I am not allowed to create tables (temporary or not) and to create procedures. How can I do it?

You can build a table with the criteria, and join on that:

select criteria.BUnit, t.value 
from (
    select 555 as BUnit from dual
    union select 556 from dual
    union select 557 from dual
    union select 558 from dual
) criteria
left join myTable t 
    on t.BUnit = criteria.BUnit

Without a temporary table, you can't since you must find a way to put all the values of BUnit into a table of some kind and then join myTable against that table to get the NULL values.

You could generate the SQL dynamically in your app and send down something like this:

select 555, (select value from myTable where BUnit = 555)
from dual
union
select 556, (select value from myTable where BUnit = 556)
from dual
...

But before you attempt this, I suggest that you create the desired result structure in your app and then fill in the values you get from the database.