Aug 242012

Click the SQL for scripts

  7 Responses to “For some things you have to get ANSI”

  1. select *
    i_want_to_return_everything “have to get ANSI?”,
    in_both_these_sets “no, but it can save you”
    “have to get ANSI?”.though_some_items = “no, but it can save you”.are_only_in_here(+) and
    “have to get ANSI?”.vice = “no, but it can save you”.versa(+)
    select *
    i_want_to_return_everything “full table scans and also”,
    in_both_these_sets “this ugly union construct”
    “full table scans and also”.though_some_items(+) = “this ugly union construct”.are_only_in_here and
    “full table scans and also”.vice(+) = “this ugly union construct”.versa

    • Very good :) It does take a few minutes to understand the Oracle version though, as well as being ugly!

    • It’s not so easy when we have something like that:

      with a(a) as (select * from table(ku$_objnumset(null,1,2,2,3,3,3)))
      ,b(b) as (select * from a)
      select *
      from a full join b on a=b
      order by 1 nulls first

      or “partition outer join”
      And only with 12c we can avoid ora-01417 for oracle syntax.

      • Thanks Sayan – I’m not familiar with ku$_objnumset. Have you got details on how it works?

        • Chris,

          it’s just undocumented collection in SYS schema: TYPE ku$_ObjNumSet IS TABLE OF NUMBER;
          I use it for test-cases only, though we can use documented sys.odci* types(varrays) for the same purposes.

  2. Good point, Sayan ! – I knew my reply was sloppy, but couldn’t resist …
    And there are ( probably many ) cases where it works correctly

    • Of course, Matthias!
      We can do it with a little change: union all instead of “union”, and antijoin in second part instead outer join:

      a(a) as (select * from table(ku$_objnumset(null,1,2,2,3,3,3)))
      ,b(b) as (select * from a)
      /* full join: */
      select * from a,b where a.a=b.b(+)
      union all
      select * from a,b where b.b=a.a(+) and a.a is null
      order by 1 nulls first

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>