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
 

 

Fetch unique rows in a query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Wed Apr 04, 2012 10:10 am    Post subject: Fetch unique rows in a query
Reply with quote

Hi,

I have SQL query which is given below,

Code:
SELECT A.WRITING_AGENT_NUM                         
            ,A.AGNT_CASE_END_DT                         
            ,A.DISTRICT_OFFICE_CD                       
            ,A.AGENCY_NUM                               
            ,A.INDEX_NUM                                 
            ,A.AGNT_CASE_STRT_DT                         
            ,A.WRIT_AGENT_SHR_PCT                       
            ,A.WRAG_SEQ_NUM                             
            ,' '
            ,' '                                       
FROM TABLE A                 
     WHERE A.CASE_NUM = '707003756'                 
        AND A.WRAG_SEQ_NUM =                       
           (SELECT MAX(C.WRAG_SEQ_NUM)             
              FROM TABLE C   
             WHERE C.CASE_NUM = A.CASE_NUM)         

The out put of the query is,
Code:
WRITING_AGENT  AGNT_CASE .. AGENCY_NUM INDEX_NUM ...
_NUM                   _END_DT
    1                     12/31/9999             88                    1
    1                     01/01/2012             88                    1
    2                     12/31/9999             90                    2
    2                     01/01/2012             90                    2

But i need only one row for each Writing agent num. Please advise on this.

Thanks in advance.
Back to top
View user's profile Send private message

Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Wed Apr 04, 2012 12:17 pm    Post subject: Reply to: Fetch unique rows in a query
Reply with quote

you may have to relook at column AGNT_CASE_END_DT, as it has different values in both rows, are you sure this date value doesnt matter ?
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Wed Apr 04, 2012 12:29 pm    Post subject:
Reply with quote

As a matter of fact, the values of AGNT_CASE_END_DT doesn't matter.

But i have figured a way of doing it. But, i will have to split the query into two different SQLs. It does my job.

Here is the solution i thought of,
Code:

SELECT DISTINCT A.CASE_NUM, A.WRAG_SEQ_NUM,A.WRITING_AGENT_NUM
FROM TABLE A                     
     WHERE A.CASE_NUM = '707003756'                   
     AND   A.WRAG_SEQ_NUM =                           
           (SELECT MAX(C.WRAG_SEQ_NUM)                 
            FROM TABLE C         
            WHERE C.CASE_NUM = A.CASE_NUM)             

Then with the above results i will write another query which will fetch other columns and this query will have 'FETCH FIRST ROW ONLY'.

Let me know if there is any other solution for this.
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Wed Apr 04, 2012 1:53 pm    Post subject:
Reply with quote

you can try taking out AGNT_CASE_END_DT column in your first query, you should be able to achieve your result. Test and try.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Apr 04, 2012 3:39 pm    Post subject:
Reply with quote

