I am trying to join
tables based on the primary key
of address no, 1. However, the issue below creates duplicate record for the same address no. 003 78057911 (wpphtp !=F) and 003 78057922 (wpphtp = F) are the same fields in the database
, however I want to extract it on a different column. How can I do so?
select ABAN8,'CREATE',ABALPH, '','', '','','','','','', EAEMAL,'',
CASE
WHEN wpphtp != 'F'
THEN WPAR1
else ' '
END AS prefix ,'', CASE
WHEN wpphtp != 'F'
THEN WPph1
else ' '
END AS phone, '',CASE
WHEN wpphtp = 'F'
THEN wpar1
else ' '
END AS prefixfax,' ',CASE
WHEN wpphtp = 'F'
THEN wpph1
else ' '
END AS fax from PRODDTA.F0111
join PRODDTA.F01151 on proddta.f0111.wwan8 = PRODDTA.F01151.EAAN8
join PRODDTA.F0115 JOIN PRODDTA.F0101 ON PRODDTA.F0115.wpAN8 = PRODDTA.F0101.ABAN8
ON PRODDTA.F0115.wpAN8 = PRODDTA.F0111.wwAN8 where wwidln < 1
order by wwan8
Result:
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | [email protected] | 003 | 78057911
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | [email protected] | 003 | 78057911
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | [email protected] | 003 | 78057922
1 | CREATE | E-GLOBAL INNOVATIVE SDN BHD | [email protected] | 003 | 78057922
Expected Result:
1 CREATE | E-GLOBAL INNOVATIVE SDN BHD | [email protected] | 003 | 78057911 | 003 | 78057922
1 CREATE | E-GLOBAL INNOVATIVE SDN BHD | [email protected] | 003 | 78057911 | 003 | 78057922
You can use conditional aggregation to get the values for different
wpphtp
in different columns. Without seeing table structures and sample data it's hard to be 100% certain but this should work:In the
GROUP BY
clause, you need to add all the other column names (i.e. everything exceptprefix
,phone
,prefixfax
andfax
).