Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2005

Tip: Looking for answers? Try searching our database.

SQL

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
talashil@hotmail.com - 28 Apr 2005 20:01 GMT
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 - 29 Apr 2005 10:34 GMT
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
Tonkuma - 30 Apr 2005 07:24 GMT
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 - 30 Apr 2005 06:24 GMT
> I have SQL ...
>
[quoted text clipped - 22 lines]
> )
> 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.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Knut Stolze - 30 Apr 2005 08:19 GMT
> I have SQL ...
>
[quoted text clipped - 22 lines]
> )
> 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 ... )"

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.