Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Using SPUFI with DISTINCT and MAX is not working for me

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

New User


Joined: 27 Apr 2005
Posts: 15
Location: United States

PostPosted: Fri Nov 30, 2018 6:24 am    Post subject: Using SPUFI with DISTINCT and MAX is not working for me
Reply with quote

Could I please get advise on a SPUFI where I am trying to Select the record
with matching data in Col1 and Col2 and the most recent date in Col3.

I have tried using Distinct on Col1 & Col2 and MAX on Col3, but it is not working for me.
Could anyone please advise? Thank you so much in advance.
This is the DB2 table:

Code:

Col1           Col2            Col3               Col4
17             101437       01/06/2018            344
17             101437       08/19/2017            344
17             150013       08/19/2017            344
17             150013       08/05/2017            350
17            9954099       05/05/2018            646
17            9954099       03/03/2018            344
17            9954099       10/21/2017            350


The results I want are no duplicates in Col1 & Col2 and max date in Col3.

Code:

17             101437      01/06/2018            344
17             150013      08/19/2017            344
17            9954099      05/05/2018            646


I have tried SPUFIs like this:

Code:

SELECT DISTINCT
       COL1,
       COL2,
       MAX(COL3),
       COL4
  FROM DB2TEST.TABLE
 GROUP BY COL1, COL2;
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2123
Location: UK

PostPosted: Fri Nov 30, 2018 2:33 pm    Post subject: Reply to: Using SPUFI with DISTINCT and MAX is not working for me
Reply with quote

You say that you do not want duplicates in col 1 but your desired output shows all values in col 1 to be the same. f you meant no duplicates of conacatenated col1 and col2 then you should say so and you should concatenate them in your query.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Nov 30, 2018 9:05 pm    Post subject:
Reply with quote

You need all columns in Group by as that you have it in SELECT (without columns used in aggregate function). You need to think of a different approach. Hint could be to have a correlated query to find max date for the outer select to get that particular row.
Moreover , Distinct and Group by is ambiguous.
Back to top
View user's profile Send private message
Time2Live

New User


Joined: 27 Apr 2005
Posts: 15
Location: United States

PostPosted: Mon Dec 03, 2018 10:01 pm    Post subject:
Reply with quote

Hi all,
I did mean to say that the concat of col1 and col2 should be unique.
They are the 2 separate fields that are the table's key.
I tried what Rohit hinted, but dont know how to fix the error.

Code:

SELECT Col1,
       Col2,
       Col3,
       Col4 
  FROM DB2TEST.TABLE OUTR
  WHERE Col3  >
     (SELECT AVG(Col3)
        FROM DB2TEST.TABLE INNR
        WHERE INNR.col1 = OUTR.col1
          AND INNR.col2 = OUTR.col2);

DSNT408I SQLCODE = -171, ERROR:  THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF AVG IS INVALID
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Dec 04, 2018 9:30 pm    Post subject:
Reply with quote

Why AVG when you needed MAX in SELECT ? You need latest entry to be picked up or that's no more the situation.
Back to top
View user's profile Send private message
Time2Live

New User


Joined: 27 Apr 2005
Posts: 15
Location: United States

PostPosted: Tue Dec 04, 2018 11:25 pm    Post subject:
Reply with quote

Hi All,

Our DBA just got back from vacation and showed me how to do what I really wanted to do which is this correlated query.
Thank you all for your time! icon_biggrin.gif

Code:

SELECT A.COL1,
       A.COL2,
       C.COL3,
       C.COL4
  FROM DB2TEST.TABLE2 A, DB2TEST.TABLE1 C
  WHERE A.COL1 = C.COL1
    AND A.COL2 = C.COL2
    AND C.COL4 =
     (SELECT MAX(B.COL4)
        FROM DB2TEST.TABLE1 B
        WHERE A.COL1 = B.COL1
          AND A.COL2 = B.COL2);
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Dec 04, 2018 11:39 pm    Post subject:
Reply with quote

Okay. But why do you have to use DB2TEST.TABLE1 twice when one can do the work? Plus you are doing MAX on col4 when in initial post you said you want MAX of col3 and that won't assure you the same output every time you run than what you listed above as expected output.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2448
Location: @my desk

PostPosted: Wed Dec 05, 2018 8:45 pm    Post subject:
Reply with quote

Quote:
why do you have to use DB2TEST.TABLE1 twice when one can do the work
Rohit, do you mind posting a sample?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Wed Dec 05, 2018 9:57 pm    Post subject:
Reply with quote

Arun, DB2TEST.TABLE2 table is not required at all since all the information needed in the final output is present in DB2TEST.TABLE1. Why join DB2TEST.TABLE2 unnecessarily? If TS has a requirement to check if the row present in DB2TEST.TABLE2 or not then just EXISTS on DB2TEST.TABLE2 can be used later point.

This is possibly how it can be done using single use of DB2TEST.TABLE1

Code:
select final_t.col1, final_t.col2, final_t.col3, final_t.col4
(SELECT col1,col2,col3,col4,
  ROW_NUMBER() OVER
  (PARTITION BY col1,col2 ORDER BY col4 DESC) as col5
  FROM DB2TEST.TABLE1) final_T
where col5 = 1


If TS wants those rows which makes match with DB2TEST.TABLE2 then just twist like this and its done.
Code:
select final_t.col1, final_t.col2, final_t.col3, final_t.col4
(SELECT col1,col2,col3,col4,
  ROW_NUMBER() OVER
  (PARTITION BY col1,col2 ORDER BY col4 DESC) as col5
  FROM DB2TEST.TABLE1) final_T
where col5 = 1
and exists (select 1 from DB2TEST.TABLE2 b
                 where final_t.col1 = b.col1
                     and final_t.col2 = b.col2)
                 
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 DSLIST Command is not working thesumitk TSO/ISPF 14 Mon Dec 10, 2018 8:10 pm
No new posts IBM's FTP not working with TLS encryp... sathyajes TSO/ISPF 10 Tue Aug 21, 2018 1:37 pm
No new posts SDSF - SJ command not working properly mistah kurtz IBM Tools 5 Tue Jun 26, 2018 2:33 pm
No new posts REXX LISTDSI () SMSINFO is not working upendrasri CLIST & REXX 4 Fri Nov 17, 2017 11:33 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us