|
View previous topic :: View next topic
|
| Author |
Message |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
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 |
|
 |
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| What have you tried yourself, and what challenges have you faced doing that? |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
Hi Chandan,
We have DB2V9 installed.Thanks a lot it worked.
Regards
Amar |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 216
Location: USA
|
|
|
|
| Awsome solution. Thanks a lot GuyC |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| null > '' will always return false |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Thanks GuyC |
|
| Back to top |
|
|
|
|
