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
 

 

Need help on SELECT DISTINCT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Sep 23, 2010 1:06 pm    Post subject: Need help on SELECT DISTINCT
Reply with quote

Hi,

Please advice on the below requirement.

TABLE: A
Columns : A1,A2,A3
Data:

A1 A2 A3
1 1
1 1 A
1 1
2 2
2 2
2 2
3 3 B

Output should be:

A1 A2 Host variable
1 1 Y
2 2 N
3 3 Y

I need to select all the distinct rows for which A1,A2 together should be unique(DISTINCT A1,A2).

But at the same time, out of the same group A1,A2 if any of the row has a value in column A3 then we should set the host variable to 'Y' else it should b 'N'.

Can you please advice if we can implement this in single query.

Regards
Amar
Back to top
View user's profile Send private message

Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Sep 23, 2010 4:56 pm    Post subject:
Reply with quote

What have you tried yourself, and what challenges have you faced doing that?
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Sep 23, 2010 5:30 pm    Post subject:
Reply with quote

I was thinking to get the A1,A2 using first SELECT DISTINCT and then a seperate query to select A3 for greater than spaces using the input we have got in first execution.
But was thinking that it will be 2 hits to DB2 and so will be a performance issue. Was thinking to write it in single query. But I couldn't able to get one.

Regards
Amar
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Sep 24, 2010 2:11 am    Post subject:
Reply with quote

Hi Amar,

If you have DB2V9 installed at you place you can use below query to get desired result

Select DISTINCT T1.A1, T1.A2,
CASE WHEN T2.A3 IS NOT NULL THEN 'Y' ELSE 'N' END
FROM A T1
LEFT OUTER JOIN A T2
ON T1.A1 = T2.A1 AND T1.A2=T2.A2 AND T2.A3 > ' '


If you are using lower version of DB2 then you need to use below query

Select DISTINCT A1, A2,COALESCE(A3,'N') > ' '
From A

Then check value of A3 and if its not equal to 'N' set to 'Y' while writing

But here if you multiple rows with A3 > ' ' for given A1, A2 combination then you will end up in getting multiple rows for same A1,A2 combination
That time you need to handle it through your program logic.

Hope it helps.

Thanks,
Chandan
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Fri Sep 24, 2010 10:00 am    Post subject:
Reply with quote

Hi Chandan,

We have DB2V9 installed.Thanks a lot it worked.

Regards
Amar
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Sep 24, 2010 1:24 pm    Post subject:
Reply with quote

Code:
select A1, A2,
case when max(a3) > '' then 'Y' else 'N' end
from tab1
group by A1,A2
works as well
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Fri Sep 24, 2010 2:41 pm    Post subject: Reply to: Need help on SELECT DISTINCT
Reply with quote

Awsome solution. Thanks a lot GuyC
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Sep 24, 2010 7:27 pm    Post subject:
Reply with quote

Hi GuyC,

Nice one.. I am just wondering how the query will react if for a given combination of A1, A2 the column A3 is having all nulls.

I tried this one at that time it gave me the result as 'N' but I am not sure whether it will give same results always as MAX will return it as Null and how the compare will work every time??

Please guide in this

Regards,
Chandan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 27, 2010 3:24 pm    Post subject:
Reply with quote

null > '' will always return false
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Sep 28, 2010 12:32 am    Post subject: Reply to: Need help on SELECT DISTINCT
Reply with quote

Thanks GuyC
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 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 Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm
No new posts Can I use sort to select sample recor... Steve Ironmonger DFSORT/ICETOOL 3 Tue Nov 10, 2015 10:02 pm


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