
www.Usenet.com
| <-- __Chronological__ --> | <-- __Thread__ --> |
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__ --> |