I think I have seen this in all the versions of Toad 14 I have run. I have never had the problem in a lower version. I was running 14.1.120.923 when this happened. I copied a working query from an open file, opened another editor tab, pasted the code in and ran it. It threw an error that the last identifier, in an order by clause, was invalid (ORA-00904). However the identifier was correct. The SQL was closed with a terminating semi-colon. I tried saving the SQL to a file and then reformatting it with no change. I finally got it to run by adding another character to the end of the identifier, making the identifier now incorrect. Here is the code.
select s.step_name, d.name, d.type_cd, d.sucess_rows, d.start_ts, d.end_ts, d.end_ts - d.start_ts dur_d, (d.end_ts - d.start_ts) * 86400 "DUR_D(S)"
from obi_etl_dac.w_etl_run_step s
join obi_etl_dac.w_etl_run_sdtl d on s.row_wid = d.run_step_wid
where 1 = 1
and d.type_cd not in ('Truncate Table', 'Drop Index', 'PAUSE_FOR_GROUP_TASKS', 'Notify Dependents', 'Preceding Action', 'Stored Procedure')
and run_wid = (select row_wid
from obi_etl_dac.w_etl_defn_run
where name = 'SRS Weekly - Full: ETL Run - 2021-06-13 00:00:00.05')
and d.name like 'PC_Agg_PSFinCostsFact_A2%'
order by d.start_ts, d.end_ts;
So if I changed the last indentifier to "d.end_tss" the SQL would run. That appended character could be any character, although adding another semi-colon did not work. If I added another query below this one the query would run. Just adding additional blank lines after the query did not work though.
I turned on spool SQL (Main Menu -> Database -> Spool to Screen) to try to reproduce this but it worked OK for me. At least I guess it was working OK - Toad was sending the complete query to Oracle but of course I got a "Table or view does not exist" error.
What does spool SQL show you? Can you reproduce the problem with any query, or do you think there is something in particular about this one? Also, are there any XMLTYPE columns in those tables? It doesn't look that way, judging by the names, at least. XMLTYPE has caused problems in other queries.
Here is the spool SQL output, which I guess is what you would expect:
----------------------------------
-- Session: OBI_READ@BISERVER
-- Timestamp: 06:11:27.698
select s.step_name, d.name, d.type_cd, d.sucess_rows, d.start_ts, d.end_ts, d.end_ts - d.start_ts dur_d, (d.end_ts - d.start_ts) * 86400 "DUR_D(S)"
from obi_etl_dac.w_etl_run_step s
join obi_etl_dac.w_etl_run_sdtl d on s.row_wid = d.run_step_wid
where 1 = 1
and d.type_cd not in ('Truncate Table', 'Drop Index', 'PAUSE_FOR_GROUP_TASKS', 'Notify Dependents', 'Preceding Action', 'Stored Procedure')
and run_wid = (select row_wid
from obi_etl_dac.w_etl_defn_run
where name = 'SRS Weekly - Full: ETL Run - 2021-06-13 00:00:00.05')
and d.name like 'PC_Agg_PSFinCostsFact_A2%'
order by d.start_ts, d.end_t;
-- ORA-00904: "D"."END_T": invalid identifier
-- Elapsed Time: 0.112 seconds
It's not any query, just certain ones. It doesn't happen often, but when it does it is annoying. I have had the problem in the past but don't have any saved SQL to compare this to, so I haven't identified any pattern. No no XML.
In the original case I had copied the single statement from an open tab with a file holding multiple statements into a new tab and had the problem. To generate the spool output I copied the SQL I had posted from this page into a new tab and reproduced the problem.
Ok. I tried pasting your SQL into one editor tab, then copy/paste from one to tab to a new tab, but still it worked ok for me.
Maybe it is related to some setting. Will you zip up and send me your user files folder? Instructions on how to do that, as well as my email address are here.
Also, please send the file that you had open in the first tab.
This will be fixed in the next Toad beta. If you are on 14.2, you can get the current beta here. The beta with this fix for this will come out on July 26th.