About ORA-00907: missing right parenthesis troubleshooting

When writing the stored procedure, the system time format conversion is used to compare with the database field, when I test throw the ORA-00907: missing right parenthesis, the default right brace;

The following is my error code:

Dynamic_ SQL: = 'select t3.departmentcode, t3.total from (select departmentcode, count (*) total from '| str_tablename | 'where(LASTUPDATEDTIME between to_date (to_char (sysdate-1, "yyyy-mm-dd"), ''yyyy-mm-dd'') and to_date (to_char (sysdate, "yyyy-mm-dd"), ''yyyy-mm-dd ''))And departmentcode is not NULL group by departmentcode) t3, v_s_data_input_dept_usernum t4 where t3.departmentcode = '| str_departmentcode | '';

After correction, the code is successfully executed:

Dynamic_ SQL: = 'select t3.departmentcode, t3.total from (select departmentcode, count (*) total from '| str_tablename | 'where (LASTUPDATEDTIME between to_date (''' | to_char (sysdate-1, 'yyyy-mm-dd') | ''', '''yyyy-mm-dd '') and to_date (''' | to_char (sysdate, 'yyyy-mm-dd') | ''', ''yyyy-mm-dd ''))And departmentcode is not NULL group by departmentcode) t3, v_s_data_input_dept_usernum t4 where t3.departmentcode = '| str_departmentcode | '';

Comparison of the red part

I hope this will be helpful for similar errors.

Second case

(Oracle test) select statements using SQL statements often use aliases of fields and tables. Note that the field alias can be prefixed with the as keyword or omitted without writing, however, the table name alias cannot be prefixed with as; otherwise, the following error is reported:

Command error, row: 11 columns: 20
Error Report:
SQL error: ORA-00907: missing right brackets
00907. 00000-"missing right parenthesis"
* Cause:
* Action: