Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Remove duplicates from Query Results

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Tue Feb 28, 2012 4:33 pm    Post subject: Remove duplicates from Query Results
Reply with quote

Hi,

I executed below query and found results as below:

Code:
SELECT DISTINCT A1.MANAGER_CODE                             
                          ,A1.STATUS_CODE                                     
                          ,B1.STATUS_CODE       
                          ,B1.PRODUCT_CODE                                     
FROM  MANAGER_TAB  A1                                 
         ,PRODUCT_TAB  B1                                 
WHERE A1.MANAGER_ID = B1.MANAGER_ID
   AND A1.MANAGER_CODE IN ('28B00Q0P', '28B00Q06', '28B00Q1K')
ORDER BY A1.MANAGER_CODE                                     
WITH UR;         


Result of the above query is:

Code:
MANAGER_CODE   STATUS_CODE  STATUS_CODE  PRODUCT_CODE
---------+---------+---------+---------+-------------------------------------
28B00Q0P                  H                            A                 SPS       
28B00Q0P                  H                            H                 SPS       
28B00Q06                  H                            A                 SPS       
28B00Q06                  H                            H                 SPS       
28B00Q1K                  A                            A                 SPS       

But my expected result is:

Code:
MANAGER_CODE   STATUS_CODE  STATUS_CODE  PRODUCT_CODE
---------+---------+---------+---------+-------------------------------------
28B00Q0P                  H                            A                 SPS       
28B00Q06                  H                            A                 SPS       
28B00Q1K                  A                            A                 SPS       

I need one row for each distinct MANAGER_CODE. Suppose there are three rows for the same MANAGER_CODE, retriving any one row is fine.

Please any one help me on this query.
Back to top
View user's profile Send private message

sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Tue Feb 28, 2012 5:01 pm    Post subject: Reply to: Remove duplicates from Query Results
Reply with quote

I have also tried the below query and got one row more

Code:
SELECT
     ( SELECT A1.MANAGER_CODE
         FROM MANAGER_TAB A1
       WHERE A1.MANAGER_ID = B1.MANAGER_ID
       FETCH FIRST 1 ROW ONLY
     )
       ,A1.STATUS_CODE
       ,B1.STATUS_CODE
       ,B1.PRODUCT_CODE
FROM MANAGER_TAB A1
        ,PRODUCT_TAB B1
WHERE A1.MANAGER_ID = B1.MANAGER_ID
    AND A1.MANAGER_CODE IN ('28B00Q0P', '28B00Q06', '28B00Q1K')   
ORDER BY A1.MANAGER_CODE
WITH UR;

The output of above query is
Code:
MANAGER_CODE    STATUS_CODE    STATUS_CODE    PRODUCT_CODE
28B00Q0P                         H                       A                        SPS 
28B00Q0P                         H                       H                        SPS 
28B00Q06                         H                       A                        SPS 
28B00Q06                         H                       H                        SPS 
28B00Q1K                         A                       A                        SPS 
28B00Q1K                         A                       A                        SPS
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Tue Feb 28, 2012 5:05 pm    Post subject: Reply to: Remove duplicates from Query Results
Reply with quote

try adding this to your where clause in your first post query

A1.STATUS_CODE =B1.STATUS_CODE
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1651
Location: NY,USA

PostPosted: Tue Feb 28, 2012 5:32 pm    Post subject:
Reply with quote

try doing group by MANAGER_CODE STATUS_CODE STATUS_CODE PRODUCT_CODE in your query
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us