SQLRPGLE.我的 SQL 查询有问题吗? [英] SQLRPGLE. Is there something wrong with my SQL Query?

问题描述

我得到的错误是SQL0117 Statement contains wrong number of values".

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                                                       

我不是故意在此处指定 column_names，因为我想将所有列从 NOEDEH 复制到 NOEDTSR.
这两个表的结构相同，用于编译两者的DDS(Data Description Structure)完全相同.

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.

有什么想法吗?

推荐答案

你已经解决了你的问题.Mark 提供了一个简单的 SQL 来轻松生成包含列列表的字符串.

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

这是我随身携带的一条 SQL 语句.它将允许您生成以下内容之一:

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

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

只需取消注释语句底部相应的 select * 行即可.

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;

这篇关于SQLRPGLE.我的 SQL 查询有问题吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！