By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

SQL

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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')

Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.