For ex: I have a record with name = "sga_target", and no record exists where name = "_exafusion_enabled". I still want to display a row with "_exafusion_enabled" as name and NULL as value, without using an outer join with any other table.

select name,value from v$spparameter where name 

Desired output :

name                 value
-------------------- -----
sga_target           18G
_exafusion_enabled   null  

(actually name column does not have the value "_exafusion_enabled")

How can we do this?

  • 1
    In other words, how do you select from a table something that is not there? You can't. And what's wrong with outer join? – mustaccio Sep 26 '18 at 16:24
  • 1
    @mustaccio - the MODEL clause let's you add rows to a resulting data set. So, yes, it is possible, but (imho) not maintainable. – Michael Kutz Sep 26 '18 at 16:45
  • 1
    To clarify - do you want to hard-code in "_exafusion_enabled" with a NULL value, assuming that you'll never have a row with that name? Or is this a case where you want a row with a NULL value if the name doesn't exist, and the row with the actual value if it does? – RDFozz Sep 26 '18 at 18:07
  • 1
    What's wrong with using an outer join? – a_horse_with_no_name Sep 26 '18 at 18:48

I'd go about trying to feed the input in using a "pseudo-table" or view .. (as opposed to an IN clause):

here's a very simple example ... if you have more requirements on how to feed the values in, there's lots of examples around the web showing how to build a "table" like w_input, with the data coming in.

  with w_input as (
        select 'sga_target'  name from dual union all
        select '_exafusion_enabled'  from dual
  select wi.name, nvl(sp.value,'null') value
    from w_input  wi
        LEFT OUTER JOIN v$spparameter  sp
           ON sp.name = wi.name

  NAME               VALUE
  ------------------ ------------------------------
  sga_target         1610612736
  _exafusion_enabled null

  2 rows selected.
  • Even though the OP specified they didn't want a OUTER JOIN solution, they might not have understood this possibility. It's worth proposing (and is the best solution I can think of). – RDFozz Sep 26 '18 at 18:11
  • yeah, not sure why that restriction, to be honest ... (and also honest: I didn't notice it until you pointed it out ... heh) shrug – Ditto Sep 26 '18 at 19:24
  • Maybe they just meant that there was no second table involved in their query that would provide a natural way to get the NULL in value; or, maybe there's a dictate from above not to use an OUTER JOIN. The OP can always note that this violates their requirements, if they can't use it - it's still the best answer I can think of. – RDFozz Sep 26 '18 at 20:51
  • I just made a minute change and it served my purpose.Great thought Ditto!!! World is full of innovators and creative people...Kudos... – satya Sep 27 '18 at 11:37
select name,value 
from v$spparameter 
where name = 'sga_target'
Union all
Select '_exafusion_enabled', Null 
from dual
  • I assume that, if there is a row where name = "_exafusion_enabled", the OP wants to see that row, and not have an extra row with that name and a NULL value. – RDFozz Sep 26 '18 at 16:49
  • 1
    @RDFozz I don't assume, that's not what he said... – SQLRaptor Sep 26 '18 at 16:53
  • @RDFozz The op specifically states that there is no record for the second row that is desired in the results. – Joe W Sep 26 '18 at 17:27
  • @JoeW - OK, you're right; there is nothing that explicitly states that, in the future, "_exafusion_enabled" will be added, and that the OP would want to use the actual value in that case instead of NULL. I have merely assumed that's true. I'll ask. – RDFozz Sep 26 '18 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.