What are good hints that a nested SQL query cannot be unnested?

I could come up with thousands of examples... Here is just one I pulled out of my github repo that I use to seed a generic date dimension full of 20 years of data. I usually snowflake data dimensions in BI for drill down capabilities.  You can imagine something similar to this for Week, Month, QTR, Year etc.... This of course is an Oracle specific implementation but I have similar ones for other mature db that can do recursive SQL.  i.e. Postgres / SQLServer (though the SQLServer optimizer is not as fast with recursive SQL with larger data sets) etc. This example is interesting to me because it does not require a table and demonstrates an easy way to generate data with very little code.  SELECT  ROWNUM+999 DAY_DT_PK ,DAY_DT ,TO_CHAR(DAY_DT,'MM/DD/YYYY')   DAY_CHAR_MMDDYYYY ,TO_CHAR(DAY_DT,'DDD')          DAY_CHAR_DDD ,TO_CHAR(DAY_DT,'DD')           DAY_CHAR_DD ,TO_CHAR(DAY_DT,'D')            DAY_CHAR_D ,TO_CHAR(DAY_DT,'Day')          DAY_CHAR_DAY ,TO_CHAR(DAY_DT,'DY')           DAY_CHAR_DY FROM(     SELECT     (TRUNC(SYSDATE,'YEAR')+8000)-LEVEL DAY_DT     FROM     DUAL     CONNECT BY LEVEL <= 10000     ORDER BY DAY_DT )A WHERE          DAY_DT >= TO_DATE('20100101','YYYYMMDD') AND DAY_DT <= TO_DATE('20300101','YYYYMMDD')