Trying to filter on records based on the start of the fiscal year. We have a table to lookup(my_date_lookup_table) with a column name that allows us to search on the YYYY and return the start date of the current fiscal year. The idea is to get the curretn yyyy and return the start date and then filter on my_table recrods based on whether MY_DATE is greater than or equal to the start of the fiscal year:
select * from my_table
where MY_DATE >= trunc(select distinct(FISCAL_START_DATE_COLUMN_HERE) from my_date_looup_table where fiscal_year_name = TO_CHAR(SYSDATE, 'YYYY'))
I think I am close but am unable to get the query to work - it hangs on errror "missing expression in the inner select statement).... Any help getting the right inner select statement to return a date that can be evaluated would be greatly appreciated.