| Author |
Topic |
|
SP273
Starting Member
14 Posts |
 Posted - 2011-04-20 : 19:24:57
|
| select f, Parent , d , s , M, max(c), P FROM ( select distinct hph.LoadID as f, --hph.parent Case when (substring (hph.Parent,1,2)IN ('D0','D1','D2','D3','D4','D5','D6','D7','D8','D9')) Then 'Direct Projects' when (substring (hph.Parent,1,6)IN ('DRESVE')) Then 'Direct Projects' When (substring (hph.Parent,1,2)IN ('F0','F1','F2','F3','F4','F5','F6','F7','F8','F9')) Then 'Fringe Projects' When (substring (hph.Parent,1,2)IN ('M0','M1','M2','M3','M4','M5','M6','M7','M8','M9')) Then 'Marketing Projects' When (substring (hph.Parent,1,2)IN ('H0','H1','H2','H3','H4','H5','H6','H7','H8','H9')) Then 'Overhead Projects' When (substring (scp.Parent,1,6)IN ('F09500')) Then 'Overhead Projects' When (substring (hph.Parent,1,2)IN ('P0','P1','P2','P3','P4','P5','P6','P7','P8','P9')) Then 'Proposal Projects' ELSE hph.Parent END AS Parent, LEFT(scp.child,6)as d, hph.DataStorage as S, hph.MemberValidForPlan1 as M, hph.Plan1Aggregation as P, hph.[Alias=Default]as c from HS_Project_Hierarchy hph left join oracle_Convert_Projects scp on Left(scp.child,6) = hph.child where scp.DataStorage NOT IN ('ShareData') UNION ALL select distinct scp.LoadID as f, --scp.parent, Case when (substring (scp.Parent,1,2)IN ('D0','D1','D2','D3','D4','D5','D6','D7','D8','D9')) Then 'Direct Projects' when (substring (scp.Parent,1,6)IN ('DRESVE')) Then 'Direct Projects' When (substring (scp.Parent,1,2)IN ('F0','F1','F2','F3','F4','F5','F6','F7','F8','F9')) Then 'Fringe Projects' When (substring (scp.Parent,1,2)IN ('M0','M1','M2','M3','M4','M5','M6','M7','M8','M9')) Then 'Marketing Projects' When (substring (scp.Parent,1,2)IN ('H0','H1','H2','H3','H4','H5','H6','H7','H8','H9')) Then 'Overhead Projects' When (substring (scp.Parent,1,2)IN ('H0','H1','H2','H3','H4','H5','H6','H7','H8','H9')) Then 'Overhead Projects' When (substring (scp.Parent,1,6)IN ('F09500')) Then 'Overhead Projects' When (substring (hph.Parent,1,2)IN ('P0','P1','P2','P3','P4','P5','P6','P7','P8','P9')) Then 'Proposal Projects' ELSE scp.Parent END AS Parent, LEFT(scp.child,6)as d, scp.DataStorage as S, scp.MemberValidForPlan1 as M, scp.Plan1Aggregation as P, SUBSTRING(hph.[Alias=Default],1,case len(hph.[Alias=Default]) when 0 then 99 else len(hph.[Alias=Default])-18 END) AS c from oracle_Convert_Projects scp left join HS_Project_Hierarchy hph on scp.child = hph.child where scp.DataStorage NOT IN ('ShareData') AND LEFT (scp.child,6) <> 'F09500')t group by f, Parent , d , s , M, P I have the above query & i m trying to remove the duplicate rows by using max in my outer query from the rows that get from my 2 inner queries. But when i run this query it still gives me duplicate rows. Can anybody know where i might be going wrong with the max statement or a way to modify this query so that i can have distinct rows.Thanks! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-20 : 21:06:28
|
| There should be/will be only one row for each unique combination of the values of the six columns: f, Parent, d, s, M, and P. Are you seeing results that contradict this? |
 |
|
|
SP273
Starting Member
14 Posts |
Posted - 2011-04-20 : 21:15:25
|
| thanks for your reply:below is an example of duplicate rows i see in my outputf Parent d s M c p 1 Direct Projects D4000 StoreData NULL hitech4000 +1 Direct Projects D4000 StoreData 1 hitech4000 (hitech base) +according to my understanding it should pick the second row as per my max on c in the query but it gives me 2 rows for same project.Thanks |
|
|
|
SP273
Starting Member
14 Posts |
Posted - 2011-04-20 : 21:19:47
|
| Please discard my earlier post actually the problem was because of column M. I corrected it.Thanks for your help |
|
|
|
|
|
|
Query Help