CROSS JOIN with temp table and CASE statement

Question

I'm trying to CROSS JOIN a table with a view. Here is my code:

 SELECT *
 FROM   ( SELECT    * ,
                    CASE TypeId
                      WHEN 1 THEN InGs828S
                      WHEN 2 THEN InGS828H
                      WHEN 3 THEN InGS828L
                      WHEN 4 THEN InEasyFlowWD
                    END AS InDevice
          FROM      dbo.Ports
        ) tempTable
        CROSS JOIN dbo.vwDevices
WHERE InDevice=1

I get Msg 207, Level 16, State 1, Line 4 Invalid column name 'TypeId'. TypeId is a column in view vwDevices.

My question is why can't I use any columns from vwDevices in CASE *ColName*, while I can use columns from table Ports?

Answer 1

You can do it the other way around:

SELECT *
 FROM  dbo.vwDevices AS v
CROSS APPLY ( SELECT    * ,
                    CASE v.TypeId
                      WHEN 1 THEN InGs828S
                      WHEN 2 THEN InGS828H
                      WHEN 3 THEN InGS828L
                      WHEN 4 THEN InEasyFlowWD
                    END AS InDevice
          FROM      dbo.Ports
        ) AS tempTable
WHERE tempTable.InDevice=1

Answer 2

Here's yet another way of rewriting your query (a join too, like in @gbn's answer, but the CASE expression is replaced with a disjunction of conjunctions):

SELECT *
FROM dbo.Ports p
  INNER JOIN dbo.vwDevices d
     ON p.TypeId = 1 AND p.InGs828S = 1
     OR p.TypeId = 2 AND p.InGs828H = 1
     OR p.TypeId = 3 AND p.InGs828L = 1
     OR p.TypeId = 4 AND p.InEasyFlowWD = 1

Answer 3

You'd need CROSS APPLY to plug in columns from another table

However, are you sure you need it at all?

SELECT *
FROM
    dbo.vwDevices D
    INNER JOIN
    dbo.Ports P ON 
                   CASE D.TypeId
                      WHEN 1 THEN P.InGs828S
                      WHEN 2 THEN P.InGS828H
                      WHEN 3 THEN P.InGS828L
                      WHEN 4 THEN P.InEasyFlowWD
                    END = 1