?
Solved

SQL Query: based on date lookup

Posted on 2012-09-12
5
Medium Priority
?
697 Views
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.
0
Comment
Question by:gNome
  • 2
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38390896
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
 
LVL 8

Expert Comment

by:Christoffer Swanström
ID: 38390956
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38390973
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
 
LVL 8

Expert Comment

by:Christoffer Swanström
ID: 38391009
Good point, didn't know that.
0
 
LVL 1

Author Closing Comment

by:gNome
ID: 38391010
Gold
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

755 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