HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

CROSS JOIN with temp table and CASE statement

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
casestatementcrosswithtempjoinandtable

Problem

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?

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#22353, answer score: 4

Revisions (0)

No revisions yet.