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

Database

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.
1 REPLY
Enthusiast

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

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)

Line 7 of your query - you have a substring not substr! I dont believe it worked exactly like this as a query.
(You have other instances of substring with substr format parameters.)