View previous topic :: View next topic
|
Author |
Message |
Shweta12j
New User
Joined: 10 May 2010 Posts: 32 Location: Mumbai
|
|
|
|
Hi All ,
Greetings!!
I want to design a query where a unique row can be retrieved with varying inputs in where clause for example.
A Table is there with its name as Name_Element. Following are the columns.
Identifier - Nme_Element
1 - TEST
2 - TEST
3 - TEST
1 - TEST2
4 - TEST2
1 - TEST3
3 - TEST3
4 - TEST3
From the above values , i want to retrieve a unique identifier which is common for TEST,TEST2 and TEST3 which is 1 as per the above values.
Currently I am doing this with the help of cobol . I am just curious to know if this can be done via DB2 also.
Appreciate yor suggestion on this.
Regards,
Shweta. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi,
Assuming your table will have only 3 Nme_Element
Try below query.this is untested
Code: |
SELECT Identifier,COUNT(Nme_Element )
FROM TABLE
GROUBY BY Identifier
HAVING COUNT(Nme_Element ) = 3 |
|
|
Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010 Posts: 32 Location: Mumbai
|
|
|
|
Hi Chandan ,
Can you pls explain how in the query it is extracting the common identifier between the nme_elements.
It would be helpful if you can provide me a slighlty tuned query.
Regards,
Shweta. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
What does "slightly tuned" mean to you? I do not understand the question. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Shweta,
Does this query giving you desired results? Or you facing any issues?
I am just taking those identifiers which has three occurrences..
I am also on same boat as Dick what you mean by slightly tuned query?
Regards,
Chandan |
|
Back to top |
|
|
Shweta12j
New User
Joined: 10 May 2010 Posts: 32 Location: Mumbai
|
|
|
|
Hi Chandan,
By tuned I meant if you can provide me the query using the example mentioned above. I am not able to understand how this query would work.
Hence could not execute it.
Regards,
Shweta. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
What you want is not "tuning".
To learn about the given code, suggest you run it in your test environment with a small number of rows to be processed. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Shweta,
Have you tried executing this query? What problems your arefacing with this query?
Regards,
Chandan |
|
Back to top |
|
|
|