By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

Condition as part of FROM statement???

P: 47
I'm trying to have the FROM statement in a SQL query result in a different database depending on the date.

In the middle of each month, our system puts the most recent data in a NEXT database, then at the beginning of the next month NEXT is put into CURRENT and NEXT is emptied.

I want my query to always use the most up-to-date database, or from the first to the 20th use CURRENT, thereafter use NEXT. I've tried to do this with a CASE statement, but I keep getting syntax errors.

Here's a sample of the query that failed:
Expand|Select|Wrap|Line Numbers
  1.     SELECT stuff.id
  2.     FROM    
  3.     case when day(getdate())>19 then CURRENT.dbo.facts 
  4.         else NEXT.dbo.facts
  5.         end
  6.     as stuff
  7.  
Does anyone have an idea of how I can put a condition in my FROM statement?

Thanks,


Dante
Feb 23 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Do you need this to return a single value or a group of recordset?


-- CK
Feb 24 '09 #2

P: 47
CK:

Thanks for your reply.

I'm working with a view that I'd like to keep up with the latest data. I believe this means that I'm looking for a recordset.

Thanks.

Dante

PS. I'm not trained as a technical person.
Feb 24 '09 #3

ck9663
Expert 2.5K+
P: 2,878
You have two options:

1. Use a table-function instead of a view.

2. Use UNION (watch out for the difference in table structure), add a new column to identify where the record came from. Use the entire UNION as subquery and use WHERE to grab those records coming from whichever database you want.



-- CK
Feb 24 '09 #4

P: 47
Thanks,

I love the union query idea, although I may end up using the function instead.


Dante
Feb 24 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.