RE: Just as a learning exercise

From: <Dominic.Brooks_at_barclayscapital.com>
Date: Wed, 4 May 2011 15:50:25 +0100
Message-ID: <853BE8E3785A554D92010F1FB6C0B279B1E0ACE1_at_LDNPCMMGMB11.INTRANET.BARCAPINT.COM>

Sent previously - possible unsuccessfully due to format? Attempt 2.

BTW, some example data would have been nice.

1=1 can often be found in "frameworks" generating dynamic sql to save having logic to determine whether your predicate is "WHERE ." or "AND ."

In the top half of the query at least, I believe Thomas has used 1=1 because the JOIN clause requires an ON, therefore a dummy 1=1 is required to force the cartesian join. Using CROSS JOIN would be clearer perhaps.

Anyway, ignoring distractions :)

ANSI syntax makes this sort of thing easy because it distinguishes between join predicates and filter predicates. There is not really such a distinction with the traditional Oracle syntax.

So, for example whereas with ANSI syntax you can conditionally join two tables via the ON clause e.g.
from    inner_table
left outer join

        outer_table
on      outer_table.join_col       = inner_table.join_col
and     inner_table.some_inner_col = some_condition;

With Oracle syntax, a conditional outer join like this could be implemented using a DECODE e.g.
from    inner_table, outer_table
where  decode(inner_table.some_inner_col,

               some_condition,
               inner_table.join_col,
               NULL) = outer_table.join_col (+);

Having said all this. that is not really what you're trying to do here.

For your specific example, Thomas, you're looking to filter t and then outer join to two tables (p to s and t to s). To this in Oracle you normally use an inline view or variation thereof (subquery factoring / with).

Just looking at the top half to keep things "simple", with my version of the data.. ANSI might look like this:
WITH persons

     (id, first_name, last_name)
     AS
     (SELECT 1,'James','Jamieson' FROM dual

      UNION ALL
      SELECT 2,'Jeff','Jefferson' FROM dual)

,    required_courses
     (ap_code, title, type) 
     AS
     (SELECT 1,'Fraud Prevention'  ,'Training' FROM dual

      UNION ALL
      SELECT 2,'Employee Happiness','Survey'   FROM dual
      UNION ALL
      SELECT 3,'Some other survey' ,'Survey'   FROM dual)

,    training_survey 
     (user_id, survey_app, created)
     AS
     (SELECT 1,1,TO_DATE('01-MAY-2010 12:12','DD-MON-YYYY HH24:MI')

      FROM   DUAL
      UNION  ALL
      SELECT 1,2,TO_DATE('01-MAY-2011 09:38','DD-MON-YYYY HH24:MI')
      FROM   DUAL
      UNION  ALL
      SELECT 2,1,TO_DATE('01-JAN-2010 10:00','DD-MON-YYYY HH24:MI')     
      FROM   DUAL
      UNION  ALL
      SELECT 2,3,TO_DATE('01-FEB-2011 10:00','DD-MON-YYYY HH24:MI')     
      FROM   DUAL)        

SELECT *
FROM   persons p
CROSS JOIN
       required_courses s
LEFT JOIN

       training_survey t
ON     s.ap_code   = t.survey_app
AND    t.user_id   = p.id
AND    t.created  >= TO_DATE('01-JAN-2011','DD-MON-YYYY')

WHERE  s.type      = 'Survey';

Traditional syntax like this:
WITH ... same as above
SELECT *

FROM   (SELECT *
        FROM   persons          p
        ,      required_courses s
        WHERE  s.type = 'Survey') ps
,      (SELECT t.*
        FROM   training_survey  t
        WHERE  t.created >= TO_DATE('01-JAN-2011','DD-MON-YYYY')) t

WHERE  t.survey_app (+) = ps.ap_code
AND    t.user_id    (+) = ps.id;

This is one way, probably not the only way, maybe not the best way. Hope it helps.

Cheers,
Dominic

---

This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Barclays. Any views or opinions presented are solely those of the author and do not necessarily represent those of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent to the foregoing. Barclays Capital is the investment banking division of Barclays Bank PLC, a company registered in England (number 1026167) with its registered offic  e at 1 Churchill Place, London, E14 5HP. This email may relate to or be sent from other members of the Barclays Group.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 04 2011 - 09:50:25 CDT