Error trying to create table from select while select work perfectly fine by itself

General

Error trying to create table from select while select work perfectly fine by itself

Hi, I have a simple SQL where I am trying to create a table using a select statement, I have no idea why this is bumping me out. The select by itself is working and returning rows successfully. Please help.

This is the SQL -

create table dbc.tb2 as
(select
Rcrd_No,
compltn_dt,
substr(compltn_dt,1,position(' ' in compltn_dt )) as dt,
trim(substr(compltn_dt,position(' ' in compltn_dt )+1,characters(compltn_dt)))as ts,
Trim(case when position ('/' in dt) = 2 then 0||substring(dt,1,1) else substr(dt,1,2) end) as mth,
trim(case when position ('/' in dt) = 2 and characters(dt) = 10 then substr(dt,3,2) else
case when position ('/' in dt) = 3 and characters(dt) =10 then 0||substr(dt,4,1) else
case when position ('/' in dt) = 2 and characters(dt) =9 then 0||substr(dt,3,1) else
case when position ('/' in dt) = 3 and characters(dt) =11 then substr(dt,4,2) else ' ' end end end end)as dy,
trim(case when characters (dt) = 10 and substr(dt,5,1) = '/' then substr(dt,6,4) else
case when characters (dt) = 9 and substr(dt,4,1) = '/' then substr(dt,5,4) else
case when characters(dt) = 11 and substr (dt,6,1) = '/' then substr(dt,7,4) else ' ' end end end )as yyyy,
trim(case when characters(ts) = 4 and position(':' in ts) = 2 then 0||substring(ts,1,1) else substring(ts,1,2) end) as hh,
trim(case when characters(ts) = 5 then substr(ts,4,2) else substr(ts,3,2) end)as mi,
cast((mth||dy||yyyy) as date format 'mmddyyyy') as comp_dt,
cast((mth||dy||yyyy||' '||hh||mi) as timestamp(0) format 'mmddyyyybhhmi') as comp_ts,
trim(reg_date) as rdt,
Trim(case when position ('/' in rdt) = 2 then 0||substring(rdt,1,1) else substr(rdt,1,2) end) as mthr,
trim(case when position ('/' in rdt) = 2 and characters(rdt) = 8 then 0||substr(rdt,3,1) else
case when position ('/' in rdt) = 3 and characters(rdt) =9 then 0||substr(rdt,4,1) else
case when position ('/' in rdt) = 2 and characters(rdt) =9 then substr(rdt,3,2) else
case when position ('/' in rdt) = 3 and characters(rdt) =10 then substr(rdt,4,2) else ' ' end end end end)as dyr,
trim(case when characters (rdt) = 10 and substr(rdt,6,1) = '/' then substr(rdt,7,4) else
case when characters (rdt) = 9 and substr(rdt,5,1) = '/' then substr(rdt,6,4) else
case when characters(rdt) = 8 and substr (rdt,4,1) = '/' then substr(rdt,5,4) else ' ' end end end )as yyyyr,
cast((mthr||dyr||yyyyr) as date format 'mmddyyyy') as Reg_dt,
trim(Actvty_date) as adt,
Trim(case when position ('/' in adt) = 2 then 0||substring(adt,1,1) else substr(adt,1,2) end) as mtha,
trim(case when position ('/' in adt) = 2 and characters(adt) = 8 then 0||substr(adt,3,1) else
case when position ('/' in adt) = 3 and characters(adt) =9 then 0||substr(adt,4,1) else
case when position ('/' in adt) = 2 and characters(adt) =9 then substr(adt,3,2) else
case when position ('/' in adt) = 3 and characters(adt) =10 then substr(adt,4,2) else ' ' end end end end)as dya,
trim(case when characters (adt) = 10 and substr(adt,6,1) = '/' then substr(adt,7,4) else
case when characters (adt) = 9 and substr(adt,5,1) = '/' then substr(adt,6,4) else
case when characters(adt) = 8 and substr (adt,4,1) = '/' then substr(adt,5,4) else ' ' end end end )as yyyya,
cast((mtha||dya||yyyya) as date format 'mmddyyyy') as Actvty_dt
from
dbc.tbname
)
with data primary index (Rcrd_No)

The error I am getting is Create Table Failed: 3706: Syntax error : Expected something between the word dt and ,

Any help is highly appreciated.
2 REPLIES
N/A

Re: Error trying to create table from select while select work perfectly fine by itself

See Database Forum.
N/A

Re: Error trying to create table from select while select work perfectly fine by itself

||substring(dt,1,1) else <----- Your Issue is here..... Use ---> substr
----------------------------

