JOIN 조건에서 CASE 문을 사용할 수 있습니까?

JOIN 조건에서 CASE 문을 사용할 수 있습니까?

---

다음 이미지는 Microsoft SQL Server 2008 R2 시스템 뷰의 일부입니다. 이미지에서 sys.partitions와 의 관계는 sys.allocation_units의 값에 따라 달라집니다 sys.allocation_units.type. 그래서 그것들을 합치려면 다음과 비슷한 것을 쓰십시오.

SELECT  *
FROM    sys.indexes i
        JOIN sys.partitions p
            ON i.index_id = p.index_id 
        JOIN sys.allocation_units a
            ON CASE
               WHEN a.type IN (1, 3)
                   THEN a.container_id = p.hobt_id 
               WHEN a.type IN (2)
                   THEN a.container_id = p.partition_id
               END 

그러나 위 코드는 구문 오류를 발생시킵니다. 나는 그것이 CASE성명서 때문이라고 생각합니다 . 누구든지 조금 설명하는 데 도움이 될 수 있습니까?

---

오류 메시지 추가 :

메시지 102, 수준 15, 상태 1, 줄 6 '='근처의 구문이 잘못되었습니다.

---

CASE표현식의 값 리턴 THEN절의 부. 따라서 다음과 같이 사용할 수 있습니다.

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

---

Instead, you simply JOIN to both tables, and in your SELECT clause, return data from the one that matches:

I suggest you to go through this link Conditional Joins in SQL Server and T-SQL Case Statement in a JOIN ON Clause

e.g.

    SELECT  *
FROM    sys.indexes i
        JOIN sys.partitions p
            ON i.index_id = p.index_id 
        JOIN sys.allocation_units a
            ON a.container_id =
            CASE
               WHEN a.type IN (1, 3)
                   THEN  p.hobt_id 
               WHEN a.type IN (2)
                   THEN p.partition_id
               END 

Edit: As per comments.

You can not specify the join condition as you are doing.. Check the query above that have no error. I have take out the common column up and the right column value will be evaluated on condition.

---

Try this:

...JOIN sys.allocation_units a ON 
  (a.type=2 AND a.container_id = p.partition_id)
  OR (a.type IN (1, 3) AND a.container_id = p.hobt_id)

---

I think you need two case statements:

SELECT  *
FROM    sys.indexes i
    JOIN sys.partitions p
        ON i.index_id = p.index_id 
    JOIN sys.allocation_units a
        ON 
        -- left side of join on statement
            CASE
               WHEN a.type IN (1, 3)
                   THEN a.container_id
               WHEN a.type IN (2)
                   THEN a.container_id
            END 
        = 
        -- right side of join on statement
            CASE
               WHEN a.type IN (1, 3)
                   THEN p.hobt_id
               WHEN a.type IN (2)
                   THEN p.partition_id
            END             

This is because:

• the CASE statement returns a single value at the END
  
• the ON statement compares two values
  
• your CASE statement was doing the comparison inside of the CASE statement. I would guess that if you put your CASE statement in your SELECT you would get a boolean '1' or '0' indicating whether the CASE statement evaluated to True or False
  

---

This seems nice

https://bytes.com/topic/sql-server/answers/881862-joining-different-tables-based-condition

FROM YourMainTable
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom =  DepCity.Code
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable

---

I took your example and edited it:

SELECT  *
FROM    sys.indexes i
    JOIN sys.partitions p
        ON i.index_id = p.index_id 
    JOIN sys.allocation_units a
        ON CASE
           WHEN a.type IN (1, 3)
               THEN p.hobt_id 
           WHEN a.type IN (2)
               THEN p.partition_id
           ELSE NULL
           END = a.container_id

---

Here I have compared the difference in two different result sets. Hope this might be helpful.

SELECT main.ColumnName, compare.Value PreviousValue,  main.Value CurrentValue
FROM 
(
    SELECT 'Name' AS ColumnName, 'John' as Value UNION ALL
    SELECT 'UserName' AS ColumnName, 'jh001' as Value UNION ALL
    SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
    SELECT 'Phone' AS ColumnName, NULL as Value UNION ALL
    SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
    SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
    SELECT 'IsActive' AS ColumnName, '1' as Value
) main
INNER JOIN
(
    SELECT 'Name' AS ColumnName, 'Rahul' as Value UNION ALL
    SELECT 'UserName' AS ColumnName, 'rh001' as Value UNION ALL
    SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
    SELECT 'Phone' AS ColumnName, '01722112233' as Value UNION ALL
    SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
    SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
    SELECT 'IsActive' AS ColumnName, '1' as Value
) compare
ON main.ColumnName = compare.ColumnName AND
CASE 
    WHEN main.Value IS NULL AND compare.Value IS NULL THEN 0
    WHEN main.Value IS NULL AND compare.Value IS NOT NULL THEN 1
    WHEN main.Value IS NOT NULL AND compare.Value IS NULL THEN 1
    WHEN main.Value <> compare.Value THEN 1
END = 1 

---

Took DonkeyKong's example.

문제는 선언 된 변수를 사용해야합니다. 이를 통해 비교해야 할 내용의 왼쪽과 오른쪽을 지정할 수 있습니다. 사용자의 선택에 따라 다른 필드를 연결해야하는 SSRS 보고서를 지원하기위한 것입니다.

초기 사례는 선택을 기반으로 필드 선택을 설정 한 다음 조인을 위해 일치시켜야하는 필드를 설정할 수 있습니다.

변수가 다른 필드에서 선택해야하는 경우 오른쪽에 두 번째 사례 설명을 추가 할 수 있습니다.

LEFT OUTER JOIN Dashboard_Group_Level_Matching ON
       case
         when @Level  = 'lvl1' then  cw.Lvl1
         when @Level  = 'lvl2' then  cw.Lvl2
         when @Level  = 'lvl3' then  cw.Lvl3
       end
    = Dashboard_Group_Level_Matching.Dashboard_Level_Name

참고 URL : https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition