分类下拉的时候没有数据

H1导入H2数据 分类下拉没数据,下拉语句跟踪报错

select classcode, classname, classgroupno
  from (SELECT distinct k.classcode,
               (select listagg(a.classname, '-') within group(order by a.levels) classname
                  from t_class_base a
                 where a.levels <> 0
                 start with a.classgroupno = k.classgroupno
                        and a.classcode = k.classcode
                connect by PRIOR a.parentno = a.classcode) as classname,
               k.classgroupno
          FROM t_class_base k
         WHERE k.levels <> 0
           AND parentno IS NOT NULL
           AND connect_by_isleaf = 1
        connect by prior classcode = parentno 
           AND EXISTS (SELECT 1
                  FROM t_class t
                 WHERE t.classcode = k.classgroupno
                   and t.compid = 1
                   and t.status = 1)
        UNION ALL
        SELECT NULL, NULL, classgroupno
          FROM t_class_base
         WHERE levels = 0)

报错:

ORA-01436: 用户数据中的 CONNECT BY 循环

查询出错的数据(1.子目录没有匹配到父目录的情况)

select* from t_class_base  where PARENTNO not in (select classcode from t_class_base)

查出来之后进行简单修补

INSERT INTO t_class_base (classcode, classname, levels, classgroupno, lastmodify, lasttime, notes, stamp, parentno, isglobalclass, classabc, status)
SELECT PARENTNO, classname, levels-1, classgroupno, 168, SYSDATE, '补充无上级编码', 0, 
       SUBSTR(PARENTNO, 0, LENGTHB(PARENTNO)-2), 0, f_st2zjm(classname), 1
FROM (
    SELECT t.*, 
           ROW_NUMBER() OVER (PARTITION BY PARENTNO ORDER BY ROWID) AS rn
    FROM t_class_base t
    WHERE PARENTNO NOT IN (SELECT classcode FROM t_class_base)
    AND PARENTNO <> '*'
)
WHERE rn = 1

查询出错的数据(2.子目录和父目录一样的情况)

SELECT classcode, parentno, CONNECT_BY_ISCYCLE as is_cycle
  FROM t_class_base
 WHERE CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE PRIOR classcode = parentno
 START WITH parentno IS NOT NULL;

查出来之后进行数据修正

update t_class_base set parentno = '' where classcode = ''

数据库里面有数据但是商品信息里面查不到

因为Oracle有些字符是看不到的!!!

BEGIN
    FOR i IN (SELECT compid, wareid, warename,waregeneralname,warespec,waremark    FROM t_ware) LOOP
        FOR x IN 1 .. 31 LOOP
            --1~31之间的不可见字符全部替换为空白
            i.warename     := TRIM(REPLACE(i.warename, chr(x), ''));
            i.waregeneralname := TRIM(REPLACE(i.waregeneralname, chr(x), ''));
        END LOOP;
        UPDATE t_ware
        SET    warename = to_single_byte(i.warename), waregeneralname = to_single_byte(i.waregeneralname)
        WHERE  compid = i.compid AND wareid = i.wareid;
        COMMIT;
    END LOOP;
END;

上面的脚本可以扩展使用到 t_ware_ext ,t_comp_ware,t_vencus,等,只要界面不显示数据,但是数据库有,大部分会是特殊字符的原因