Using Case When with LeftJoin

Question

Using Case When with LeftJoin

Navigation

#1 by (2 votes)

3

I am putting together a proc, but depending on the value of a parameter, I would like the left join to be different. I have tried the querys below:

This works, however, else as null or even else , if the parameter COD_FORNECEDOR_FILTRO is null , the query returns nothing.

LEFT JOIN FORNECEDOR F
        ON F.ID_FORNECEDOR = T.ID_FORNECEDOR 
        AND F.COD_FORNECEDOR = 
        (CASE WHEN @COD_FORNECEDOR_FILTRO IS NOT NULL 
        THEN @COD_FORNECEDOR_FILTRO 
        ELSE NULL
        END)

This does not work.

CASE WHEN COD_FORNECEDOR_FILTRO IS NOT NULL 
THEN LEFT JOIN FORNECEDOR F ON F.ID_FORNECEDOR = T.ID_FORNECEDOR AND F.COD_FORNECEDOR = @COD_FORNECEDOR_FILTRO
ELSE LEFT JOIN FORNECEDOR F ON F.ID_FORNECEDOR = T.ID_FORNECEDOR 
END

The idea is that if the parameter has value, have join with the field F.COD_FORNECEDOR if not, only with F.ID_FORNECEDOR

I can not do it the way I want. Can you help me?

sql join sql-server case

asked by anonymous 07.08.2016 / 22:45

1 answer

How to make NetBeans recognize the syntax of the ES2015? How to make '' tremendous '' effect with css

score 2 · Accepted Answer

You can use the F.COD_FORNECEDOR field itself when the value is null .

LEFT JOIN FORNECEDOR F
        ON F.ID_FORNECEDOR = T.ID_FORNECEDOR 
        AND F.COD_FORNECEDOR = 
        (CASE WHEN @COD_FORNECEDOR_FILTRO IS NOT NULL 
        THEN @COD_FORNECEDOR_FILTRO 
        ELSE F.COD_FORNECEDOR
        END)