problem with outer join

building semantic layer
1

Hai,
I use sybase sql server 11 and have problems creating correlated queries using outer joins. ex:
SELECT
building.building_id,
substring(servicing_wire_npa.swc_clli,1,8), servicing_wire_npa.npa_nxx,
company.sales_city
FROM
building,
servicing_wire_npa,
company
WHERE
( company.sales_city=building.sales_city ) AND ( building.co_clli*=substring(servicing_wire_npa.swc_clli,1,8) ) AND servicing_wire_npa.npa_nxx = ANY
(
SELECT
min( SubAlias__51.npa_nxx )
FROM
servicing_wire_npa SubAlias__51
WHERE
substring(SubAlias__51.swc_clli,1,8) = substring(servicing_wire_npa.swc_clli,1,8) )
)
It Gives me error message like

A SUBQUERY IN THIS QUERY IS CORRELATED TO AN INNER TABLE OF AN OUTER JOIN. THIS IS NOT ALLOWED IN TSQL SEMANTICS. How do I get around this problem.

Any Sugestions?

Thank You

Radhika

Listserv Archives (BOB member since 2002-06-25)

2

Radhika,

Would a view work? Try this:

CREATE VIEW V1 AS
SELECT
substring(servicing_wire_npa.swc_clli,1,8) co_clli, npa_nxx
FROM
servicing_wire_npa
WHERE
npa_nxx = ANY
( SELECT min( a.npa_nxx )
FROM servicing_wire_npa a
WHERE substring(servicing_wire_npa.swc_clli,1,8) = substring(a.swc_clli,1,8)
)

Then your query becomes:

SELECT
building.building_id,
v1.co_clli,
v1.npa_nxx,
company.sales_city
FROM
building,
v1,
company
WHERE
( company.sales_city=building.sales_city ) AND ( building.co_clli*=v1.co_clli )

Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com

SELECT
building.building_id,
substring(servicing_wire_npa.swc_clli,1,8), servicing_wire_npa.npa_nxx,
company.sales_city
FROM
building,
servicing_wire_npa,
company
WHERE
( company.sales_city=building.sales_city ) AND ( building.co_clli*=substring(servicing_wire_npa.swc_clli,1,8) ) AND servicing_wire_npa.npa_nxx = ANY
(
SELECT
min( SubAlias__51.npa_nxx )
FROM
servicing_wire_npa SubAlias__51
WHERE
substring(SubAlias__51.swc_clli,1,8) =
substring(servicing_wire_npa.swc_clli,1,8)
)
)
It Gives me error message like

A SUBQUERY IN THIS QUERY IS CORRELATED TO AN INNER TABLE OF AN OUTER JOIN. THIS IS NOT ALLOWED IN TSQL SEMANTICS.

Listserv Archives (BOB member since 2002-06-25)