11-26-2012 04:49 PM
In the query below I want to attach the country code to the cell number based on MKT_ID_Cd.
But the output doesnt do that.i just gives out the phone number,
Say the iso_ph_cd is 52 (country mexico) for mkt _id_cd 002 then the cell_nbr must be 527714561241 , but what i see is only 7714561241
create table cell_phone as
a.GALC_ACCT_NO as acct_no
,CATX(d.iso_ph_cd, compress(a.GALC_ALRT_PHONE_NO,,'KD') as cell_nbr
from outdata.DMGADR_171212 a
,max(GALC_LST_UPDT_TS) as last_updt_dt
from outdata.DMGADR_171212 b
and b.GALC_ALRT_PHONE_NO ne ''
and input(compress(b.GALC_ALRT_PHONE_NO,,'KD'),10.) > 0
group by b.GALC_ACCT_NO
) b on a.GALC_ACCT_NO=b.GALC_ACCT_NO and a.GALC_LST_UPDT_TS=b.last_updt_dt
left join outdata1.isocodes d on a.mkt_id_cd=d.mkt_id_cd
and a.GALC_ALRT_PHONE_NO is not null
and input(compress(a.GALC_ALRT_PHONE_NO,,'KD'),10.) > 0
11-26-2012 05:06 PM
I think you're looking for CATS rather than CATX.
In CATX, the first item in the list is a delimiter to use between the remaining set of items. But your remaining set of items has only one item listed, and so doesn't need a delimiter.