Home » SQL & PL/SQL » SQL & PL/SQL » comparison ALL with subquery returns no rows - TRUE (Oracle 11.2.0.3)
comparison ALL with subquery returns no rows - TRUE [message #654289] |
Fri, 29 July 2016 07:30 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have two tables T and T_ODS.
Requirement is - For a given rfrl_no of table T verify if it exists in T_ODS, if rfrl_no does not exists then the output needs to be 'F' or no rows.
If the rfrl_no exists then verify if spp_pat_id is matching and that's the only distinct spp_pat_id for the rfrl_no then return 'S'.
create table t (rfrl_no number, spp_pat_id VARCHAR2(10), hub_pat_id VARCHAR2(10), msa_pat_id VARCHAR2(10) )
begin
INSERT INTO T VALUES (10, 100, 1000, 2000);
--
INSERT INTO T VALUES (20, 100, 1000, 2000);
INSERT INTO T VALUES (20, 101, 1000, 2000);
INSERT INTO T VALUES (20, 102, '', 2000);
--
INSERT INTO T VALUES (30, 100, 1000, 2000);
INSERT INTO T VALUES (30, 100, 1001, 2001);
--
INSERT INTO T VALUES (40, 10, 1000, 2000);
INSERT INTO T VALUES (40, 11, 1000, 2000);
INSERT INTO T VALUES (40, 12, 1001, 2000);
INSERT INTO T VALUES (40, 13, 1002, 2000);
INSERT INTO T VALUES (40, 14, 1002, 2000);
END;
/
create table t_ods (rfrl_no number, spp_pat_id number, hub_pat_id number, msa_pat_id number)
begin
insert into t_ods values (20, 100, '', '');
--
insert into t_ods values (30, 100, 200, 300);
insert into t_ods values (30, 101, 200, 300);
--
insert into t_ods values (40, 10, 1000, 201);
insert into t_ods values (40, 10, 1000, 201);
insert into t_ods values (40, 10, 1000, 201);
--
end;
/
Above in table "T" for rfrl_no=10 and spp_pat_id=100, there is no possible rfrl_no entry in T_ODS, so the output needs to be 'F'
For rfrl_no=20 and spp_pat_id=100, there is matching rfrl_no and spp_pat_id in table T_ODS, so the output needs to be 'S'
For rfrl_no=30 and spp_pat_id=100, there is matching rfrl_no and spp_pat_id but the problem is there is another spp_pat_id i.e. 101 so the output needs to 'F'
For rfrl_no=40 and spp_pat_id=10, there is matching rfrl_no and spp_pat_id and all the possible spp_pat_ids are same i.e. 10, so the output needs to be 'S'
My first thought was to use =All AND subquery.
i.e.
SELECT DISTINCT 'S'
FROM t s
WHERE s.rfrl_no = 40
AND s.spp_pat_id = 10
AND (s.spp_pat_id) = ALL (SELECT tt.spp_pat_id
FROM t_ods tt
WHERE rfrl_no = tt.rfrl_no
)
This works well, except the case where in there is no matching rfrl_no in T_ODS e.g. T.rfrl_no=10 and T.spp_pat_id=100.
It seems the subquery returns TRUE if there is no matching record or the join condition is not met.
For which case, I thought of having outer joins and NVL i.e. -
SELECT DISTINCT 'S'
FROM t s
WHERE s.rfrl_no = 10
AND s.spp_pat_id = 100
AND (s.spp_pat_id) =
ALL (SELECT NVL2 (tt.rfrl_no,
tt.spp_pat_id,
s.spp_pat_id - 1
)
FROM t_ods tt
WHERE s.rfrl_no = tt.rfrl_no(+))
But it looks like I can't make use of outerjoins to solve this, as it still returns S for rfrl_no=10 and spp_pat_id=100.
So finally, I used -
SELECT DISTINCT 'S'
FROM t s
WHERE s.rfrl_no = 10
AND s.spp_pat_id = 100
AND (s.spp_pat_id) =
ALL ((SELECT nvl2(tt.rfrl_no,tt.spp_pat_id, s.spp_pat_id-1)
FROM t_ods tt , t s1
WHERE s1.rfrl_no = tt.rfrl_no(+)
and s1.rfrl_no = s.rfrl_no
and s1.spp_pat_id = s.spp_pat_id
)
)
This works as expected, please let me know if there any better of doing it or any other alternatives to achieve it.
Regards,
Pointers
|
|
|
|
|
|
|
|
|
Re: comparison ALL with subquery returns no rows - TRUE [message #654316 is a reply to message #654315] |
Sat, 30 July 2016 03:08 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You mean that rfrl_no number can be null and spp_pat_id can be null?
Anyway, create an index with both these columns and you will don't access twice the table as you won't access it once, only the index and both times the same blocks, so at least the second time the blocks will be in memory.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:29:16 CDT 2024
|