patternsqlMinor
CROSS JOIN with temp table and CASE statement
Viewed 0 times
casestatementcrosswithtempjoinandtable
Problem
I'm trying to
I get
My question is why can't I use any columns from
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=1I 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=1Code 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=1Context
StackExchange Database Administrators Q#22353, answer score: 4
Revisions (0)
No revisions yet.