Hi All,
I am creating one CTAS where i am putting column datatype/size while creating it and it is failing with the below error:
SQL Error: ORA-01773: may not specify column datatypes in this CREATE TABLE
01773. 00000 - "may not specify column datatypes in this CREATE TABLE"
Below is the CTAS, please suggest.
CREATE TABLE CL_GRP_DTL_TMP4(EODS_GRP_ID NUMBER(38),
EODS_CL_ID NUMBER(38,0),PRTT_CDE CHAR(5 BYTE),FACE_PRFL_EODS_DSTR_ID VARCHAR2(50 BYTE),
GRP_ALT_ID VARCHAR2(30 BYTE),GRP_ALT_ID_CNTX_CD VARCHAR2(50 BYTE),CL_ALT_ID VARCHAR2(30 BYTE),CL_ALT_ID_CNTX_CD VARCHAR2(50 BYTE),
FACE_PRFL_DSTR_ALT_ID VARCHAR2(30 BYTE),FACE_PRFL_DSTR_ALT_ID_CNTX_CD VARCHAR2(50 BYTE),CL_LAST_NME VARCHAR2(100 BYTE),
CL_FIRST_NME VARCHAR2(30 BYTE),CL_MID_NME VARCHAR2(30 BYTE),CL_BTH_DTE DATE,CL_DECSD_CDE VARCHAR2(20 BYTE),CL_EMPL_STAT_CDE VARCHAR2(20 BYTE),
CL_MRTL_STAT_CDE VARCHAR2(20 BYTE),PRM_CL_GRP_IND CHAR(1 BYTE),GRP_FP_ACCT_IND CHAR(1 BYTE),GRP_ACTV_ACCT_IND CHAR(1 BYTE),
GRP_FP_IND VARCHAR2(1 BYTE),GRP_PRLM_GOAL_IND VARCHAR2(1 BYTE),CL_HSLD_INCM_AMT NUMBER(12,0),CRS_AUM_AMT NUMBER(22,5),
CL_TOT_VIEW_AST_AMT NUMBER,CL_RGST_FOR_SECR_SITE_IND VARCHAR2(1 BYTE),CL_RGST_FOR_TOT_VIEW_IND VARCHAR2(1 BYTE),CL_NXT_MEET_DTE DATE,
CRTE_PGM CHAR(5 BYTE),CRTE_TSTP DATE,UPDT_PGM CHAR(5 BYTE),UPDT_TSTP DATE,
CONSTRAINT CL_GRP_DTL_TMP4 PRIMARY KEY (FACE_PRFL_EODS_DSTR_ID,EODS_GRP_ID, EODS_CL_ID,PRTT_CDE)
USING INDEX LOCAL ) PARTITION BY HASH (FACE_PRFL_EODS_DSTR_ID) PARTITIONS 8
AS
With
GRP_ELEG as
(Select ag.eods_grp_id, sum(case when cif.prod_ctg_cde = '013' then 1 ELSE 0 end) CNT_FP,
sum(case when cif.prod_ctg_cde <> '013' then 1 ELSE 0 end) CNT_OTH
FROM acct_grp ag, cif_acct_prx cif
WHERE ag.eods_acct_id = cif.eods_acct_id
AND cif.ACCT_STAT_CDE in ('01','02')
AND ag.ACCT_GRP_END_DTE is null
AND AG.PRTT_CDE = 'CGA_1'
AND cif.prtt_cde = 'CGA_0'
group by ag.eods_grp_id),
GRP_LIST as
(Select cl.cl_alt_id,cl.cl_alt_id_cntx_cde,gp.eods_grp_id, cg.eods_cl_id, p.CL_LAST_NME, p.CL_FIRST_NME, p.CL_MID_NME, p.BTH_DTE CL_BTH_DTE, p.DECSD_CDE CL_DECSD_CDE,
CLP.EMPL_STAT_CDE CL_EMPL_STAT_CDE, P.MRTL_STAT_CDE CL_MRTL_STAT_CDE,FP.face_prfl_id,
clp.ANNL_INCM_AMT CL_HSLD_INCM_AMT, aum.AUM_VAL_AMT CRS_AUM_AMT,dstr.dstr_alt_id,dstr.dstr_alt_id_cntx_cde
FROM Grp gp
, crs_aum aum
, cl_grp cg
, pers p
, cl_prfl clp
, face_prfl fp
, grp_face_prfl gfp
, cl_alt_id cl
, dstr_alt_id dstr
WHERE gp.GRP_CTG_CDE = '001'
AND cg.EODS_GRP_ID = gp.EODS_GRP_ID
AND cg.CL_GRP_ROLE_END_DTE is null
AND cg.CL_ROLE_IN_GRP_TYP_CDE = '01'
AND aum.EODS_GRP_ID(+) = gp.EODS_GRP_ID
AND p.eods_cl_id = cg.eods_cl_id
AND cg.eods_cl_id = cl.eods_cl_id
AND clp.eods_cl_id(+) = cg.eods_cl_id
AND fp.face_prfl_id=gfp.face_prfl_id
AND cg.eods_grp_id = gfp.eods_grp_id
AND fp.eods_dstr_id= dstr.eods_dstr_id
AND GP.PRTT_CDE = 'CGA_0'
AND AUM.PRTT_CDE(+)= 'CRS_0'
AND CG.PRTT_CDE = 'CGA_1'
AND P.PRTT_CDE = 'CGA_0'
AND clp.prtt_cde(+)= 'CGA_0'
AND fp.prtt_cde = 'DMU_0'
AND gfp.prtt_cde = 'DMU_1'),
TV_BAL as
(Select eods_cl_id, sum(sum.CURR_BAL_AMT) CL_TOT_VIEW_AST_AMT
FROM cash_edge_acct_prfl AP
, cash_edge_acct_sum SUM
WHERE AP.acct_id = SUM.acct_id
AND AP.PRTT_CDE = 'CSEG_1'
and sum.prtt_cde = 'CSEG_1'
and (
(ACCT_TYP_cde = 'DDA' AND EXTN_ACCT_TYP_cde IN ('DDA', 'CMA'))
OR
(ACCT_TYP_cde = 'INS' AND EXTN_ACCT_TYP_cde IN ('ALI', 'WLI', 'ULI', 'TLI') )
OR
(ACCT_TYP_cde = 'OAA' AND EXTN_ACCT_TYP_cde IN ('CDA') )
OR
(ACCT_TYP_cde = 'SDA' AND EXTN_ACCT_TYP_cde IN ('SDA', 'MMA')))
group by eods_cl_id),
PRM as
(Select b.eods_cl_id, c.eods_grp_id, c.grp_alt_id,c.grp_alt_id_cntx_cde, '001' || lpad( min(to_number(substr(c.grp_alt_id,4))) over(partition by b.eods_cl_id),10,'0') PRM_GRP
FROM cl_grp a, pers b, grp_alt_id c
where a.eods_cl_id = b.eods_cl_id
and a.eods_grp_id = c.eods_grp_id
and a.prtt_cde = 'CGA_1'
and b.prtt_cde = 'CGA_0')
select
b.EODS_GRP_ID, b.EODS_CL_ID,'CGA_0' PRTT_CDE,b.FACE_PRFL_ID FACE_PRFL_EODS_DSTR_ID,i.grp_alt_id,i.grp_alt_id_cntx_cde ,
b.cl_alt_id,b.cl_alt_id_cntx_cde ,b.dstr_alt_id face_prfl_dstr_alt_id,b.dstr_alt_id_cntx_cde ,b.CL_LAST_NME, b.CL_FIRST_NME,
b.CL_MID_NME, b.cl_bth_dte, b.cl_decsd_cde, b.cl_empl_stat_cde, b.cl_mrtl_stat_cde,
(case when i.GRP_ALT_ID=i.PRM_GRP then 'Y' else 'N' end) PRM_CL_GRP_IND,
(case when a.CNT_FP>0 then 'Y' else 'N' end) as GRP_FP_ACCT_IND,
(case when a.CNT_OTH>0 then 'Y' else 'N' end) as GRP_ACTV_ACCT_IND,
decode(g.eods_grp_id,NULL, 'N', 'Y') GRP_FP_IND,
decode(h.eods_grp_id,NULL, 'N', 'Y') GRP_PRLM_GOAL_IND,
b.CL_HSLD_INCM_AMT , b.CRS_AUM_AMT, c.CL_TOT_VIEW_AST_AMT,
decode(e.ENRL_STAT_CDE,'ACTIVE','Y','N') CL_RGST_FOR_SECR_SITE_IND,
decode(d.ENRL_STAT_CDE,'ACTIVE','Y','N') CL_RGST_FOR_TOT_VIEW_IND,
CL_NXT_MEET_DTE CL_NXT_MEET_DTE, 'BATCH' CRTE_PGM, sysdate CRTE_TSTP, 'Batch' Updt_pgm, sysdate updt_tstp
FROM GRP_ELEG a, GRP_LIST b , TV_BAL c, tot_view_rgst d, secr_site_rgst e, cl_meet f,
(SELECT DISTINCT EODS_GRP_ID FROM FP_GRP) G,
(select distinct eods_grp_id from fp_prlm_goal where upper(PRLM_GOAL_STAT_CDE) in ('CAPTURED','SENTTOFPTOOL')) h,prm i
WHERE a.eods_grp_id(+) = b.eods_grp_id
AND c.eods_cl_id(+) = b.eods_cl_id
AND d.EODS_CL_ID (+) = b.eods_cl_id
AND e.EODS_CL_ID (+) = b.eods_cl_id
AND f.EODS_CL_ID (+) = b.eods_cl_id
AND b.eods_grp_id=g.eods_grp_id(+)
AND b.eods_grp_id=h.eods_grp_id(+)
AND i.eods_cl_id = b.eods_cl_id
AND i.eods_grp_id = b.eods_grp_id
AND d.prtt_cde(+) = 'EEPM_0'
AND E.PRTT_CDE(+) = 'EEPM_0'
AND F.PRTT_CDE(+) = 'CRM_0';