March 25, 2004 at 11:04 am
Hi,
I want to use CASE statement in FROM clause, for example
DECLARE @I INT
SET @I = 1
SELECT * FROM
TABLE_A A,
CASE WHEN @I = 1 THEN TABLE_B ELSE TABLE_C END AS B
WHERE A.ID = B.IB
with out using EXEC('sql') is this possible, please help me if you have any ideas.
March 25, 2004 at 11:24 am
Sorry you cannot do.
March 25, 2004 at 11:27 am
No. You will have to use EXEC ('sql'). Maybe something like this:
DECLARE @I INT,
@a AS VARCHAR(100),
@TableName AS SYSNAME
SELECT @I = 1
SELECT @TableNAme = CASE
WHEN @I = 1 THEN 'TABLE_B'
ELSE 'TABLE_C'
END
SELECT @a = 'SELECT * FROM TABLE_A A, ' + @TableName + ' AS B WHERE A.ID = B.IB'
EXEC (@a)
March 25, 2004 at 11:31 am
Sorry just occurred to me that if the structure of the tables are roughly the same you could potentially use a UDF like in the following example to pull it off.
(Note requries SQL 2000)
CREATE FUNCTION dbo.Test (@tblName varchar(255))
RETURNS @varX TABLE ( IDVAL VARCHAR(20) )
AS
BEGIN
If @tblName = 'orders'
INSERT @varX (IDVAL) select OrderID from orders
ELSE
INSERT @varX (IDVAL) select CustomerID from customers
RETURN
END
March 26, 2004 at 3:45 am
I'm making two assumptions here:
1. Table_B and Table_C have the same structure
2. These tables are not huge
DECLARE @I INT
SET @I = 1
SELECT *
FROM TABLE_A A,
(select *,1 tblno from TABLE_B
union
select *,2 tblno from TABLE_C) B
WHERE A.ID = B.ID
and B.tblno = @I
If the assumptions are not true a outer join will have to be tried out.
Hope this helps.
AM
March 26, 2004 at 4:44 am
Hallo!
Try to redefine a task:
First: I think you do not have to SELECT *
Second:
Rewrite code as:
DECLARE @I INT
SET @I = 1
SELECT
A.*
,specificField =
CASE
WHEN @i = 1 THEN (SELECT specificField FROM TABLE_B WHERE A.Id = Id)
ELSE (SELECT specificField FROM TABLE_C WHERE A.Id = Id)
END
FROM
TABLE_A
-----------
Of course for many fields you must rewrite for every field you want to select. We have a lot of such code.
Good luck!
March 26, 2004 at 11:08 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply