Select * from (
Select the classname, ccacode ccaname from nc6501. XGT_RCB_factor_material m where
M.m aterialcode in
(SELECT code FROM nc6501. Bd_marbasclass c START WITH c.p k_marbasclass='1001 a110000000000uzh'
CONNECT BY the PRIOR c.p k_parent=c.p k_marbasclass)) a
Where ccacode in (select trim (ccacode) from nc6501. XGT_RCB_CCADEP where depcode in (' 20200 '))

As shown in figure, after the recursive, ccacode if specified value directly, is that you can check out the results, but if it is in the subquery, not the value, is why ah?

CodePudding user response:

You put the trim to_char (ccacode), plus individual name

CodePudding user response:

On both sides and trim, plus all the correct table alias

CodePudding user response:

SELECT code FROM nc6501. Bd_marbasclass c START WITH c.p k_marbasclass='1001 a110000000000uzh'
CONNECT BY the PRIOR c.p k_parent=c.p k_marbasclass)) a
Where ccacode in (select trim (ccacode) from nc6501. XGT_RCB_CCADEP where depcode in (' 20200 ')

This sentence should return empty, the reason should be nc6501. Bd_marbasclass table of c.p k_marbasclass='1001 a110000000000uzh conditions. There is no nc6501 XGT_RCB_CCADEP. Depcode in (' 20200')

CodePudding user response:

Nc6501. XGT_RCB_CCADEP ccacode is CHAR or VARCHAR?
I estimate is CHAR have led to this problem,