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.

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
)

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.

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

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.