regexp_substr 变量标识符无效

[微妙联系]

SELECT pdn.brands
FROM pd_dsp_notice pdn
LEFT JOIN pd_dsp_notice_prompt pdnp ON pdnp.notice_id = pdn.id AND pdnp.user_id = 3
LEFT JOIN au_dealer au ON au.organization_no = 3
WHERE 1 = 1
AND (pdn.modify_date >= sysdate - 180 OR pdn.state = 1)
AND (pdn.promulgate_user_id = 3 OR pdn.audit_user_id = 3 OR
(pdn.auditrs = 1 AND
(pdn.id IN (SELECT notice_id
FROM pd_dsp_notice_viewer nv
WHERE nv.viewuser_id = 3) OR
((SELECT count(*) AS num
FROM (
SELECT regexp_substr(3, '[^_]+', 1, level) AS s1
FROM dual
CONNECT BY level <= regexp_count(3, '_') + 1) d1
JOIN (SELECT regexp_substr(pdn.brands, '[^_]+', 1, 1) AS s2
FROM dual
CONNECT BY level <= regexp_count(pdn.brands, '_') + 1) d2 ON d2.s2 =
d1.s1) > 0 AND
(SELECT count(*) AS num
FROM (SELECT regexp_substr(au.parent_area_code,
'[^_]+',
1,
level) AS s1
FROM dual
CONNECT BY level <=
regexp_count(au.parent_area_code, '_') + 1) d1
JOIN (SELECT regexp_substr(pdn.orgs, '[^_]+', 1, level) AS s2
FROM dual
CONNECT BY level <= regexp_count(pdn.orgs, '_') + 1) d2 ON d2.s2 =
d1.s1) > 0 AND
(pdn.busis IS NULL OR instr(pdn.busis, '2') > 0)


))))
AND pdn.promulgate_user_id = 3


---------regexp_count(pdn.brands, '_')
报pdn.brands 标识符无效

[stelf]

单独拿出来执行试试呢

[卖水果的net]

Oracle 对多层查询 （使用小括号），只认两层，你这引用，至少三层了，要提出来才可以。

[AHUA1001]

你把regexp_count(pdn.brands, '_')里的pdn.brands，换成一个常量试试看，比如换成regexp_count('abc_123', '_')。 因为这里引用的pdn.，在括号外的好多层，怀疑是这里引用不到这个表。