Como usar resultado do IIF para buscar dados de outra tabela no SQL Server?

[Alexandro Schnoor]

Bom dia pessoal, estou começando nos estudos de análise de dados e estou tentando montar uma view em meu SQL server para apresentar os dados em um BI.

Os dados que preciso apresentar são de faturamento na empresa que trabalho e o que preciso está "espalhado" em ao menos 11 tabelas diferentes, até aí tudo certo, consigo através de JOINs buscar os dados diversos e relacionar tabelas e até mesmo bancos distintos dentro do mesmo SQL, mas uma coisa fugiu do meu conhecimento atual e para isso peço ajuda dos senhores.

Eu preciso buscar na tabela 'USU_TNatCar' o campo 'USU_DesCar' para apresentar em tela, para isso preciso buscar na tabela 'E120PED' o campo 'USU_CodCar' que é campo chave da tabela anterior. A minha tabela principal de faturamento, 'E140NFV' não possui o campo chave da tabela 'E120PED' que é o 'NumPed', para isso preciso buscar em 4 tabelas secundárias de acordo com o tipo de nota fiscal que é emitido, para me trazer essas tabelas montei um IIF:

    IIF (t1.codsnf = '01', t4.numped,
        IIF (t1.codsnf = 'CTE' OR t1.codsnf = 'CT2', t6.numped,
            IIF (t1.codsnf = 'NFS' OR t1.codsnf = 'S01', t5.numped,
                IIF (t5.numped IS NULL, t7.numped, NULL)))) AS Pedido

Na coluna 'Pedido' consigo a informação de forma correta, mas agora não sei como usar essa coluna no JOIN com a tabela E120PED, se eu faço o JOIN dessa forma:

LEFT JOIN E120PED ON E140NFV.CODEMP = E120PED.CODEMP 
                  AND E140NFV.CODFIL = E120PED.CODFIL 
                  AND E120PED.NUMPED = Pedido

Eu recebo um erro dizendo que não posso usar 'Pedido' no JOIN.

Vou postar meu código aqui abaixo e agradeceria muito se conseguissem me dar uma luz sobre como processar esse campo para ter a informação USU_DesCar.

Código:

SELECT
    t1.codcli, t2.apecli, t1.numnfv, t1.datemi, t1.codsnf,
    CASE
        WHEN t1.codsnf = 'CTE' THEN t1.volnfv
        WHEN t1.codsnf = 'CT2' THEN t1.volnfv
        WHEN t1.codsnf = 'NFS' THEN t1.volnfv
        WHEN t1.codsnf = 'S01' THEN t1.volnfv
        WHEN t1.codsnf = '01' THEN t1.pesliq
    END AS Peso,
    t1.vlrliq, t1.plavei, t1.plareb, t1.plare2, t3.nommot,
    IIF (t1.codsnf = '01', t4.numped,
        IIF (t1.codsnf = 'CTE' OR t1.codsnf = 'CT2', t6.numped,
            IIF (t1.codsnf = 'NFS' OR t1.codsnf = 'S01', t5.numped,
                IIF (t5.numped IS NULL, t7.numped, NULL)))) AS Pedido
FROM
    e140nfv t1
        LEFT JOIN e085cli t2 ON t1.codcli = t2.codcli
        LEFT JOIN e073mot t3 ON t1.codmtr = t3.codmtr AND t1.codtra = t3.codtra
        LEFT JOIN e140ipv t4 ON t1.codemp = t4.codemp AND t1.codfil = t4.codfil AND t1.numnfv = t4.numnfv AND t1.codsnf = t4.codsnf AND t1.datemi = t4.datger
        LEFT JOIN e140isv t5 ON t5.codemp = t1.codemp AND t5.codfil = t1.codfil AND t5.codsnf = t1.codsnf AND t5.numnfv = t1.numnfv AND t1.datemi = t4.datger
        LEFT JOIN cteweb.dbo.cte t6 ON t6.codemp = t1.codemp AND t6.codfil = t1.codfil AND t6.codsnf = t1.codsnf AND t6.numnfv = t1.numnfv
        LEFT JOIN cteweb.dbo.nfse t7 ON t7.codemp = t1.codemp AND t7.codfil = t1.codfil AND t7.codsnf = t1.codsnf AND t7.numnfv = t1.numnfv
