I'm running Oracle
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit
Production PL/SQL Release 12.2.0.1.0 - Production "CORE
12.2.0.1.0 - Production" TNS for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
The issue I'm facing is, again, with LISTAGG and SUBSTR.
In the below sqlfiddles the are two sqls, I did the first SELECT DISTINCT just to show me that all works as expected. Then in the second I'm adding LISTAGG to group and put commas etc. and to have all values in one row.
In the following sqlfiddle I get an error "invalid identifier" http://www.sqlfiddle.com/#!4/cf13785/1
while this next works http://www.sqlfiddle.com/#!4/cf13785/13 but it does not give the expected result as if the SUBSTR function is not considered and the wdocname field is read in its integrity. You see in the fiddle that some values are duplicated.
I know that this sounds like a duplicate question, but I've been reading all LISTAGG posts and I did not come to a point.
Thanks for helping.
It certainly is an invalid identifier as you're now querying a subquery, not original table.
Should be
SQL> SELECT LISTAGG (CHR (39) || docs || CHR (39), ', ') WITHIN GROUP (ORDER BY docs) result
2 FROM (
3 SELECT DISTINCT SUBSTR (ordref.wdocname, 1, 5) AS docs
4 FROM ordref
5 );
RESULT
--------------------------------------------------------------------------------
'AM00A', 'AN00C', 'AS00A', 'AV00A', 'AV00B', 'BC00A', 'ER00B', 'IV00C', 'OD00B',
'OV00A', 'OV00C', 'SL00B', 'TR00B', 'VL00B'
SQL>