Crossing of Tables with Multiple Coalesce

Question

Expensive,

I have a table of "processes requested for consultation" and two tables with information of registered processes (the "new" processes and the "old" processes). My intention is to bring the information contained in the registers of the processes to the table of consulted processes, in particular the column "subject".

Despite being a simple question, Table A has a major flaw: some old processes, which have already been migrated to "new processes", do not appear in Table A (query), which forces me to have to resort to a "Table D"of converting old processes to new ones, thus extracting the much desired subject contained in Table B. In addition, my table crossing key are two fields: the year and number of the process - since the process number can be repeated in different years, but never in a single year.

Exemplifying my problem:

Tabela A (processos consultados)
ano_pcs_novo  nro_pcs_novo   pcs_antigo   outras_A 
A             a              NULL         X
B             b              NULL         X
NULL          NULL           AAA          X
NULL          NULL           BBB          X
C             c              NULL         X
A             a              NULL         X
NULL          NULL           DDD          X

Tabela B (cadastro dos processos novos)
ano_pcs_novo  nro_pcs_novo   assunto  outras_B
A             a              1        Y
B             b              2        Y       
C             c              3        Y
D             d              4        Y
E             e              5        Y
F             f              6        Y

Tabela C (cadastro dos processos antigos)
pcs_antigo   outras_C
AAA          Z
BBB          Z
CCC          Z
DDD          Z  

Tabela D (conversão de processos antigos para novos)
pcs_antigo  ano_pcs_novo  nro_pcs_novo
AAA         A             a
BBB         B             b
CCC         C             c
DDD         D             d

I wish:

Tabela A
ano_pcs_novo  nro_pcs_novo  pcs_antigo assunto outras_A outras_B outras_C
A             a             NULL       1       X         Y         Z
B             b             NULL       2       X         Y         Z
A             a             AAA        1       X         Y         Z
B             b             BBB        2       X         Y         Z
C             c             NULL       3       X         Y         Z
A             a             NULL       1       X         Y         Z
D             d             DDD        4       X         Y         Z

Comments: In Table A the consulted process fields are repeated. In Table B, C and D the fields are unique. Not all old processes have been converted.

How can I proceed? With help from the community, the closest I got involved using Select Distinct, Coalesce and Left Join, but as my main key here are two columns (ano_pcs_new and nro_pcs_new) I couldn’t go any further. I imagine I have to use concatenate, but I couldn’t perform the query properly.

Answer 1

see if it solves your problem:

SELECT DISTINCT
Coalesce(A.ano_pcs_novo,D.ano_pcs_novo) as ano_pcs_novo,
Coalesce(A.nro_pcs_novo,D.nro_pcs_novo) as nro_pcs_novo,
A.pcs_antigo,
B.assunto,
A.outras_A,
B.outras_B,
C.outras_C
FROM A

LEFT OUTER JOIN D on D.pcs_antigo = A.pcs_antigo 
                  or (D.ano_pcs_novo = A.ano_pcs_novo  
                      and D.nro_pcs_novo = A.nro_pcs_novo)
LEFT OUTER JOIN B on B.ano_pcs_novo = D.ano_pcs_novo 
                      and B.nro_pcs_novo = D.nro_pcs_novo
LEFT OUTER JOIN C on C.pcs_antigo = D.pcs_antigo 

Answer 2

Here’s an initial suggestion:

-- código #1 v4
SELECT coalesce(A.ano_pcs_novo, D.ano_pcs_novo) as ano_pcs_novo,
       coalesce(A.nro_pcs_novo, D.nro_pcs_novo) as nro_pcs_novo,
       A.pcs_antigo,
       B.assunto, A.outras_A, B.outras_B, C.outras_C
       --,case when A.pcs_antigo is null then B.outras_x else C.outras_x end as outras_x
       --,case when A.pcs_antigo is null then B.outras_y else C.outras_y end as outras_y
       --,case when A.pcs_antigo is null then B.outras_z else C.outras_z end as outras_z
  from TabelaA as A
       left join TabelaB as B on B.ano_pcs_novo = A.ano_pcs_novo
                                 and B.nro_pcs_novo = A.nro_pcs_novo
       left join TabelaC as C on C.pcs_antigo = A.pcs_antigo
       left join TabelaD as D on D.pcs_antigo = A.pcs_antigo;
go

In the code above, the columns outras_x, outras_y and outras_z refer to information in common to tables B and C and that has to come either from table B or table C (depending on whether it is a new or old process). For example, there is the column nameReferent in the process, both old and new, in table A should be placed either the contents of table B (if it is a new process) or table C (if it is an old process).

Answer 3

From the structure you presented, it seems to me that the desired output is something like this:

SELECT 
  COALESCE(A.ano_pcs_novo, D.ano_pcs_novo) AS ano_pcs_novo, 
  COALESCE(A.nro_pcs_novo, D.nro_pcs_novo) AS nro_pcs_novo, A.pcs_antigo, 
  COALESCE(B.assunto, B1.assunto), A.outras_A, 
  COALESCE(B.outras_B, B1.outras_B), 
  COALESCE(C.outras_C, C1.outras_C)
FROM A 
  LEFT JOIN C ON A.pcs_antigo = C.pcs_antigo
  LEFT JOIN D ON D.pcs_antigo = C.pcs_antigo
  LEFT JOIN B AS B1 ON D.ano_pcs_novo = B1.ano_pcs_novo AND D.nro_pcs_novo = B1.nro_pcs_novo
  LEFT JOIN B ON A.ano_pcs_novo = B.ano_pcs_novo AND A.nro_pcs_novo = B.nro_pcs_novo
  LEFT JOIN D AS D1 ON D1.ano_pcs_novo = A.ano_pcs_novo AND D1.nro_pcs_novo = A.nro_pcs_novo
  LEFT JOIN C AS C1 ON D1.pcs_antigo = C1.pcs_antigo

Here’s a DB-Fiddle to test this hypothesis. I created an extra ID on this Fiddle on A so that the result came in the correct order.