SQLRPGLE. Is there something wrong with my SQL Query?

Question

Here is the SQL Query that I'm trying to run in my program. However I can't even compile it, I'm getting an error that's just telling me something is wrong in the query.

The error I get is "SQL0117 Statement contains wrong number of values".

/Free                                                        
   exec sql                                                  
   INSERT INTO NOEDTSR                                       
        SELECT * FROM NOEDEH AS deh                          
        WHERE EXISTS (SELECT act.AC2ACT FROM ACTEST AS act   
                              WHERE  act.AC2ACT = deh.N1ACTE 
                              AND    act.AC2CRB IN('C0','C2')
                              AND    act.AC2TYT = 'DEN')     
        AND   NOT EXISTS (SELECT sin.SISTE FROM SINREG AS sin
                              WHERE  sin.SISTE  = deh.N1STE  
                              AND    sin.SIGRP  = deh.N1GRP
                              AND    sin.SIIND  = deh.N1IND   
                              AND    sin.SIRANG = deh.N1RANG  
                              AND    sin.SIACTE = deh.N1ACTE  
                              AND    sin.SIREEL = deh.N1MERG  
                              AND    sin.SISECU = deh.N1EBAS) 
         AND N1DBSS = 20                                       
         AND N1DBSA = 21                                       
         AND N1TIER = '000000000000000';                       
 /end-free                                                       

I'm not specifying column_names here on purpose as I want to copy all of the columns from NOEDEH to NOEDTSR.
These two tables have the same structure as the DDS(Data Description Structure) used to compile both is the exactly same.

Any thoughts?

Answer 1

You've already figured out your issue. Mark provide a simple SQL to easily generate a string that contains a list of columns.

Here's a SQL statement I keep handy. It will allow you to generate one of the following:

• string of the short column names
• string of the long columns names
• rpg data structure using the short names
• rpg data structure using the long names

Just uncomment the appropriate select * line at the bottom of the statement.

with selected (system_table_name, system_table_schema) 
     --  enter file & library name here
  as (values ('MYTABLE','MYLIB') )
, tbl as (
select 
        case
           when data_type = 'DECIMAL' then 'packed'
           when data_type = 'NUMERIC' then 'zoned'
           when data_type = 'TIMESTMP' then 'timestamp'
           when data_type = 'INTEGER' then 'int'
           else lower(data_type)  
         end
        concat case
                 when data_type = 'INTEGER' and length >= 8 then '(20'
                 when data_type = 'INTEGER' and length >= 4 then '(10'
                 when data_type = 'INTEGER' and length >= 2 then '(5'
                 when data_type in ('TIMESTMP','DATE','TIME') then '' 
                 else '(' concat length
               end
        concat case 
                 when numeric_scale is null then ''
                 when data_type = 'INTEGER' then ''
                 else ':' concat numeric_scale
               end
        concat case
                 when data_type in ('TIMESTMP','DATE','TIME') then ';'   
                 else ');' 
               end 
        as rpg_type 
,system_column_name, length, numeric_scale,
column_text, column_name, ordinal_position
from qsys2.syscolumns syscolumns
 join selected using(system_table_name,system_table_schema)
)
-- build RPG DS
, rpg_ds as (
 select lower(system_column_name) concat ' ' concat rpg_type as rpg_ds_subfield
   from tbl
 order by ordinal_position
)
-- build rpg long name ds
, rpg_ds_long_name as (
 select lower(column_name) concat ' ' concat rpg_type as rpg_ds_subfield
   from tbl
 order by ordinal_position
)
---- build a string of all columns in the table using short names
, list_short_names as (
select listagg(lower(trim(system_column_name)),', ') 
    within group (order by ordinal_position)
from tbl
)
---- build a string of all columns in the table using short names
, list_long_names as (
select listagg(lower(trim(column_name)),', ') 
    within group (order by ordinal_position)
from tbl
)
-- use one of these depending on what you're looking for
--select * from rpg_ds_long_name;
--select * from rpg_ds;
--select * from list_long_names;
select * from list_short_names;