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.

already found it, thanks:

SELECT cust_num, status, isnull (location, 'NONE'),
isnull (cdk_ptu_idc, '?'), itm_num

FROM t211_lbl t211 LEFT OUTER JOIN location loc
ON t211.cust_num = loc.t063_lct_nbr
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
WHERE t211_lbl_nbr = 9782296



idogan_tech (AT) yahoo (DOT) com (Ibrahim DOGAN) wrote in message news:<6bf58828.0312020708.70276769 (AT) posting (DOT) google.com>...