SQL Query: based on date lookup

Posted on 2012-09-12
Last Modified: 2012-09-12
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.
Question by:gNome
    LVL 73

    Accepted Solution

    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')))
    LVL 8

    Expert Comment

    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?
    LVL 73

    Expert Comment

    The parentheses are required to use the subselect as a value.

    attached is a simplified example that illustrates the problem and the fix
    LVL 8

    Expert Comment

    Good point, didn't know that.
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now