Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Maanos.

Asked: September 27, 2018 - 11:03 am UTC

Last updated: September 28, 2018 - 9:17 am UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

I want a Query that should fetch a single record based on match conditions:
table looks like

create table SERVICES
(
  srvc     VARCHAR2(10) not null,
  location VARCHAR2(10),
  grp      VARCHAR2(10),
  empno    VARCHAR2(10),
  price    NUMBER default 0 not null);

alter table SERVICES
  add constraint uq_services unique (SRVC, LOCATION, GRP, EMPNO);

insert into services(srvc, location, empno, grp, price)
values('srv1', null, null, null, 1500);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', null, 2000);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', 'A', 2500);
commit;


I want to query this table based on following conditions:
srvc = 'srv1' and location='home' and empno = '123' and grp = 'A' ----> It should return price 2500

srvc = 'srv1' and location='home' and empno = '123' and grp = 'B' ----> It should return price 2000

srvc = 'srv1' and location='office' and empno = '456' and grp = 'B' ----> It should return price 1500

I want to match rows where the columns match the specified value or the column is null.

Please help me in building the query.
regards

and Chris said...

So you want to return the row that matches the most criteria?

If so, make a query that checks if each column matches the bind variable or is null. Then sort the matching rows on each column. This will place the null-valued rows last. And get the first of these:

create table SERVICES
(
  srvc     VARCHAR2(10) not null,
  location VARCHAR2(10),
  grp      VARCHAR2(10),
  empno    VARCHAR2(10),
  price    NUMBER default 0 not null);

alter table SERVICES
  add constraint uq_services unique (SRVC, LOCATION, GRP, EMPNO);

insert into services(srvc, location, empno, grp, price)
values('srv1', null, null, null, 1500);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', null, 2000);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', 'A', 2500);
commit;

var srv varchar2(10);
var loc varchar2(10);
var emp number;
var g   varchar2(1);

exec :srv := 'srv1';
exec :loc := 'home';
exec :emp := '123';
exec :g   := 'A';

select * from (
  select * from services
  where  ( srvc = :srv or srvc is null )
  and    ( location = :loc or location is null )
  and    ( empno = :emp or empno is null )
  and    ( grp = :g or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;

SRVC   LOCATION   GRP   EMPNO   PRICE   
srv1   <null>     A     123        2500 

exec :srv := 'srv1';
exec :loc := 'home';
exec :emp := '123';
exec :g   := 'B';

select * from (
  select * from services
  where  ( srvc = :srv or srvc is null )
  and    ( location = :loc or location is null )
  and    ( empno = :emp or empno is null )
  and    ( grp = :g or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;

SRVC   LOCATION   GRP      EMPNO   PRICE   
srv1   <null>     <null>   123        2000 

exec :srv := 'srv1';
exec :loc := 'office';
exec :emp := '456';
exec :g   := 'B';

select * from (
  select * from services
  where  ( srvc = :srv or srvc is null )
  and    ( location = :loc or location is null )
  and    ( empno = :emp or empno is null )
  and    ( grp = :g or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;

SRVC   LOCATION   GRP      EMPNO    PRICE   
srv1   <null>     <null>   <null>      1500 

Rating

  (1 rating)

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

Comments

Thanks

Maanos Ajnabi, September 28, 2018 - 5:00 am UTC

Dear Chris Saxon

Thanks for the help.
it is really most simple and appropriate solution.

regards
Chris Saxon
September 28, 2018 - 9:17 am UTC

You're welcome

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.