|
View previous topic :: View next topic
|
| Author |
Message |
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
Hi,
Please find the details below. Thanks for taking a look.
TABLE:
Col1 Col2 Col3
1 a i
3 c i
2 b i
2 b ii
1 a ii
3 c i
4 b iii
5 d i
OUTPUT NEEDED:
Col1 Col2 Col3
1 a i
1 a ii
2 b i
2 b ii
REJECTED RECORDS:
Col1 Col2 Col3
3 c i
3 c i
4 b iii
5 d i
Requirement:
I need unique records with key being col1 and col2 and only the records which have more than one distinct value in col3.
Note: GROUP BY doesnt work with WHERE.
Thanks,
Soundarr |
|
| Back to top |
|
 |
pavankumarhassaji
New User
Joined: 20 Aug 2007
Posts: 11
Location: Canada
|
|
|
|
Hi soundarr,
As per your question, SELECT DISTINCT Col1, Col2, Col3 will work. Try it.
Thanks,
Pavan Hassaji. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| As per your question, SELECT DISTINCT Col1, Col2, Col3 will work. Try it. |
Did you try this before posting? This query does not provide the required output.
| Quote: |
| GROUP BY doesnt work with WHERE. |
I believe it does when coded correctly. . . It wouldn't solve this requirement, though.
For your requirement to find rows that only have multiple col3 values for the same col1/2 values, more than a simple query may be needed. |
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
Hi scherrer,
If GROUP BY works with WHERE, i can find a solution. But I get an error saying "DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WHERE".
Can you please guide me here or provide me any link.
Regards,
Soundarr |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| Could you please show your query? |
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
SELECT A,B,C FROM TB132
GROUP BY A,B
HAVING COUNT(*)>1
WHERE COUNT(DISTINCT C)>1; |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
try this one ..
| Code: |
SELECT,A.COL1,A.COL2,A.COL3 FROM TB132 A
,TB132 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3
AND EXISTS ( SELECT COL1 FROM TB132 GROUP BY COL1 HAVING
COUNT(COL1) > 1)
ORDER BY A.COL1,A.COL2,A.COL3;
|
|
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
GROUP BY works with WHERE. Sorry for the wrong Note.
Apologies,
Soundarr |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| GROUP BY works with WHERE. Sorry for the wrong Note. |
Not a problem 
Do you have a solution for your requirement or is something else needed? |
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
No solution yet...:-)
WHERE works with GROUP BY but not "WHERE COUNT(DISTINCT C)>1".
Regards,
Soundarr |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| WHERE works with GROUP BY but not "WHERE COUNT(DISTINCT C)>1". |
Yup.
Did you try the code ashimer posted? I believe it will be close, but does not take into consideration the col2 part of the requirement. |
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
Hi Ashimer,
Thanks much!
But even this works:
| Code: |
SELECT A.COL1,A.COL2,A.COL3 FROM TB132 A
,TB132 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3
; |
Correct me if this isnt as good as the first version.
Thanks Scherrer!
I also would like to know if there is any keyword(within WHERE clause) to find if a field contains a given value. For example, i want to find if any record in the table has value "1" for col1. I dont want to use EXISTS..:-)
Regards,
Soundarr |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
hi soundarr,
use LIKE '%1%' in where clause or else after retrieving the data use INPECT verb available in cobol ..
thanks,
ashimer |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
soundarr you are right .. even without exists it will give the desired o/p but make sure you include DISTINCT in your select ..
| Code: |
SELECT DISTINCT A.COL1,A.COL2,A.COL3 FROM TB132 A
,TB132 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3
;
|
|
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
Hi Ashimer,
Even without distinct, i am seeing the desired output. They are all unique. Pls correct if i am incorrect.
Regarding my second query, I wasnt clear enough. I know abt LIKE but i dint want that. And i donno how to explain that...:-)
Thanks a lot!
Soundarr |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Hi Soundarr,
try adding one more row with values say ..
Col1 Col2 Col3
-----------------
2 b iii
this time you wont get the desired o/p without DISTINCT ...
thanks,
ashimer |
|
| Back to top |
|
|
soundarr
New User
Joined: 17 Jan 2008
Posts: 25
Location: Chennai
|
|
|
|
Hi Ashimer,
Distinct matters! I actually had another predicate in the WHERE clause which made me get the desired result. Thanks a lot!
Regards,
Soundarr |
|
| Back to top |
|
|
|
|
