CODEWITHSUNDEEP
sqloracle-database
Indian
Indian

Reputation: 527

Using REGEXP_LIKE in old style join

I have PL/SQL query with old style joins (with using (+) ). And now I need to add left joined table with REGEXP_LIKE clause. How can I make this?

In ANSI-style the query look like this:

select
    *
from
    deals d
    left join
        auctions a on a.session_code = d.session_code
    left join
        auction_history ah on ah.auction_code = a.auction_code and
                                REGEXP_LIKE(ah.auction_code, '^[A-Z]+')
where
    trunc(d.operday) = trunc(sysdate)

And in old style I want to get something like this:

select
    *
from
    deals d,
    auctions a,
    auction_history ah,
where
    trunc(d.operday) = trunc(sysdate) and
    d.session_code = a.session_code (+) and 
    (a.auction_code = ah.auction_code (+) and 
        REGEXP_LIKE(ah.auction_code, '^[A-Z]+'))

But it doesn't return deals which session_code is null.

Thanks in advance!

Upvotes: 2

Views: 3779

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

REGEXP_LIKE is treated as a regular function regarding legacy joins. Here's an example:

SQL> WITH main_table AS
  2  (SELECT 1 ID FROM dual UNION ALL
  3   SELECT 2 FROM dual),
  4  lookup_table AS
  5  (SELECT 1 ID, 'txt' txt FROM dual UNION ALL
  6   SELECT 2 ID, '999' txt FROM dual)
  7  SELECT m.id, l.txt
  8    FROM main_table m, lookup_table l
  9   WHERE m.id = l.id(+)
 10     AND REGEXP_LIKE(l.txt(+), '^[A-Z]+');

        ID TXT
---------- ---
         1 txt
         2

Still, I would advise against using old-style joins in recent editions.

Upvotes: 4

Related Questions