Skip to Main Content
  • Questions

  • Find value if found return the same or else look some other value to return in Oracle SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, JPartheeban.

Asked: January 16, 2017 - 4:43 pm UTC

Last updated: January 17, 2017 - 11:49 am UTC

Version: latest

Viewed 1000+ times

You Asked

Hi

I have to test 4 scenarios,

Condition: Input is single and same value: will not be change

Scenario 1:
Check input value if exist return the same value based on that input

Scenario 2:
If first scenario is getting false then need to find other value1 in the table and check that value1 is exist or not, if exist then return value1

Scenario 3:
If scenario 1 & 2 is not returning any value then, need to find other value2 in the table and check that value2 is exist or not, if exist then return value2

Scenario 4:
Nothing available then return empty value

test cases.

create table temp1
(owner varchar2(2),
version varchar2(2)
);

insert into temp1 values (1, 'en');
insert into temp1 values (2, 'G');
insert into temp1 values (3, 'I');
insert into temp1 values (4, '');

select * from temp1:

owner version
====== ========
1 en
2 G
3 I
4

Input: en
Scenario 1:
Check input value, if exist return the same value based on that input

select * from temp1 where version=:Input;

owner version
====== ========
1 en

--delete from temp1 where version='en';

select * from temp1:

owner version
====== ========
2 G
3 I
4


Input: en
Scenario 2:
'en' is deleted and Now we are providing the same 'en' as input and we have to check either 'I' is available or not in the table, if 'I' is available then we have to display the version = 'I'

owner version
====== ========
3 I

Now

--delete from temp1 where version='I';

select * from temp1:
owner version
====== ========
2 G
4


Input: en
Scenario 3:
'en' and 'I' values are deleted/ not available in the table and Now I am providing the same 'en' as input and we have to check either '' empty value is available or not in the table, if '' empty is available then we have to display the version = '' empty value

owner version
====== ========
4

delete from temp1 where version='';

select * from temp1:
owner version
====== ========
2 G


Input: en
Scenario 4:
'en', 'I' and '' empty values are deleted/ not available in the table and Now I am providing the same 'en' as input and we have to check either en or 'I' or '' empty value is available or not in the table. if nothing available then we have to display empty value

owner version
====== ========


can you please help me on this scenarios

Thanks
JPartheeban

and Chris said...

I don't understand where your values for 'I' and '' come from?

In any case, one way to do this is:

- Find all the rows that match any of these values (en, I, null)
- Add an order by, mapping these values to their priority order
- Return the first row

For example:

create table temp1 (
 owner varchar2(2), 
 version varchar2(2)
);

insert into temp1 values (1, 'en');
insert into temp1 values (2, 'G');
insert into temp1 values (3, 'I');
insert into temp1 values (4, '');
commit;

select * from (
  select * from temp1
  where  version in ('en', 'I') or version is null
  order  by case
   when version = 'en' then 1
   when version = 'I' then 2
   when version is null then 3
  end 
)
where  rownum = 1;

OWNER  VERSION  
1      en 

delete temp1 where owner = 1;

select * from (
  select * from temp1
  where  version in ('en', 'I') or version is null
  order  by case
   when version = 'en' then 1
   when version = 'I' then 2
   when version is null then 3
  end 
)
where  rownum = 1;

OWNER  VERSION  
3      I  

delete temp1 where owner = 3;

select * from (
  select * from temp1
  where  version in ('en', 'I') or version is null
  order  by case
   when version = 'en' then 1
   when version = 'I' then 2
   when version is null then 3
  end 
)
where  rownum = 1;

OWNER  VERSION  
4


Side note: the empty string ('') is equivalent to null in Oracle Database. So 

where col = ''


won't return anything! You need to use the "is null" condition to test for this.

Rating

  (2 ratings)

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

Comments

Find value if found return the same or else look some other value to return in Oracle SQL

JPartheeban J, January 17, 2017 - 5:20 am UTC

Thanks Chris,

I am not looking the Order by clause and sorry about which I forget this concept needs to get from PLSQL code, where I am thinking the code like below,



select owner, version
from temp1
where version = :input;

select count(1) into check1
from temp1
where version = :input;

--delete temp1 where version = 'en'; --execute after got the 'en' result

if check1 <1 then

select owner, version
from temp1 a
where not exists (select 1 from temp1 b where a.owner=b.owner and version = :input )
and version = case when version='I' then version end;

select count(1) into check1
from temp1 a
where not exists (select 1 from temp1 b where a.owner=b.owner and version = :input )
and version = case when version='I' then version end;

end if;

--delete temp1 where version = 'I'; --execute after got the 'I' result

if check2 <1 than

select owner, version
from temp1 a
where not exists (select 1 from temp1 b where a.owner=b.owner and version = :input )
and version is null

select count(1) into check3
from temp1 a
where not exists (select 1 from temp1 b where a.owner=b.owner and version = :input )
and version is null

else

select owner, version
from temp1 a
where not exists (select 1 from temp1 b where a.owner=b.owner and version = :input )
and version = 'none'

end if;


--delete temp1 where version is null; --execute after got the null result

Thanks
JPartheeban
Chris Saxon
January 17, 2017 - 11:49 am UTC

You may not be looking for the order by solution, but it's far superior to branching if/else PL/SQL!

If you need to do this in PL/SQL, just put it in a procedure:

create or replace procedure p as 
begin
  select ...
  into   ...
  from (
    select * from temp1
    where  version in ('en', 'I') or version is null
    order  by case
      when version = 'en' then 1
      when version = 'I' then 2
      when version is null then 3
    end 
  )
  where  rownum = 1;
end;
/

Find value if found return the same or else look some other value to return in Oracle SQL

JPartheeban J, January 17, 2017 - 5:43 am UTC

Hi

Can you please guide me this concept is possible in SQL with UNION and CASE?

Thanks
JPartheeban
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.