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
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