You Asked

Hello

I have a table like this

ID    NTYPE
1       0
2       0
3       1
4       2

I need a select to get all IDs according of a list of NTYPE (1 to N), but if any of the NTYPE list does not exist then get where NTYPE = 0..

For example:
If NTYPE list = 1 then The IDs must be 3
If NTYPE list = 1,2 then The IDs must be 3,4
If NTYPE list = 2,3 then The IDs must be 1,2,4... 1 and 2 because there is not records where ntype = 3 then get where NTYPE = 0...

I can make a procedure that can do this but I would like do it in a select

Is it possible? Any Idea?

Thanks in advance

and Chris said...

So you want to join each input value to ntype and:

- if it exists, return the corresponding ids
- if it doesn't, the ids for ntype 0?

If so, you can do this by passing your inputs as a table. Then join where the values equal the ntype. Or the ntype is zero. And there are no other rows in your table matching with an ntype matching the input.

Which gives something like:

create table t (
  id int, ntype int 
);

insert into t values ( 1, 0 );
insert into t values ( 2, 0 );
insert into t values ( 3, 1 );
insert into t values ( 4, 2 );
commit;

with inputs as (
  select 1 n from dual
)
  select t.id 
  from   t
  join   inputs i
  on     t.ntype = i.n or (
    t.ntype = 0 and 
    not exists (
      select * from t t2
      where  t2.ntype = i.n
    )
  );

ID   
   3 

with inputs as (
  select 2 n from dual union all
  select 1 n from dual
)
  select t.id 
  from   t
  join   inputs i
  on     t.ntype = i.n or (
    t.ntype = 0 and 
    not exists (
      select * from t t2
      where  t2.ntype = i.n
    )
  );

ID   
   4 
   3 

with inputs as (
  select 2 n from dual union all
  select 3 n from dual
)
  select t.id t
  join   inputs i
  on     t.ntype = i.n or (
    t.ntype = 0 and 
    not exists (
      select * from t t2
      where  t2.ntype = i.n
    )
  );

ID   
   4 
   1 
   2

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alternate solution

Mark Wooldridge Wooldridge, November 26, 2018 - 6:18 pm UTC

n_ttyp object was used to easily provide the required input data.

The question didn't state what happens if there are multiple values that do not exist, e.g. 3, 7, 9, does the output generate multiple occurrences of ID values or distinct?

create type n_ttyp as table of number
/

create table m
(id number,
 ntyp number)
/

insert into m values(1, 0);
insert into m values(2, 0);
insert into m values(3, 1);
insert into m values(4, 2);

commit;

  with q as (
select *
  from table(n_ttyp(2,3)))
select m2.id,
       m.*,
       q.*
  from m m2,
       m,
       q
 where m.ntyp (+)= q.column_value
   and m2.ntyp = nvl(m.ntyp, 0)
/
        ID         ID       NTYP COLUMN_VALUE
---------- ---------- ---------- ------------
         1                                  3
         2                                  3
         4          4          2            2


  with q as (
select *
  from table(n_ttyp(1,2)))
select m2.id, m.*, q.*
  from m m2,
       m,
       q
 where m.ntyp (+)= q.column_value
   and m2.ntyp = nvl(m.ntyp, 0)
/

        ID         ID       NTYP COLUMN_VALUE
---------- ---------- ---------- ------------
         3          3          1            1
         4          4          2            2


  with q as (
select *
  from table(n_ttyp(1)))
select m2.id, m.*, q.*
  from m m2,
       m,
       q
 where m.ntyp (+)= q.column_value
   and m2.ntyp = nvl(m.ntyp, 0)
/

        ID         ID       NTYP COLUMN_VALUE
---------- ---------- ---------- ------------
         3          3          1            1

November 27, 2018 - 4:23 am UTC

Nice input.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Alternate solution

More to Explore

SQL

Select first value if exists, otherwise select another value

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Alternate solution

More to Explore

SQL