case statement in the FROM

[ktucci]

i need to put a case statement in the FROM section of a query...is this possible or is there an alternative that i am missing...thanks in advance

select distinct
ud_cycend, UD_BILLTO_NUMBER, UD_RESALE_CODE, all_lines_master.dbo.temp_carrier_002.RESALE_UR, all_lines_master.dbo.temp_carrier_002.RESALE_DESC, max(ud_date) as date, ---ud_network_code, ARRIER_NUMBER_REFERENCE.CARRIER_NAME

from
case when month(getdate()) = 1 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20011231
when month(getdate()) = 1 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20011130
when month(getdate()) = 2 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020131
when month(getdate()) = 2 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20011231
when month(getdate()) = 3 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020228
when month(getdate()) = 3 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020131
when month(getdate()) = 4 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020331
when month(getdate()) = 4 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020228
when month(getdate()) = 5 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020430
when month(getdate()) = 5 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020331
when month(getdate()) = 6 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020531
when month(getdate()) = 6 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020430
when month(getdate()) = 7 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020630
when month(getdate()) = 7 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020531
when month(getdate()) = 8 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020731
when month(getdate()) = 8 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020630
when month(getdate()) = 9 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020831
when month(getdate()) = 9 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020731
when month(getdate()) = 10 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020930
when month(getdate()) = 10 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020831
when month(getdate()) = 11 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20021031
when month(getdate()) = 11 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020930
when month(getdate()) = 12 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20021130
when month(getdate()) = 12 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20021031 end aa

inner join all_lines_master.dbo.temp_carrier_002 on all_lines_master.dbo.temp_carrier_002.RESALE_CODE = aa.UD_RESALE_CODE
group by ud_cycend, ud_billto_number, ud_resale_code, all_lines_master.dbo.temp_carrier_002.RESALE_DESC, RESALE_UR, ud_rate_type
order by UD_BILLTO_NUMBER, UD_RESALE_CODE

 

[rac2]

Hello ktucci,

I don't think so. My T-SQL Reference says that the FROM clause can contain a list of table_names or view_names. It doesn't say anything about expressions. It says a CASE statement is allowed anywhere an expression is used. I tried this in Query Analyzer and got a syntax error (SQL Server 7).

An alternative would be to put the logic into a stored procedure. Construct the archive table name from the date. Or make a table to look up the table name given the date.

Put the table name in a variable and use the exec() function to construct the appropriate query.

DECLARE @archiveTableName VARCHAR(50)

exec("SELECT DISTINCT . . . FROM " + @archiveTableName + " aa INNER JOIN . . . ")

Construct the archive table name with your logic like this

SELECT @archiveTableName =
CASE
 . . .
when month(getdate()) = 11 and day(getdate()) > 12 then ARCDATA.DBO.OIV_UD_ARC_100_20021031
when month(getdate()) = 11 and day(getdate()) < 12 then ARCDATA.DBO.OIV_UD_ARC_100_20020930

 . . .
END

Or pull it out of a lookup table like this

SELECT @archiveTableName = table_name FROM archive_tables 
WHERE getdate() BETWEEN period_begin AND period_end