This untested (i don't have the tables) should work for DB2V9 up

Code:
SELECT b2.WRITING_AGENT_NUM                         
      ,b2.AGNT_CASE_END_DT                         
      ,b2.DISTRICT_OFFICE_CD                       
      ,b2.AGENCY_NUM                               
      ,b2.INDEX_NUM                                 
      ,b2.AGNT_CASE_STRT_DT                         
      ,b2.WRIT_AGENT_SHR_PCT                       
      ,b2.WRAG_SEQ_NUM                             
      ,' '
      ,' '                                       
FROM (SELECT CASE_NUM
           , WRITING_AGENT_NUM
           , max(WRAG_SEQ_NUM) as wrag_seq_num
        FROM TABLEA                     
       WHERE CASE_NUM = '707003756'                   
      group by CASE_NUM
             , WRITING_AGENT_NUM ) A
   , table (select * from tableA B where
       where a.CASE_NUM = b.CASE_NUM
         and a.wrag_seq_num      = b.wrag_seq_num
         and a.writing_agent_num = b.writing_agent_num
         fetch first 1 row only) B2
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Wed Apr 04, 2012 4:06 pm    Post subject:
Reply with quote

Code:

SELECT b2.WRITING_AGENT_NUM                         
      ,b2.AGNT_CASE_END_DT                         
      ,b2.DISTRICT_OFFICE_CD                       
      ,b2.AGENCY_NUM                               
      ,b2.INDEX_NUM                                 
      ,b2.AGNT_CASE_STRT_DT                         
      ,b2.WRIT_AGENT_SHR_PCT                       
      ,b2.WRAG_SEQ_NUM                             
      ,' '
      ,' '
FROM TABLE (SELECT CASE_NUM                               
                               , WRITING_AGENT_NUM                           
                               , MAX(WRAG_SEQ_NUM)
                          AS WRAG_SEQ_NUM           
                     FROM TUWCMAG_COMM_AGENT                 
                   WHERE CASE_NUM = '707003756'                       
                   GROUP BY CASE_NUM                                   
                                  , WRITING_AGENT_NUM ) A                     
       , TABLE (SELECT * from TUWCMAG_COMM_AGENT A, 
                                           TUWCMAG_COMM_AGENT B   
                     WHERE A.CASE_NUM = A.CASE_NUM                     
                         AND A.WRAG_SEQ_NUM      = B.WRAG_SEQ_NUM         
                         AND A.WRITING_AGENT_NUM = B.WRITING_AGENT_NUM   
                        FETCH FIRST 1 ROW ONLY) B2                     


When i ran this query, i am getting the below errro.
SQL error at or before FETCH (line 23, position 10)

Please advise.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Apr 04, 2012 8:54 pm    Post subject:
Reply with quote

Hello,

Which release of DB2 are you using?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Apr 04, 2012 9:04 pm    Post subject:
Reply with quote

during adaptation of the query you made a few mistakes.

Quote:
...
TABLE (SELECT * from -- TUWCMAG_COMM_AGENT A, This should not be here
TUWCMAG_COMM_AGENT B
WHERE A.CASE_NUM = B.CASE_NUM -- This should compare A to B, not A to A
AND A.WRAG_SEQ_NUM = B.WRAG_SEQ_NUM
AND A.WRITING_AGENT_NUM = B.WRITING_AGENT_NUM
FETCH FIRST 1 ROW ONLY) B2

and as already mentioned and asked : You DB2 should be in Version 9 New Function Mode or higher.
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Thu Apr 05, 2012 10:05 am    Post subject:
Reply with quote

Thanks for the responses.

I am currently using DB2 V09.02.00.

But still after the above changes, i am getting the same db2 error. icon_sad.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Thu Apr 05, 2012 12:59 pm    Post subject: Reply to: Fetch unique rows in a query
Reply with quote

Show what you have used and any output/errors you are getting.

I have no idea which is Psychic Day this week, so it's not today :-)
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Thu Apr 05, 2012 1:48 pm    Post subject:
Reply with quote

Code:


SELECT b2.WRITING_AGENT_NUM                         
      ,b2.AGNT_CASE_END_DT                         
      ,b2.DISTRICT_OFFICE_CD                       
      ,b2.AGENCY_NUM                               
      ,b2.INDEX_NUM                                 
      ,b2.AGNT_CASE_STRT_DT                         
      ,b2.WRIT_AGENT_SHR_PCT                       
      ,b2.WRAG_SEQ_NUM                             
      ,' '
      ,' '
FROM TABLE (SELECT CASE_NUM                               
                               , WRITING_AGENT_NUM                           
                               , MAX(WRAG_SEQ_NUM)
                          AS WRAG_SEQ_NUM           
                     FROM TUWCMAG_COMM_AGENT                 
                   WHERE CASE_NUM = '707003756'                       
                   GROUP BY CASE_NUM                                   
                                  , WRITING_AGENT_NUM ) A                     
       , TABLE (SELECT * from TUWCMAG_COMM_AGENT B   
                     WHERE A.CASE_NUM = B.CASE_NUM                     
                         AND A.WRAG_SEQ_NUM      = B.WRAG_SEQ_NUM         
                         AND A.WRITING_AGENT_NUM = B.WRITING_AGENT_NUM   
                        FETCH FIRST 1 ROW ONLY) B2   


output error:
Quote:


QUERY MESSAGES:
SQL error at or before FETCH
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Apr 05, 2012 3:00 pm    Post subject: Reply to: Fetch unique rows in a query
Reply with quote

Quote:
I have no idea which is Psychic Day this week, so it's not today :-)


Usually Psychic day is on wednesday,
sometimes if somebody is on holiday on wednesday, it gets shifted to friday icon_biggrin.gif
( the psychic day, naturally, bot the somebody )
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Apr 05, 2012 8:42 pm    Post subject:
Reply with quote

Hello,

Did you ensure that you are running in New Function Mode?

If you are not sure, ask your dba.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Apr 06, 2012 12:40 pm    Post subject:
Reply with quote

the following is correct sql (=tested) and
is about the same as the above.
Code:
select B2.dbname,b2.tsname,b2.createdts,b2.name from
(select dbname
    , tsname
    , max(CREATEDTS) as createdts
    from sysibm.systables
where creator = 'SYSIBM'
group by dbname,tsname) A
, table (
select * from sysibm.systables B
where B.dbname = a.dbname
  and b.tsname = a.tsname
  and b.createdts = a.createdts
  fetch first 1 row only) B2


Which tool are you using to run this,
I can't recall a tool returning "SQL error at or before FETCH" without an SQLCODE/STATE
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Fri Apr 06, 2012 12:46 pm    Post subject:
Reply with quote

Is keyword 'TABLE' not required before this code?

Code:

(select dbname
    , tsname
    , max(CREATEDTS) as createdts
    from sysibm.systables
where creator = 'SYSIBM'
group by dbname,tsname) A


How will the variables used in the second select get valus from table A?

I am using QMF.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Apr 06, 2012 4:50 pm    Post subject:
Reply with quote

keyword table is only required for correlated subqueries in a FROM-clause (ie. a query that can not be run on its own, but uses columns from other tables/subqueries).
A is a non-correlated subquery
B2 is a correlated subquery
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Apr 06, 2012 8:11 pm    Post subject:
Reply with quote

Hello,

Did you run the code provided by GuyC? If not, why not?

If you actually look at the provided code, it HAS "table" where you expect . . .

Unless i've gotten lost. . .
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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