Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

converting standard SELECT to ANSI outer-join form



I'm trying to convert the following query to ANSI form and am stuck
when it came to convert the subquery in WHERE clause. ( (subquery) *=
loc.cdk_ptu_idc ):

SELECT COUNT(1) 
FROM  t211_lbl t211, location loc
WHERE t211_lbl_nbr  = 9782296 AND cust_num   *= t063_lct_nbr AND
stg_fnc_code in (10,90)
       AND (SELECT e195_cdk_ptu_idc                           
            FROM e202_pdc_pln e202, e195_pln_lct e195
            WHERE e195.e202_pdc_pln_nbr = e202.e202_pdc_pln_nbr      
                  AND e195.e202_pdc_pln_dt  = e202.e202_pdc_pln_dt    
                    AND e202.wav_nbr  =  t211.wav_nbr
                    AND e195.cust_num  =  t211.t063_dtn_lct_nbr
            ) *=  loc.cdk_ptu_idc


* I converted all joins except the one in WHERE clause and came up
with the following but it doesn't produce the same result set:

SELECT COUNT(1)
FROM  t211_lbl t211 LEFT OUTER JOIN location loc NOHOLDLOCK

       ON  t211.cust_num = loc.t063_lct_nbr

WHERE t211_lbl_nbr = 9782296 AND stg_fnc_code in (10,90)
      AND (SELECT e195_cdk_ptu_idc
           FROM  (e202_pdc_pln e202 INNER JOIN e195_pln_lct e195
                   ON e195.e202_pdc_pln_nbr = e202.e202_pdc_pln_nbr
AND
                       e195.e202_pdc_pln_dt  = e202.e202_pdc_pln_dt)
            WHERE  e202.wav_nbr   =  t211.wav_nbr AND
                    e195.cust_num  =  t211.t063_dtn_lct_nbr) = 
loc.cdk_ptu_idc

* So the problem is how to convert the outer-join in subquery to ANSI
form. It uses corelated (external table) table (loc) and there is no
way to reference that table in WHERE clause in ANSI form..

Thanks,
i.d.



<-- __Chronological__ --> <-- __Thread__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.