Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: select query result set

    Hi,
    I just want the common rows(FIRST_NO) which are in

    PL_PLA ='BBB' AND PL_PLA='CCC'

    which should not in other PL_PLA rows

    DB2 9.1 Z/OS

    Code:
    SELECT
     T1.PL_PLA
    ,T1.FIRST_NO
    ,T1.FLT_SHT
    
    FROM BASE_TABLE T1
    
    WHERE
    
    T1.PL_PLA in ('BBB','CCC')
    
    
    
    
    BASE TABLE
    
    PL_PLA FIRST_NO FLT_SHT
    R23     322       IEMC
    1EE     444       FDDD  
    AAA     111       QQQQ
    AAA     222       WWWW
    BBB     111       REEE
    BBB     222       WSQQ
    BBB     333       EWDG
    CCC     111       EEDD
    CCC     222       EWDQ
    CCC     444       UKCL
    DDD     111       DDDD
    DDD     222       VVVV
    EEE     111       EWEW
     
    ACTUAL RESULT SET GETTING
    
    PL_PLA FIRST_NO FLT_SHT
    BBB     111       REEE
    BBB     222       WSQQ
    BBB     333       EWDG
    CCC     111       EEDD
    CCC     222       EWDQ
    CCC     444       UKCL
    EXPECTED RESULT SET
    
    PL_PLA FIRST_NO FLT_SHT
    
    BBB     111       REEE
    BBB     222       WSQQ
    CCC     111       EEDD
    CCC     222       EWDQ

    Thanks,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An idea may be...
    (1) Take INTERSECT ALL of FIRST_NO where PL_PLA ='BBB' and FIRST_NO where PL_PLA='CCC'.
    (2) Then join the result of (1) and BASE_TABLE to get the values of other columns.

    Though I couldn't understand the phrase "which should not in other PL_PLA rows", the idea may return your EXPECTED RESULT SET.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is the rule that would apply to eliminate this :BBB 333 EWDG
    or what means : which should not in other PL_PLA rows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •