chokka
asked on
Pick first three characters of a column for Join query in Oracle
This is in Oracle.
I have to write an Inner Join query between first set of query and the second set of query.
Second set of query has a column name pri_psl_cd. To this column i need to see the prefix 3 letters matching criteria to the upper join query for the prefix 3 letters of cte.pri_psl_cd. I wonder is there any way to read first three characters of the column and matching with first 3 characters of the second query for that column.
I have to write an Inner Join query between first set of query and the second set of query.
Second set of query has a column name pri_psl_cd. To this column i need to see the prefix 3 letters matching criteria to the upper join query for the prefix 3 letters of cte.pri_psl_cd. I wonder is there any way to read first three characters of the column and matching with first 3 characters of the second query for that column.
with cte as
(
select A.ac_sys_nr,A.ac_nr,A.ac_na,
trunc(A.ac_opn_dt) open_date,
B.pri_psl_cd,B.customer_seg_cd,
B.psl_job_cgy_elg_cd
from A55601.tacprof A
Inner Join a55601.tacphas B
on A.ac_sys_nr = B.ac_sys_nr
Inner Join a55601.tactosr C
on A.ac_sys_nr = C.ac_sys_nr
where A.ac_ad_cny_cd = 'CA'
and A.dat_cx_tm_prd_nr = 0
and trunc(A.ac_opn_dt) >= '15-FEB-2015'
and B.customer_seg_cd <> 'EN'
and B.cny_cd = 'CA'
and B.dat_cx_tm_prd_nr = 0
and B.psl_job_cgy_elg_cd = '2'
and C.svc_pvr_pyr_nr in ('S13CAEXNP','S13CAEXNA')
)
select cte.ac_sys_nr,cte.ac_nr,cte.ac_na,cte.open_date,cte.pri_psl_cd,
cte.customer_seg_cd,cte.psl_job_cgy_elg_cd
from cte
/
select * from a55601.tpcplsr
where dat_cx_tm_prd_nr = 0
and cny_cd = 'CA'
-- and pri_psl_cd = Pick Prefix of first 3 Letters.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER