SQL

[talashil]

I have SQL ...

select A.UNITCOMMON_ID,
A.POLICY_ID,
A.LOGICAL_ENTITY_ID,
A.VERS_ORIG_MAJ_MIN,
A.ROW_ACTIVE_STS_D,
A.COMPANY_ID,
A.PRODUCT_ID

from POL_UNITCOMMON A,
POL_UNITCOMMON_V B

where A.POLICY_ID = 11
and A.UNITCOMMON_ID = B.UNITCOMMON_ID
and B.DELETED_F = 'N'
and B.VERS_CNTL_MAJ_MIN =
(
select max(I.VERS_CNTL_MAJ_MIN)
from POL_UNITCOMMON_V I
where I.UNITCOMMON_ID = B.UNITCOMMON_ID
and I.VERS_CNTL_MAJ_MIN <= 0
and I.ROW_ACTIVE_STS_D <= 9999999
and (A.LOGICAL_TABLE_C = 'PPU' or A.LOGICAL_TABLE_C = 'CPU')
)
can I modify the query ie using Joins instead of subselect

[Tonkuma]

I can't understand why you want to use join instead of subselect.
But, you might use following query.
select A.UNITCOMMON_ID,
A.POLICY_ID,
A.LOGICAL_ENTITY_ID,
A.VERS_ORIG_MAJ_MIN,
A.ROW_ACTIVE_STS_D,
A.COMPANY_ID,
A.PRODUCT_ID

from POL_UNITCOMMON A,
POL_UNITCOMMON_V B,
TABLE
(
select max(I.VERS_CNTL_MAJ_MIN) AS MAX_VERS_CNTL_MAJ_MIN
from POL_UNITCOMMON_V I
where I.UNITCOMMON_ID = B.UNITCOMMON_ID
and I.VERS_CNTL_MAJ_MIN <= 0
and I.ROW_ACTIVE_STS_D <= 9999999
and (A.LOGICAL_TABLE_C = 'PPU' or A.LOGICAL_TABLE_C =
'CPU')
) C
where A.POLICY_ID = 11
and A.UNITCOMMON_ID = B.UNITCOMMON_ID
and B.DELETED_F = 'N'
and B.VERS_CNTL_MAJ_MIN = C.MAX_VERS_CNTL_MAJ_MIN

[Knut Stolze]

ta******@hotmail.com wrote:

I have SQL ...

select A.UNITCOMMON_ID,
A.POLICY_ID,
A.LOGICAL_ENTITY_ID,
A.VERS_ORIG_MAJ_MIN,
A.ROW_ACTIVE_STS_D,
A.COMPANY_ID,
A.PRODUCT_ID

from POL_UNITCOMMON A,
POL_UNITCOMMON_V B

where A.POLICY_ID = 11
and A.UNITCOMMON_ID = B.UNITCOMMON_ID
and B.DELETED_F = 'N'
and B.VERS_CNTL_MAJ_MIN =
(
select max(I.VERS_CNTL_MAJ_MIN)
from POL_UNITCOMMON_V I
where I.UNITCOMMON_ID = B.UNITCOMMON_ID
and I.VERS_CNTL_MAJ_MIN <= 0
and I.ROW_ACTIVE_STS_D <= 9999999
and (A.LOGICAL_TABLE_C = 'PPU' or A.LOGICAL_TABLE_C = 'CPU')
)
can I modify the query ie using Joins instead of subselect

Why do you want to do that? You're supposed to write the query in a way you
like and DB2 is supposed to generate the most efficient access plan,
regardless of the way the query is written.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Tonkuma]

It would be better to move out "and (A.LOGICAL_TABLE_C = 'PPU' or
A.LOGICAL_TABLE_C = 'CPU' )" from subquery
select A.UNITCOMMON_ID,
A.POLICY_ID,
A.LOGICAL_ENTITY_ID,
A.VERS_ORIG_MAJ_MIN,
A.ROW_ACTIVE_STS_D,
A.COMPANY_ID,
A.PRODUCT_ID

from POL_UNITCOMMON A,
POL_UNITCOMMON_V B,
TABLE
(
select max(I.VERS_CNTL_MAJ_MIN) AS MAX_VERS_CNTL_MAJ_MIN
from POL_UNITCOMMON_V I
where I.UNITCOMMON_ID = B.UNITCOMMON_ID
and I.VERS_CNTL_MAJ_MIN <= 0
and I.ROW_ACTIVE_STS_D <= 9999999
) C

where A.POLICY_ID = 11
and A.UNITCOMMON_ID = B.UNITCOMMON_ID
and B.DELETED_F = 'N'
and B.VERS_CNTL_MAJ_MIN = C.MAX_VERS_CNTL_MAJ_MIN
and (A.LOGICAL_TABLE_C = 'PPU' or A.LOGICAL_TABLE_C = 'CPU')

[Knut Stolze]

ta******@hotmail.com wrote:

I have SQL ...

select A.UNITCOMMON_ID,
A.POLICY_ID,
A.LOGICAL_ENTITY_ID,
A.VERS_ORIG_MAJ_MIN,
A.ROW_ACTIVE_STS_D,
A.COMPANY_ID,
A.PRODUCT_ID

from POL_UNITCOMMON A,
POL_UNITCOMMON_V B

where A.POLICY_ID = 11
and A.UNITCOMMON_ID = B.UNITCOMMON_ID
and B.DELETED_F = 'N'
and B.VERS_CNTL_MAJ_MIN =
(
select max(I.VERS_CNTL_MAJ_MIN)
from POL_UNITCOMMON_V I
where I.UNITCOMMON_ID = B.UNITCOMMON_ID
and I.VERS_CNTL_MAJ_MIN <= 0
and I.ROW_ACTIVE_STS_D <= 9999999
and (A.LOGICAL_TABLE_C = 'PPU' or A.LOGICAL_TABLE_C = 'CPU')
)
can I modify the query ie using Joins instead of subselect

Personally, I'd write the query that way:

SELECT a.unitcommon_id, a.policy_id, a.logical_entity_id,
a.vers_orig_maj_min, a.row_active_sts_d,
a.company_id, a.product_id
FROM pol_unitcommon AS a
WHERE a.policy_id = 11 AND
a.logical_table_c IN ( 'PPU', 'CPU' ) AND
EXISTS ( SELECT 1
FROM pol_unitcommon AS b
WHERE a.unitcommon_id = b.unitcommon_id AND
b.deleted_f = 'N' AND
b.vers_cntl_maj_min =
( SELECT MAX(i.vers_cnt_maj_min)
FROM pos_unitcommon_v AS i
WHERE i.unitcommon_id = b.unitcommon_id AND
i.vers_cntl_maj_min <= 0 AND
i.row_active_sts_d <= 9999999 ) )

There I see directly that only data from a single table is requested. The
rows are restricted based on the subquery in the EXISTS predicate, which
could also be phrased as a non-correlated subquery using "a.unitcommon_id
IN ( SELECT b.unitcommon_id FROM ... )"

--
Knut Stolze
Information Integration
IBM Germany / University of Jena