Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Your CASE is equivalent to the following: CASE TypeId WHEN 1 THEN InGs828S END AS InDevice I am not sure if that is what you want. –  AlexKuznetsov Aug 13 '12 at 13:08
    
My case statement was incomplete, I've edited my question. –  r.zarei Aug 13 '12 at 13:54
    
@AlexKuznetsov: My case statement was incomplete, I've edited my question. –  r.zarei Aug 13 '12 at 14:04
add comment

3 Answers 3

up vote 4 down vote accepted

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
share|improve this answer
    
Thanks Mr. AlexKuznetsov, your code works fine. –  r.zarei Aug 14 '12 at 15:19
add comment

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
share|improve this answer
    
Neither cross join nor cross apply have an on clause? –  Andomar Aug 13 '12 at 11:44
    
@Andomar: cross join no, cross apply needs some correlation –  gbn Aug 13 '12 at 11:45
    
@gbn: your provided code is wrong and causes syntax error. –  r.zarei Aug 13 '12 at 13:57
    
@gbn: Please check my question again I've updated case statment –  r.zarei Aug 13 '12 at 14:02
3  
@gbn: I think you meant either JOIN ... ON or CROSS JOIN ... WHERE –  ypercube Aug 13 '12 at 16:39
show 1 more comment

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
share|improve this answer
    
Disjunction of conjunctions is a phrase I need to find excuses to use! –  Mark Storey-Smith Aug 31 '12 at 22:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.