时间戳

首页 > 解决方案 > 大家好，我正在使用 apex 19.1 创建经典报告时遇到一些错误

sql - 大家好，我正在使用 apex 19.1 创建经典报告时遇到一些错误

问题描述

大家好，我正在研究 apex 19.1 在创建经典报告时遇到一些错误，该报告在块下执行时，

declare
vquery varchar2(4000);
BEGIN
   if 
(:P3_POSTCODE is not null
           and
           :P3_HUISNR is not null
           )
      then
      vquery :='SELECT * FROM LVV_AANSLUITPUNT where lvv_aansluitpunt.la_post_code = :P3_POSTCODE 
          and lvv_aansluitpunt.la_huis_nr = :P3_HUISNR and ((lvv_aansluitpunt.la_dat_aanvang is not null 
          and lvv_aansluitpunt.la_dat_eind is null ) or (lvv_aansluitpunt.la_dat_aanvang is null 
          and lvv_aansluitpunt.la_dat_eind is null )) and lvv_aansluitpunt.an_code is not null 
          and lvv_aansluitpunt.vl_id is not null 
          and not exists (select '*' from lvv_juridische_lever jl where jl.vv_id = lvv_aansluitpunt.vv_id and upper(jl.nv_code) ="B")
          and exists (select '*' from lvv_vv_leiding vl where vl.vl_id = lvv_aansluitpunt.vl_id 
          and ((vl.vl_dat_aanvang is not null and vl.vl_dat_eind is null ) or (vl.vl_dat_aanvang is null and vl.vl_dat_eind is null ))) 
          and exists (select '*' from lvv_vaste_verbinding vv , lvv_soort_artikel sa where vv.vv_id = lvv_aansluitpunt.vv_id and vv.sa_code = sa.sa_code and sa.sa_aansl_drager_j_n = "J")';


      else

    vquery := 'select distinct sa.sa_code
       ,      sa.sa_type_verbinding
       ,      vv.vv_id
       ,      nvl(vv.vv_lijnbenaming_nw,vv.vv_lijnbenaming)
       ,      vl.vl_id
       ,      vl.vl_lijnbenaming

       from   lvv_soort_artikel      sa
       ,      lvv_vv_leiding         vl
       ,      lvv_juridische_lever   jl
       ,      lvv_ldm_aansluitpunt   la
       ,      lvv_vaste_verbinding   vv
       where  :P3_VV_LIJNBEN =
       nvl(vv.vv_lijnbenaming_nw,vv.vv_lijnbenaming)
       and    vv.vv_id                         = vl.vv_id
       and    vv.vv_id                         = jl.vv_id
       and    vv.vv_id                         = la.vv_id
       and    vv.sa_code                       = sa.sa_code
       and    la.an_code          is not null
       and    vl.vl_id            is not null
       and    jl.nv_code                        <> "B"
       and    sa.sa_aansl_drager_j_n            =  "j"
       and   ((la.la_dat_aanvang is not null
               and
               la.la_dat_eind    is null
              )
              or
              (la.la_dat_aanvang is null
              and
               la.la_dat_eind     is null
              ))
       and    ((vl.vl_dat_aanvang is not null
                and
                vl.vl_dat_eind is null
               )
               or
               (vl.vl_dat_aanvang is null
                and
                vl.vl_dat_eind is null
              ))';

      end if ;

      RETURN vquery;

    end;

执行时返回的错误是：- ORA-20999：解析返回的查询导致“ORA-20999：解析 SQL 查询失败！

ORA-06550：第 24 行，第 40 列：ORA-00904：“j”：无效标识符

”。

标签： sqlplsqloracle11goracle-apexoracle-apex-19.1

解决方案

在 oracle 中，单引号用于字符串文字。双引号用于表名/列名。

用单引号替换查询中文字 'j' 的双引号。如果字符串 vquery 有换行符，则每行应包含在字符串中，并且必须连接在一起。

 else

vquery := 'select distinct sa.sa_code '||chr(10)||
   ',      sa.sa_type_verbinding '||chr(10)||
   ',      vv.vv_id '||chr(10)||
   ',      nvl(vv.vv_lijnbenaming_nw,vv.vv_lijnbenaming) '||chr(10)||
   ',      vl.vl_id '||chr(10)||
   ',      vl.vl_lijnbenaming '||chr(10)||

  ' from   lvv_soort_artikel      sa '||chr(10)||
  ' ,      lvv_vv_leiding         vl '||chr(10)||
  ' ,      lvv_juridische_lever   jl '||chr(10)||
  ' ,      lvv_ldm_aansluitpunt   la '||chr(10)||

要为字符串文字转义单引号，请使用两个单引号而不是一个，如下所示：

   'and    jl.nv_code                        <> ''B'''||chr(10)||
   'and    sa.sa_aansl_drager_j_n            =  ''j'''||chr(10)||

推荐阅读