IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fetch unique rows in a query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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: 1281
Location: Belgium

PostPosted: Wed Apr 04, 2012 3:39 pm
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
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

Moderator Emeritus


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

PostPosted: Wed Apr 04, 2012 8:54 pm
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: 1281
Location: Belgium

PostPosted: Wed Apr 04, 2012 9:04 pm
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Apr 05, 2012 12:59 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Thu Apr 05, 2012 3:00 pm
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

Moderator Emeritus


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

PostPosted: Thu Apr 05, 2012 8:42 pm
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: 1281
Location: Belgium

PostPosted: Fri Apr 06, 2012 12:40 pm
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
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: 1281
Location: Belgium

PostPosted: Fri Apr 06, 2012 4:50 pm
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

Moderator Emeritus


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

PostPosted: Fri Apr 06, 2012 8:11 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts To fetch records that has Ttamp value... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
Search our Forums:

Back to Top