create table tb2 as(
select
Rcrd_No,
compltn_dt,
substr(compltn_dt,1,position(' ' in compltn_dt )) as dt,
trim(substr(compltn_dt,position(' ' in compltn_dt )+1,characters(compltn_dt)))as ts,
Trim(case when position ('/' in dt) = 2 then 0||substring(dt,1,1) else substr(dt,1,2) end) as mth,
trim(case when position ('/' in dt) = 2 and characters(dt) = 10 then substr(dt,3,2) else
case when position ('/' in dt) = 3 and characters(dt) =10 then 0||substr(dt,4,1) else
case when position ('/' in dt) = 2 and characters(dt) =9 then 0||substr(dt,3,1) else
case when position ('/' in dt) = 3 and characters(dt) =11 then substr(dt,4,2) else ' ' end end end end)as dy,
trim(case when characters (dt) = 10 and substr(dt,5,1) = '/' then substr(dt,6,4) else
case when characters (dt) = 9 and substr(dt,4,1) = '/' then substr(dt,5,4) else
case when characters(dt) = 11 and substr (dt,6,1) = '/' then substr(dt,7,4) else ' ' end end end )as yyyy,
trim(case when characters(ts) = 4 and position(':' in ts) = 2 then 0||substr(ts,1,1) else substr(ts,1,2) end) as hh,
trim(case when characters(ts) = 5 then substr(ts,4,2) else substr(ts,3,2) end)as mi,
cast((mth||dy||yyyy) as date format 'mmddyyyy') as comp_dt,
cast((mth||dy||yyyy||' '||hh||mi) as timestamp(0) format 'mmddyyyybhhmi') as comp_ts,
trim(reg_date) as rdt,
Trim(case when position ('/' in rdt) = 2 then 0||substr(rdt,1,1) else substr(rdt,1,2) end) as mthr,
trim(case when position ('/' in rdt) = 2 and characters(rdt) = 8 then 0||substr(rdt,3,1) else
case when position ('/' in rdt) = 3 and characters(rdt) =9 then 0||substr(rdt,4,1) else
case when position ('/' in rdt) = 2 and characters(rdt) =9 then substr(rdt,3,2) else
case when position ('/' in rdt) = 3 and characters(rdt) =10 then substr(rdt,4,2) else ' ' end end end end)as dyr,
trim(case when characters (rdt) = 10 and substr(rdt,6,1) = '/' then substr(rdt,7,4) else
case when characters (rdt) = 9 and substr(rdt,5,1) = '/' then substr(rdt,6,4) else
case when characters(rdt) = 8 and substr (rdt,4,1) = '/' then substr(rdt,5,4) else ' ' end end end )as yyyyr,
cast((mthr||dyr||yyyyr) as date format 'mmddyyyy') as Reg_dt,
trim(Actvty_date) as adt,
Trim(case when position ('/' in adt) = 2 then 0||substr(adt,1,1) else substr(adt,1,2) end) as mtha,
trim(case when position ('/' in adt) = 2 and characters(adt) = 8 then 0||substr(adt,3,1) else
case when position ('/' in adt) = 3 and characters(adt) =9 then 0||substr(adt,4,1) else
case when position ('/' in adt) = 2 and characters(adt) =9 then substr(adt,3,2) else
case when position ('/' in adt) = 3 and characters(adt) =10 then substr(adt,4,2) else ' ' end end end end)as dya,
trim(case when characters (adt) = 10 and substr(adt,6,1) = '/' then substr(adt,7,4) else
case when characters (adt) = 9 and substr(adt,5,1) = '/' then substr(adt,6,4) else
case when characters(adt) = 8 and substr (adt,4,1) = '/' then substr(adt,5,4) else ' ' end end end )as yyyya,
cast((mtha||dya||yyyya) as date format 'mmddyyyy') as Actvty_dt
from
tbname )
with data primary index (Rcrd_No)
-------------------

CREATE SET TABLE tb2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Rcrd_No SMALLINT,
compltn_dt VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
dt VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
ts VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
mth VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
dy VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
yyyy VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
hh VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
mi VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
comp_dt DATE FORMAT 'YY/MM/DD',
comp_ts TIMESTAMP(0),
rdt VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
mthr VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
dyr VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
yyyyr VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Reg_dt DATE FORMAT 'YY/MM/DD',
adt VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
mtha VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
dya VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
yyyya VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Actvty_dt DATE FORMAT 'YY/MM/DD')
PRIMARY INDEX ( Rcrd_No );
------------------------

CREATE MULTISET TABLE tbname
( Rcrd_No SMALLINT,
compltn_dt varchar(10),
reg_date varchar(10),
Actvty_date varchar(10)
)
----------------