SQL Query: based on date lookup

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.
LVL 1
gNomeAsked:
Who is Participating?
 
sdstuberCommented:
add an extra set of parentheses around the subselect


S ELECT *
  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')))
0
 
Christoffer SwanströmPartnerCommented:
sdstuber:  why would the extra parentheses help?

gNome: is that the exact query that you are running? Or have you simplified it, changed column names etc.? Could you show the table structure of the two tables involved?
0
 
sdstuberCommented:
The parentheses are required to use the subselect as a value.


attached is a simplified example that illustrates the problem and the fix
ee.txt
0
 
Christoffer SwanströmPartnerCommented:
Good point, didn't know that.
0
 
gNomeAuthor Commented:
Gold
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.