Column datatype/size in the CTAS — oracle-tech

    Forum Stats

  • 3,714,815 Users
  • 2,242,633 Discussions
  • 7,845,076 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Column datatype/size in the CTAS

curious_mind
curious_mind Member Posts: 249 Bronze Badge
edited October 2020 in SQL & PL/SQL

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';

Answers

  • mathguy
    mathguy Member Posts: 9,463 Gold Crown

    You could do it in a single statement (using CAST in the SELECT part, not in the column list at the top of CREATE STATEMENT), but it's messy and error prone.

    In my opinion, it would be better to create table as (...) and allow the columns to be created in the data types and "sizes" as they exist currently in the base tables. Then DESCRIBE the newly created table, and see if and where you need to make changes. You should only "enlarge" the columns, not "shrink" them, so that should be relatively painless. Make sure you don't change a column to something like VARCHAR2(20) when the data from the other tables may have strings of length 24, etc. This won't happen if you follow the process I suggested. On the other hand, if you use CAST(... AS VARCHAR2(20)) in the CTAS statement, your longer strings will be silently truncated to 20 characters - you won't even know this happened till a furious user will come to you wanting to know how that happened.

Sign In or Register to comment.