Crossing of Multiple Coalesce Tables

Navigation
2

Expensive,

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

Although it is a simple question, Table A has a major flaw: some old processes that have already been migrated to "new processes" do not appear in Table A (query), which forces me to have to resort for a "Table D", of converting old processes to new ones, in order to extract the much desired subject contained in Table B. In addition, my table cross key are two fields: the year and process number - since the process number may repeat itself in different years, but never in the same 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 query 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 community help, 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 could not go any further. I guess I have to use concatenation, but I was not able to query properly.

    
asked by anonymous 04.07.2017 / 19:47

3 answers

2

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
    
04.07.2017 / 20:52
1

By the structure that 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 in this Fiddle in A so that the result came in the correct order.

    
04.07.2017 / 20:57
1

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 above code, the other_x, other_y, and other_z columns refer to information in common with tables B and C and must either come from table B or table C (depending on whether it is a new or old process). For example, if the column name requester in the process, either old or new, table A must be placed, or the contents of table B (if it is a new process) or table C (if it is an old process).

    
04.07.2017 / 20:29
Select element, ignoring specific class String for KeyValuePair using LINQ