WHERE
    t1.datemi BETWEEN '01/08/2024' AND '25/08/2024'

Editado Setembro 3 por Casa do Desenvolvedor
Inserção do código na caixa de 'code'.

[Leandro Rezende dos Santos]

Tente usar subselect. Veja a seguir:

SELECT
    t1.codcli, t2.apecli, t1.numnfv, t1.datemi, t1.codsnf,
    CASE
        WHEN t1.codsnf = 'CTE' THEN t1.volnfv
        WHEN t1.codsnf = 'CT2' THEN t1.volnfv
        WHEN t1.codsnf = 'NFS' THEN t1.volnfv
        WHEN t1.codsnf = 'S01' THEN t1.volnfv
        WHEN t1.codsnf = '01' THEN t1.pesliq
    END AS Peso,
    t1.vlrliq, t1.plavei, t1.plareb, t1.plare2, t3.nommot,
   (select IIF (t1.codsnf = '01', t4.numped,
        IIF (t1.codsnf = 'CTE' OR t1.codsnf = 'CT2', t6.numped,
            IIF (t1.codsnf = 'NFS' OR t1.codsnf = 'S01', t5.numped,
                IIF (t5.numped IS NULL, t7.numped, NULL))))) AS Pedido
FROM
    e140nfv t1
        LEFT JOIN e085cli t2 ON t1.codcli = t2.codcli
        LEFT JOIN e073mot t3 ON t1.codmtr = t3.codmtr AND t1.codtra = t3.codtra
        LEFT JOIN e140ipv t4 ON t1.codemp = t4.codemp AND t1.codfil = t4.codfil AND t1.numnfv = t4.numnfv AND t1.codsnf = t4.codsnf AND t1.datemi = t4.datger
        LEFT JOIN e140isv t5 ON t5.codemp = t1.codemp AND t5.codfil = t1.codfil AND t5.codsnf = t1.codsnf AND t5.numnfv = t1.numnfv AND t1.datemi = t4.datger
        LEFT JOIN cteweb.dbo.cte t6 ON t6.codemp = t1.codemp AND t6.codfil = t1.codfil AND t6.codsnf = t1.codsnf AND t6.numnfv = t1.numnfv
        LEFT JOIN cteweb.dbo.nfse t7 ON t7.codemp = t1.codemp AND t7.codfil = t1.codfil AND t7.codsnf = t1.codsnf AND t7.numnfv = t1.numnfv
WHERE
    t1.datemi BETWEEN '01/08/2024' AND '25/08/2024'

 

[Alexandro Schnoor]

Bom dia Leandro, obrigado pela resposta no tópico, eu acabei esquecendo de retornar aqui mas consegui resolver mais ou menos como vc mencionou, acabei resolvendo tudo com um select dentro do IIF, o código pronto ficou assim:

    IIF (t1.codsnf = '01',
    (SELECT TOP(1) a.usu_descar FROM usu_tnatcar a LEFT JOIN e120ped b ON b.usu_codcar = a.usu_codcar WHERE b.codemp = t1.codemp AND b.codfil = t1.codfil AND b.numped = t4.numped),
        IIF (t1.codsnf = 'CTE' OR t1.codsnf = 'CT2',
        (SELECT TOP(1) a.usu_descar FROM usu_tnatcar a LEFT JOIN e120ped b ON b.usu_codcar = a.usu_codcar WHERE b.codemp = t1.codemp AND b.codfil = t1.codfil AND b.numped = t6.numped),
            IIF (t1.codsnf = 'NFS' OR t1.codsnf = 'S01',
            (SELECT TOP(1) a.usu_descar FROM usu_tnatcar a LEFT JOIN e120ped b ON b.usu_codcar = a.usu_codcar WHERE b.codemp = t1.codemp AND b.codfil = t1.codfil AND b.numped = t5.numped),
                IIF (t5.numped IS NULL,
                (SELECT TOP(1) a.usu_descar FROM usu_tnatcar a LEFT JOIN e120ped b ON b.usu_codcar = a.usu_codcar WHERE b.codemp = t1.codemp AND b.codfil = t1.codfil AND b.numped = t7.numped), NULL)))) AS Produto,