Results 1 to 2 of 2

Thread: ANSI join help

  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Question ANSI join help

    I run this SQL for reportin purposes and it will no longer run since our conversion to SQL 2008. I am a non-technical guy trying to convert a SQL statement to ANSI join syntax.

    Any assistance with this conversion is greatly appreciated.

    SELECT a.prc_dtl_cd,
    a.platts_mo,
    b.platts_ind
    FROM #platts_prc1 a,
    prc_frml_dtl b,
    platts_diff c,
    platts_diff d
    WHERE b.prc_dtl_cd = a.prc_dtl_cd
    AND c.mkt_cd =* b.mkt_cd
    AND c.merc_ct_mo_cd =* b.merc_ct_mo_cd
    AND c.prc_days_cd =* b.prc_days_cd
    AND c.platts_mo =* a.platts_mo
    AND d.mkt_cd = CASE b.mkt_cd
    WHEN 'AGP+-MDCSH' THEN 'AGP+-MDCSH'
    WHEN 'ARGUS P+' THEN 'ARGUS P+'
    ELSE 'PPLUS'
    END
    AND d.merc_ct_mo_cd =* b.merc_ct_mo_cd
    AND d.prc_days_cd =* b.prc_days_cd
    AND d.platts_mo =* a.platts_mo ;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.prc_dtl_cd
         , a.platts_mo
         , b.platts_ind
      FROM #platts_prc1 a
    INNER
      JOIN prc_frml_dtl b
        ON b.prc_dtl_cd = a.prc_dtl_cd
    LEFT OUTER
      JOIN platts_diff c
        ON c.mkt_cd = b.mkt_cd
       AND c.merc_ct_mo_cd = b.merc_ct_mo_cd
       AND c.prc_days_cd = b.prc_days_cd
       AND c.platts_mo = a.platts_mo
    LEFT OUTER
      JOIN platts_diff d
        ON d.merc_ct_mo_cd = b.merc_ct_mo_cd
       AND d.prc_days_cd = b.prc_days_cd
       AND d.platts_mo = a.platts_mo ;
       AND d.mkt_cd = 
           CASE b.mkt_cd WHEN 'AGP+-MDCSH' THEN 'AGP+-MDCSH'
                         WHEN 'ARGUS P+' THEN 'ARGUS P+'
                         ELSE 'PPLUS' END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •