CODEWITHSUNDEEP
oracle-databasesql-insert
yingyang
yingyang

Reputation: 47

Error inserting rows in oracle

I'm having a trouble on inserting in oracle. I don't know what's wrong with the query. Please help me to fix that error. Thank you..

Query:

insert into import_temp(reference,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13)
select a.reference,a.field_name
  ,max(a.user_name) as username, max(a.timestamp) as timestamp
from (
select distinct 'VAL-'||t.reference as reference
      ,case -- tracker information validation
            when (row_number() over (order by id))=1 and trim(replace(col1,chr(13),''))='Tracker.Antifraud.Input' then 'tracker'
            -- header validation
            when (row_number() over (order by id))<3 then 'header1'
            when (row_number() over (order by id))=3 and trim(col1)<>'Column1' then 'Unknown column '||t.col1
            when (row_number() over (order by id))=3 and trim(col2)<>'Column2' then 'Unknown column '||t.col2
            when (row_number() over (order by id))=3 and trim(col3)<>'Column3' then 'Unknown column '||t.col3
            when (row_number() over (order by id))=3 and trim(col4)<>'Column4' then 'Unknown column '||t.col4
            when (row_number() over (order by id))=3 and trim(col5)<>'Column5' then 'Unknown column '||t.col5
            when (row_number() over (order by id))=3 and trim(col6)<>'Column6' then 'Unknown column '||t.col6
            when (row_number() over (order by id))=3 and trim(col7)<>'Column7' then 'Unknown column '||t.col7
            when (row_number() over (order by id))=3 and trim(col8)<>'Column8' then 'Unknown column '||t.col8
            when (row_number() over (order by id))=3 and trim(col9)<>'Column9' then 'Unknown column '||t.col9
            when (row_number() over (order by id))=3 and trim(col10)<>'Column10' then 'Unknown column '||t.col10
            when (row_number() over (order by id))=3 and trim(col11)<>'Column11' then 'Unknown column '||t.col11
            when (row_number() over (order by id))=3 and trim(col12)<>'Column12' then 'Unknown column '||t.col12
            when (row_number() over (order by id))=3 and trim(col13)<>'Column13' then 'Unknown column '||t.col13
            when (row_number() over (order by id))=3 then 'header'
            -- record validation
            when (row_number() over (order by id))>3 then 'record'
            else 'unknown row '||(row_number() over (order by id))
       end as field_name
      ,case when (row_number() over (order by id))=1 then trim(t.col5) end as user_name
      ,case when (row_number() over (order by id))=1 and isdate(trim(t.col10),'yyMMddhh24miss')='Y' then trim(t.col10) end as timestamp
from params p
inner join import_temp t on p.GUID=t.reference
where not t.col1 is null 
) a
group by a.reference,a.field_name;

ERROR:

   SQL Error: ORA-00947: not enough values
   00947. 00000 -  "not enough values"

Upvotes: 0

Views: 84

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

The immediate error is that you list 14 columns in your INSERT clause but your SELECT only returns 4 columns. 

insert into import_temp(reference,col1,col2,col3,col4,col5,col6,col7,
                        col8,col9,col10,col11,col12,col13)
-- 14 columns above
--
-- 4 columns below
select a.reference,a.field_name
  ,max(a.user_name) as username, max(a.timestamp) as timestamp
from (

Based on the colum names, it's not obvious to me that your SELECT is just returning the first 4 columns specified in your INSERT.

Upvotes: 1

Related Questions