Re: SQL and IN

Christoph Seidel wrote:

>> To do this type of thing, I usually create a second table...
>> You do an outer join between the date table and the widget table

IMO, that's the only way you can do it using regular SQL.

You can not magically add rows to a SQL projection using literals this way. Oracle can only show you what it has - it does not have those extra "null rows" that you want to see. Thus, you need to create them. And the easiest to do that, is via a second table containing the full list of possible PK's and outer joining to that.

Alternatively you need to use row-by-row PL/SQL processing where you programatically determine if there are "missing" rows and then add them to the output set.

Or do something silly like this:
select
 id,
 max( object_type ) as object_type
from (

   select 1 as ID, NULL as object_type from dual    union all
   select 67238 as ID, NULL as object_type from dual    union all
   select 4 as ID, NULL as object_type from dual    union all
   select object_id as ID, object_type from user_objects    where object_id in (1,67238,4)
     )
group by id

This way you manually add the rows that are missing. Which is a very messy method. Much easier creating a second table up front with a distinct list of all potential id's.

--
Billy