SQLRPGLE.我的SQL查询有问题吗

Question

这是我试图在程序中运行的SQL查询。然而，我甚至不能编译它，我得到了一个错误，它只是告诉我查询中有问题。我得到的错误是">SQL0117语句包含错误数量的值"；。

/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                                                       

我不是故意在此处指定column_name的，因为我想将所有列从NOEDEH复制到NOEDSR。
这两个表的结构与用于编译的DDS(数据描述结构(完全相同。

有什么想法吗？

Answer 1

您已经解决了问题。Mark提供了一个简单的SQL，可以轻松地生成一个包含列列表的字符串。

下面是我随身携带的SQL语句。它将允许您生成以下内容之一：

• 短列名字符串
• 长列名字符串
• 使用短名称的rpg数据结构
• 使用长名称的rpg数据结构

只需取消对语句底部相应的select *行的注释。

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;