Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    3

    Question Unanswered: Need Query to select records from a DB2 table on specific condition

    Hi,

    I haven't been using db2 much and I need help in writing a query.

    My requirement.
    ---------------
    I have a table AAA in which records are inserted like given below. For each Component_name, this table gets 0000 status record only after 9030 status record .

    Component_name AAAAAAAA has records with status code 2030, 9030 and 0000. Component_name CCCCCCCC has records with status code 2030 and 9030.

    I need to find all components in this table that has received 9030 status but not 0000 status. So, the output that I am looking for from the resulting query is component name CCCCCCCC because this component has only 9030 status and not 0000 status.

    Table AAA
    ----------
    Component_name status_code
    AAAAAAAA 2030
    AAAAAAAA 9030
    AAAAAAAA 0000
    CCCCCCCC 2030
    CCCCCCCC 9030

    Let me know if you need more information.

    Thanks.
    Last edited by sukhoiM35; 02-11-07 at 22:36.

  2. #2
    Join Date
    Apr 2004
    Posts
    64
    You can try this one,

    select component_name from AAA A1
    where A1.status_code = 9030 and
    not exists (Select 1 from AAA A2
    where A1.component_name = A2.component_name and
    A2.status_code = 0000
    )

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    alternative query:

    SELECT component_name
    FROM AAA
    WHERE status_code = '9030'
    AND component_name NOT IN
    ( SELECT component_name from AAA
    WHERE status_code = '0000' )


    if the combination of component_name and status_code is unique and status_code '0000' may only appear if status_code '9030' is present another possible query is:

    SELECT component_name
    FROM AAA
    WHERE status_code IN ('0000','9030')
    GROUP BY component_name
    HAVING count(*) = 1


    try all the alternatives for best performance.

  4. #4
    Join Date
    Feb 2007
    Posts
    3

    Smile

    Thanks xamar and umayer. I'll try this today and let you guys know.

  5. #5
    Join Date
    Feb 2007
    Posts
    3

    Smile

    Hi,

    Just want to thank both of you for giving the query. I was able to execute them and got the desired results. Thanks for your help.

Posting Permissions

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