Condition as part of FROM statement???

[dantebothermy]

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:

    SELECT stuff.id
    FROM    
    case when day(getdate())>19 then CURRENT.dbo.facts 
        else NEXT.dbo.facts
        end
    as stuff
 

Does anyone have an idea of how I can put a condition in my FROM statement?

Thanks,


Dante

[ck9663]

Do you need this to return a single value or a group of recordset?


-- CK

[dantebothermy]

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.

[ck9663]

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

[dantebothermy]

Thanks,

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


Dante