How to create a View with 2 tables with different columns in SQL?

Asked

Viewed 48 times

6

I have the following query:

CREATE
    OR REPLACE FORCE VIEW "Resumo_vendas" (
    "TIPO_REGISTRO"
    ,"VLR_DESCONTOS"
    ,"VLR_ACRESCIMOS"
    ,"VLR_PIS"
    ,"VLR_COFINS"
    ,"VLR_ICMS"
    ,"DTA_EMISSAO_NOTA"
    ) AS (
    SELECT 1 AS tipo_registro
    ,m01am AS vlr_descontos
    ,m01by AS vlr_acrescimos
    ,m01cia AS vlr_pis
    ,m01cib AS vlr_cofins
    ,m01cic AS vlr_icms
    ,NULL AS dta_emissao_nota FROM tabela1 t01
    )

UNION ALL

(
    SELECT 45 AS tipo_registro
        ,m45am AS vlr_descontos
        ,m45by AS vlr_acrescimos
        ,m45cia AS vlr_pis
        ,m45cib AS vlr_cofins
        ,m45cic AS vlr_icms
        ,M45xa AS dta_emissao_nota FROM tabela45 t45
    );

My problem is: Tabela1 does not have the columns "VLR_PIS", "VLR_COFINS" and "VLR_ICMS" and in table45 yes. Is there any way I can create this View without deleting these columns?

mysql sql database query

  • If such columns do not exist in your tabela1 What values do you want to consider in this case? It is not just replace the field name with the value to be considered? For example. 0 AS VLR_PIS in place of m01cia AS vlr_pis.

  • In reality, table 45 is a continuation of table 1, but these fields only started to have value from table 45. So in table 1 these fields didn’t even exist. I will try to use this 0 AS VLR_PIS

2 answers

5

Hello...

What matters is that in UNION, all returns are equal in quantity and type, so in columns that do not exist, put null, or a default value.

For example:

select 
    tipo 'pessoa'
    , nome 
    , data_nascimento
    , local_trabalho
from
    pessoas
union all 
select 
    tipo 'animal'
    , nome 
    , data_nacimento
    null local_trabalho
from 
    animais

Note that in this case, animals have no workplace, but to keep the number of columns we put the value null for all records.

4


You could create these columns with the artificial value NULL.

EX :

CREATE
    OR REPLACE FORCE VIEW "Resumo_vendas" (
    "TIPO_REGISTRO"
    ,"VLR_DESCONTOS"
    ,"VLR_ACRESCIMOS"
    ,"VLR_PIS"
    ,"VLR_COFINS"
    ,"VLR_ICMS"
    ,"DTA_EMISSAO_NOTA"
    ) AS (
    SELECT 1 AS tipo_registro
    ,m01am AS vlr_descontos
    ,m01by AS vlr_acrescimos
    ,NULL AS vlr_pis
    ,NULL AS vlr_cofins
    ,NULL AS vlr_icms
    ,NULL AS dta_emissao_nota FROM tabela1 t01
    )

UNION ALL

(
    SELECT 45 AS tipo_registro
        ,m45am AS vlr_descontos
        ,m45by AS vlr_acrescimos
        ,m45cia AS vlr_pis
        ,m45cib AS vlr_cofins
        ,m45cic AS vlr_icms
        ,M45xa AS dta_emissao_nota FROM tabela45 t45
    );

Browser other questions tagged mysql sql database query

You are not signed in. Login or sign up in order to post.