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 Go to next message
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 #654308 is a reply to message #654289] Fri, 29 July 2016 23:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> SELECT 'S', rfrl_no, spp_pat_id
  2  FROM   t
  3  WHERE  EXISTS
  4  	    (SELECT *
  5  	     FROM   t_ods
  6  	     WHERE  t_ods.rfrl_no = t.rfrl_no
  7  	     AND    t_ods.spp_pat_id = t.spp_pat_id)
  8  AND    NOT EXISTS
  9  	    (SELECT *
 10  	     FROM   t_ods
 11  	     WHERE  t_ods.rfrl_no = t.rfrl_no
 12  	     AND    t_ods.spp_pat_id != t.spp_pat_id)
 13  ORDER  BY rfrl_no, spp_pat_id
 14  /

'    RFRL_NO SPP_PAT_ID
- ---------- ----------
S         20 100
S         40 10

2 rows selected.
Re: comparison ALL with subquery returns no rows - TRUE [message #654311 is a reply to message #654308] Sat, 30 July 2016 01:54 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you Barbara for teaching a new thing.

Only thing is that T_ODS is quite huge table in our case.

The query access it twice. Is it possible to access it only once and get hold the results.

Regards,
Pointers
Re: comparison ALL with subquery returns no rows - TRUE [message #654312 is a reply to message #654311] Sat, 30 July 2016 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Isn't (rfrl_no number, spp_pat_i) the key of the table?

Re: comparison ALL with subquery returns no rows - TRUE [message #654313 is a reply to message #654312] Sat, 30 July 2016 02:21 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi Micheal,

You mean for index. Its not primary key by the way.

Regards,
Pointers
Re: comparison ALL with subquery returns no rows - TRUE [message #654314 is a reply to message #654313] Sat, 30 July 2016 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I mean a primary or unique key.
What is the primary key of the table?

Re: comparison ALL with subquery returns no rows - TRUE [message #654315 is a reply to message #654314] Sat, 30 July 2016 02:32 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Its a warehouse system. Its in the form of denormalized.

There is no primary key defined as data might be repetitive and could be null as well.



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 Go to previous message
Michel Cadot
Messages: 68625
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.

Previous Topic: how create synonym for full database
Next Topic: Same record with Space and Without Space
Goto Forum:
  


Current Time: Thu Mar 28 19:41:39 CDT 2024